SQL Window Functions Interview Questions: The 6 Patterns That Actually Come Up

If a data-analyst interview has one SQL topic that separates candidates, it's window functions. Aggregations and joins get you through the screening round. Window functions are what the interviewer reaches for when they want to know whether you can actually think in SQL, and in my experience they show up in some form in most analyst interviews that go past the basics.

The good news: the questions are not infinite. Almost everything interviewers ask maps to six patterns. This guide walks through all six with runnable queries, and every output table you'll see is an unedited SQLite result, not a mockup. It's written for analysts preparing for SQL interview rounds; the rarer functions (NTILE, FIRST_VALUE, named windows) are deliberately out of scope so the patterns that decide interviews get full attention.

Key takeaways

• A window function computes across related rows without collapsing them. That "without collapsing" part is what interviewers are really testing.
• Six patterns cover most real questions: the ranking trio, top N per group, LAG comparisons, running totals, moving averages, and percent of total.
• You cannot filter on a window function in the same WHERE clause. Interviewers love this trap, and we trigger the actual error below.

What interviewers are really testing

Window functions aren't new, which is part of why interviewers trust them as a benchmark: they entered the SQL standard back in SQL:2003, and by now every engine you'll meet supports them, from PostgreSQL to SQLite (since version 3.25 in 2018). Some modern warehouses like Snowflake, BigQuery and DuckDB even added a QUALIFY clause that filters on window results directly, but interviews are usually conducted in standard SQL, where the subquery idiom you'll see in Pattern 2 is still the expected answer.

A window function looks at other rows relative to the current one (a "window") and computes something, while every row stays in the result. Compare that to GROUP BY, which collapses rows into one per group. Interviewers probe this exact distinction because it's where the mental model lives: if you know what grain your output has at every step, window functions are easy. If you don't, no amount of memorized syntax saves you.

Two tiny tables power every example below. Monthly revenue:

CREATE TABLE monthly_revenue (month TEXT, revenue REAL);
INSERT INTO monthly_revenue VALUES
  ('2026-01', 42000), ('2026-02', 38500), ('2026-03', 51200),
  ('2026-04', 49800), ('2026-05', 57400), ('2026-06', 53100);

And sales per rep:

CREATE TABLE rep_sales (rep TEXT, region TEXT, amount REAL);
INSERT INTO rep_sales VALUES
  ('Maya','North',9400), ('Tomer','North',8800), ('Lior','North',8800),
  ('Adi','North',7200),  ('Noa','South',9900),  ('Eitan','South',6500),
  ('Shira','South',6100);

Notice that Tomer and Lior are tied at 8800. That tie is there on purpose.

Pattern 1: the ranking trio and what ties do to it

The question sounds like "rank the reps by sales". The real question is whether you know how ROW_NUMBER, RANK, and DENSE_RANK treat a tie differently:

SELECT rep, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,
       RANK()       OVER (ORDER BY amount DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_rnk
FROM rep_sales
WHERE region = 'North';

Read Adi's row carefully. ROW_NUMBER gave the tied reps arbitrary distinct numbers. RANK gave them both 2 and then skipped to 4. DENSE_RANK gave them both 2 and continued to 3. The interview follow-up is always some version of "which one do you want for the second-highest salary problem", and the answer depends on whether tied values should count as one rank or two. If you can explain Adi's row, you own this question.

Pattern 2: top N per group, and the trap waiting inside it

This is probably the single most common window question in analyst interviews: "top 2 reps in each region". The natural instinct is to filter the rank directly:

SELECT rep, amount,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn
FROM rep_sales
WHERE rn <= 2;

SQLite rejects it on the spot:

Parse error: misuse of aliased window function rn

That error is the lesson. Window functions are evaluated after WHERE, so the filter cannot see rn yet. Interviewers set this trap deliberately, and the fix is the idiom you should be able to write half asleep: compute the window in a subquery, filter outside it.

SELECT region, rep, amount
FROM (
  SELECT region, rep, amount,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn
  FROM rep_sales
)
WHERE rn <= 2;

PARTITION BY region restarts the numbering for each region, which is the whole point: one query, an independent top 2 inside every group.

Pattern 3: month-over-month change with LAG

"How did revenue change versus the previous month" is the bread and butter of analyst work, and interviewers know it:

SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / LAG(revenue) OVER (ORDER BY month), 1) AS change_pct
FROM monthly_revenue;

Two details earn points here. First, January's NULL: there is no previous month, and a good answer says out loud how the report should handle it. Second, the ORDER BY inside OVER is what defines "previous". If your months are stored as text that doesn't sort chronologically, LAG silently compares the wrong rows. LEAD is the same function pointed forward, and retention questions ("did the user come back the next month") are this exact pattern wearing different clothes.

Pattern 4: running totals

Cumulative revenue, running headcount, growing user counts. One clause does all of them:

SELECT month,
       revenue,
       SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly_revenue;

SUM with an ORDER BY inside OVER accumulates row by row instead of producing one grand total. The interview twist is usually "now make it restart every year", and by this point you know the answer: add PARTITION BY to the OVER clause.

Pattern 5: moving averages and the frame clause

The frame clause is where candidates who memorized syntax get separated from candidates who understand it. A three-month moving average:

SELECT month,
       revenue,
       ROUND(AVG(revenue) OVER (
         ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ), 0) AS avg_3mo
FROM monthly_revenue;

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means "this row and the two before it", so the first two months average over fewer rows. Saying that unprompted is exactly the kind of edge-case awareness interviewers listen for. If you want the full mechanics of frames, including the subtle default behavior when you omit the clause, the SQLite window function documentation is the cleanest reference I know.

Pattern 6: percent of total without losing the rows

"What share of regional sales did each rep bring in" tempts people toward a join against a grouped subquery. A window aggregate does it in one pass, keeping every row:

SELECT rep, region, amount,
       ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY region), 1) AS pct_of_region
FROM rep_sales
ORDER BY region, amount DESC;

SUM OVER with PARTITION BY and no ORDER BY gives the group total on every row, so the division happens at the original grain. This one query demonstrates the entire window-function idea in miniature: aggregate context, row-level output.

How to turn the patterns into interview performance

Reading the six patterns is the easy part. Being able to produce them under a ticking clock, while explaining ties and NULLs out loud, is a different skill, and it only comes from reps. Pick the practice questions below, sketch which pattern applies before you type, solve without help, and then explain your OVER clause to an empty room. It feels silly and it works.

Common questions

What is a window function in SQL?

A function that computes a value across a set of rows related to the current row, without collapsing them the way GROUP BY does. Every row keeps its identity and gains aggregate context, like a running total or a rank. The PostgreSQL window function tutorial is a solid standard-SQL reference if you want the formal definition.

What is the difference between RANK and DENSE_RANK?

Both give tied rows the same rank. After the tie, RANK skips numbers (1, 2, 2, 4) and DENSE_RANK does not (1, 2, 2, 3). The Adi row in the first example above shows both behaviors side by side on real output.

Can you use a window function in a WHERE clause?

No. WHERE runs before window functions are evaluated, so the database rejects it; SQLite's exact error is "misuse of aliased window function". Wrap the window in a subquery or CTE and filter outside. This is one of the most common traps in live interviews.

What do PARTITION BY and ORDER BY do inside OVER?

PARTITION BY splits the rows into independent groups, like resetting the calculation per region. ORDER BY defines the sequence inside each group, which is what makes ranks, running totals, and LAG meaningful. Together they answer "compared to whom, in what order".

The bottom line

Window functions look like a syntax topic and are actually a grain topic. The six patterns here, ranking with ties, top N per group, LAG comparisons, running totals, frames, and percent of total, cover nearly every real interview question I've seen or asked. Learn to say what each OVER clause does to the shape of the output, and the syntax takes care of itself.

The practice questions below all run free in your browser with instant grading, so the ties and NULLs can surprise you here instead of in the interview.