Window functions: RANK and LAG

Game: chapters VIII and XI

An aggregate squeezes many rows into one. A window function looks at many rows too, but keeps every row and writes its answer beside it.

The idea of a window

Suppose you want each customer's orders numbered first, second, third, in date order. GROUP BY cannot do it, because it collapses each customer into a single row. A window function computes a value for every row using a set of related rows, its window, and leaves the rows as they are.

The window is described by OVER (...) right after the function. Inside the parentheses, PARTITION BY splits the rows into independent groups (one per customer, say), and ORDER BY sets the order inside each group. Both parts are optional: an empty OVER () means "all rows, in no particular order". Window functions need SQLite 3.25 (2018) or later, which every current SQLite tool and Python include. The examples use the bakery database from the bakery database page.

Numbering and ranking

Three functions give positions. ROW_NUMBER() numbers the rows 1, 2, 3 without gaps or ties. RANK() gives tied rows the same rank and then skips: 1, 1, 3. DENSE_RANK() gives ties the same rank without skipping: 1, 1, 2. They differ only when the ORDER BY value ties, and here it does at the top: over the four weeks the bakery sold 58 croissants and 58 cinnamon rolls, so the pastries have two best sellers. RANK and DENSE_RANK say so, both 1; ROW_NUMBER must still put one first, and which one is not defined unless you add a second sort key, as the example does with p.name (only in the ROW_NUMBER window, so that RANK and DENSE_RANK still show the tie). Ask "the best seller" with ROW_NUMBER() = 1 and you get one of the two, by the alphabet; ask with RANK() = 1 and you get both.

The window here runs over a grouped result: GROUP BY first makes one row per pastry with its SUM(oi.quantity), then the window functions order those rows, which is why the OVER can sort by the aggregate. The example keeps only the pastries with WHERE, so one window covers them all. To rank every category separately in one query, write OVER (PARTITION BY p.category ORDER BY SUM(oi.quantity) DESC) and group by the category too: the numbering then starts again at 1 in each category.

SELECT p.name, SUM(oi.quantity) AS sold,
       ROW_NUMBER() OVER (ORDER BY SUM(oi.quantity) DESC, p.name) AS row_number,
       RANK()       OVER (ORDER BY SUM(oi.quantity) DESC) AS rank,
       DENSE_RANK() OVER (ORDER BY SUM(oi.quantity) DESC) AS dense_rank
FROM order_item AS oi
JOIN product AS p ON p.id = oi.product_id
WHERE p.category = 'pastry'
GROUP BY p.id
ORDER BY sold DESC, p.name;

A window function cannot appear in WHERE, because windows are computed after the rows have been filtered (see Order of execution). To keep, say, only the first order of each customer (here the eight customers the bakery started with, ids 1 to 8), compute the number in a CTE or a subquery, then filter on it in the outer query. This "number, then filter" pattern answers every question of the form "the first", "the latest" or "the top two" per group.

WITH numbered AS (
  SELECT customer_id, day, time,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY day, time) AS n
  FROM orders
)
SELECT customer_id, day, time
FROM numbered
WHERE n = 1 AND customer_id <= 8
ORDER BY customer_id;

Seven rows for eight customers: Hugo Silva (id 8) never ordered, so he has no row to number.

Looking at the previous row: LAG

LAG(column) returns the value of that column in the previous row of the window; LEAD(column) returns the next one. Put them side by side with the current value and you can measure the change between consecutive rows: the gap between two visits, the growth from one month to the next. The first row of each partition has no previous row, so LAG returns NULL there. An optional second argument looks further back, and a third gives a default instead of NULL.

SELECT customer_id, day, time,
       LAG(day) OVER (PARTITION BY customer_id ORDER BY day, time) AS previous_visit
FROM orders
WHERE customer_id IN (17, 20)
ORDER BY customer_id, day, time;

Both customers came twice on one day, and for the second visit of that day the previous visit is the same day. That is what the window's ORDER BY day, time is for: with day alone the two orders of 9 March (or of 11 March) would tie, and which of them counts as "previous" would be left to chance.

Dates written as text cannot be subtracted directly. Every order here falls in March 2026, so the day of the month is enough: substr cuts it out of the text and CAST(... AS INTEGER) turns it into a number, and the difference between two of them is a gap in days. (SQLite's julianday() would handle any two dates, but PostgreSQL has no such function: there you would turn the text into a real date with CAST(day AS DATE) and subtract.) Wrapping the window in a CTE then lets you filter or sort on that gap, for instance to find the longest silence between two visits of the same customer.

WITH visits AS (
  SELECT customer_id, day,
         LAG(day) OVER (PARTITION BY customer_id ORDER BY day, time) AS previous_visit
  FROM orders
)
SELECT customer_id, previous_visit, day,
       CAST(substr(day, 9, 2) AS INTEGER) - CAST(substr(previous_visit, 9, 2) AS INTEGER) AS gap_days
FROM visits
WHERE previous_visit IS NOT NULL
ORDER BY gap_days DESC, customer_id
LIMIT 4;

The LIMIT is 4, not 3, because three customers tie at 15 days: with LIMIT 3 the cut would fall inside the tie, and only the second sort key, customer_id, would decide who is left out. Look at the rows just past a cut before you trust it. The same technique works with times: turning each HH:MM value into minutes, hours times sixty plus minutes, cut out and cast the same way, gives the minutes between two times on the same day.

Partitions matter

Forgetting PARTITION BY in a LAG is the classic error: the previous row then belongs to another customer, and the gap you compute is meaningless. Ask yourself, before you write the window, "previous within what?" The answer goes into PARTITION BY, and "previous in which order?" goes into ORDER BY. If the order can tie (two orders on the same day), add a second key so that "previous" is well defined.

Common mistakes

Using a window function in WHERE or HAVING instead of filtering in an outer query. Omitting PARTITION BY, so that the window runs across groups. Omitting the ORDER BY inside OVER, which makes LAG and ROW_NUMBER depend on an arbitrary order. Confusing the ORDER BY inside the window, which drives the computation, with the one at the end of the query, which only sorts the output. Choosing ROW_NUMBER when ties should share a place, or RANK when exactly one row per group is wanted.

Try it: Best seller of each category and Back again, on the exercises page.

In the game: Chapter VIII, The First Order Before Dawn, practises RANK() OVER with a CTE; Chapter XI, The Silence, practises LAG() OVER.