SQL Window Functions
Window functions compute aggregates and rankings across row partitions while keeping every row, expressing running totals, deltas, and top-N-per-group without self-joins.
Definition
Window functions evaluate an expression over a frame of rows related to the current row, defined by PARTITION BY and ORDER BY inside an OVER clause. They return one value per input row rather than collapsing groups. Ranking, offset, and aggregate window functions cover leaderboards, period-over-period deltas, cumulative metrics, and gaps-and-islands problems.
Context and scope
Used in analytics SQL for moving averages, sessionization, deduplication by keeping the latest row per key, and cumulative distributions. Supported by every major engine with subtle frame-default differences.
Common misconceptions
- Window functions group rows — they partition without collapsing; GROUP BY is the collapsing counterpart
- The default frame is everything — aggregate window functions default to a moving range frame from partition start to current row, which changes running sums
- They cannot filter their own results in the same query level — ranking output belongs in a subquery or CTE wrapped by WHERE
Anti-patterns
- Filtering on ROW_NUMBER output in the same SELECT level and fighting the planner instead of wrapping a CTE
- Assuming frame defaults match across engines and porting running-total SQL with different results
- Solving top-N-per-group with correlated subqueries and re-scanning the table instead of one windowed pass
Related concepts
Related examples
Authoritative source
https://www.postgresql.org/docs/current/tutorial-window.html
What it is
GROUP BY folds a set of rows into one output row. A window function keeps all rows and attaches a computed value computed over related rows — per partition, per ordering, per frame. That single distinction powers per-row rankings, cumulative sequences, and comparisons to neighbors.
How it works
OVER (PARTITION BY … ORDER BY …) defines the window; the optional frame — ROWS or RANGE between bounds — scopes aggregates like SUM and AVG. ROW_NUMBER, RANK, and DENSE_RANK rank; LAG and LEAD reach neighbors; FIRST_VALUE and NTH_VALUE pick positional values. Logical evaluation happens after grouping and before ORDER BY of the result.
When to use it
Running totals and deltas in financial reports, session stitching from event streams, latest-row-per-entity dedup, and top-N within groups. One pass replaces self-joins and correlated subqueries that engines execute row by row.
Pitfalls
Filtering on window output requires nesting one level because windows compute after WHERE. Frame semantics — RANGE versus ROWS, CURRENT ROW inclusivity — differ subtly across engines and between SQL standards modes. Large partitions without pruning can still spill sorts to disk.