Window functions: running totals, ranks and gaps

Each row gets its own little window into the group — and, unlike group by, every row stays on the page.

The idea

A window function does a calculation across a set of rows related to the current row — and returns one value per row, without collapsing anything. OVER opens the window, PARTITION BY decides which rows share a window (the group), ORDER BY orders them inside it, and the frame decides how many of those rows this particular row gets to see.

Think of it as a translucent frame that slides down the table. For a running total it stretches from the top of the group to the current row; for a 7-day rolling sum it’s just the last week; for LAG it’s a single row behind. Change the function and the frame changes shape.

Slide the frame · watch the computed column fill in

Function:

Partition by:

customer A customer B the frame this row can see

Current row

—

computed value

—

Pick a function and a partition, then step down the table. Each output row computes from only the rows inside its frame — and no rows disappear.

How it works

The clause reads left to right: which group, in what order, seeing how far back. A running total is a frame from the group’s start to here; a rolling window trims it to a time span; LAG peeks one row back.

SUM(amount) OVER (PARTITION BY cust ORDER BY day)   -- running total per customer

customer A, in day order:
  day  1   amount 40   frame = {40}            -> 40
  day  4   amount 25   frame = {40, 25}        -> 65
  day  8   amount 40   frame = {40, 25, 40}    -> 105
  day 12   amount 70   frame = {40,25,40,70}   -> 175

... RANGE BETWEEN '6 days' PRECEDING AND CURRENT ROW   -- 7-day rolling
  day  8   window = days 2..8  -> {25(day4), 40(day8)}  -> 65   (day 1 is now too old)
  day 12   window = days 6..12 -> {40(day8), 70(day12)} -> 110

RANK vs ROW_NUMBER. Ranking customer A’s amounts (70, 40, 40, 25) by size: RANK gives 1, 2, 2, 4 — ties share, then it skips. ROW_NUMBER gives 1, 2, 3, 4 — no ties, arbitrary order between equals. DENSE_RANK gives 1, 2, 2, 3 — no gap. For a true top-N-per-group you usually want ROW_NUMBER in a subquery, then filter.

When to use it

Reach for a window when…	The trade-off
You need a per-row calc that looks at neighbours: running total, rolling average, share-of-group.	You keep every row — but you can’t filter on the window result in `WHERE`; wrap it in a subquery / CTE.
You want top-N within each group without losing the other rows.	`GROUP BY` would collapse them; window keeps them, so you filter afterwards.
Time-between-events, streaks, or period-over-period with `LAG` / `LEAD`.	The first row of each partition has no previous row — expect a `NULL`.

Watch out for

Filtering on a window in WHERE. Window functions run after WHERE/GROUP BY, so WHERE rnk = 1 is an error. Compute it in a subquery or CTE, then filter in the outer query.
The invisible default frame. With ORDER BY but no explicit frame, the default is RANGE ... CURRENT ROW, which pulls in all rows tied on the order key. For a strict row-by-row running total, spell out ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
ROWS vs RANGE for “7-day.” ROWS 6 PRECEDING means the last 7 rows; a 7-day window needs RANGE with a date interval, or gaps and duplicate days will fool you.
RANK’s gaps. After a tie, RANK skips numbers (1, 2, 2, 4). If you need dense positions use DENSE_RANK; if you need exactly one winner use ROW_NUMBER.
Forgetting ORDER BY inside OVER. A running total or LAG without an order is undefined — the “running” has no direction.

Worked example

An interviewer asks: “For each customer, find their highest-value order and their 7-day spend.” A strong answer reaches for windows, not a self-join: ROW_NUMBER() OVER (PARTITION BY cust ORDER BY amount DESC) in a CTE, then WHERE rn = 1 in the outer query for the top order — keeping every row available. The 7-day spend is SUM(amount) OVER (PARTITION BY cust ORDER BY day RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW). The tell of a strong candidate is naming why it’s a window and not a GROUP BY: they still need the individual orders on the page.

Check yourself

You wrote RANK() OVER (PARTITION BY cust ORDER BY amount DESC) AS rnk and now want only each customer’s top order. What’s the fix?

You need days-since-previous-order per customer. Which window fits?