Window functions compute across rows without collapsing them

A window function performs a calculation over a set of rows related to the current row — the window — and returns one value per input row. That last part is the whole point. GROUP BY reduces many rows to one; a window function leaves every row in place and attaches a computed value to it. If you need each order shown alongside its customer's total, or its rank within its category, or the difference from the previous month, you need a window function, because grouping would destroy the detail you are trying to display.

The syntax is an ordinary function call followed by OVER:

SELECT
  order_id, customer_id, amount,
  SUM(amount)  OVER (PARTITION BY customer_id)                        AS customer_total,
  RANK()       OVER (PARTITION BY customer_id ORDER BY amount DESC)   AS rank_in_customer,
  LAG(amount)  OVER (PARTITION BY customer_id ORDER BY created_at)    AS previous_amount
FROM orders;

PARTITION BY is the window analogue of GROUP BY: it divides rows into independent groups, and the calculation restarts at each boundary. Omitting it makes the whole result one partition. ORDER BY inside OVER establishes the sequence used by ranking functions, offset functions, and any frame — and it is entirely independent of the query's final ORDER BY, which only affects presentation.

Evaluation order, and why top-N queries are nested

Window functions are evaluated in the SELECT phase, after FROM, WHERE, GROUP BY and HAVING, and before DISTINCT, ORDER BY and LIMIT. Two consequences follow, and they explain most confusion.

First, a window function sees only rows that survived WHERE. Adding a filter changes the window's contents, so a running total over filtered rows is a running total of the filtered set — not of the underlying table with some rows hidden. That is usually what you want, but it must be a decision.

Second, you cannot filter on a window result in WHERE or HAVING, because neither has run yet at the point the window is computed. WHERE ROW_NUMBER() OVER (...) <= 3 is an error. The remedy is to compute the window in a subquery or CTE and filter in the enclosing query — which is exactly why every "top N per group" query has a nested shape, and not a stylistic quirk:

WITH ranked AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC, product_id) AS position
  FROM product_revenue
)
SELECT * FROM ranked WHERE position <= 3;

Because they run after grouping, window functions can also operate over aggregated rows. SUM(SUM(amount)) OVER () in a grouped query looks bizarre but is precise: the inner SUM aggregates within each group, the outer window sums across the grouped result. That is the one-pass way to compute each group's percentage of the overall total.

Ranking, and the tie question nobody asks

ROW_NUMBER() assigns 1, 2, 3 with no ties ever — rows tied on the ordering key get arbitrary distinct numbers. RANK() gives tied rows the same rank and then skips: 1, 2, 2, 4. DENSE_RANK() gives the same rank without skipping: 1, 2, 2, 3. NTILE(n) distributes rows into n buckets as evenly as it can.

The distinction matters more than it looks. "Show me the top three products" written with ROW_NUMBER always returns exactly three rows, breaking ties arbitrarily — so two products with identical revenue produce a different winner between runs. Written with RANK, it returns four rows when two tie for third. Stakeholders almost always mean the second; engineers almost always write the first. It is worth asking rather than assuming.

Whichever you choose, add a deterministic tiebreaker to the ORDER BY — typically the primary key. Without one, a tied ROW_NUMBER is genuinely nondeterministic and the "top three" can change between identical runs.

Frames: ROWS, RANGE and GROUPS

Within a partition, the frame decides which rows the calculation actually sees. This is where the most common and most invisible bug lives.

When you write ORDER BY inside OVER without a frame clause, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE operates on values, not positions: all rows tied on the ordering expression — its peers — are included together. So a running total ordered by a date with several rows per date jumps straight to that whole date's total on its first row, instead of accumulating within the date. The output looks plausible and is wrong.

ROWS counts physical rows and has no peer behaviour, so ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives strict row-by-row accumulation. When you want a running total, say ROWS explicitly. GROUPS counts distinct peer groups, which is occasionally the exact tool for "the previous three distinct dates".

Frames also make moving calculations expressible: AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) is a seven-row moving average. Note row, not day: if a day is missing from the data, that window silently spans nine calendar days. For a calendar-based window, either densify the series first by joining onto a generated date range, or use a RANGE frame with an interval offset — RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW — which is defined in time rather than in rows.

One more frame trap: LAST_VALUE(x) OVER (ORDER BY y) returns the current row's value, not the partition's last, because the default frame ends at the current row. It needs ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. FIRST_VALUE happens to work under the default frame, which makes the asymmetry easy to miss.

Offset functions

LAG(x, n, default) and LEAD(x, n, default) read a value from n rows back or forward within the partition, ignoring the frame entirely. They are the natural way to express period-over-period change, gap detection between events, and state transitions: amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY month).

The first row of each partition has no previous row, so LAG returns NULL there. Supplying the third argument — LAG(amount, 1, 0) — is usually cleaner than coalescing afterwards, though a NULL is sometimes the more honest answer: for the first month, "no change" and "change of zero" are different claims.

Practical notes

Repeating an identical OVER (...) several times is noisy and invites drift when one copy is edited. A named WINDOW w AS (PARTITION BY customer_id ORDER BY created_at) clause declares it once, and each function then says OVER w.

On cost: a window function generally needs its input sorted by the partition and order keys, so a sort often appears in the plan. A matching index can let the planner use an ordered scan and avoid an explicit sort, though that is a cost-based choice rather than a guarantee. Several windows with the same definition can also share ordering work — another reason to make identical windows literally identical.

On NULLs: they sort together and their position is controlled by NULLS FIRST/NULLS LAST, which changes who ranks first and where gaps land. Aggregates used as window functions skip NULL inputs, exactly as they do when grouped, so AVG over a window divides by the non-NULL count.

Finally, window functions can be filtered per-function with FILTER (WHERE ...) on aggregate windows, which lets one pass compute several differently-scoped running measures without a self-join.

Worked example: LAST_VALUE under the default frame is the current row

Three rows in a partition, amounts 10, 20, 30, ORDER BY day.

expression	value on the middle row
LAST_VALUE(amount) OVER (ORDER BY day)	20 (frame ends at current row)
LAST_VALUE(amount) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)	30
FIRST_VALUE(amount) OVER (ORDER BY day)	10 (default frame happens to work)

The default RANGE frame is not the partition. Say ROWS when you mean a running total.