SQL for Analysis · 15 min · 180 XP
Window functions
Rank, number and run totals across rows without collapsing them.
GROUP BY gives one row per group. Sometimes you need every row and a figure about its group beside it: each order numbered within its customer's orders, each month with the running total so far, each customer's latest order. That's a window function: an aggregate followed by OVER (...), which doesn't collapse the rows.
Every order, by customer15 rows
| customer | order_id | order_date | status |
|---|---|---|---|
| Aisha Khan | 106 | 2026-02-06 | paid |
| Aisha Khan | 111 | 2026-03-08 | refunded |
| Emma Novak | 112 | 2026-03-12 | paid |
| Liam Walsh | 102 | 2026-01-09 | paid |
| Liam Walsh | 107 | 2026-02-11 | pending |
| Lucas Silva | 109 | 2026-02-25 | paid |
| Lucas Silva | 115 | 2026-03-29 | pending |
| Maja Berg | 101 | 2026-01-04 | paid |
| Maja Berg | 103 | 2026-01-15 | refunded |
| Maja Berg | 108 | 2026-02-19 | paid |
| Maja Berg | 114 | 2026-03-24 | paid |
| Noah Keller | 105 | 2026-02-02 | paid |
| Noah Keller | 113 | 2026-03-18 | paid |
| Sofia Rossi | 104 | 2026-01-22 | paid |
| Sofia Rossi | 110 | 2026-03-03 | paid |
The query
SELECT c.name AS customer, o.id AS order_id, o.order_date, o.status FROM orders AS o JOIN customers AS c ON c.id = o.customer_id ORDER BY c.name, o.order_date;
Numbering within groups
SELECT id, customer_id, order_date,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date
) AS nth_order
FROM orders
WHERE status = 'paid';PARTITION BY splits the rows into groups (here, one per customer), and the numbering starts again in each group. The ORDER BY inside OVER sets the order within a group. Leave out PARTITION BY and the whole result is one window.
Running totals
A running total is SUM(...) OVER (ORDER BY ...): for each row, the sum of everything up to and including it.
WITH monthly AS (
SELECT substr(o.order_date, 1, 7) AS month,
SUM(i.quantity * i.unit_price) AS revenue
FROM orders AS o
JOIN order_items AS i ON i.order_id = o.id
WHERE o.status = 'paid'
GROUP BY month
)
SELECT month, revenue,
SUM(revenue) OVER (ORDER BY month) AS running_total
FROM monthly;
-- month revenue running_total
-- 2026-01 543.0 543.0
-- 2026-02 472.0 1015.0
-- 2026-03 613.0 1628.0Ties: three ways to number them
When values tie, the three numbering functions part ways. ROW_NUMBER gives every row its own number, so it has to break the tie somehow: here by name, because without a second sort key the database picks any order. RANK gives tied rows the same number, then skips as many places as rows shared it. DENSE_RANK gives the same number and doesn't skip. Units sold of each product, in paid orders:
| product | units | row_number | rank | dense_rank |
|---|---|---|---|---|
| Wool Socks (3-pack) | 7 | 1 | 1 | 1 |
| Trail Runner Shoes | 4 | 2 | 2 | 2 |
| Water Bottle | 4 | 3 | 2 | 2 |
| Headlamp | 3 | 4 | 4 | 3 |
| Merino Base Layer | 3 | 5 | 4 | 3 |
| Rain Shell Jacket | 3 | 6 | 4 | 3 |
| Trekking Poles | 1 | 7 | 7 | 4 |
The query
WITH units AS (
SELECT p.name AS product, SUM(i.quantity) AS units
FROM order_items AS i
JOIN orders AS o ON o.id = i.order_id
JOIN products AS p ON p.id = i.product_id
WHERE o.status = 'paid'
GROUP BY p.id
)
SELECT product, units,
ROW_NUMBER() OVER (ORDER BY units DESC, product) AS row_number,
RANK() OVER (ORDER BY units DESC) AS rank,
DENSE_RANK() OVER (ORDER BY units DESC) AS dense_rank
FROM units
ORDER BY units DESC, product;Use RANK for a league table, where a tie for second means nobody is third. Use DENSE_RANK when you want the top three values, however many rows share them. Use ROW_NUMBER when you need exactly one row per position, such as each customer's latest order.
Filtering on a window
Window functions are calculated after WHERE, so you can't filter on one there. Compute it in a CTE and filter in the outer query. That's the standard way to get the latest X per Y.
Loading your workspace…