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.