SQL Window Functions: Running Totals and Ranks

A window function calculates over related rows while keeping each detail row in the result. That makes it useful for a running balance, a leaderboard, or a comparison with the previous event. A grouped aggregate collapses rows; a window calculation attaches an answer to them.

Start with a table containing account_id, happened_at, id, and amount. The id is a tie-breaker because two events can share a timestamp. Without a deterministic ordering, a row-by-row running total can change between otherwise equivalent query plans.

Write the frame you mean

SELECT account_id, happened_at, amount,
       SUM(amount) OVER (
         PARTITION BY account_id
         ORDER BY happened_at, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM events
ORDER BY account_id, happened_at, id;

The partition keeps accounts separate. The window ordering defines calculation order, and the ROWS frame includes all preceding rows plus the current one. The final ORDER BY controls display order. It is separate from the ORDER BY inside OVER.

Test two rows with the same timestamp. A default RANGE-based frame can treat peers together, which may give both rows the same cumulative value. That can be appropriate for a cumulative total by date, but it is different from a transaction-by-transaction balance.

Choose a rank policy before writing the query

ROW_NUMBER assigns a unique sequence position. RANK gives equal scores an equal rank and leaves gaps afterward. DENSE_RANK also ties equal scores but leaves no gaps. A top-three query therefore means different things under each function: three rows, three occupied ranks, or three distinct score levels.

When a product needs exactly one winner, define a tie-breaker and use the function matching that policy. Avoid silently breaking ties with an arbitrary query order. Likewise, LAG reads the preceding row in the chosen ordering; if dates are missing, it does not automatically mean yesterday.

Check the edge rows and the query plan

Include a one-row partition, a tied score, a null value, and a gap in dates. LAST_VALUE is particularly sensitive to its frame: the default often ends at the current peer group rather than at the partition’s final row. Specify an unbounded following endpoint when the whole partition is the intended scope.

Only after the result is correct should you tune it. Inspect the actual filter, partition, and ordering work, then consider an index that matches the workload. An index chosen from the function name alone is unlikely to answer the real performance question.

Reference tables to keep nearby

• SQL Window Functions with Examples — PostgreSQL window functions and explicit frame patterns for ranks, running totals, and neighboring rows.

Primary sources

These are the specifications and project documentation behind the examples. Check the relevant implementation and version before applying a pattern.

• PostgreSQL window function tutorial
• PostgreSQL window functions