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
customerorder_idorder_datestatus
Aisha Khan1062026-02-06paid
Aisha Khan1112026-03-08refunded
Emma Novak1122026-03-12paid
Liam Walsh1022026-01-09paid
Liam Walsh1072026-02-11pending
Lucas Silva1092026-02-25paid
Lucas Silva1152026-03-29pending
Maja Berg1012026-01-04paid
Maja Berg1032026-01-15refunded
Maja Berg1082026-02-19paid
Maja Berg1142026-03-24paid
Noah Keller1052026-02-02paid
Noah Keller1132026-03-18paid
Sofia Rossi1042026-01-22paid
Sofia Rossi1102026-03-03paid
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

sql
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.

sql
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.0

Ties: 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:

Units sold, numbered three ways
productunitsrow_numberrankdense_rank
Wool Socks (3-pack)7111
Trail Runner Shoes4222
Water Bottle4322
Headlamp3443
Merino Base Layer3543
Rain Shell Jacket3643
Trekking Poles1774
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…