SQL Window Functions and CTEs: Top-N, Running Totals, Recursion

Say you have a table of employees and you want each person's salary next to the average for their department. GROUP BY dept gives you the averages, but the individual rows are gone. The usual workaround is to join the table back to its own aggregate (the SQL joins topic covers that kind of self-join), which works and reads badly.

A window function does it in one pass. select name, dept, salary, avg(salary) over (partition by dept) from emp returns every employee with the average of their own department attached: 102.5 on all four Eng rows, for example. The function looks at a set of related rows (the window) for each row and writes one value beside it.

Most interview questions in this area come in a few shapes: top N per group, a running total, the previous row's value, rank with ties, removing duplicates. Each one needs a calculation across rows without losing the rows.

Everything below was run on Python's built-in sqlite3, SQLite 3.45.1 (window functions need 3.25 or later). Here is the whole data set, so you can re-run every printed result:

Eleven people, three departments, and ties on purpose (Ben and Cara, Ines and Jon, Esha and Farid). boss holds the id of the person someone reports to; Asha has none.

Every window call has two halves: the function (sum, rank, lag, any aggregate) and an OVER (...) clause that defines the window. Inside the parentheses you can put three things.

PARTITION BY dept splits the rows into independent groups. Leave it out and the whole result is one partition. ORDER BY salary desc sorts the rows inside each partition, and for ranking functions and running totals it is what gives the calculation meaning. The optional third part is the frame, which narrows the window further, and section 4 covers it.

The named WINDOW w clause lets several columns share one definition. PostgreSQL and SQLite both accept it.

Order of evaluation explains the biggest restriction. Window functions run after FROM, WHERE, GROUP BY and HAVING, so they can only appear in the SELECT list and ORDER BY. Trying where rank() over (order by salary) = 1 in SQLite fails with misuse of window function rank(). The fix is to compute the rank in a subquery or CTE and filter in the outer query, which is the pattern in section 6.

The three ranking functions differ only in how they treat equal values. All three were run over partition by dept order by salary desc:

Ben and Cara tie at 100. row_number still hands out 2 and 3, so one of them is arbitrarily second. rank gives both a 2, then skips to 4 for Dev, because two rows share place 2 and nobody holds 3. dense_rank gives both a 2 and Dev gets 3.

Ties also make row_number non-deterministic: which of Ben or Cara gets 2 can change between runs or engines unless the ORDER BY ends in a unique column such as id. And "second highest salary" has two readings. The second distinct value is dense_rank = 2, which returned Ben and Cara for Eng, Kira for HR and Gita for Sales. The person in second place is rank = 2, which exists only in Eng here (Ben and Cara); HR and Sales have no rank 2 because two people share first.

row_number is also the standard way to remove duplicates. A small users table (ids 1 to 6) shows it with repeated emails: a@x.com appears three times and b@x.com twice.

Keep the rows where rn = 1 and you get ids 1, 2 and 4, the earliest row per email. Deleting with delete from users where id in (select id from (... ) where rn > 1) left the same three rows. The order by id inside the window decides which copy survives, so say which one you want.

A running total is an aggregate with an ORDER BY inside OVER. It looks simple, and it has one trap.

The frame is the slice of the partition the function actually sees for the current row. If you write ORDER BY and no frame, the default is from the start of the partition through the current row plus its peers, meaning rows that tie with it on the ORDER BY value. This is the RANGE behaviour. Without ORDER BY the frame is the whole partition. That is how the PostgreSQL docs describe it, and SQLite gave the same results here.

The orders table has two rows in March (150 and 50). Comparing the default against an explicit ROWS frame:

On the first March row the default frame already includes the second March row, so it jumped to 450, while ROWS gave 400. The diagram shows the same mechanism on the employee data: for Ben, the default frame reaches Cara because they tie at 100.

The last_value surprise has the same cause. On the Eng department, last_value(name) over (partition by dept order by salary desc) returned Asha, Cara, Cara, Dev for the four rows (Ben and Cara are peers, so Ben's frame already ends at Cara), not Dev every time. Adding rows between unbounded preceding and unbounded following returned Dev on all four rows.

lag(value, offset, default) returns the value from offset rows before the current row, using the window's ordering; lead looks forward. The offset defaults to 1, and the default value for rows with nothing before them is NULL. These two functions replace the old self-join on the previous month (see the SQL joins topic), which breaks as soon as a month is missing.

A month-over-month change on the orders data, where the aggregate is computed first and the window runs over the grouped result:

The first month has no previous row, so its change is NULL, shown as None by Python. Passing a third argument, lag(x, 1, 0), would turn that into 0 when that is what you want.

Writing sum(amount) inside lag(...) is legal because window functions run after GROUP BY, so the grouped sums are the rows being windowed. lag is positional: if January were missing, February would compare against whatever row came before it, so check for gaps when consecutive periods matter.

A common table expression is a named query attached to one statement with WITH. For the top-N-per-group task it is the cleanest way around the no-window-in-WHERE rule:

On the sample data this returned nine rows: Asha, Ben and Cara for Eng (Ben and Cara tie), Ines, Jon and Kira for HR, and Esha, Farid and Gita for Sales. Hugo, fourth in HR, is cut. A subquery in FROM could do the same job; the CTE just lets you name each step and read the query top to bottom.

WITH RECURSIVE handles trees and graphs. It has a non-recursive starting query, then UNION ALL, then a recursive query that refers to the CTE itself. The database runs the start, then repeatedly runs the recursive part on the rows just produced, until a round returns nothing. An org chart from the boss column:

Asha came back at depth 0; Ben, Cara, Esha and Ines at depth 1; Dev, Farid, Gita, Jon and Kira at depth 2; and Hugo, who reports to Kira, at depth 3.

Cyclic data is the trap. On a copy of the table where Asha reports to Hugo, the loop is closed. With that edit nobody has a NULL boss, so the query above would return nothing; to see the loop the recursion has to start from where id = 1. Started there, it never stops without a guard. Adding where chain.depth < 6 to the recursive part makes it stop: 21 rows came back, depth 0 through 6, with every person repeating as the loop went round (Asha showed up again at depth 4). The cap ends the recursion but does not remove the repeats, so a real query should also carry a path or visited-id column and skip ids already seen.

UNION instead of UNION ALL can also end a cycle, but only when the recursive rows hold nothing that changes every round. In the query above every row carries depth, which grows, so no row is ever a duplicate and UNION would not help. Selecting just id with UNION, again starting from id = 1 on the same looped table, returned 11 rows and stopped.

Performance is engine-specific. In PostgreSQL 12 and later, a side-effect-free, non-recursive CTE referenced once is inlined into the main query, and one referenced several times is computed once and stored, with MATERIALIZED and NOT MATERIALIZED to override. That was not run on PostgreSQL; it comes from its documentation. Other engines and older PostgreSQL versions behave differently, so a CTE is not automatically faster or slower than a subquery.

The other classic way to get the top 2 per group is a correlated subquery: keep a row if fewer than 2 rows in its group have a higher salary. It is correct, and it runs a separate count for every row.

The test table has 100,000 rows in SQLite, 1,000 departments, random salaries and an index on (dept, salary). Each query was timed with time.perf_counter, best of three, and the whole script was repeated three times on a sandbox with unknown other load:

Both queries returned the same 2,000 rows, and the window version was about 2.5 times faster. Earlier runs on a busier machine gave much slower times for both and a wider gap, roughly 3 to 5 times, so absolute numbers move a lot and only the ordering held. This is one engine, one machine and one data shape; treat it as an illustration, not a benchmark. Without the index, the correlated query was too slow to time here.

explain query plan shows the difference. The correlated query reads SCAN b, then for every row runs SEARCH x USING COVERING INDEX ix (dept=? AND salary>?), a fresh index lookup and count each time. The window query reads SCAN big USING COVERING INDEX ix once. The index supplies the dept order, but the window sorts by salary desc while the index is ascending, so the plan still contains USE TEMP B-TREE FOR RIGHT PART OF ORDER BY and runs the ranking inside a co-routine. The index helps the window version a little (0.12 s without it, 0.09 s with it) and the correlated one far more. The database indexing topic and the query optimization and EXPLAIN topic cover how to read plans like these.

Q: What is the difference between row_number, rank and dense_rank?
A: All three number rows inside a partition by ORDER BY. row_number never repeats, even on ties. rank gives tied rows the same number and then skips (1, 2, 2, 4). dense_rank gives ties the same number without skipping (1, 2, 2, 3).

Q: How do you get the top 2 earners in each department?
A: Compute dense_rank() (or rank, or row_number if exactly two rows per group are wanted) over partition by dept order by salary desc in a CTE or subquery, then filter rank <= 2 in the outer query. Say which tie behaviour you chose.

Q: Why can't I use a window function in WHERE?
A: WHERE is evaluated before window functions run, so the value does not exist yet. Window functions are allowed only in SELECT and ORDER BY. Wrap the query in a CTE or subquery and filter outside.

Q: My running total jumps on duplicate dates. Why?
A: With ORDER BY and no frame, the default frame includes the current row's peers, rows with an equal ORDER BY value. Use rows between unbounded preceding and current row, or add a unique tiebreaker column to the ORDER BY.

Q: Is a CTE faster than a subquery?
A: Not by itself. It mainly improves readability and enables recursion. Whether it is inlined or computed once depends on the engine; in PostgreSQL 12 and later a single-use CTE is inlined by default and a multi-use one is materialized unless you say otherwise.

Q: How do you find the nth highest salary per department?
A: Compute dense_rank() over (partition by dept order by salary desc) in a CTE and filter dr = n. dense_rank gives the nth distinct salary; rank would skip numbers after ties and might return no row for n.