Window functions

A normal aggregate like SUM collapses many rows into one. A window function computes an aggregate over a “window” of rows without collapsing — every input row still gets its own output row. That single difference is what makes running totals, rankings, and previous-row comparisons possible in one clean query.

You turn an aggregate into a window function by adding OVER (…), which has three pieces: PARTITION BY splits the rows into independent groups (like GROUP BY, but the rows survive); ORDER BY orders the rows within each partition; and the frame decides which rows around the current one the function actually reads. When you give an ORDER BY and no explicit frame, the default is “everything from the start of the partition through the current row” — which is exactly why SUM(...) OVER (ORDER BY date) gives a running total instead of a grand total.

That frame is the whole trick, and it is invisible in the SQL — so let us draw it. For one user’s three orders, the running total at each row sums the shaded frame:

A running total

SELECT order_date,
       revenue,
       SUM(revenue) OVER (ORDER BY order_date) AS running_total
FROM   orders;

order_date	revenue	running_total
2024-01-05	30	30
2024-01-12	900	930
2024-01-20	50	980

The same SUM you already know — but OVER (ORDER BY order_date) keeps every row and accumulates through time. Add PARTITION BY country and each country gets its own independent running total, still one row per order, still no GROUP BY.

LAG and LEAD — reach into the neighbouring row

LAG(col) hands you col from the previous row in the partition’s order; LEAD(col) from the next. Pair it with date math and you have gap analysis for free:

SELECT order_date,
       LAG(order_date) OVER (ORDER BY order_date) AS prev_order,
       julianday(order_date)
         - julianday(LAG(order_date) OVER (ORDER BY order_date)) AS days_since
FROM   orders;

order_date	prev_order	days_since
2024-01-05	NULL	NULL
2024-01-12	2024-01-05	7
2024-01-20	2024-01-12	8

The first row has no previous, so LAG returns NULL. After that you get day-over-day deltas — the raw material of churn windows, session gaps, and “days since last purchase.”

Top-N per group — the wrap-and-filter idiom

The pattern you will reach for most is “the top few rows within each group.” You number the rows with ROW_NUMBER() OVER (PARTITION BY group ORDER BY value DESC) and then keep the low numbers — but a window function is evaluated after the WHERE of its own SELECT, so you cannot filter on it inline. You wrap the query and filter in the outer one:

SELECT * FROM (
  SELECT u.name, o.order_date,
         ROW_NUMBER() OVER (PARTITION BY u.id ORDER BY o.order_date DESC) AS rn
  FROM   orders o
  JOIN   users u ON u.id = o.user_id
) t
WHERE rn <= 2;          -- top 2 most recent orders per user

name	order_date	rn
Asha	2024-01-20	1
Asha	2024-01-12	2
Carlos	2024-01-08	1

Asha’s two most recent orders survive, her oldest is dropped, and Carlos — with a single order — keeps it. That wrap-and-filter shape is the canonical top-N-per-group query, and you will write it constantly. (The ranking lesson goes deep on ROW_NUMBER vs RANK vs DENSE_RANK for the ties.)

Practice

Quick check

0/3

Q1What's the difference between a window function and an aggregate?

Window functions can use SUM, aggregates can'tAggregates collapse rows into groups; window functions keep every rowWindow functions only work on numbersAggregates need an OVER clause

Check answer

Q2Why does SUM(revenue) OVER (ORDER BY date) give a running total, not a grand total?

ORDER BY sorts the output onlyThe default frame is 'partition start through the current row', so it accumulatesSUM behaves differently inside OVERIt's actually a grand total

Check answer

Q3You want the top 3 customers per region. Cleanest approach?

WHERE RANK() OVER (...) <= 3Compute ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) in a subquery, then filter WHERE rn <= 3SELECT TOP 3 ... GROUP BY regionA LIMIT inside each PARTITION BY

Check answer