SQL Window Functions

Explain the purpose and common SQL window functions.

What Are Window Functions

Window functions compute values over a set of related rows without collapsing them. Unlike GROUP BY, every row is retained in the result.

Syntax

function() OVER (PARTITION BY col ORDER BY col ROWS BETWEEN ...)

Ranking Functions

• ROW_NUMBER(): Unique number per row, no ties.
• RANK(): Tied rows share rank; next rank skips numbers.
• DENSE_RANK(): Tied rows share rank; next rank does not skip.

Aggregation Functions

• SUM() OVER (PARTITION BY dept): Running total per department while keeping all rows.
• AVG() OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW): 7-day rolling average.

Offset Functions

• LAG(col, 1): Value from the previous row (period-over-period growth).
• LEAD(col, 1): Value from the next row.

Use Cases

Leaderboards, rolling statistics, period-over-period comparisons, Top-N per group.