SQL Window Functions cheat sheet
Ranking, offsets, frames, and the five production patterns — one printable page.
Ranking functions
row_number() over (partition by k order by t desc)- Unique 1,2,3… per partition. The dedup workhorse: keep rn = 1.
rank() over (order by score desc)- Ties share a rank, next rank skips (1,1,3).
dense_rank() over (order by score desc)- Ties share a rank, no gaps (1,1,2).
ntile(4) over (order by revenue)- Buckets rows into N equal groups (quartiles here).
Offset functions
lag(x) over (order by month)- Value from the previous row. lag(x, 3) reaches back 3 rows.
lead(x) over (order by month)- Value from the next row.
first_value(x) over (partition by k order by t)- First value in the window frame.
last_value(x) over (... rows between unbounded preceding and unbounded following)- Needs the full frame — default frame stops at current row!
Aggregates as windows
sum(x) over (order by d)- Running total (default frame = start to current row).
sum(x) over (partition by k)- Group total on every row — no GROUP BY collapse.
avg(x) over (order by d rows between 6 preceding and current row)- 7-row moving average.
count(*) over ()- Total row count without collapsing rows.
Frames
rows between 3 preceding and current row- Physical rows — use for moving windows.
range between interval 7 day preceding and current row- Value-based frame (dates/numbers), handles gaps.
rows between unbounded preceding and unbounded following- The whole partition.
The patterns
with r as (select *, row_number() over (partition by id order by updated_at desc) rn from t) select * from r where rn = 1- Deduplicate: latest record per key.
where rank_in_group <= 3- Top-N per group (rank in a CTE first — no window functions in WHERE).
sum(is_new_session) over (partition by user_id order by ts)- Sessionize: flag boundaries with lag, then running sum for session IDs.
round(100.0 * (x - lag(x) over w) / lag(x) over w, 1)- Percent change vs previous period.
From DataLane — tutorials at/blog, practice SQL live in theplayground.