Window Functions

Calculations across a set of related rows — like a running total or a rank — without collapsing them into a single row the way GROUP BY does.

What is it?

Aggregation answers "one summary value per group" by collapsing rows together — one row per customer, one row per category. But sometimes you want a per-row value that's still calculated using its group as context, while keeping every original row: "each order, plus that customer's running total so far," or "each product's price, plus its rank within its category."

A window function does exactly this, using OVER (...), optionally with PARTITION BY (which rows count as a group) and ORDER BY (their order within that group). Unlike GROUP BY, it doesn't merge rows — every original row stays in the output, just with an extra computed column alongside it.

Explain like I'm 10

GROUP BY is like handing a customer one combined receipt total. A window function is like handing back every individual line item, but with a running total (or a rank against everything else they bought) printed on each line — nothing gets merged away, each line just gets extra context.

Examples

Ranking within a partition

SELECT name, category, price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
FROM products;

Every product row is kept, but each one now also shows where its price ranks within its own category, highest first.

A running total

SELECT id, amount,
  SUM(amount) OVER (ORDER BY id) AS running_total
FROM payments;

Each row shows its own amount plus the cumulative sum of every row up to and including it, ordered by id — no GROUP BY, and no rows merged together.

How it works

For each output row, the database determines its "window" — the other rows that belong with it, from PARTITION BY, in the order given by ORDER BY — and computes the function (RANK, SUM, ROW_NUMBER, LAG/LEAD, and others) over that window. Critically, the original row is still returned as-is; the window function's result is just an extra column added alongside it, which is the core difference from GROUP BY, which discards the individual rows entirely in favor of one row per group.

Why does it exist?

Without window functions, something like "rank within category" or "a running total" required a self-join or a correlated subquery per row — verbose to write and slow at scale, since the database effectively has to reprocess related rows for every single output row by hand instead of computing it in one pass.

When to use it

Reach for a window function for leaderboards and rankings, running totals or moving averages, comparing a row to the previous or next one (LAG/LEAD), or "top N rows per group" queries.

When not to use it

If you genuinely want one summarized row per group, with the individual rows discarded, plain GROUP BY aggregation is simpler and says exactly that — a window function that keeps every row is the wrong tool when you don't actually need every row.

Common mistakes

  • Forgetting PARTITION BY entirely, which computes the ranking or total across the whole table instead of restarting it per group.

  • Confusing RANK (leaves gaps in the numbering after ties), DENSE_RANK (no gaps after ties), and ROW_NUMBER (always a unique sequential number, ties broken arbitrarily).

  • Trying to filter directly on a window function's result in a WHERE clause, which isn't allowed — WHERE is evaluated before window functions are computed.

Practice exercises

  1. Easy:

    Write a query using ROW_NUMBER() to number each customer's orders in the order they were placed.

  2. Medium:

    Write a query that shows each employee's salary along with their salary rank within their own department.

  3. Hard:

    Explain why WHERE price_rank = 1 fails directly after a window function that computes price_rank, and rewrite the query using a CTE so it works.

Interview questions

What's the fundamental difference between GROUP BY and a window function?

GROUP BY collapses rows into a single summary row per group; a window function keeps every original row while still computing a value using that row's group as context.

What's the difference between RANK, DENSE_RANK, and ROW_NUMBER?

RANK leaves gaps in the numbering after a tie (e.g. 1, 1, 3), DENSE_RANK doesn't leave gaps (1, 1, 2), and ROW_NUMBER always assigns a unique sequential number regardless of ties.

Why can't you filter directly on a window function's result in a WHERE clause?

WHERE is evaluated before window functions are computed, so the value doesn't exist yet at that stage — you need to compute it in a subquery or CTE and filter in the outer query instead.

What does PARTITION BY control, and what happens if you omit it entirely?

It defines which rows count as one group for the window function's calculation; omitting it treats the entire result set as a single partition, so the function runs across every row rather than restarting per group.

What does ORDER BY inside an OVER(...) clause do for a running-total-style SUM, that's different from its role in RANK?

For RANK it decides the ranking order itself; for an aggregate like SUM it instead defines the order rows accumulate in, which — combined with the default frame — is what turns a plain sum into a running total up to the current row.

Given `payments(id, amount)` = (1,100), (2,50), (3,200), what does `SELECT id, amount, SUM(amount) OVER (ORDER BY id) AS running_total FROM payments;` output, row by row?

id 1: 100, id 2: 150, id 3: 350 — with ORDER BY present and no explicit frame, each row's total accumulates every row from the start of the partition through the current row, in id order.

What is a window function's 'frame', and how does it differ from its partition?

The partition is the whole group a row belongs to; the frame is the specific subset of rows within that partition (e.g. 'from the start up to the current row') that the function actually computes over for that row — a single partition can contain many different frames as you move row to row.

What's the default frame for an aggregate window function when ORDER BY is given but no explicit ROWS/RANGE clause is specified?

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — everything from the start of the partition through the current row (and, with RANGE, through any other rows tied with it on the ORDER BY value).

Given `products(name, category, price)` = (Alpha, Electronics, 300), (Beta, Electronics, 300), (Gamma, Electronics, 100), (Delta, Furniture, 500), what does `RANK() OVER (PARTITION BY category ORDER BY price DESC)` give each row, versus DENSE_RANK and ROW_NUMBER?

Electronics: Alpha and Beta tie for the top price, so RANK gives both 1 and Gamma 3 (skipping 2); DENSE_RANK gives Alpha and Beta 1 and Gamma 2 (no gap); ROW_NUMBER gives Alpha/Beta 1 and 2 in some arbitrary tie-broken order and Gamma 3. Furniture is its own partition, so Delta gets 1 in all three.

Why do RANK and DENSE_RANK produce identical results when there are no ties in the ORDER BY column, but diverge as soon as a tie appears?

With no ties, every row moves to the next number in sequence either way; a tie is the only thing that makes RANK skip ahead (to account for the tied rows) while DENSE_RANK keeps counting without a gap.

What does LAG() return for the very first row in its partition, by default, and why?

NULL — there's no preceding row to look back to for the first row, and LAG's value when there's nothing there is NULL unless you supply your own default.

Given `stock_prices(day, price)` = (1,10), (2,12), (3,9), what does `LAG(price) OVER (ORDER BY day)` output for each row?

day 1: NULL, day 2: 10, day 3: 12 — each row shows the price from the immediately preceding day in the ordering, with nothing available for the first.

How would you make LAG() return 0 instead of NULL for the first row?

Pass a default as its third argument: LAG(price, 1, 0) — offset of 1 row back, defaulting to 0 when there is no such row.

What's the difference between LAG/LEAD and FIRST_VALUE/LAST_VALUE?

LAG/LEAD look a fixed number of rows before or after the current row; FIRST_VALUE/LAST_VALUE instead return the value from the first or last row of the current frame, regardless of how many rows away that is.

Why does LAST_VALUE often unexpectedly return the current row's own value instead of the actual last row in the partition?

With the default frame (up through the current row), the 'last' row of that frame is always the current row itself — getting the true last row of the whole partition requires explicitly widening the frame, e.g. to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

What is `ROWS BETWEEN n PRECEDING AND CURRENT ROW` used for, and how does it differ from the default frame?

It computes over a fixed-size sliding window of the last n rows plus the current one (useful for a moving average), rather than the default frame's ever-growing window from the start of the partition through the current row.

Given `sales(day, amount)` = (1,10), (2,20), (3,30), what does `SUM(amount) OVER (ORDER BY day ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)` output?

day 1: 10 (no preceding row available, so just itself), day 2: 30 (10+20), day 3: 50 (20+30) — a 2-row moving sum instead of a running total of everything so far.

What's the difference between a ROWS frame and a RANGE frame when there are duplicate ORDER BY values?

ROWS counts a literal number of physical rows regardless of ties; RANGE instead includes every row that shares the same ORDER BY value as the boundary row, so rows tied with the current row's value can be pulled into the frame together even if that's more than the fixed row count you'd expect.

How would you get the top 2 highest-priced products per category using a window function?

Compute ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn in a CTE or subquery, then filter the outer query for WHERE rn <= 2.

Why do you need a subquery or CTE to actually filter to 'top N per group', rather than adding a WHERE or LIMIT directly?

The window function's result isn't available yet at the point WHERE is evaluated, and LIMIT caps the whole result set's row count rather than restarting per group — both need the ranked value to already exist as a column, which only a subquery or CTE around the window function provides.

How could you use ROW_NUMBER() to remove exact duplicate rows from a table, keeping just one copy of each?

Partition by the columns that define a 'duplicate', number the rows within each partition with ROW_NUMBER(), and delete (or exclude, in a query) every row where that number is greater than 1.

Can you use one window function's result as an argument to another window function in the same SELECT list without a subquery?

No — window functions are computed at the same late stage of query processing, so one can't yet see another's output; nesting them requires computing the first in a subquery or CTE and referencing that column from an outer window function.

Where do window functions fit in SQL's logical order of query processing, relative to WHERE, GROUP BY/HAVING, and the final SELECT list?

After WHERE, GROUP BY, and HAVING have already run (so they operate on the already-filtered, already-grouped rows), but before the final SELECT list's ordinary expressions and before ORDER BY/DISTINCT — which is exactly why WHERE can't reference their results, and ORDER BY can.

Why can an aggregate window function like SUM() OVER(...) appear in the same query as a plain GROUP BY aggregate, and what would that combination be useful for?

They operate at different stages and can coexist — e.g. grouping to compute a per-department total while a window function alongside it compares each individual row (not just the group) against a separate window, like each employee's salary against the department average, in one query.

What's the performance concern with computing a window function's PARTITION BY/ORDER BY over a very large, unindexed column?

The database typically needs the rows sorted by the partition and order columns to compute the window efficiently; without a supporting index it may need to sort the whole result set from scratch, which gets expensive as the row count grows.

How does a window function's performance generally compare to an equivalent correlated subquery computing the same per-row value, and why?

A window function is typically computed in one pass over data already partitioned/sorted; a correlated subquery re-evaluates its inner query once per outer row, which usually does much more repeated work for the same result.

What does NTILE(4) OVER (ORDER BY score) compute, conceptually?

It divides the ordered rows into 4 roughly equal-sized buckets and labels each row with which bucket (1 through 4) it falls into — useful for quartiles or similar equal-sized groupings.

Debugging: a query with `RANK() OVER (PARTITION BY department ORDER BY salary DESC)` returns rank 1 for every single row. What's the most likely mistake?

The ORDER BY clause is missing or not actually being applied inside the OVER(...) — without an ordering, every row within a partition is treated as tied with every other, so RANK() assigns 1 to all of them.

Why is it a mistake to assume window functions always run slower than writing the same logic as a self-join?

A window function is typically computed in a single pass over data the database has already partitioned and sorted once; a self-join re-scans and re-matches the table against itself, which is usually far more expensive for the same per-row result.

What can a window function do that a plain aggregate function used without OVER() fundamentally cannot?

Keep every individual row in the output while still computing a value from its group, instead of collapsing the group down into a single summarized row.