A window function performs calculations across a defined set of rows while preserving the original row identity in the output.
Unlike GROUP BY, which collapses multiple rows into a single summary, window functions keep all individual records intact.
Every window function uses an OVER clause, which establishes the window frame or partition of rows for the calculation.
The PARTITION BY sub-clause divides the dataset into logical groups, restarting the calculation boundary for each partition.
The ORDER BY sub-clause within the OVER clause determines the sequence in which rows inside each partition are evaluated.
Ranking functions assign numerical ranks: ROW_NUMBER gives unique sequential integers, while RANK assigns tied values identical ranks.
DENSE_RANK functions like RANK but avoids leaving gaps in the ranking sequence following tied entries.
Aggregate functions can be used as window functions: writing SUM of revenue OVER ORDER BY date computes a running total.
Value navigation functions include LAG and LEAD, which retrieve values from previous or subsequent rows without performing self joins.
Using LAG allows analysts to compute period-over-period growth by comparing today revenue directly against yesterday revenue.
Window frames can be refined using the ROWS or RANGE clauses to specify moving average windows, such as a seven-day rolling average.
A rolling seven-day frame specifies ROWS BETWEEN six PRECEDING AND CURRENT ROW to smooth out weekly seasonality.
Window functions execute after WHERE, GROUP BY, and HAVING clauses, so they cannot be used inside WHERE to filter rows directly.
To filter based on a window function result, such as selecting only rank one rows, the query must be wrapped in a CTE or subquery.
Window functions eliminate expensive self joins and subqueries, making them an indispensable advanced tool for analytical SQL.