Window Functions: ROW_NUMBER, RANK and Running Totals

GROUP BY is powerful, but it has a cost: it collapses many rows into one. Sometimes you want a calculation over a group of rows and still keep every row. For example: show every product with its rank inside its category. Or show every order with a running total. Window functions do this, and they are a favourite topic in analyst interviews.

The shape: OVER

A window function is any function followed by OVER (...). The brackets say which rows to look at (PARTITION BY) and in what order (ORDER BY). The rows are not collapsed, so each row keeps its own line, and the function adds one extra column.

function() OVER (PARTITION BY group_column ORDER BY sort_column)

PARTITION BY is like GROUP BY for the window: the calculation restarts for each group. If you leave it out, the whole result is one group.

ROW_NUMBER: number the rows

Number every customer's orders by date, starting at 1 for each customer.

SELECT id, customer_id, order_date,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) AS nth
FROM orders
ORDER BY customer_id, order_date;

id | customer_id | order_date | nth
1  | 1           | 2024-06-02 | 1
2  | 1           | 2024-06-20 | 2
9  | 1           | 2024-08-02 | 3
3  | 2           | 2024-06-21 | 1
7  | 2           | 2024-07-15 | 2
4  | 3           | 2024-07-01 | 1
...

Customer 1 has orders numbered 1, 2, 3 and customer 2 starts again at 1. Every order is still there, which is the difference from GROUP BY.

The latest row for each customer

A very common request is “the most recent order for each customer”. Number the rows from newest to oldest, then keep number 1. Because you cannot filter on a window function in the same WHERE, put the numbering in a subquery or CTE first.

SELECT customer_id, id, order_date
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
)
WHERE rn = 1
ORDER BY customer_id;

customer_id | id | order_date
1           | 9  | 2024-08-02
2           | 7  | 2024-07-15
3           | 4  | 2024-07-01
4           | 5  | 2024-07-04
5           | 6  | 2024-07-09
6           | 8  | 2024-07-18
7           | 10 | 2024-08-05

This “latest per group” pattern, also written “top 1 per group”, is one of the most useful patterns in SQL. Change rn = 1 to rn <= 3 to get the top three per group.

RANK and DENSE_RANK

RANK gives the same number to ties, then skips. DENSE_RANK gives the same number to ties and does not skip. Compare them with an example where two prices tie (the shop data has no ties, so here are the idea and the shape).

price | ROW_NUMBER | RANK | DENSE_RANK
900   | 1          | 1    | 1
800   | 2          | 2    | 2
800   | 3          | 2    | 2
700   | 4          | 4    | 3

ROW_NUMBER never repeats. RANK repeats for ties, then jumps (1, 2, 2, 4). DENSE_RANK repeats for ties and carries on without a gap (1, 2, 2, 3). Pick the one that matches the question you were asked: “top 3 products” with ties is a question you should clarify first.

Here is RANK in the shop data. It ranks products by price inside each category.

SELECT name, category, price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS rnk
FROM products
ORDER BY category, rnk;

name         | category   | price | rnk
Backpack     | Bags       | 1400  | 1
Desk lamp    | Home       | 650   | 1
Water bottle | Home       | 300   | 2
Laptop stand | Office     | 1800  | 1
Mouse        | Office     | 700   | 2
Notebook     | Stationery | 120   | 1
Pen pack     | Stationery | 80    | 2
Sticky notes | Stationery | 60    | 3

Keep only rnk = 1 and you have the dearest product in each category, using the same subquery pattern as before.

Running totals with SUM OVER

Any aggregate can be a window function. With ORDER BY inside OVER, SUM becomes a running total, adding each row to the ones before it. Here a small example with the order ids, to keep the numbers easy to check by eye.

SELECT order_date, id,
  SUM(id) OVER (ORDER BY order_date, id) AS running
FROM orders
WHERE id <= 4;

order_date | id | running
2024-06-02 | 1  | 1
2024-06-20 | 2  | 3
2024-06-21 | 3  | 6
2024-07-01 | 4  | 10

A real report would use order value instead of id, for a running sales total by day. With no ORDER BY inside OVER, the function covers the whole group, so COUNT(*) OVER () puts the same total on every row. That is useful for percentages: each row's value divided by the total, without a separate query.

LAG and LEAD: look at the previous or next row

SELECT name, price,
  LAG(price) OVER (ORDER BY id) AS previous_price
FROM products
LIMIT 3;

name      | price | previous_price
Notebook  | 120   | NULL
Pen pack  | 80    | 120
Desk lamp | 650   | 80

LAG reads a value from the row before; LEAD from the row after. They are used for “change compared with last month”, and the first row has NULL because nothing comes before it.

Window functions versus GROUP BY

• GROUP BY returns one row for each group. Detail rows disappear.
• A window function keeps every row and adds a calculated column.
• Use GROUP BY for summaries. Use window functions for ranks, running totals, comparisons with the previous row and “top N per group”.

Common mistakes

• Trying to filter on a window function in WHERE. Wrap it in a subquery or CTE and filter outside.
• Forgetting the ORDER BY inside OVER for ROW_NUMBER, RANK and running totals, which makes the numbering meaningless.
• Using ROW_NUMBER when ties matter. Decide whether ties share a rank.

Your task

1. Show every product with its position by price across all products (1 is the dearest), using RANK() OVER (ORDER BY price DESC).
2. For each customer, show only their first order using ROW_NUMBER and a subquery. Customer 1's first order is 1.
3. Write the query that shows the cheapest product in each category.
4. Show each order with the count of all orders on the same row using COUNT(*) OVER ().

How to check it worked

• The dearest product has rank 1 (Laptop stand) and the cheapest has rank 8.
• The first-order query returns 7 rows, one for each customer who has ordered.
• Every row in the last query shows 10.