Window Functions

How do you calculate a running total or assign a rank while keeping every source row? A SQL window function reads a set of related rows, computes one value for the current row, and adds that value to the row. Unlike GROUP BY, it does not reduce each group to one result row.

Core idea: a window function can read several rows and still return one value to every original row id. It augments the relation; GROUP BY changes its grain.

An OVER specification answers three separate questions:

• PARTITION BY decides which rows may interact. Each partition restarts independently.
• ORDER BY inside OVER gives rows a logical order for the calculation.
• The function and, when relevant, its frame decide which ordered rows contribute to the current value.

That window-local ORDER BY does not guarantee the final presentation order. Add an outer query-level ORDER BY when row order matters to a caller.

How a Running Window SUM Works

This lesson makes the frame explicit:

SELECT
  id,
  region,
  day,
  amount,
  SUM(amount) OVER (
    PARTITION BY region
    ORDER BY day, id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_sum
FROM sales
ORDER BY region, day, id;

The ORDER BY day, id inside OVER controls which rows enter each running prefix. The outer ORDER BY region, day, id controls the order returned to the caller. They solve different problems and may coexist in the same query.

For each current row, the ROWS frame begins at the first row in that row's region and ends at the current row. The running sum is the sum of amount across exactly those frame members.

Running SUM Step by Step: Partition, Order, and Write Back

The default relation is deliberately interleaved:

PARTITION BY region first forms two independent logical partitions. ORDER BY day, id then establishes each partition's calculation order:

• North: s3 → s5 → s1 → s7
• South: s2 → s6 → s4

The North frames grow as follows:

1. s3 reads [s3]: 30 = 30.
2. s5 reads [s3, s5]: 30 + 50 = 80.
3. s1 reads [s3, s5, s1]: 30 + 50 + 30 = 110.
4. s7 reads [s3, s5, s1, s7]: 30 + 50 + 30 + 20 = 130.

South starts again from zero. Its three values are 40, 50, and 90 in window order. Each value is then written back by row id, so the source-order teaching table becomes:

The table keeps source order to make row identity easy to inspect. SQL does not promise that order without an outer ORDER BY.

RANK and DENSE_RANK

Ranking uses the same partition-and-order structure but no aggregate row frame:

SELECT
  id,
  region,
  amount,
  RANK() OVER (
    PARTITION BY region
    ORDER BY amount DESC
  ) AS rank
FROM sales;

Rows with the same ORDER BY value are peers. Every row in a peer group receives the same result.

• RANK = 1 + the number of preceding rows with a strictly greater amount.
• DENSE_RANK = 1 + the number of distinct preceding amounts.

That distinction appears after a tie. North orders as s5(50), s1(30), s3(30), s7(20). Both 30 rows receive 2. RANK assigns the next row 4 because three rows precede it; DENSE_RANK assigns 3 because only two distinct amounts precede it.

South orders as s2(40), s4(40), s6(10). The two 40 rows are peers. The 10 row receives RANK = 3 or DENSE_RANK = 2.

Across the original source ids, the complete outputs are:

The Wide peers preset makes the gap harder to miss: four West rows tie at amount 90. The next two rows receive ranks 5 and 6, but dense ranks 2 and 3.

Window Functions Versus GROUP BY

Both operations can partition rows conceptually, but they produce different result grains.

For the default data, the window result has seven rows while GROUP BY region has two. Replacing the running window sum with a grouped sum therefore does more than change syntax: it removes row identities such as s1 and s3 from the result grain.

Removing PARTITION BY region changes a different contract. In the Partition reset preset, the real global ORDER BY day, id sequence is p2, p3, p1, p4, with running sums 10, 110, 210, 215. The first South row is still 10; the error becomes visible after rows from the two regions begin sharing one prefix. With partitioning, North is 100, 200 and South is 10, 15.

Window Function Edge Cases

• Empty input: no row means no partition, frame, peer group, or result row. A window function never invents a row.
• One-row partition: a running prefix frame contains only that row, and both ranking functions return 1.
• Ties: peer rows receive the same RANK or DENSE_RANK. Only the next value reveals whether gaps are preserved.
• Duplicate frame-order values: a ROWS frame depends on row positions. Add a deterministic tiebreaker such as id when several rows share the same day and their order would change intermediate running totals.
• Missing outer order: window-local order controls the calculation but not the order in which result rows are returned.

Common Mistakes

• Treating PARTITION BY as if it collapses rows. It defines independent calculation scopes; it does not set the output grain.
• Assuming the running total follows source-table order. It follows the ORDER BY inside OVER.
• Forgetting the frame clause. Some database defaults use a value-based RANGE frame, which can include peers together and differ from an explicit row-by-row ROWS frame.
• Calculating RANK by counting distinct preceding values. That is DENSE_RANK; ordinary RANK counts preceding rows and can leave gaps.
• Expecting a stable final order because the window has ORDER BY. Use an outer ORDER BY for presentation.
• Reading the workbench as a physical sort or buffer plan. The query specifies logical semantics; the optimizer chooses an execution plan.

A Note on Simplification: Logical Semantics, Not an Execution Plan

This lesson models logical partitions, window-local ordering, an explicit prefix ROWS frame, peer groups, ranking gaps, row-identity preservation, and result cardinality. It omits physical executor strategies, memory and spill behavior, collation, NULL ordering, numeric overflow and precision, named windows, FILTER, other frame bounds, RANGE and GROUPS frames, exclusion clauses, and dialect-specific defaults. Consult your database's documentation before relying on an implicit frame or a particular NULL order.