Part 2 — Querying › Chapter 13

Window functions

ROW_NUMBER, RANK, DENSE_RANK and OVER(PARTITION BY …). The highest-leverage thing in modern SQL.

From the advert

“Ottima conoscenza del linguaggio SQL” — if you learn one thing from this course that changes what you can do at work, it is this chapter.

Aggregate without collapsing

Same arithmetic, different shape of answer. Keeping the rows is the whole point — it is what lets a row be compared with its neighbours.

🧒 Simple

GROUP BY puts your receipts into envelopes and you lose the receipts. A window function leaves every receipt on the table and writes a note in the corner of each one: “this is the third biggest in its shop”, “the shop's total is 412”. Nothing is thrown away.

🎓 Professional

A window function computes a value over a set of rows related to the current row without collapsing the result. OVER (PARTITION BY … ORDER BY …) defines that set. Crucially, window functions are evaluated after WHERE, GROUP BY and HAVING, and before ORDER BY — which is exactly why you cannot filter on one in WHERE and always need a wrapping query or CTE to do it.

The defining property: an aggregate that does not collapse the rows it aggregates.

SELECT                                                              -- no GROUP BY anywhere
  customer_id,                                                      -- the raw row…
  placed_at,                                                        -- …unchanged…
  total_cents,                                                      -- …and still here
  SUM(total_cents) OVER (PARTITION BY customer_id)                  -- the customer's WHOLE spend…
                   AS customer_total,                               -- …repeated on every row
  ROW_NUMBER()     OVER (PARTITION BY customer_id                   -- restart at each customer…
                         ORDER BY placed_at)                        -- …counting in date order…
                   AS nth_order,                                    -- …1, 2, 3 …
  total_cents * 100.0                                               -- 100.0 forces REAL maths
    / SUM(total_cents) OVER (PARTITION BY customer_id)              -- divided by that same total
                   AS pct_of_customer                               -- this row's share, per cent
FROM orders;                                                        -- one table, one pass
-- Same number of rows as `orders`. Every row now knows about its neighbours.

Window versus GROUP BY, and one arithmetic warning

GROUP BY replaces many rows with one. OVER (PARTITION BY …) leaves the rows alone and adds a column computed across the partition. That is the entire difference, and it is why you can show a row and its share of the total in one query.
total_cents * 100.0 / SUM(…) — the 100.0 is doing two jobs: turning the ratio into a percentage, and forcing REAL arithmetic. As integers, total_cents / SUM(…) would be 0 for every row but the largest.
If a customer's total could be zero, wrap the divisor in NULLIF(…, 0). Here it cannot, because a check constraint forbids negative totals and the partition contains at least this row.
Window functions are computed in step 5 with SELECT, so they cannot appear in WHERE — which is what the next examples work around.

The three ranking functions, and the difference that is always asked

Function	Ties get	Next value after a tie	Use for
`ROW_NUMBER()`	Different numbers (arbitrary)	n + 1	De-duplication, exactly-one-per-group
`RANK()`	The same number	Skips (1, 1, 3)	Competition ranking
`DENSE_RANK()`	The same number	No gap (1, 1, 2)	“The 3rd distinct salary”
`NTILE(n)`	—	—	Quartiles, deciles, bucketing

The three ranking functions on the same four salaries — the difference is entirely about ties.

-- salary:  5000  5000  4000  3000     ← two people tied at the top
ROW_NUMBER()  ->    1     2     3     4   -- always distinct: the tie is broken arbitrarily
RANK()        ->    1     1     3     4   -- ties share, and the next rank SKIPS
DENSE_RANK()  ->    1     1     2     3   -- ties share, and no rank is skipped

How to choose, in one line each

ROW_NUMBER() — “give me exactly one row per group”. Deduplication, top-1-per-customer, pagination keys.
RANK() — “position in a leaderboard”, where two firsts mean there is no second.
DENSE_RANK() — “the nth distinct value”. This is the right answer to “find the second highest salary”, the most-asked SQL interview question there is, and ROW_NUMBER() gets it wrong the moment there is a tie.
All three return BIGINT, and all three require an ORDER BY inside OVER — without one, “first” has no meaning.

⚠ Trap

ROW_NUMBER() breaks ties arbitrarily, and the arbitrary choice can change between runs when the plan changes. If you use it to pick “the one row to keep”, add a deterministic tie-breaker — ORDER BY updated_at DESC, id DESC. Without it, a de-duplication job can delete a different row each time it runs, which is a genuinely horrible bug to diagnose.

Top-N per group: the canonical use

Top-N per group, the canonical shape: rank inside a CTE, filter outside it.

-- The most recent order per customer.
WITH ranked AS (                                     -- the CTE exists so rn has somewhere to live
  SELECT o.*,                                        -- every column of orders…
         ROW_NUMBER() OVER (PARTITION BY customer_id -- …plus a number restarting per customer
                            ORDER BY placed_at DESC, -- newest first…
                                     id DESC) AS rn  -- …with a deterministic tie-breaker
  FROM orders o                                      -- every order is read and ranked
)                                                    -- end of the CTE
SELECT * FROM ranked WHERE rn = 1;                   -- now rn is an ordinary column, so WHERE works

The two details an interviewer is listening for

The tie-breaker. Two orders placed in the same second are ordered arbitrarily without , id DESC, so the query can return a different row on different runs. That is a genuinely horrible bug to diagnose, and adding one column prevents it.
Why the wrapper exists. WHERE rn = 1 inside the same SELECT is illegal: the window function has not been computed when WHERE runs. Say that out loud and you have explained SQL's evaluation order without being asked.
Change = 1 to <= 3 and it is top-three-per-customer. Nothing else changes, which is why this shape is worth memorising.

Why the CTE is not optional

WHERE rn = 1 cannot go in the same SELECT, because WHERE is evaluated before window functions exist (chapter 06). Every top-N-per-group query therefore has two levels. If you find yourself writing WHERE ROW_NUMBER() OVER (…) = 1 and getting a syntax error, this is why — and the error message will not tell you.

De-duplication, done properly

De-duplication as one statement: number the duplicates, delete everything that is not number one.

-- Keep the newest row per email address; delete the rest. No temp table.
WITH dupes AS (                                      -- name the duplicates before touching them
  SELECT id,                                         -- only the key is needed downstream
         ROW_NUMBER() OVER (PARTITION BY LOWER(email)  -- case-insensitive grouping
                            ORDER BY updated_at DESC,  -- newest survives…
                                     id DESC) AS rn    -- …deterministically
  FROM customers                                    -- read every row once
)                                                    -- end of the CTE
DELETE FROM customers                                -- the write, driven by the CTE above
WHERE id IN (SELECT id FROM dupes WHERE rn > 1);     -- everything that is not the survivor

Run the SELECT first. Every time.

Replace DELETE FROM customers with SELECT * FROM customers and read what comes back before you run the delete. This is the cheapest habit in the whole course.
LOWER(email) groups Anna@x.it with anna@x.it. It is also not sargable — but that is irrelevant here, because this statement is meant to read the whole table once.
Then add the constraint that should have prevented it: CREATE UNIQUE INDEX … ON customers (LOWER(email)). Cleaning up without adding the constraint means doing it again next quarter.
Wrap it in a transaction, check the row count, and only then COMMIT (chapter 21).

✔ Before you run this on production

Run the SELECT half first and read the count. Then wrap the delete in an explicit transaction and check the affected row count against that number before committing. “I would run the select first and compare the counts inside a transaction” is a sentence that makes interviewers relax.

Naming a window

Repeating OVER (PARTITION BY customer_id ORDER BY placed_at) five times is how a typo gets into one of them. Standard SQL lets you name it once:

Naming a window once, so three functions cannot drift apart.

SELECT customer_id, placed_at,                    -- the raw columns
       ROW_NUMBER()     OVER w AS nth,            -- all three share the same window…
       LAG(total_cents) OVER w AS prev_total,     -- …so a change is made in ONE place
       SUM(total_cents) OVER w AS running_total   -- running: the frame defaults to "so far"
FROM orders                                       -- one table, one pass
WINDOW w AS (PARTITION BY customer_id ORDER BY placed_at)  -- defined once, after FROM
ORDER BY customer_id, placed_at;                  -- the presentation order, unrelated to w
-- PostgreSQL, MySQL 8, SQLite, Oracle. SQL Server does not support WINDOW.

Why this is worth doing

Writing (PARTITION BY customer_id ORDER BY placed_at) five times is how a typo gets into one of them — and a window that differs by one column produces plausible, wrong numbers in a single column of the report.
SUM(…) OVER w has an ORDER BY and no frame, so the default frame applies: RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — a running total that includes every row tying with the current one. Chapter 14 is about when that default is wrong.
LAG(total_cents) returns NULL on each customer's first order, because there is no previous row in the partition. That NULL is data, not an error.

What they cost

🎓 The index that makes them fast

A window function needs its input sorted by PARTITION BY then ORDER BY. An index on exactly (customer_id, placed_at) supplies that order for free and the plan shows no sort operator. Without it, the engine sorts — and if the estimate is wrong, that sort spills to disk, which is the single most common reason a window query is slow. Look for a Sort with a spill warning before you blame the function.

Three window functions over one shared window, in a single pass.

-- Every customer's orders: numbered, as a share of their spend, and the gap in days.
SELECT customer_id, id, placed_at,                          -- the raw columns, unchanged
       ROW_NUMBER() OVER w                    AS nth_order, -- 1, 2, 3 … per customer
       ROUND(100.0 * total_cents                            -- 100.0 → REAL, so no truncation
             / SUM(total_cents)                             -- share of the customer's total…
               OVER (PARTITION BY customer_id), 1) AS pct,  -- …a DIFFERENT, unordered window
       julianday(placed_at)                                 -- this order as a day number…
         - julianday(LAG(placed_at) OVER w)                 -- …minus the previous order's…
                                              AS days_since_prev  -- …is a gap in days
FROM orders                                                 -- the only table involved
WINDOW w AS (PARTITION BY customer_id ORDER BY placed_at)   -- the shared, ordered window
ORDER BY customer_id, placed_at;                            -- read it chronologically

Two windows in one query, and a date conversion

w is ordered, so anything using it sees a running frame. The percentage needs the customer's whole total, so it uses an unordered window written out in full. Getting this wrong gives a “share” that grows down the page.
julianday(x) converts a timestamp to a REAL day number, so subtracting two of them gives a difference in days with fractions. PostgreSQL subtracts timestamps directly and returns an INTERVAL; SQL Server needs DATEDIFF(day, prev, placed_at), which counts midnights crossed rather than elapsed time.
The first order of each customer has no previous row, so LAG is NULL and the subtraction is NULL. That is the honest answer to “how long since the previous order” when there was not one.

A window function adds a column about neighbouring rows without collapsing the result.
Window functions run after WHERE/GROUP BY/HAVING and before ORDER BY. Filtering one always needs a CTE or derived table.
ROW_NUMBER gives distinct numbers, RANK skips after ties, DENSE_RANK does not.
ROW_NUMBER breaks ties arbitrarily and non-deterministically. Add a tie-breaker whenever the result decides something.
Top-N per group is ROW_NUMBER() OVER (PARTITION BY … ORDER BY …) filtered in an outer query.
De-duplicate by ranking within the duplicate key and deleting rn > 1 — after checking the count.
Name a repeated window with WINDOW w AS (…) — everywhere except SQL Server.
An index on (partition cols, order cols) removes the sort. A spilling sort is the usual cause of a slow window query.

Interview questions this chapter answers

Difference between ROW_NUMBER, RANK and DENSE_RANK?

On ties: ROW_NUMBER gives each row a distinct number arbitrarily; RANK gives ties the same number and then skips (1,1,3); DENSE_RANK gives ties the same number without skipping (1,1,2). Use ROW_NUMBER when you need exactly one row per group, DENSE_RANK for “the nth distinct value”.

Find the second-highest salary.

DENSE_RANK() OVER (ORDER BY salary DESC) filtered to 2, because the question means the second distinct salary — ROW_NUMBER would return the second row, which is the same salary again if the top two are tied. Mentioning that distinction unprompted is the point of the question.

Why can't I write WHERE ROW_NUMBER() OVER (…) = 1?

Because WHERE is evaluated before window functions are computed, so the value does not exist yet. Wrap the query in a CTE or derived table and filter in the outer level. Same reason you cannot use a SELECT alias in WHERE.