Databases · interactive

Window functions

GROUP BY squashes rows into one per group. A window function keeps every row and adds a value computed over related rows: its partition, in an order, over a frame. Click a row to see which rows its value came from.

SELECT rep, month, amount,
  SUM(amount) OVER (PARTITION BY rep ORDER BY month)
FROM sales;

rep	month	amount	Running SUM
Ada	1	300	300
Ada	2	500	800
Ada	3	200	1000
Bo	1	400	400
Bo	2	400	800
Bo	3	700	1500

Faded: other partitions. Shaded: rows that fed the selected value.

Row Bo, month 2: sum of Bo's months 1–2 = 800.

Function

Step 3 of 4 · read left to right

1
PARTITION BY splits the rows
Like GROUP BY, but the rows stay. Each partition is computed separately.
PARTITION BY rep
2
ORDER BY sets the sequence
Ranking functions and LAG/LEAD need an order within the partition.
Top-N per group: ROW_NUMBER() ≤ 3.
3
The frame picks the rows to aggregate
With ORDER BY, SUM defaults to “from the start up to this row”: a running total. ROWS BETWEEN 2 PRECEDING AND CURRENT ROW gives a moving window.
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
4
One pass instead of self-joins
Month-over-month change, rankings and running totals used to need correlated subqueries. Window functions do them in one sorted scan.
Filter on them in an outer query or with QUALIFY.

In production

Tools and technologies

Every modern SQL database supports window functions.

Not tied to one language — use these names when you search docs or design a system.