SQL Window Function Patterns: A Library of 10 Reusable Recipes
Ten window-function patterns you will use for years — running totals, moving averages, deduplication, top-N per group, gap detection, sessionization, and percent-of-total — each as a copy-ready query.
Quick Answer
- The workhorse frame:
OVER (PARTITION BY group ORDER BY sort_col)with frame clauses added only when you need running or sliding behavior. - ROW_NUMBER for dedup and top-N, LAG/LEAD for comparisons, SUM OVER for running totals — those three cover most real requests.
- Windows do not reduce rows. Filtering on a window result needs a subquery or QUALIFY — that is the pattern that unlocks everything else.
1. Deduplication: One Row Per Group
SELECT * FROM (SELECT t.*, ROW_NUMBER() OVER (PARTITION BY email ORDER BY updated_at DESC, id DESC) AS rn FROM contacts t) x WHERE rn = 1;
The tiebreaker (id DESC) makes it deterministic — without it, two contacts updated in the same second dedup arbitrarily and the "bug" moves between runs. This pattern also powers latest-record-per-group.
2. Top-N Per Group
Same skeleton, different filter: WHERE rn <= 3. Decide ties consciously — ROW_NUMBER gives exactly N rows; RANK keeps tied rows at the boundary (can exceed N). Full comparison: RANK vs ROW_NUMBER.
3. Running Total
SUM(amount) OVER (PARTITION BY account ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
Spell out the frame. With duplicate days, the default RANGE frame sums all rows of the same day at once (all pegged to the day's end total), while ROWS sums row by row — different numbers, and only one matches the intent. Also covered in the running total guide.
4. Moving Average
AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
The first six rows average fewer observations — that is correct behavior, not a bug, but label the early values in reports or require a full window with COUNT(*) OVER (...) before showing them.
5. Period-over-Period Comparison
revenue - LAG(revenue) OVER (ORDER BY month) AS mom_change, plus the percentage: ROUND(100.0 * (revenue - LAG(revenue) OVER w) / NULLIF(LAG(revenue) OVER w, 0), 1) FROM monthly WINDOW w AS (ORDER BY month);
The WINDOW clause names the spec once — cleaner than repeating OVER three times. NULLIF handles the first row and any zero-revenue month. Worked example: month-over-month growth.
6. Percent of Total
ROUND(100.0 * amount / SUM(amount) OVER (), 2) AS pct_of_total
Empty OVER() = whole result set; add PARTITION BY category for "share within its category". Watch the integer-division trap: multiply by 100.0 (float) before dividing, or cast.
7. Gap Detection in Sequences
SELECT day, LEAD(day) OVER (ORDER BY day) AS next_day FROM (SELECT DISTINCT day FROM events) d; — then compare in an outer query where next_day > day + 1. This finds missing dates without generating a spine; the spine-based alternative is in missing dates.
8. Sessionization: Group Events Into Sessions
Mark a new session when the gap since the previous event exceeds 30 minutes, then sum the markers:
WITH marked AS (SELECT user_id, ts, CASE WHEN ts - LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) > INTERVAL '30 minutes' OR LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) IS NULL THEN 1 ELSE 0 END AS is_new FROM events) SELECT user_id, ts, SUM(is_new) OVER (PARTITION BY user_id ORDER BY ts) AS session_id FROM marked;
A running SUM of 0/1 markers — the pattern generalizes to any "new group when X changes" problem (state changes, consecutive-run detection).
9. First and Last Value in Group
FIRST_VALUE(price) OVER (PARTITION BY product ORDER BY day) AS opening_price, LAST_VALUE(price) OVER (PARTITION BY product ORDER BY day ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS closing_price
The LAST_VALUE frame is mandatory: its default frame ends at the current row, so without the frame clause it returns the current price, not the final one. This is the most common LAST_VALUE bug.
10. Filtering on Window Results
Windows compute after WHERE and cannot appear in it. Two ways to filter: wrap in a subquery/CTE and filter outside (portable everywhere), or use QUALIFY (BigQuery, Snowflake, DuckDB, MySQL 8 does not have it): SELECT *, ROW_NUMBER() OVER w AS rn FROM t WINDOW w AS (PARTITION BY g ORDER BY d DESC) QUALIFY rn = 1;
The QUALIFY form is strictly cleaner where available; the subquery form is universal. Every pattern above assumes one of these two shapes when the result feeds a filter.
Try It Yourself
Describe the pattern by name — "sessionize events with a 30-minute timeout" or "top 3 products per category" — and our SQL generator expands it into the full query for your dialect, frame clauses included.