Window functions

An aggregate destroys rows. A hundred go in, four come out, and the detail is gone. That is usually what you want, and sometimes it is precisely the problem: you want each row to keep existing while also knowing something about the rows around it. Its rank among all the sales. The running total up to that day. What the previous month did.

Window functions do that. The syntax is a normal function followed by OVER (...), and the OVER clause describes which other rows this row is allowed to look at. Nothing collapses. A hundred rows go in and a hundred come out, each with an extra column.

This is the point in the course where people who have only ever used spreadsheets stop being able to fake it, and it is worth going slowly.

The OVER clause

SUM(revenue) OVER (ORDER BY date) is the running total in the starter query. Read it as: for each row, sum the revenue of every row from the start of the ordering up to and including this one. Row one holds its own revenue, row two holds the first two added together, and by the last row you have the file total.

Two things can go inside the brackets, and both are optional. PARTITION BY splits the rows into independent sets, so the calculation restarts for each one. ORDER BY decides the sequence within a set. Leave both out and SUM(revenue) OVER () puts the grand total on every row, which is exactly how you compute each row's share of the whole in a single pass.

PARTITION BY looks like GROUP BY and is not. GROUP BY reduces the rows; PARTITION BY only decides who each row compares itself against. You can use both in the same query, and once in a while you have to.

Ranking

ROW_NUMBER(), RANK() and DENSE_RANK() all number rows in the order you specify, and they differ only in how they handle ties. Given values 10, 10, 9: ROW_NUMBER gives 1, 2, 3 and picks arbitrarily between the tied rows. RANK gives 1, 1, 3. DENSE_RANK gives 1, 1, 2.

Choose deliberately. ROW_NUMBER is right when you need exactly one row per group and genuinely do not care which, such as deduplication. RANK is right for a leaderboard, where two people in joint first place should both see a 1. DENSE_RANK is right when the numbers themselves are a scale rather than a position.

The exercises here use RANK, and that is not an accident: a graded exercise using ROW_NUMBER over a column with ties would be unfair, because the answer would depend on a coin toss inside the engine.

Top N per group

Here is the question that lesson 5 could not answer: the best two sales in each region. LIMIT cannot do it, because a limit applies to the whole result and not to each region. A window function can:

SELECT region, date, revenue,
       RANK() OVER (PARTITION BY region ORDER BY revenue DESC) AS revenue_rank
FROM csv
QUALIFY revenue_rank <= 2

Rank within each region, then keep the top two of each. QUALIFY is to window functions what HAVING is to aggregates, and it exists because you cannot put a window function in a WHERE clause: windows are computed after WHERE has already run.

QUALIFY is a DuckDB and Snowflake convenience rather than standard SQL. Everywhere else you wrap the query in a subquery and filter outside it, which is the same idea with more punctuation and is covered in lesson 14. I am teaching QUALIFY anyway, because it is worth knowing the shortest form exists, and because the site's full editor runs the same engine.

LAG and LEAD

LAG(revenue) OVER (ORDER BY date) returns the revenue of the previous row. LEAD does the same for the next one. The first row's LAG is null, because there is nothing before it, and that null is correct rather than a nuisance: it is telling you the change is unknown, not zero.

This is the whole basis of period-over-period reporting. Aggregate to months, then compare each month to its LAG, and you have growth:

100.0 * (total - LAG(total) OVER (ORDER BY month)) / LAG(total) OVER (ORDER BY month)

You will write exactly that in the capstone. Both functions take an optional second argument for how many rows back to look, so LAG(total, 12) over monthly data is the year-on-year comparison.

Frames, briefly

There is a third thing that can go inside OVER, and most people meet it by accident. A frame clause such as ROWS BETWEEN 6 PRECEDING AND CURRENT ROW narrows the window to a sliding stretch of rows, which is how you compute a seven-day moving average.

The accident is this: when you write an ORDER BY inside OVER and no frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and RANGE includes every row that ties on the ordering value. With one row per day, as in this file, RANGE and ROWS agree exactly. With several rows per day they do not, and a running total will jump in steps rather than row by row. If a running total ever looks like it is double counting, this is why, and spelling out ROWS fixes it.

What to look up next

Look up "SQL window frame ROWS vs RANGE" and read one worked example with duplicate ordering values. It is a fifteen-minute read that will save you from a whole category of running total that is subtly, plausibly wrong.