Window function

A window function computes a value across a set of rows related to the current row — a running total, a rank, a moving average, the value from the previous row — while still returning one output row for every input row, unlike a standard GROUP BY aggregation, which collapses many rows into one per group.

The "window" is defined with an OVER() clause specifying how rows are partitioned, similar to a GROUP BY but without collapsing rows, and ordered; functions like RANK(), LAG(), LEAD(), and SUM() OVER (...) then operate within that window. A typical use is calculating each customer's rank within their region, or the change in a metric from the prior period, in a single query without a self-join.

Window functions matter because they replace what used to require self-joins or procedural code with a single readable expression, and they are heavily used in cohort analysis, running totals, and period-over-period comparisons. They are frequently combined with a common table expression to keep multi-step logic readable. A common mistake is confusing a window function's PARTITION BY with GROUP BY: partitioning does not reduce the row count, which trips up analysts expecting a summarized result.