Calculate across related rows

← SQL examples

Window functions add a ranking or running calculation while keeping each original row. A partition is the group used for that calculation.

Before you run it: Supported frames and combinations are listed below. These examples use window functions in the SELECT list.

First load the four-order numeric sample. PARTITION BY customer_id restarts the calculation for each customer. ORDER BY inside OVER chooses the order of that calculation; it does not by itself promise a sorted final response.

Follow the exampleCopy Anchor Link

1. Load the sample orders.
2. Run the ranking example within each customer.
3. Run the running-total example.
4. Compare each result with the partition and order in OVER.

Supported operationsCopy Anchor Link

ROW_NUMBER(), RANK(), DENSE_RANK(), LAG() and LEAD() with OVER (PARTITION BY ... ORDER BY ...) all execute server-side through the SQL translator, alongside SUM / AVG / COUNT / MIN / MAX over a window and the value and distribution functions (FIRST_VALUE, LAST_VALUE, NTH_VALUE, NTILE, PERCENT_RANK, CUME_DIST). Explicit ROWS BETWEEN / RANGE frame clauses (running totals, moving windows) execute too — on the frame-respecting functions: the aggregates plus FIRST_VALUE / LAST_VALUE / NTH_VALUE. Ranking and offset functions ignore a frame by definition, so writing one on ROW_NUMBER, RANK, LAG, LEAD or NTILE is refused with a hint rather than silently dropped.

Rank orders for each customerCopy Anchor Link

Rank each customer's orders by amount - the canonical window use - runs server-side:

SELECT id, customer_id, amount_cents,
       ROW_NUMBER() OVER (
         PARTITION BY customer_id
         ORDER BY amount_cents DESC
       ) AS rn
  FROM shop.orders

Each row comes back with its rn computed in one pass - no client-side sorting needed.

Calculate a running totalCopy Anchor Link

Add a ROWS BETWEEN frame to accumulate a running total per partition — the classic dashboard shape — in one server-side pass:

SELECT id, customer_id, amount_cents,
       SUM(amount_cents) OVER (
         PARTITION BY customer_id
         ORDER BY id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running
  FROM shop.orders

With the four-row sample, running totals amounts in ID order within each customer: customer 1 → 14900, 18800; customer 2 → 7200, 29200. Each original order remains in the result beside its running value.

The two frame units differ in what they accept. ROWS takes every bound — UNBOUNDED PRECEDING, N PRECEDING, CURRENT ROW, N FOLLOWING, UNBOUNDED FOLLOWING — so a moving window such as ROWS BETWEEN 1 PRECEDING AND CURRENT ROW works. RANGE takes peer bounds only (UNBOUNDED PRECEDING, CURRENT ROW, UNBOUNDED FOLLOWING), where CURRENT ROW spans the whole tie group; a value offset such as RANGE BETWEEN 5 PRECEDING is refused, as are the GROUPS frame unit and EXCLUDE.

Check query and frame limitsCopy Anchor Link

A window function runs over a single-table SELECT. It cannot yet appear in the same SELECT as a JOIN, a GROUP BY or DISTINCT, and the OVER call has to sit in the select list - you can ORDER BY the alias it was given, but not write the call itself into a WHERE or ORDER BY expression. Do the join or the aggregate in a subquery first, then window over its output. Named windows (WINDOW w AS (...) with OVER w) are supported; chaining one named window off another is not.

Expected resultCopy Anchor Link

Rankings and totals appear beside each order instead of collapsing orders into a summary.

ContinueCopy Anchor Link

Continue to summarize rows by status.