SQL window functions

The problem it solves

Say you want each treatment listed with what it cost, alongside what the whole year cost, so you can see the share. With GROUP BY you cannot: grouping to get the total destroys the individual rows, and not grouping means no total. The usual workaround is to run the query twice, or to join the table to a summary of itself.

OVER () is the shortcut. It says: work this aggregate out across a set of rows, but give me the answer on every row rather than instead of them.

SELECT object_title, cost,
       SUM(cost) OVER () AS grand_total
FROM treatments
ORDER BY cost DESC;

PARTITION BY — one window per group

An empty OVER () means the window is the whole result. PARTITION BY narrows it: the aggregate restarts for each distinct value, so every row gets its own group's total rather than the grand total.

This is GROUP BY's job description with the rows left intact, and it is what makes percentage-of-group a single query. Note the Overmantel Mirror at 44.1% of the East wing — you have the detail row and the context in the same result, which no GROUP BY can give you.

SELECT wing, object_title, cost,
       SUM(cost) OVER (PARTITION BY wing) AS wing_total,
       ROUND(100.0 * cost / SUM(cost) OVER (PARTITION BY wing), 1) AS pct_of_wing
FROM treatments
ORDER BY wing, cost DESC;

ROW_NUMBER, RANK and DENSE_RANK — and why they differ

All three number rows in an order you choose. They only disagree when there are ties, which is exactly why most examples of them are useless: on a column with no repeated values they return three identical columns.

This table has ties on purpose. Three treatments took 6 hours. Run the query and read those three rows across.

ROW_NUMBER gives them 9, 10 and 11 — it always produces consecutive integers, so it has to break the tie somehow, and *how* is undefined. Run it twice and those three could swap. RANK gives all three 9 and then jumps to 12, leaving a gap the size of the tie. DENSE_RANK gives all three 6 and continues at 7, no gaps.

Pick by what you are doing. ROW_NUMBER when you need exactly one row per group and do not care which. RANK for a leaderboard, where two silver medals mean no bronze. DENSE_RANK when the numbers are labels for distinct levels rather than positions.

SELECT object_title, hours,
       ROW_NUMBER() OVER (ORDER BY hours DESC) AS row_number,
       RANK()       OVER (ORDER BY hours DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY hours DESC) AS dense_rank
FROM treatments
ORDER BY hours DESC, object_title;

Top N per group — the pattern worth memorising

"The most expensive treatment in each wing" is a question GROUP BY cannot answer. MAX(cost) gives you the number but not which object it belongs to, and adding object_title to the SELECT list breaks the query.

ROW_NUMBER partitioned by the group and ordered by what you are ranking on, then filtered to 1, answers it directly. Change the 1 to <= 3 and you have the top three per wing.

The subquery is required, and this is the one rule about window functions that catches everyone: **you cannot use a window function in WHERE**. WHERE runs before the window is computed, so the column does not exist yet. Compute it in an inner query, filter in the outer one.

SELECT wing, object_title, cost
FROM (
  SELECT wing, object_title, cost,
         ROW_NUMBER() OVER (PARTITION BY wing ORDER BY cost DESC) AS rn
  FROM treatments
) ranked
WHERE rn = 1
ORDER BY wing;

Running totals

Add ORDER BY inside the OVER clause and the window stops being the whole partition. It becomes everything up to and including the current row, in that order — which turns SUM into a running total for free.

This is the second thing OVER's ORDER BY does, and it surprises people: outside the parentheses ORDER BY sorts the output, inside them it defines what the window can see. Two different jobs, same keyword.

SELECT started_on, object_title, cost,
       SUM(cost) OVER (ORDER BY started_on) AS running_total
FROM treatments
ORDER BY started_on;

LAG and LEAD — comparing a row with its neighbours

LAG reaches back to the previous row in the window, LEAD reaches forward. Subtract and you have change over time in one expression, without joining the table to itself on a date offset.

The first row's LAG is NULL — there is nothing before it — so the change column is NULL too. That is correct and it is also the thing that quietly breaks a chart. COALESCE(LAG(cost) OVER (...), 0) if you want a zero there, but decide it rather than discovering it.

SELECT started_on, cost,
       LAG(cost) OVER (ORDER BY started_on) AS previous,
       cost - LAG(cost) OVER (ORDER BY started_on) AS change
FROM treatments
ORDER BY started_on;

Where window functions run, and why it matters

The clause order is FROM, WHERE, GROUP BY, HAVING, **window functions**, SELECT, ORDER BY. Almost every error message you will get from this feature follows from that one line.

It is why WHERE cannot see a window column — WHERE has already run. It is why HAVING cannot either. It is why a window function can sit happily in ORDER BY, which runs later. And it is why a window function operates on rows that survived WHERE: filter first, and the running total runs over what is left.

You can also aggregate and window in the same query, because windows run after grouping. SUM(cost) OVER () in a grouped query gives you the total of the group totals — occasionally exactly what you want, and confusing the first time you see it.