What are window functions?
Window functions perform calculations across a set of rows related to the current row — without collapsing the result set like GROUP BY does. They're essential for rankings, running totals, period-over-period comparisons, and gap detection.
ROW_NUMBER vs RANK vs DENSE_RANK
sql
SELECT employee_name, salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
RANK() OVER (ORDER BY salary DESC) AS rank,
DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;- ROW_NUMBER: unique sequential number, no ties
- RANK: ties get the same rank, next rank skips
- DENSE_RANK: ties get the same rank, no gaps
LAG and LEAD
sql
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) AS prev_month,
LEAD(revenue) OVER (ORDER BY month) AS next_month
FROM monthly_revenue;PARTITION BY
sql
SELECT department, employee_name, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;Rolling averages
sql
SELECT day, dau,
AVG(dau) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d_avg
FROM daily_active_users;Chion's validator checks that PARTITION BY references valid categorical columns and that ORDER BY uses the correct sort direction.