Window Functions
Perform relative partition operations using ROW_NUMBER, RANK, DENSE_RANK, LAG, and LEAD window frames.
SQL Query Architecture & Optimization Guide
Window functions perform calculations across a set of table rows related to the current row without collapsing them into single-row groups. Key operations include ranking (ROW_NUMBER, RANK, DENSE_RANK), moving offsets (LEAD, LAG), and running totals.
Window functions execute after WHERE, GROUP BY, and HAVING filtering, but before the final ORDER BY and LIMIT. The window engine divides rows into partitions and calculates metrics across a sliding frame of rows.
Because window functions execute after the WHERE clause, you cannot filter window results directly in the same query block (e.g. WHERE ROW_NUMBER() = 1 is invalid). Wrap the query in a CTE or subquery to filter by window results.
Interactive WebAssembly SQL Playground
Frequently Asked Questions on Window Functions
What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?
ROW_NUMBER assigns unique sequential integers to all rows. RANK assigns identical rank numbers to tied values and skips subsequent numbers (e.g. 1, 2, 2, 4). DENSE_RANK assigns identical numbers to ties without skipping (e.g. 1, 2, 2, 3).
Why can window functions not be used directly in WHERE or HAVING clauses?
Window functions calculate their values after WHERE and HAVING filtering steps are complete. To filter by a computed window metric, encapsulate the query in a Common Table Expression (CTE).
What is the difference between ROWS BETWEEN and RANGE BETWEEN in window frames?
ROWS defines physical row offsets relative to the current row. RANGE defines logical value intervals based on the ORDER BY column values.