Window functions let you compute row‑level rankings, offsets, and cumulative metrics in a single pass, eliminating self‑joins and temporary tables.
Step‑by‑step usage
1. Identify the partitioning key(s) that define the analytical scope (e.g., customer_id, order_date).
2. Choose the appropriate function:
- ROW_NUMBER() – unique sequential number per partition.
- RANK() – gaps when ties occur.
- DENSE_RANK() – no gaps.
- LEAD() / LAG() – fetch a value from a following or preceding row.
3. Add an ORDER BY clause inside the OVER() to define the ranking order.
4. Combine with WHERE or QUALIFY (Snowflake, BigQuery) to filter top‑N or change‑point rows.
5. Use the result in downstream joins, CTEs, or materialized views without extra aggregation steps.
Comparison table
| Function | Unique? | Gaps on tie? | Typical use |
|----------|---------|--------------|-------------|
| ROW_NUMBER() | Yes | N/A | Pagination, deduplication |
| RANK() | No | Yes | Leaderboards with ties |
| DENSE_RANK() | No | No | Tiered pricing bands |
| LEAD()/LAG() | N/A | N/A | Period‑over‑period deltas |
Example: top 3 orders per region
SELECT *
FROM (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY o.region
ORDER BY o.total_amount DESC) AS rn
FROM orders o
WHERE o.order_date >= DATEADD(day, -30, CURRENT_DATE)
) t
WHERE rn <= 3;The query runs in a single scan, uses the optimizer’s window spool, and on a 10 TB star schema table reduces I/O by ~70 % compared with a self‑join approach.