Loading...
Window functions look intimidating and are secretly simple: keep every row, and add one calculated column beside it. Switch between the functions below and watch the board change.
Conor Robertson
June 22, 2026 · 10 min read
The questions that come up most once people start writing window functions for real.
QueryCase teaches SQL through mystery cases on real datasets. Music, sports, film, and more. Your first cases are completely free.
Start your first caseFree to start. No credit card required.
Window functions are the wall most people hit after they are comfortable with SELECT, WHERE and JOIN. The syntax looks alien, the explanations talk about "frames" and "partitions", and it all feels like a different language. It is not.
Above is a leaderboard of five detectives, ranked by the XP they have earned closing cases. The explorer is the whole idea: pick a function, steer it with PARTITION BY and ORDER BY, and watch it add one column without throwing a single row away. Tap any number in that new column to see exactly which rows it was built from. The board still shows all five detectives, every time.
That last part is the secret. Everything you already know about SUM and COUNT still applies. A window function just keeps the rows.
The mental model
A normal aggregate collapses rows: GROUP BY crushes each group down to a single summary row and the individual names are gone. A window function does the same maths but keeps every row, writing the answer in a new column beside each one. That is the entire difference. Running totals, rankings, "this row versus the last": all of it is just an aggregate that refused to collapse.
Here is the same calculation said both ways. On the left, GROUP BY squad adds up each squad and hands back one row per squad: the names are gone. On the right, the identical sum wrapped in SUM(xp) OVER (PARTITION BY squad) writes each squad total beside every member, with all five detectives still on the board.
One row per squad. The five names are gone.
Same totals. GROUP BY returns one row per squad. The window writes each squad total beside every member, all five rows kept.
Same totals, different row counts. That gap, two rows versus five, is the entire idea. Once you see it, window functions stop being mysterious.
Picture standing on one row and looking out at the rows around it. That view, the slice of rows a single row is allowed to see, is the window. For a running total it is every row from the top down to you. For LAG it is the one row directly above. For a rank it is everyone you are competing against.
The calculation never leaves that window. Change the window with PARTITION BY and ORDER BY and you change what each row can see, which changes the answer. It is why the same SUM can be a grand total, a running total, or a per-group total depending only on the brackets. Tap a number back in the explorer and the window it used lights up in the table beside it.
Every window function reads the same way once you can name the three parts. Learn them on one expression and you can read any of them.
SUM, COUNT, AVG, RANK and friends all work here. Nothing new yet.PARTITION BY groups the rows (here, by squad) and restarts the calculation for each group. ORDER BY sequences them (here, highest XP first) so a running total or rank knows what "above" means. Use either, both, or neither.The calculation on the left is old news. The word OVER is the entire trick. The brackets on the right are where you steer it with PARTITION BY and ORDER BY: which groups, and in what order. Both are optional, which is why a window can be as short as OVER ().
There are only a handful worth memorising, and they split cleanly into ranking functions, running aggregates, and the row-to-row pair.
The SUM() OVER card stands in for a whole family. AVG, COUNT, MIN and MAX all take an OVER clause and behave identically: the rows stay, and the aggregate is written beside each one. Anything you would reach for after GROUP BY works as a window function too, just without the collapse. The explorer uses SUM to keep things simple, but every one of them follows the same rules you just watched.
The three ranking functions trip people up the most, because they look interchangeable until a tie shows up. So let's put them side by side.
They behave identically until two rows tie, and then they part ways on the very next row. Flip between them and watch what happens to Sterling in 4th.
After two detectives share 2nd, RANK jumps to 4, skipping 3. The gap counts how many tied above.
ROW_NUMBER() is the third sibling: it refuses to tie at all, handing out a unique number even when the scores are identical. Reach for it when you need exactly one row per group, like the single most recent order per customer.
By default a window runs straight down the whole table. PARTITION BY slices it into groups and starts the calculation over for each slice.
Go back to the explorer and switch PARTITION BY from none to squad. Watch ROW_NUMBER restart at 1 for Alpha, then again for Bravo. It is GROUP BY for windows: the same grouping, but every row is kept.
While you are there, flip ORDER BY to xp ASC. The numbers change, but the rows stay exactly where they were. That is proof the ORDER BY inside OVER steers the calculation, not how the rows are listed.
The pattern you'll use constantly: top N per group
Combine PARTITION BY with ROW_NUMBER() and you get the answer to a question that comes up everywhere: the top item in each category. ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) numbers each category from 1, so filtering to where that equals 1 gives you the best seller in every category at once. Most recent order per customer, highest score per team, latest status per ticket: same shape every time.
Here is the question almost every window function tutorial is quietly building towards: who are the top two earners in each department? It needs a ranking, restarted per group, then a filter. That is PARTITION BY, ORDER BY, and one twist.
WITH ranked AS (
SELECTname, department, salary, ROW_NUMBER() OVER ( PARTITION BY department ORDER BY salary DESC ) AS dept_rank FROM employees ) SELECT name, department, salary FROM ranked WHERE dept_rank <= 2;
Read it top to bottom. ROW_NUMBER() numbers each department's salaries from highest down, PARTITION BY department restarts that count for every department, and dept_rank <= 2 keeps the top two of each. Swap department for squad and salary for xp and it is the exact query the explorer has been running.
The twist is the WITH ... AS (...) wrapper, a CTE. You cannot put WHERE dept_rank <= 2 in the same query as the window function, because the rank has not been worked out yet at the point WHERE runs. So you compute the rank in one step, then filter it in the next. This wrap-then-filter shape is behind almost every top-N-per-group query you will ever write.
Once the idea clicks, the only thing left is matching the question to the function. Read this one backwards: find the result you are after on the left, and the function is on the right.
| You want | Reach for | In the wild |
|---|---|---|
| A unique number for every row, in order | ROW_NUMBER() | The single most recent order per customer |
| Standings where tied rows share a place | RANK() | A leaderboard with a joint 2nd |
| A ranking with no gaps after ties | DENSE_RANK() | The top 3 distinct prices |
| A running total down the rows | SUM(x) OVER (ORDER BY ...) | Cumulative revenue by date |
| Each row as a share of the whole | x / SUM(x) OVER () | Percent of total sales |
| This row compared to the previous one | LAG(x) | Month-over-month change |
| This row compared to the next one | LEAD(x) | Days until the next visit |
| The same calculation, restarted per group | add PARTITION BY ... | A running total per customer |
| Rows split into N equal buckets | NTILE(n) | Quartiles, deciles, percentiles |
You need the top 3 distinct scores on the board, with no gaps in the numbering even when two detectives tie. Which function do you reach for?
The ORDER BY inside the brackets controls the calculation, deciding what counts as "above" for a running total or a rank. The ORDER BY at the end of the query only controls how the final rows are displayed. They are different jobs, and you often need both.
SUM(xp) OVER () with empty brackets gives every row the same grand total, which is occasionally exactly what you want, since xp / SUM(xp) OVER () is how you get each row's share of the total. To make it climb instead, you need SUM(xp) OVER (ORDER BY xp DESC). The order is what turns a single number into a progression. Set the explorer to SUM and flip ORDER BY between none and xp DESC to watch the grand total become a running total.
Give two rows the same value and nothing else to go on, and the database is free to number them in either order, and it may pick differently each run. When the tiebreak matters, add a second column to the ORDER BY, like ORDER BY xp DESC, name, so the result is stable every time.
The fastest way to learn this is to write it, not read it. Reading about window functions gets you to "I follow that." Writing them, watching the extra column appear, and filtering on it gets you to "I reach for OVER without thinking." The gap is just reps: pick a question, add the window, and check the column against what you expected.