What Window Functions Do That GROUP BY Can't
GROUP BY collapses rows. If you write SELECT region, SUM(amount) FROM sales GROUP BY region, you get one row per region and you lose the individual orders forever. That is fine for a total, but useless when you need a rank per row, or 'how does this order compare to the one before it?', or a running total that shows each order plus everything that came before it. Window functions keep every row AND add a computed value. I tell beginners the difference like this: GROUP BY answers 'what is the total per group?', a window function answers 'for each row, what is the value of this calculation across my chosen window?' Once that clicks, you stop writing workarounds with self-joins and subqueries.
The syntax looks intimidating at first, but it is three pieces glued together: the function (SUM, RANK, ROW_NUMBER, LAG), the OVER keyword, and inside OVER, the PARTITION BY and ORDER BY that define your window. Every window function in this guide is built from those same pieces, so learn the pieces once and you can read any window query on the internet. Let me show you on one table you can run yourself.
Setup: One Small Sales Table to Run Every Example
Everything below uses the same table. It is deliberately small — 8 rows — so you can follow every output by hand, which is the fastest way to actually learn window functions. If you are using a free SQL playground or a local SQLite/Postgres, paste this and you are ready. This is the same style of data I use in SQL for business reports, but here the focus is the technique, not the report.
Run: CREATE TABLE sales (order_id INT, region TEXT, category TEXT, amount INT);. This gives you the four columns the examples reference. I keep the columns minimal on purpose — window functions are about rows and ordering, not schema design.
Run: INSERT INTO sales VALUES (1,'West','Software',1200),(2,'East','Software',800),(3,'West','Hardware',500),(4,'East','Hardware',700),(5,'West','Software',1500),(6,'East','Hardware',900),(7,'West','Hardware',600),(8,'East','Software',1100);. Eight rows, two regions, two categories, amounts that vary enough to make every example meaningful. You will reference this exact data in each step below.
Run: SELECT * FROM sales ORDER BY order_id;. You should see all 8 rows in order_id order. This is the 'every row, no computation' baseline. Every window function you add from here starts from this same set of rows and layers a computed column on top.
Use a playground with a visible result grid (SQLite in your browser, or Postgres with pgAdmin) while you learn window functions. Watching the computed column appear next to the row it belongs to makes the whole concept click in minutes, where reading about it takes days.
ROW_NUMBER() and RANK(): Ranking Rows
The most common window function in real reporting is ranking: 'give me the top 3 regions by revenue', 'number each order by how much it cost'. Both ROW_NUMBER() and RANK() assign a number to each row within a window, and the difference is how they handle ties. ROW_NUMBER gives every row a unique number even if values are equal. RANK gives tied rows the same number and skips the next. Which one you want depends on whether a tie should split the ranking or share it.
Run: SELECT order_id, amount, ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn FROM sales;. Each row gets a unique 1-8 rank by amount. This is your 'rank every row' workhorse — it is how you build a leaderboard, a 'top 3 per region' query, or a deduplication tool.
Run: SELECT order_id, region, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM sales;. Now the numbering restarts at 1 for each region. The West region has its own 1-4, the East its own 1-4. This is the single most useful ranking pattern in real work — 'top customer per region', 'best product per category' all use this exact shape.
Run: SELECT order_id, amount, ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn, RANK() OVER (ORDER BY amount DESC) AS rk FROM sales;. Look at the two 900 rows (order 6, order 4 if you check amounts — adjust for your data). RANK gives both the same rank and then skips; ROW_NUMBER gives them different numbers. I use RANK when the tie should be acknowledged as 'equal', and ROW_NUMBER when I literally just need a position.
A mistake I see constantly: using ROW_NUMBER to deduplicate and accidentally deleting real data because two legitimately different rows got the same ORDER BY. Before you use ROW_NUMBER as a dedupe key, run it first and eyeball the window — are the tied rows really duplicates, or just equal values on the column you sorted by? Same rule I cover in cleaning data with SQL.
LAG() and LEAD(): Compare a Row to the One Before
Here is where window functions pay for themselves. 'How much did this month's sales change from last month?' is a question GROUP BY cannot answer without a self-join. LAG(amount, 1) pulls the amount from the previous row in the window, and LEAD(amount, 1) pulls it from the next row. The result: each row carries both its own value and the previous row's value, so the month-over-month change is just a subtraction.
Run: SELECT order_id, amount, LAG(amount) OVER (ORDER BY order_id) AS prev_amount FROM sales;. Order 1 has NULL for prev_amount (there is no previous row), and every other row shows the amount of the order that came before it. The NULL on the first row is expected — you will need to decide what it means rather than treat it as an error.
Run: SELECT order_id, amount, amount - LAG(amount) OVER (ORDER BY order_id) AS change FROM sales;. Now every row shows how much it increased or decreased from the previous order. This is the exact pattern behind 'week over week revenue change' and 'did this customer spend more or less this time'. The arithmetic is trivial once LAG hands you the previous value.
Run: SELECT order_id, amount, LEAD(amount) OVER (ORDER BY order_id) AS next_amount FROM sales;. Same idea, opposite direction — the last row is NULL because there is no row after it. LEAD is rarer than LAG, but it is the tool for 'what happens after this event', like looking at the next order to see if a customer came back.
For real month-over-month analysis, partition and order by a real date: LAG(revenue) OVER (PARTITION BY region ORDER BY month). I wrote the running-totals version of this in my business reporting queries, and the LAG pattern is how you turn that from a static list into a 'here is exactly why that number moved' narrative.
SUM() OVER(): Running Totals and Moving Averages
A running total is the amount of each row plus everything before it in the window. The GROUP BY version of a cumulative sum is painful — you need a self-join or a subquery. With a window function it is one line: SUM(amount) OVER (ORDER BY order_id). Every row shows the sum up to and including itself. This is the 'cumulative revenue' chart every business report needs, and it is why I keep returning to this pattern.
Run: SELECT order_id, amount, SUM(amount) OVER (ORDER BY order_id) AS running_total FROM sales;. Order 1 shows 1200, order 2 shows 2000 (1200+800), order 3 shows 2500, and so on until the last row equals the grand total of the table. That is the running total. Notice each row keeps its own identity — this is the whole point vs GROUP BY.
Run: SELECT order_id, region, amount, SUM(amount) OVER (PARTITION BY region ORDER BY order_id) AS region_running FROM sales;. Now the running total resets for each region. The West orders accumulate to their own total, the East to theirs. This is the pattern behind 'cumulative sales per region' or 'customer lifetime spend where the customer is the partition'.
Run: SELECT order_id, amount, AVG(amount) OVER (ORDER BY order_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg FROM sales;. Each row shows the average of itself and the two rows before it — a 3-point moving average that smooths noise. The ROWS BETWEEN clause is how you control exactly which rows are in the window. This is the standard tool for smoothing a noisy daily metric into a readable trend.
When the moving average looks 'flat' in the first couple rows, that is the window shrinking, not an error — row 1 has only itself, row 2 has two rows. Decide whether you want that (it is honest) or whether you want to pad with a NULL until you have a full window using ROWS BETWEEN 2 PRECEDING AND CURRENT ROW plus a CASE. Both are defensible; just know which one you are showing.
CTEs (WITH): Make Complex Queries Readable
A CTE (common table expression) is a named subquery you write with the WITH keyword, then reference by name. It does not store anything or run early — it is a readability tool that turns a 40-line nested query into labeled steps. I have seen the exact same logic written as a spaghetti of subqueries that took me 10 minutes to decode, and as three clean CTEs I read in 30 seconds. For anything with window functions, CTEs are almost mandatory, because a window function often runs on top of another result set.
Run: WITH region_totals AS (SELECT region, SUM(amount) AS total FROM sales GROUP BY region) SELECT * FROM region_totals WHERE total > 2000;. The CTE region_totals computes the per-region total once, and the outer query filters it. Compare that to writing the GROUP BY twice, or nesting it as a subquery. Named and readable beats nested every time.
Run: WITH region_totals AS (SELECT region, SUM(amount) AS total FROM sales GROUP BY region), top_regions AS (SELECT region FROM region_totals WHERE total > 2000) SELECT * FROM top_regions;. Two CTEs, separated by commas, each building on the one before. This is how you break a complex analysis into steps you can debug one at a time — run each CTE alone to check it, then chain them. I write every non-trivial report this way.
Run: WITH ranked AS (SELECT region, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM sales) SELECT * FROM ranked WHERE rn <= 2;. The CTE computes the per-region rank, then the outer query filters to the top 2 per region. You literally cannot put the WHERE rn <= 2 inside the same query as the window function without a subquery or CTE — this is the canonical reason analysts reach for CTEs.
Combine CTEs with joins for the pattern that powers most real dashboards: a CTE that cleans or ranks the base table, then a JOIN onto a dimension table. The join rules from my joins guide apply unchanged — CTEs just give you a clean, named thing to join against instead of an anonymous subquery.
The Pattern That Puts It All Together (You'll Use This One)
Now the real-world pattern: rank orders within each region, then keep only the top order per region, with the region total next to it. It is three concepts you already learned — a CTE, a window function, and a join — and it answers a genuine business question: 'for each region, what is my best order and how much did that region do overall?' If you can write and explain this one query, you have internalized window functions and CTEs together.
Run: WITH ranked AS (SELECT order_id, region, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM sales) SELECT * FROM ranked WHERE rn = 1;. You get the single highest order per region. This is the 'top 1 per group' problem that used to require a complicated self-join — now it is a CTE plus a filter.
Run: WITH ranked AS (SELECT order_id, region, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM sales), region_totals AS (SELECT region, SUM(amount) AS total FROM sales GROUP BY region) SELECT r.order_id, r.region, r.amount, rt.total FROM ranked r JOIN region_totals rt ON r.region = rt.region WHERE r.rn = 1;. Now each region's best order shows next to the region's total — 'best order was 1500, region did 3800'. This is the shape of a real 'top performer per group with group context' dashboard query.
Say it back to yourself: the first CTE ranks orders per region, the second sums per region, the join brings them together on region, and the WHERE picks rank 1. If you can narrate that flow, you can write any window function query you will meet in a job. I have given this exact one-query explanation in interviews and it lands every time.
Before you build a window-function query, always ask: 'does GROUP BY answer this?' If it does, use GROUP BY — window functions add compute and are easier to misuse. If the question needs every row to keep its identity (a rank, a previous-row comparison, a running total), reach for a window function. Knowing when NOT to use them is half the skill.
5 Window Function Mistakes That Break Queries
These are the exact issues I see in almost every analyst's first window-function query, in rough order of how often they bite. Knowing them upfront saves you from assuming SQL is broken when the real problem is a windowing habit.
ROW_NUMBER() OVER (PARTITION BY region) without an ORDER BY is technically valid but the numbering is unpredictable — the database picks an arbitrary order. The fix: always put an ORDER BY inside OVER unless you genuinely want the default. A missing ORDER BY is the #1 source of 'my ranks look random' confusion.
WHERE rn = 1 does not work in the same query as the window function — window functions are evaluated after WHERE. The fix: compute the window in a CTE or subquery, then filter outside, exactly like the combined pattern above. This trips up everyone once.
They are different tools: GROUP BY collapses rows, PARTITION BY keeps them. If you see both in one query and the rows feel 'gone', you likely grouped when you meant to partition. The fix: decide whether you need one row per group (GROUP BY) or every row plus a computation (window function), and pick one.
LAG returns NULL for the first row in a window because there is no previous row. If you subtract it, the result is NULL, and any downstream math ignores it silently. The fix: use COALESCE(LAG(amount), 0) or an IFNULL when a NULL previous value should mean 'start from 0'.
SUM(amount) OVER (ORDER BY amount) uses a running window by default (rows up to current), not the whole column. If you expected a column-wide total, you get a running total instead. The fix: if you want the full column, add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or use a plain aggregate subquery.
Your Window Function & CTE Cheat Sheet
Here is the whole thing in one place, for when you are at work and just need the reminder. Print it or keep it open next to your SQL editor.
Function (SUM, ROW_NUMBER, RANK, LAG) + OVER + (PARTITION BY [groups] ORDER BY [order]). Master these three and you can read any window query.
ROW_NUMBER = unique number even on ties. RANK = tied rows share a number, next is skipped. Pick ROW_NUMBER for a position, RANK when ties should be acknowledged as equal.
LAG looks at the previous row (month-over-month change). LEAD looks at the next row (what happens after). Both return NULL at the edge — COALESCE it when a 0 makes sense.
SUM(amount) OVER (ORDER BY id) = running total. AVG(amount) OVER (ORDER BY id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) = 3-point moving average. Add ROWS BETWEEN to control the window.
WITH name AS (SELECT ...), second AS (SELECT ...) SELECT ... . Use a CTE whenever you need a window function's result filtered by WHERE, or to break a long query into debuggable steps. Chain with commas.
If the answer needs one row per group and no per-row computation, use GROUP BY. If it needs every row plus a computation across related rows, use a window function. Using the right tool for the question is the real skill.
