# SQL Window Functions > The analyst superpower: running totals, rankings, and row-to-row comparisons without collapsing rows. OVER, PARTITION BY, and lag and lead. --- # SQL Window Functions You know how to `GROUP BY`. But the moment a question is "what's each row's rank within its group?" or "how does this row compare to the one before it?" or "show me a running total alongside every line," `GROUP BY` lets you down - it crushes your rows into summaries and throws away the detail you wanted to keep. Window functions are the fix: they run the same kind of math across a set of related rows but leave every row standing, with its answer attached. This is the feature that separates people who *query* data from people who *analyze* it. Once it clicks, a whole class of "I'd have to do that in a spreadsheet" problems becomes one line of SQL. ## How to read this Read the three phases in order - they build on each other. Phase 1 gives you the mental model that makes everything else obvious: a window function adds a column without removing a row. Phase 2 is the working core you'll use daily. Phase 3 is the patterns that look like magic until you've seen them once. Run the SQL examples in any database that supports windows (PostgreSQL, SQLite 3.25+, MySQL 8+, SQL Server, BigQuery, DuckDB) - nearly all of them now. ## The phases 1. [The window, not the group](01-the-window-not-the-group.md) - what a window function actually is, and why it doesn't collapse rows 2. [OVER, PARTITION BY, ORDER BY](02-over-partition-order.md) - the everyday core: ranking, running totals, comparing to the previous row 3. [Frames, moving averages, and top-N-per-group](03-frames-and-top-n.md) - the deeper payoff and the patterns that earn their keep --- # The window, not the group Picture the wall you keep hitting. You have a table of sales, one row per order, and your boss asks three questions in a row: - "What was each salesperson's total?" - fine, `GROUP BY` handles that. - "Now show me *every* order, but next to each one, that salesperson's running total so far." - and you freeze. The second question is different in kind: you still want all the orders on screen, and you also want a calculation that spans many rows. `GROUP BY` can't do both at once, because doing the calculation is exactly how it destroys the rows. ## What GROUP BY actually does to your rows `GROUP BY` is a meat grinder. Many rows go in; one row per group comes out. That's the whole point of it, and it's the right tool when you genuinely want a summary. ```sql SELECT salesperson, SUM(amount) AS total FROM sales GROUP BY salesperson; ``` ```text salesperson | total ------------+------ Ana | 900 Ben | 500 ``` *What just happened:* six order rows became two summary rows. The individual orders - their dates, amounts, IDs - are gone. You can't ask "what was Ana's third order?" anymore, because there's no third order in the result. The detail was the price of the summary. That tradeoff is fine until you need the detail *and* the math together. Then you need a different tool. ## What a window function does instead A window function does the same family of math - `SUM`, `COUNT`, `AVG`, ranking, comparisons - but it computes each row's answer by looking at a *window* of related rows, and then it **writes the answer onto that row and keeps the row**. Nothing collapses. The row count of your result is the row count you started with. ```sql SELECT salesperson, amount, SUM(amount) OVER (PARTITION BY salesperson) AS person_total FROM sales; ``` ```text salesperson | amount | person_total ------------+--------+------------- Ana | 400 | 900 Ana | 300 | 900 Ana | 200 | 900 Ben | 250 | 500 Ben | 250 | 500 ``` *What just happened:* every original order is still here - five rows in, five rows out. But each row now carries its salesperson's total in a new column. The `SUM` looked across each person's whole window and stamped the answer onto every row in it. That single word - `OVER` - is the line between grouping and windowing: it tells SQL not to fold the rows away, just compute over them. > The mental model in one sentence: **`GROUP BY` removes rows to make a summary; a window function adds a column without removing a row.** ## The window is "the rows related to this one" The word *window* is doing real work. For each row, SQL opens a window onto a set of other rows, and you decide which rows belong in it. In the example above, the window for any Ana row was "all the Ana rows," because we said `PARTITION BY salesperson`. The function ran over that window and reported back. That's the entire idea. The rest of this guide is about controlling the window precisely: - Which rows share a window? (`PARTITION BY`) - In what order does the window count? (`ORDER BY` - this is what makes a *running* total run) - How wide is the window around the current row? (the frame - phase 3) Get those three knobs right and you can answer almost any "compare across rows but keep the rows" question. ## Why analysts care so much about this Before window functions were widely supported, these questions were genuinely painful to answer: self-joins that ran slowly and read like a riddle, or exporting to a spreadsheet and dragging formulas. Rankings, running totals, "compared to last month," "top 3 per category" - all of it was awkward. Window functions turned a category of hard problems into readable, single-pass SQL. For builders: this matters beyond reporting. Deduplication ("keep the newest row per key"), sessionization ("group events into sessions"), and gap detection ("find the missing sequence numbers") are all window-function patterns hiding in everyday application data work. If you've ever written a tangled subquery to keep "the latest record per user," phase 3 has a cleaner answer waiting. A window function and a `GROUP BY` are not rivals - they answer different questions. Want a smaller table of summaries? Group. Want your full table with extra computed columns? Window. Knowing which question you're being asked is half the skill. ```quiz [ { "q": "What is the key difference between GROUP BY and a window function?", "choices": [ "Window functions are faster than GROUP BY in every case", "GROUP BY collapses rows into summaries; a window function keeps every row and adds a computed column", "Window functions can only compute SUM, while GROUP BY can do AVG and COUNT", "GROUP BY works on numbers; window functions work on text" ], "answer": 1, "explain": "GROUP BY reduces many rows to one per group. A window function computes over related rows but leaves every original row in place." }, { "q": "Which clause signals that you want a calculation done over a window rather than collapsing rows?", "choices": ["GROUP BY", "HAVING", "OVER", "DISTINCT"], "answer": 2, "explain": "OVER is the keyword that turns an aggregate into a window function. It tells SQL to compute across related rows without folding them away." }, { "q": "You run SELECT amount, SUM(amount) OVER (PARTITION BY person) FROM sales on a 5-row table. How many rows come back?", "choices": ["1", "One per person", "5", "It depends on the SUM value"], "answer": 2, "explain": "Window functions never reduce the row count. Five rows in means five rows out, each with the windowed sum attached." } ] ``` --- # OVER, PARTITION BY, ORDER BY Phase 1 gave you the idea. Now let's make it muscle memory. Almost every window function you'll ever write is the same shape: ```text some_function(...) OVER ( PARTITION BY ORDER BY ) ``` Three parts: the function on the outside, `PARTITION BY` to slice your data into independent groups, `ORDER BY` to give the rows a sequence inside each slice. You can use one, both, or neither - each combination unlocks a different question. Let's build it up one piece at a time, all runnable against the same little table. ## PARTITION BY - "do this separately for each group" `PARTITION BY` is the windowed cousin of `GROUP BY`: it splits the rows into groups, and the function runs independently inside each one - but the rows stay. Leave it out and the whole result is one big window. ```sql runnable WITH sales(salesperson, region, amount) AS ( VALUES ('Ana', 'East', 400), ('Ana', 'East', 300), ('Ben', 'East', 250), ('Cy', 'West', 600), ('Cy', 'West', 100) ) SELECT salesperson, region, amount, SUM(amount) OVER () AS grand_total, SUM(amount) OVER (PARTITION BY region) AS region_total FROM sales; ``` ```text salesperson | region | amount | grand_total | region_total ------------+--------+--------+-------------+------------- Ana | East | 400 | 1650 | 950 Ana | East | 300 | 1650 | 950 Ben | East | 250 | 1650 | 950 Cy | West | 600 | 1650 | 700 Cy | West | 100 | 1650 | 700 ``` *What just happened:* `OVER ()` with empty parens treats the entire table as one window - every row gets the same grand total, 1650. Add `PARTITION BY region` and the window narrows to each region, so East rows get 950 and West rows get 700. Same function, different window, and not a single row was lost. You can now show each amount *next to* its share of the regional total - something `GROUP BY` could never hand you in one query. ## ORDER BY inside OVER - this is what makes a total "run" Here's the part that surprises people: inside `OVER`, adding `ORDER BY` changes the *meaning* of an aggregate. Without it, `SUM` covers the whole window. *With* it, `SUM` covers "everything from the start of the window up to and including this row" - a **running total**. ```sql runnable WITH sales(salesperson, day, amount) AS ( VALUES ('Ana', 1, 400), ('Ana', 2, 300), ('Ana', 3, 200), ('Ben', 1, 250), ('Ben', 2, 250) ) SELECT salesperson, day, amount, SUM(amount) OVER (PARTITION BY salesperson ORDER BY day) AS running_total FROM sales; ``` ```text salesperson | day | amount | running_total ------------+-----+--------+-------------- Ana | 1 | 400 | 400 Ana | 2 | 300 | 700 Ana | 3 | 200 | 900 Ben | 1 | 250 | 250 Ben | 2 | 250 | 500 ``` *What just happened:* within each salesperson's window, the rows are now ordered by day, and `SUM` accumulates as it goes: 400, then 400+300, then +200. When the partition switches to Ben, the running total resets, because Ben is a separate window. That single `ORDER BY day` is the difference between "Ana's total" and "Ana's total *so far*" - the single most useful trick in the whole feature. > The rule to memorize: an aggregate `OVER` with no `ORDER BY` covers the **whole window**; add `ORDER BY` and it covers **the start through the current row**. Same function, two completely different answers. ## Ranking - ROW_NUMBER, RANK, DENSE_RANK Ranking functions need an order to rank by, so they always pair with `ORDER BY` inside `OVER`. The three you'll reach for look similar but differ on exactly how they treat ties. ```sql runnable WITH scores(player, points) AS ( VALUES ('Ana', 90), ('Ben', 90), ('Cy', 80), ('Dee', 70) ) SELECT player, points, ROW_NUMBER() OVER (ORDER BY points DESC) AS row_num, RANK() OVER (ORDER BY points DESC) AS rnk, DENSE_RANK() OVER (ORDER BY points DESC) AS dense_rnk FROM scores; ``` ```text player | points | row_num | rnk | dense_rnk -------+--------+---------+-----+---------- Ana | 90 | 1 | 1 | 1 Ben | 90 | 2 | 1 | 1 Cy | 80 | 3 | 3 | 2 Dee | 70 | 4 | 4 | 3 ``` *What just happened:* Ana and Ben tie at 90 points. `ROW_NUMBER` ignores the tie and assigns 1 and 2 arbitrarily - it just numbers rows. `RANK` gives them both 1, then *skips* to 3 (it leaves a gap the size of the tie). `DENSE_RANK` gives them both 1 but does *not* skip, so the next value is 2. Pick by intent: `ROW_NUMBER` when you need one unique number per row ("keep exactly one"), `RANK` when ties should share a place and gaps are fine ("joint-1st, next person is 3rd"), `DENSE_RANK` when you want tiers with no gaps. ## LAG and LEAD - reach into the previous or next row `LAG` pulls a value from a row *before* the current one; `LEAD` pulls from a row *after*. This is how you compare a row to its neighbor - yesterday vs. today, this order vs. the last one - without a self-join. ```sql runnable WITH revenue(month, amount) AS ( VALUES (1, 1000), (2, 1200), (3, 1100), (4, 1500) ) SELECT month, amount, LAG(amount) OVER (ORDER BY month) AS prev_month, amount - LAG(amount) OVER (ORDER BY month) AS change_vs_prev FROM revenue; ``` ```text month | amount | prev_month | change_vs_prev ------+--------+------------+--------------- 1 | 1000 | | 2 | 1200 | 1000 | 200 3 | 1100 | 1200 | -100 4 | 1500 | 1100 | 400 ``` *What just happened:* `LAG(amount)` reaches one row back (in `month` order) and hands you the previous month's amount. Subtract and you've got month-over-month change in a single, readable line. Month 1 has no prior row, so `LAG` returns `NULL` and the subtraction is `NULL` too - that empty first cell is expected, not a bug. `LEAD` works identically but looks forward; swap it in for "the next row's value." You can also pass an offset and a default, like `LAG(amount, 1, 0)`, to look back further or replace that `NULL` with 0. For builders: `LAG`/`LEAD` are the natural tool for detecting state changes in event logs - "did this row's status differ from the previous row's?" - and for measuring gaps between timestamps, like the time between a user's consecutive actions. The order you put in `OVER (ORDER BY ...)` *is* your definition of "previous," so choose it deliberately. Window functions also relate to joins: a self-join was the old way to compare a row to its neighbor, and window functions replace most of that. If joins are still shaky, [/guides/sql-joins-explained](/guides/sql-joins-explained) is worth a detour first. ```quiz [ { "q": "Inside OVER(...), what does adding ORDER BY do to a SUM aggregate?", "choices": [ "Nothing - ORDER BY only affects the final result order", "It turns the total into a running total: start of the window through the current row", "It removes duplicate rows before summing", "It makes the SUM cover the next row instead of the current one" ], "answer": 1, "explain": "With no ORDER BY, an aggregate covers the whole window. Add ORDER BY and it accumulates from the window's start up to the current row - a running total." }, { "q": "Players score 90, 90, 80. Which function gives them ranks 1, 1, 2 (no gap)?", "choices": ["ROW_NUMBER", "RANK", "DENSE_RANK", "NTILE"], "answer": 2, "explain": "DENSE_RANK shares the rank for ties and does not skip, so after two 1st-places the next is 2. RANK would skip to 3; ROW_NUMBER never ties." }, { "q": "You want each row to show the previous row's value (by date) without a self-join. Which function?", "choices": ["LEAD", "LAG", "FIRST_VALUE", "RANK"], "answer": 1, "explain": "LAG reaches backward to an earlier row in the window's order. LEAD reaches forward; LAG is the one for 'the previous row's value'." } ] ``` --- # Frames, moving averages, and top-N-per-group You can already do most of what people need. This phase is the deeper layer: controlling exactly how wide the window is around each row (the *frame*), which unlocks moving averages - and the single most-reached-for pattern in real analytics work, **top-N-per-group**. It's also where the two classic gotchas live, so we'll name them clearly. ## The frame: the window inside the window Here's a subtlety phase 2 glossed over. When you write `SUM(...) OVER (ORDER BY day)`, what exactly is the window? By default, with an `ORDER BY` present, it's *"every row from the start of the partition up to the current row"* - why you got a running total. That default has a formal name: ```text RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ``` The **frame** is the slice of the window the function actually operates on, relative to the current row. You usually let the default ride - but when you want a *moving* window ("the last 3 rows," not "everything so far"), you spell the frame out yourself with `ROWS BETWEEN`. ```sql runnable WITH t(day, amount) AS ( VALUES (1, 10), (2, 20), (3, 30), (4, 40), (5, 50) ) SELECT day, amount, AVG(amount) OVER ( ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ) AS moving_avg_3 FROM t; ``` ```text day | amount | moving_avg_3 ----+--------+------------- 1 | 10 | 10.0 2 | 20 | 15.0 3 | 30 | 20.0 4 | 40 | 30.0 5 | 50 | 40.0 ``` *What just happened:* `ROWS BETWEEN 2 PRECEDING AND CURRENT ROW` defines a frame of "this row plus the two before it" - a 3-row sliding window. So day 3's average is (10+20+30)/3 = 20, and day 4's is (20+30+40)/3 = 30. At the start the frame is short (day 1 only has itself, day 2 has two rows), so those averages cover fewer points. That's a moving average, the workhorse of smoothing noisy time series - and a frame is what makes it possible. > `ROWS` counts physical rows; `RANGE` counts by the `ORDER BY` *value* (so tied values share a frame). For most "last N rows" jobs you want `ROWS`. Reach for `RANGE` when you mean "everything within the same date," not "the last N records." ## Top-N-per-group: the pattern you'll use forever This is the one. "The top 3 products per category." "Each customer's most recent order." "The highest-paid employee in each department." Every one of these is the same shape, and window functions turn it into almost a template. The trick: you can't filter on a window function in `WHERE` (more on why in a moment), so you compute the rank in a subquery or CTE, then filter on it in the outer query. ```sql runnable WITH sales(product, category, revenue) AS ( VALUES ('Widget', 'Tools', 500), ('Gizmo', 'Tools', 300), ('Gadget', 'Tools', 200), ('Apple', 'Food', 900), ('Banana', 'Food', 400), ('Cherry', 'Food', 100) ), ranked AS ( SELECT product, category, revenue, ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) AS rn FROM sales ) SELECT product, category, revenue FROM ranked WHERE rn <= 2; ``` ```text product | category | revenue --------+----------+-------- Widget | Tools | 500 Gizmo | Tools | 300 Apple | Food | 900 Banana | Food | 400 ``` *What just happened:* inside the `ranked` CTE, `ROW_NUMBER` numbers each category's products from highest revenue down - and because we `PARTITION BY category`, the numbering restarts at 1 for each category. The outer query then keeps only `rn <= 2`, giving the top 2 per category, *as full rows with all their columns intact*. Want top 3? Change one number. Want each customer's single newest order? `PARTITION BY customer ORDER BY order_date DESC` and keep `rn = 1`. This one pattern replaces a whole genre of gnarly correlated subqueries. One design choice worth naming: `ROW_NUMBER` here means "exactly N rows even if there are ties." If you'd rather *include* ties for the cutoff (three products tied for 3rd all make the cut), swap in `RANK` - the structure is identical, only the ranking function changes. ## The two gotchas that trip everyone **1. You can't use a window function in `WHERE` or `GROUP BY`.** Try `WHERE ROW_NUMBER() OVER (...) <= 2` and you'll get an error. This isn't an arbitrary restriction - it's about *ordering of operations*. SQL evaluates `WHERE` to decide which rows exist *before* it computes window functions, because windows operate over the surviving rows: the window literally hasn't been calculated yet when `WHERE` runs. The fix is the pattern above: compute the window in a subquery/CTE, then filter in the outer query. (`QUALIFY`, available in Snowflake, BigQuery, and DuckDB, is a shorthand for exactly this - but the CTE works everywhere.) **2. `COUNT(*) OVER (ORDER BY x)` is not the total count.** People expect it to return the number of rows in the partition. But with `ORDER BY` present, the default frame is "start through current row," so it gives a *running count* - 1, 2, 3, ... - not the total. If you want the partition total, drop the `ORDER BY` (or write an explicit full frame). Same trap as the running-sum behavior from phase 2, and it bites people who only meant to add an order for readability. ```text -- running count (probably not what you wanted): COUNT(*) OVER (PARTITION BY category ORDER BY revenue) -- total count for the whole category: COUNT(*) OVER (PARTITION BY category) ``` *What just happened:* the only difference is the `ORDER BY`, and it silently changes the answer from "total" to "running tally." When a windowed count or sum looks wrong, the `ORDER BY` is the first thing to check. ## Where this leaves you You now have the full kit: `PARTITION BY` to slice, `ORDER BY` to sequence, frames to size the window, ranking to order, `LAG`/`LEAD` to compare neighbors, and the top-N-per-group template to keep the best rows per group. That covers the overwhelming majority of analytical SQL you'll ever write. For builders: these patterns scale down to application code beautifully. Deduplication is top-N-per-group with `rn = 1`. Sessionization uses `LAG` on timestamps to detect gaps, then a running `SUM` of "is this a new session?" flags to assign session IDs. Gap-and-island detection - finding runs of consecutive values - is built entirely from `ROW_NUMBER` arithmetic. The same six ideas keep reappearing. To see where this fits in the larger picture of moving and shaping data at scale, [/guides/what-is-data-engineering](/guides/what-is-data-engineering) is the natural next stop. The plain summary: window functions feel like a separate, intimidating corner of SQL until you internalize one sentence from phase 1 - *add a column without removing a row* - and one rule from phase 2 - *`ORDER BY` inside `OVER` turns "the whole window" into "the window so far."* Everything else is variations on those two ideas. ```quiz [ { "q": "Why can't you filter on a window function directly in WHERE (e.g. WHERE ROW_NUMBER() OVER(...) <= 3)?", "choices": [ "Window functions are too slow to use in WHERE", "WHERE is evaluated before window functions are computed, so the value doesn't exist yet", "It's allowed in every database; the syntax is just unusual", "WHERE can only compare literal values" ], "answer": 1, "explain": "SQL filters rows with WHERE before computing window functions over the survivors. Compute the window in a CTE/subquery, then filter in the outer query." }, { "q": "ROWS BETWEEN 2 PRECEDING AND CURRENT ROW with AVG produces what?", "choices": [ "The average of the entire partition", "The average of the current row and the two rows before it (a 3-row moving average)", "The average of the next two rows", "The average of only the current row" ], "answer": 1, "explain": "That frame is 'this row plus the two before it' - a sliding 3-row window, which is exactly how you build a moving average." }, { "q": "To keep the single most recent order per customer, you'd use ROW_NUMBER() OVER (PARTITION BY customer ORDER BY order_date DESC) and then filter on...", "choices": ["rn >= 1", "rn = 1", "rn <= customer", "the highest order_date in WHERE"], "answer": 1, "explain": "Numbering each customer's orders newest-first means rn = 1 is the most recent. Filter rn = 1 in the outer query to keep one row per customer." } ] ```