IntermediateLast updated: 2026-04-09 • 6 sections
Master ROW_NUMBER, RANK, LEAD, LAG, running totals, and moving averages. Works across Snowflake, BigQuery, Postgres, and Redshift.
-- General syntax
SELECT
column,
FUNCTION() OVER (
[PARTITION BY col1, col2]
[ORDER BY col3 [ASC|DESC]]
[ROWS|RANGE BETWEEN frame_start AND frame_end]
) AS alias
FROM table;
-- Frame options:
-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (default)
-- ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING
-- ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING| Function | Description | Example | Result for ties (1,1,3) |
|---|---|---|---|
| ROW_NUMBER() | Unique sequential number | ROW_NUMBER() OVER (ORDER BY salary DESC) | 1, 2, 3 (no ties) |
| RANK() | Rank with gaps for ties | RANK() OVER (ORDER BY salary DESC) | 1, 1, 3 (skips 2) |
| DENSE_RANK() | Rank without gaps | DENSE_RANK() OVER (ORDER BY salary DESC) | 1, 1, 2 (no gaps) |
| NTILE(n) | Divide into n buckets | NTILE(4) OVER (ORDER BY salary) | Quartile assignment |
| PERCENT_RANK() | Relative rank (0 to 1) | PERCENT_RANK() OVER (ORDER BY salary) | 0.0, 0.0, 0.5 |
| Function | Description | Example |
|---|---|---|
| LAG(col, n, default) | Value from n rows before | LAG(revenue, 1, 0) OVER (ORDER BY month) |
| LEAD(col, n, default) | Value from n rows after | LEAD(revenue, 1, 0) OVER (ORDER BY month) |
| FIRST_VALUE(col) | First value in the window | FIRST_VALUE(price) OVER (PARTITION BY product ORDER BY date) |
| LAST_VALUE(col) | Last value in the window | LAST_VALUE(price) OVER (PARTITION BY product ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) |
| NTH_VALUE(col, n) | Nth value in the window | NTH_VALUE(score, 2) OVER (ORDER BY score DESC) |
| Pattern | SQL | Use Case |
|---|---|---|
| Running Total | SUM(amount) OVER (ORDER BY date) | Cumulative revenue over time |
| Running Average | AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) | 7-day moving average |
| Running Count | COUNT(*) OVER (ORDER BY date) | Cumulative event count |
| Partition Total | SUM(amount) OVER (PARTITION BY dept) | Department-level total on every row |
| Percent of Total | amount / SUM(amount) OVER () * 100 | Each row as percentage of grand total |
| Percent of Group | amount / SUM(amount) OVER (PARTITION BY dept) * 100 | Each row as percentage of group total |
-- Deduplicate: keep latest row per key
SELECT * FROM (
SELECT *, ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY updated_at DESC
) AS rn
FROM raw_events
) WHERE rn = 1;
-- Snowflake shortcut using QUALIFY
SELECT * FROM raw_events
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) = 1;
-- Gap detection: find missing dates
SELECT date,
LAG(date) OVER (ORDER BY date) AS prev_date,
DATEDIFF('day', LAG(date) OVER (ORDER BY date), date) AS gap_days
FROM daily_metrics
HAVING gap_days > 1;
-- Year-over-Year comparison
SELECT month, revenue,
LAG(revenue, 12) OVER (ORDER BY month) AS revenue_last_year,
ROUND((revenue - LAG(revenue, 12) OVER (ORDER BY month))
/ NULLIF(LAG(revenue, 12) OVER (ORDER BY month), 0) * 100, 1) AS yoy_pct
FROM monthly_revenue;ROW_NUMBER assigns unique sequential numbers (no ties). RANK assigns the same number to ties but skips subsequent numbers (1,1,3). DENSE_RANK assigns the same number to ties without skipping (1,1,2). Use ROW_NUMBER for deduplication, RANK for competition-style ranking, and DENSE_RANK for dense ranking reports.
Use LAG to access previous rows (e.g., last month's revenue for month-over-month comparison). Use LEAD to access future rows (e.g., next scheduled delivery date). Both accept an offset parameter: LAG(col, 2) looks 2 rows back.
A window frame defines which rows within the partition are included in the calculation. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW gives a running total. ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING gives a 7-row moving window. Frame bounds only apply to aggregate window functions.