Chapter 4 · 10 min
Window functions and running totals
A window function computes a value across a set of related rows without collapsing those rows into
one output row. That is the whole difference from GROUP BY: grouping keeps one row per group,
while a window keeps every row and adds a computed column beside it. Interview questions use the
second shape when they ask for a running total, a rank, or a comparison with a neighbouring row.
The general form is a function followed by an OVER clause:
function(...) OVER (
PARTITION BY grouping_columns
ORDER BY ordering_columns
frame_clause
)
PARTITION BY restarts the calculation for each group. ORDER BY defines the sequence the
function walks. The frame clause decides which rows are visible at each step. A running total needs
a frame that starts at the beginning of the partition and ends at the current row:
SELECT
date_trunc('month', o.order_date)::date AS month,
sum(oi.quantity * oi.unit_price) AS revenue,
sum(sum(oi.quantity * oi.unit_price)) OVER (
ORDER BY date_trunc('month', o.order_date)::date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_revenue
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
GROUP BY 1
ORDER BY 1;
The inner sum is a grouped aggregate; the outer sum ... OVER runs after grouping and walks the
monthly result. That ordering is why a window can sit on top of GROUP BY in the same query. The
evaluation order is FROM, WHERE, GROUP BY, HAVING, then windows, then ORDER BY and
LIMIT. A window cannot see rows removed by WHERE, and a window alias cannot be used in the
WHERE or GROUP BY clause of the same query.
Frames are a safety decision
When an ORDER BY appears inside OVER without an explicit frame, Postgres uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. With RANGE, rows that tie on the ordering column are all
included in the current row’s frame, so three rows with the same month each see the same
“cumulative” total. When the result must advance row by row, write the frame explicitly with
ROWS and include a tiebreaker in the ordering, such as a primary key, so no two rows are
ambiguous.
Determinism
A window function is only as deterministic as its ordering. If two rows share every ORDER BY
column, the engine may return them in any order and the frame can differ between runs. Add a unique
column as the final tiebreaker, and sort the final result the same way the problem specifies.
Where windows are the wrong tool
If the question asks for one row per group, a window is unnecessary ceremony: GROUP BY is
simpler. If the question asks for a filter on a group aggregate, HAVING does the job. Reach for a
window only when the output must keep the underlying rows.