GROUP BY forces a brutal trade: to get a group's total you must give up its rows. But the best analytics questions need both at once — each order AND its customer's running total; each product AND its rank within its category. Window functions end the trade. Rows stay; context arrives.
What you'll learn
What
A window function computes a value across a set of rows related to the current row — its 'window' — without collapsing them. The OVER clause defines the window: PARTITION BY splits rows into groups, ORDER BY sequences them, and an optional frame narrows which neighbors count.
Why
Rankings, month-over-month deltas, running totals, share-of-group percentages, deduplication — the workhorse queries of analytics — are either impossible or grotesque with GROUP BY and self joins, and one clean line with a window function. They are the single highest-leverage 'advanced' SQL feature and a fixture of every data-science interview loop.
Where it's used
Top-N-per-group reports, cohort retention matrices, growth dashboards (MoM/WoW deltas), sessionization, and the ROW_NUMBER dedup idiom you met in the CTE lesson.
Where this runs in production
Daily Top 50 per market is RANK() OVER (PARTITION BY market ORDER BY streams DESC); listener streak features compare days with LAG.
Every product page's category rank is a partitioned ranking over rolling sales — recomputed at warehouse scale with window functions.
Month-over-month revenue change per merchant is LAG(revenue) OVER (PARTITION BY merchant ORDER BY month) subtracted from the current month.
Picture students seated by classroom (partitions), each holding their score. GROUP BY is the principal announcing one average per classroom — students go home, only averages remain. A window function is each student glancing around their OWN classroom and writing extra facts on their own sheet: 'my rank in this room', 'average of my room', 'score of the student ahead of me'. Every student keeps their sheet; every sheet gains context.
GROUP BY: n rows in, one row per group out. Window function: n rows in, n rows out — each row gains a computed column. That's the entire mental model. When a stakeholder asks for detail AND summary in the same table ('each order with its customer's total'), the answer is a window, not a join back to a grouped subquery.
SELECT
order_id, customer_id, amount,
SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
ROUND(100.0 * amount / SUM(amount) OVER (PARTITION BY customer_id), 1)
AS pct_of_customer
FROM orders;SUM as a window function: every order row survives, annotated with its customer's total and its share of it. The same aggregate you know from GROUP BY, redirected by OVER. Note 100.0 — the decimal literal prevents integer division.
All three number rows within a partition by the window ORDER BY, differing only on ties. ROW_NUMBER: arbitrary unique numbers, ties broken unpredictably (1,2,3,4) — use for dedup and strict top-N. RANK: ties share a number, next rank skips (1,2,2,4) — 'Olympic ranking'. DENSE_RANK: ties share, no gaps (1,2,2,3) — use when ranks must stay contiguous. Interviews love this distinction; remember one tied example and you can always re-derive it.
SELECT category, product_name, revenue, ROW_NUMBER() OVER w AS row_num, RANK() OVER w AS rnk, DENSE_RANK() OVER w AS dense FROM product_sales WINDOW w AS (PARTITION BY category ORDER BY revenue DESC);
With revenues 500, 300, 300, 200 in one category: row_num = 1,2,3,4; rnk = 1,2,2,4; dense = 1,2,2,3. The WINDOW clause names a window once and reuses it — the DRY option when several functions share one definition (PostgreSQL, MySQL 8, SQLite).
Window functions evaluate after WHERE, GROUP BY, and HAVING — so WHERE rn = 1 next to the ROW_NUMBER that defines rn is an error. The idiom from the CTE lesson is the law: compute the window in a CTE (or subquery), filter one level up. Corollary: window functions may not appear inside WHERE or HAVING at all; only in SELECT and ORDER BY.
One clause, three dials. Click each part to see what it controls and which functions need it.
Show each order with its customer's total spend beside it.
Three orders in, three rows out — GROUP BY would have returned two. Customer 10's both rows carry 300.00; the partition boundary keeps customer 12's 260.00 uncontaminated. No ORDER BY inside OVER, so the SUM covers the whole partition, not a running slice.
customer_id | order_id | amount | customer_total
-------------+----------+--------+----------------
10 | 1 | 100.00 | 300.00
10 | 2 | 200.00 | 300.00
12 | 4 | 260.00 | 260.00
(3 rows)Your task
The sales team wants each region's reps ranked by deals closed, keeping only each region's #1. From rep_sales(rep_name, region, deals): rank reps within their region by deals descending (ties broken by rep_name ascending), then return region, rep_name, deals for the top rep per region, sorted by region. Fill in the blanks.
region | rep_name | deals --------+----------+------- EU | Anna | 14 US | Cara | 9 (2 rows)
Write your solution in the editor on the right, then hit Run.
A table has 1,000 rows. SELECT …, SUM(x) OVER (PARTITION BY region) FROM t returns how many rows?
Values 100, 80, 80, 60 ranked descending: which function produces 1, 2, 2, 3?
What does LAG(revenue) OVER (ORDER BY month) return for the FIRST month?
What is the difference between SUM(x) OVER (PARTITION BY g) and SUM(x) OVER (PARTITION BY g ORDER BY d)?
Why, and what is the fix?
From daily_sales(sale_date, revenue), produce sale_date, revenue, a running total (running_revenue), and the day-over-day change (delta, NULL on the first day). Order by sale_date.
Explain the difference between ROW_NUMBER, RANK, and DENSE_RANK. When does the choice actually matter?
All three number rows within a partition according to the window's ORDER BY and agree completely when there are no ties; they differ only on tie handling. ROW_NUMBER assigns unique consecutive integers, breaking ties arbitrarily unless the ORDER BY includes a unique tie-breaker. RANK gives tied rows the same number and then skips — 1, 2, 2, 4 — so a rank tells you how many rows beat you. DENSE_RANK gives ties the same number without gaps — 1, 2, 2, 3 — so it counts distinct value levels. The choice matters in three situations: strict top-N-per-group and deduplication need ROW_NUMBER, because exactly one row must survive per rank; leaderboards where 'two silver medals mean no bronze' need RANK; and 'give me the top 3 price points' needs DENSE_RANK, since it ranks values rather than rows. In any production ROW_NUMBER I also add a unique tie-breaker to the ORDER BY so reruns are deterministic.
A query needs each order alongside its customer's total and percentage share. Compare the window-function approach with the join-back-to-aggregate approach.
The legacy pattern computes a grouped subquery — customer totals via GROUP BY — and joins it back to orders on customer_id, then divides. It works, but it states the intent twice (once grouped, once joined), scans conceptually twice, and every new group-level metric means widening the subquery and the join. The window version annotates in place: SUM(amount) OVER (PARTITION BY customer_id) on the orders scan itself, with the share as amount divided by that expression — one scan, no join keys to mismatch, and adding AVG or COUNT context is one more OVER column. Semantically they're identical when the join key is clean; the join version has an extra failure mode if customer_id has NULLs (NULL never joins back, silently dropping those orders, whereas PARTITION BY groups NULLs together). Modern optimizers execute the window efficiently with a sort per partition spec. My default is the window; the join-back survives mainly in engines or tools that predate window support.
How would you compute month-over-month revenue growth per merchant, and what edge cases do you handle?
Core query: LAG(revenue) OVER (PARTITION BY merchant_id ORDER BY month) gives each merchant-month its predecessor; delta is the difference and percent growth divides that by the LAG value. Edge cases are most of the real work. First month per merchant: LAG is NULL, so growth is NULL — I keep it NULL rather than defaulting to 0, because 'no prior month' isn't 'no growth'. Gap months: if a merchant has no March row, April's LAG returns February — misleading; the fix is densifying the calendar first (cross join months × merchants, left join actuals, COALESCE revenue to 0) so LAG always means 'previous calendar month'. Zero-revenue previous month: percent growth divides by zero — I NULLIF the denominator and let the dashboard render '∞/new'. Duplicate merchant-month rows would corrupt LAG ordering, so the pipeline enforces uniqueness upstream. This 'densify, then window' pattern is the professional standard for any time-series delta in SQL.
Common Mistakes to Avoid
1) Filtering a window column in the same SELECT's WHERE — it doesn't exist yet; rank in a CTE, filter outside. 2) Forgetting ORDER BY inside OVER for a running total, getting the flat partition total on every row instead. 3) Using RANK for strict top-N-per-group — ties smuggle in extra rows; ROW_NUMBER with a tie-breaker guarantees exactly N. 4) LAG over a time series with gap months — 'previous row' isn't 'previous month'; densify the calendar first. 5) Non-deterministic ROW_NUMBER from an ORDER BY with ties and no unique tie-breaker — the dedup keeps a different row on every run.
Ask the AI Tutor
Try these prompts in the AI Tutor panel: • 'ELI5: OVER and PARTITION BY with a classroom analogy of your own.' • 'Quiz me: tied values, I give ROW_NUMBER/RANK/DENSE_RANK outputs.' • 'Trace a running total row by row and show what removing ORDER BY changes.' • 'Show me the gap-month LAG bug and the calendar-densify fix.' • 'Interview mode: top-2-per-category live, then critique my tie handling.'
Glossary
Window function — computes over rows related to the current row without collapsing them. OVER — the clause defining the window; empty OVER () spans the whole result. PARTITION BY — splits rows into independent windows. Window ORDER BY — sequences rows within a partition; activates cumulative frames for aggregates. Frame — the sub-range of the partition an aggregate sees (ROWS/RANGE BETWEEN …). Running total — ordered SUM with the default cumulative frame. Moving average — AVG over an explicit sliding ROWS frame. ROW_NUMBER / RANK / DENSE_RANK — the ranking trio: unique / gapped ties / dense ties. NTILE(n) — deals rows into n near-equal buckets. LAG / LEAD — value from a preceding / following row, with optional default. WINDOW clause — names a window definition for reuse across functions. Densify — filling calendar gaps so LAG means 'previous period', not 'previous existing row'.
Recommended Resources
• Docs: PostgreSQL manual — 'Window Functions' tutorial chapter, then the frame-clause reference in SELECT. • Read: 'SQL Window Functions Explained' on Mode's blog for chart-driven intuition; LeetCode's database section for top-N-per-group drills. • Practice: on any sales-like table, write the four archetypes — group total per row, top-2-per-group, MoM delta, 7-row moving average — until the OVER grammar types itself. • Next in DSM: windows compute across rows — CASE Statements compute within one, turning conditions into columns and powering the pivot-style counting you'll combine with everything learned here.
Recap
✓ Window functions attach group context to every row — n rows in, n rows out — where GROUP BY would collapse. ✓ OVER has three dials: PARTITION BY (split), ORDER BY (sequence; turns aggregates cumulative), frame (which neighbors). ✓ The ranking trio differs only on ties: ROW_NUMBER unique, RANK gapped, DENSE_RANK dense — strict top-N wants ROW_NUMBER plus a tie-breaker. ✓ LAG/LEAD read neighboring rows for deltas and gaps; densify calendars so 'previous row' means 'previous period'. ✓ Running totals are ordered SUMs; moving averages spell out a ROWS frame. ✓ Window results can't be filtered in the same SELECT — compute in a CTE, filter one level up. Next up: CASE Statements. Window functions gave rows context from their neighbors — CASE gives them logic of their own: conditional columns, custom sort keys, and the SUM(CASE WHEN …) trick that pivots categories into columns.
Run your code to see the output here.