TopolearnTop-N per group, period-over-period change and cumulative sums are window-function problems.
An aggregate with GROUP BY collapses each group to one row. A window function computes over a set of related rows but keeps every row, adding the result as a column. The OVER clause defines the window:
SUM(total) OVER (PARTITION BY customer_id ORDER BY ordered_at)PARTITION BYsplits rows into independent groups (likeGROUP BY, without collapsing). Omit it to use the whole result.ORDER BYorders rows within each partition. For aggregates likeSUMit also makes the result cumulative: a running total.
Key functions:
row_number()numbers rows 1, 2, 3;rank()gives ties the same number and leaves gaps (1, 1, 3);dense_rank()leaves none (1, 1, 2).lag(x)andlead(x)read a value from the previous or next row, for differences between consecutive rows.first_value,last_value,ntile(n), and any ordinary aggregate.
The frame decides which rows of the partition an aggregate sees. With ORDER BY, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes rows tied with the current one (its peers): two orders at the same timestamp get the same running total. Say ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW if you want strictly row-by-row, or ROWS BETWEEN 6 PRECEDING AND CURRENT ROW for a 7-row moving average. The default frame is also why last_value surprises people: it stops at the current row.
Window functions run after WHERE, GROUP BY and HAVING, so you cannot filter on them in WHERE. Wrap the query in a CTE or subquery and filter outside; that is the standard "top N per group" pattern. Snowflake, BigQuery and DuckDB offer a QUALIFY clause for this; PostgreSQL does not.
Example
SELECT customer_id, id, total,
SUM(total) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS running,
lag(total) OVER (PARTITION BY customer_id ORDER BY ordered_at) AS prev,
rank() OVER (ORDER BY total DESC) AS overall_rank
FROM orders ORDER BY customer_id, ordered_at;
-- customer_id | id | total | running | prev | overall_rank
-- 1 | 101 | 68.50 | 68.50 | NULL | 3
-- 1 | 102 | 189.00 | 257.50 | 68.50 | 2
-- 2 | 103 | 9.99 | 9.99 | NULL | 5
-- 2 | 105 | 238.00 | 247.99 | 9.99 | 1
-- 3 | 104 | 49.00 | 49.00 | NULL | 4
-- Top order per customer: filter outside the window query
WITH ranked AS (
SELECT customer_id, id, total,
row_number() OVER (PARTITION BY customer_id ORDER BY total DESC) AS rn
FROM orders
)
SELECT customer_id, id, total FROM ranked WHERE rn = 1;
-- 1 | 102 | 189.00
-- 2 | 105 | 238.00
-- 3 | 104 | 49.00Remember
- Window functions add a computed column without collapsing rows
- PARTITION BY groups, ORDER BY orders and makes aggregates cumulative
- Default frame with ORDER BY includes peers; use ROWS for strict row frames
- Filter on window results in an outer query (no QUALIFY in PostgreSQL)
Watch out for
- Using last_value() with the default frame and getting the current row's value.