In 04-05 we left a sentence hanging: the top N per group and "any computation that has to aggregate without collapsing the rows" need window functions. This is the problem. SUM(amount) over GreenStore's 47 lines returns one row with €727.95; but what you almost always want is the 47 lines, each with its amount and, next to it, the total — so you can work out the percentage it represents, its position in a ranking or how much you've accumulated so far.

A GROUP BY can't do it: it collapses exactly what you want to keep. The OVER clause can, and it's the difference between knowing SQL and knowing how to use it. In this lesson you'll see the anatomy of OVER (PARTITION BY ... ORDER BY ... frame), where a window function runs and why that makes it impossible to filter on it in the WHERE —the single most frequent mistake there is—, the three families with their reference table, the frame and the classic LAST_VALUE trap, and six real GreenStore cases: monthly running total, moving average, change against the previous month, top N per category, customer ranking and each product against its category's average.

1. The core idea: aggregate without collapsing
2. The anatomy of OVER
3. Where it runs: the WHERE mistake
4. Ranking: ROW_NUMBER, RANK, DENSE_RANK, NTILE, PERCENT_RANK
5. Offset: LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE
6. Aggregates as windows, and the frame: ROWS versus RANGE
7. WINDOW: giving a window a name
8. The GreenStore cases
9. Performance and dialect
10. Common Mistakes and Tips
11. Exercises
12. Conclusion

A window function computes a value for each row from a set of rows related to it —its window— without reducing the number of rows in the result.

The smallest possible form is OVER (), the empty window: "every row in the result".

(First 3 of 47 rows.) The 47 lines are still there, and each one carries the €727.95 total and its percentage stuck to it. With GROUP BY this is impossible: either you have the detail, or you have the total. Here you have both.

And that's the comparison to fix in your head before going on:

The general form is function(args) OVER (PARTITION BY expr ORDER BY expr ROWS/RANGE ...), with three pieces: PARTITION BY says which groups it's split into (without it, everything is one group), ORDER BY in what order each group is walked, and the frame which slice of the group goes into the computation.

The three parts are optional and independent, and each combination means something different:

The detail that surprises everybody: adding ORDER BY to a window changes the result of a SUM, because it switches on a default frame running from the beginning to the current row. Without ORDER BY, SUM gives the group total; with it, it gives the running total. It isn't a bug: it's the gateway to section 6's frame.

Window functions are evaluated almost last: FROM → WHERE → GROUP BY → HAVING → window functions → SELECT → DISTINCT → ORDER BY → LIMIT. Two important facts follow from that. The first: a window function only sees the rows that survived the WHERE. If you filter by country = 'France', the SUM(...) OVER () will be France's total, not the shop's. The second is the number-one mistake in this lesson:

And it isn't an arbitrary limitation: when the WHERE is evaluated, the window function hasn't been computed yet, because it needs to know which rows pass the filter. It would be circular. The solution is always the same —compute at one level and filter at the next— and with CTEs (10-02) it reads perfectly:

Remember it like this: you don't filter a window function, you wrap it. The same goes for the HAVING and for JOINs.

GreenStore has a perfect tie to see it with: the rice and the tomato, with 14 units sold each.

(First 5 of 17 rows; the sixth, the bamboo toothbrush with 9 units, gets 6, 4 and 3; the seventh, Spelt pasta with 7 units, gets 7, 7 and 4: the 17 products that have been sold at least once.) Read it column by column and you won't forget it. ROW_NUMBER numbers 1, 2, 3, 4, 5, 6, 7: it never repeats, and for that you need an explicit tie-breaker (, p.id) or the choice is arbitrary and can change between runs. RANK gives 1, 1, 3, 4, 4, 4, 7: tied rows share a position and the next one jumps as many places as there were ties; it's the sporting podium. DENSE_RANK gives 1, 1, 2, 3, 3, 3, 4: they share a position with no gaps, so it's "the second best value", not "the second".

Which to use: ROW_NUMBER to pick one row per group (deduplicating, top 1); RANK for a publishable ranking; DENSE_RANK when what you're numbering is distinct values, not rows.

These functions look at another row in the same window. Over GreenStore's monthly series:

(First 3 of 12 rows.) The three arguments to LAG and LEAD are (column, offset, default_value): by default the offset is 1 and the default value is NULL —hence the *(null)* in the first month—, whereas LAG(revenue, 2, 0) looks two rows back and returns 0 when there's no such row. That third argument saves you a COALESCE and, more importantly, stops a subtraction turning into NULL.

FIRST_VALUE, LAST_VALUE and NTH_VALUE(expr, n) return the value from the first, the last and the nth row of the frame. Over this same series and with the full frame, FIRST_VALUE gives €68.80 (March 2025), LAST_VALUE gives €49.33 (February 2026) and NTH_VALUE(revenue, 3) gives €58.85 on all twelve rows. That "with the full frame" isn't a detail: the end of section 6 explains why without it the result is different.

Every aggregate function from module 4 accepts OVER: SUM, AVG, COUNT, MIN, MAX, STRING_AGG, and also the ones that carry a FILTER. The syntax is identical; the only thing that changes is that the result is stuck onto each row instead of collapsing it.

(3 of the 8 employees —Beatriz, €39,500, is missing from 3rd place—; the running column closes at €280,300.00, the total payroll.) The course's canonical figures —sum €280,300, average €35,037.50— show up here next to each employee, and the business reading is immediate: Daniel earns €37.50 less than the company's exact average, and the top three salaries eat up more than half the payroll.

The frame defines which rows of the group go into the computation for each specific row. You write it ROWS BETWEEN start AND end or RANGE BETWEEN start AND end, with these endpoints:

And the difference between the two keywords is exactly this: ROWS counts physical rows —2 PRECEDING is the two rows above, whatever they're called— whereas RANGE counts ORDER BY values: every row with the same sort value as the current one is a peer and they come in or go out together.

(First 4 of 17 rows.) RANGE gives 28 to both tied rows —it adds them together, because they're worth the same— and ROWS gives 14 and 28, advancing row by row. The three products on 9 units repeat the pattern: 67, 67, 67 with RANGE; 49, 58, 67 with ROWS.

The default frame, when there's an ORDER BY and you write no frame, is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That is: the window ends at the current row. And there lies the most famous trap in all of SQL:

(First 2 of 12 rows.) last_val is always the row itself, because the frame ends there: the "last row of the window" is the current one. FIRST_VALUE works by accident, because the frame's first row really is the first of all. The fix is to write the whole frame: LAST_VALUE(revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING).

And then last_val is 49.33 on all twelve rows: February 2026's revenue. Rule: the moment you use LAST_VALUE or NTH_VALUE, write the frame explicitly. And careful, it isn't true either that "without ORDER BY there's no frame": without ORDER BY, the default frame is the whole group, which is exactly what makes OVER () give the grand total.

When the same window shows up three times, you name it once at the end of the query —between the HAVING and the ORDER BY— and reference it by name, as in section 5: SELECT month, SUM(revenue) OVER w, AVG(revenue) OVER w FROM mv_monthly_sales WINDOW w AS (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW);

You can declare several separated by commas, and one can inherit from another: WINDOW w AS (PARTITION BY category_id), w2 AS (w ORDER BY price DESC). It's pure syntactic sugar, but it eliminates the dullest source of bugs there is: changing the ORDER BY in two of the three copies and forgetting the third.

The three computations every dashboard asks for, in a single query over the twelve-month series:

(The 2025-08 row is missing: €48.27 of revenue, €377.28 running total, €62.78 moving average and +€3.67 / +8.23 % against July.) Three readings and one warning. The running total closes at €727.95 and passes through €603.52 in December 2025: those are the course's two canonical figures, and they confirm the series is sound. The 3-month moving average smooths the noise: actual revenue jumps between €31.70 and €97.20, while the moving average moves in a much narrower band, between €41.88 and €71.87. And the percentage change is spectacular but misleading: that +196.70 % in October isn't a commercial boom, it's that September had a single order. With small volumes, percentages lie — and that's a lesson in analysis, not in SQL.

The warning: the first three rows of moving_avg_3 aren't three-month averages, but averages of one and of two, because the 2 PRECEDING frame has nothing to draw on. If the report must show only complete averages, you have to null them out with CASE WHEN COUNT(*) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) = 3 THEN ... END.

With PARTITION BY, the running total restarts. SUM(revenue) OVER (PARTITION BY left(month, 4) ORDER BY month) gives the year-to-date total: January 2026 starts over at €75.10 and February closes at €124.43, 2026's revenue.

Two promises get kept here: 02-04's DISTINCT ON and 07-04's LATERAL. The canonical pattern is ROW_NUMBER inside a CTE, filtered outside:

(6 of 9 rows; Sustainable home closes with the reusable bags, €39.60, and the detergent, €32.48.) 9 rows in total: four categories contribute two products and Personal hygiene only one, because the deodorant was never sold. Supplements doesn't show up: it has sold nothing.

And the comparison with the other two approaches, using 02-04's example —the most expensive product in each category—, which all three solve with the same 6 rows:

The criterion: top 1 in PostgreSQL and with no need for portability, DISTINCT ON; top N with a huge number of groups and an index that supports it, LATERAL; in any other case, ROW_NUMBER, which throws in the position column for free.

(4 of the 12 customers with purchases — missing are Julien Moreau, 4th with €66.90, and Javier Ortega Ruiz, 5th with €62.93; Diego closes with €31.70, Elena with €30.30 and Marta with €29.53.) The pct_cumulative column is a Pareto analysis done with a window function: the top 6 customers account for 65.89 % of revenue. And notice that NTILE(4) splits the 12 customers into four quartiles of exactly 3, without looking at the amounts: it splits by position, not by value.

In 07-02 this was solved with a correlated subquery that walked products once per row. With PARTITION BY it's a single pass:

(5 of the 20 rows.) The 20 products are still there, each with its category's average alongside. The matcha, at €22.00, costs almost two and a half times the Drinks average (€8.90); the olive oil, a little over twice the Food average (€6.18). No subquery, no repetition, a single read of products.

A window function forces the engine to sort by PARTITION BY + ORDER BY before computing; in the EXPLAIN you'll see a WindowAgg node almost always preceded by a Sort. Three practical consequences: an index on (partition_column, sort_column) can eliminate that Sort; several functions sharing the same window are computed in a single WindowAgg, so reusing the window (with WINDOW) is also faster; and filter earlier with WHERE whenever you can, because the window will work over fewer rows. Even so, a window function almost always beats the alternative: where a correlated subquery makes N passes, the window makes one.

Dialect note: window functions are SQL:2003 standard and today they're everywhere: PostgreSQL (since 8.4, with the most complete set), MySQL 8.0, MariaDB 10.2, SQLite 3.25, SQL Server 2012 and Oracle. Differences worth knowing: SQL Server doesn't allow RANGE with n PRECEDING (only ROWS) and didn't get IGNORE NULLS in LAG/LEAD until 2022; MySQL 8 has no FILTER clause; Oracle calls them analytic functions and adds KEEP (DENSE_RANK FIRST/LAST); and GROUPS as a third frame mode (besides ROWS and RANGE), together with EXCLUDE, exists in PostgreSQL 11+ and is missing from most others.

• Filtering by a window function in the WHERE or the HAVING. window functions are not allowed in WHERE. They're computed afterwards. Wrap it in a CTE or a derived table and filter outside.
• Expecting LAST_VALUE to return the last value. With the default frame it returns the current row. Write ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. And the other way round: adding ORDER BY to a SUM() OVER when you don't want a running total turns it into one; if you want the group total, don't put an ORDER BY.
• Using ROW_NUMBER without a tie-breaker. With repeated values, which row gets the 1 is arbitrary and can change between runs. Always add a unique column to the window's ORDER BY.
• Confusing RANK with DENSE_RANK —the first leaves gaps after a tie (1, 1, 3) and the second doesn't (1, 1, 2)— or thinking RANGE and ROWS are synonyms: they only are without ties; with ties, RANGE lumps them all together (28 instead of 14).
• Forgetting that the window only sees what got past the WHERE: a "percentage of the total" in a filtered query is the percentage of the filtered total. And putting a window function inside an aggregate: SUM(ROW_NUMBER() OVER ()) isn't valid; the other way round is, SUM(SUM(x)) OVER (...) is correct and common — the aggregate is computed first, the window afterwards.
• Tip: always start with OVER () and build up. First the grand total, then PARTITION BY, then ORDER BY, and only at the end the frame. Run it after every step and watch how the column changes.
• Tip: ROWS by default, since it holds no surprises with ties; RANGE only when the peer behaviour is deliberate. And name the window with WINDOW as soon as it repeats twice: you avoid changing the ORDER BY in one copy and not the other, and the engine computes it only once.

Over the 47 order lines, write a query showing, for each line: the order, the product, its amount, its order's total, the percentage it represents within the order and its position within the order by amount. No GROUP BY, no subqueries: just OVER. Check on order 1 that the percentages add up to 100 and the total is €42.10.

Management wants the three highest-billing customers in each country, with their position. (1) Write it with ROW_NUMBER and a CTE. (2) How many rows does it return and why isn't it 9? (3) If instead of "the top three" they asked for "everyone tied in third place", which function would you use?

A colleague wants to flag the months in which revenue beat the twelve-month average and has written SELECT month, revenue FROM mv_monthly_sales WHERE revenue > AVG(revenue) OVER ();. (1) What error does it give and why? (2) Fix it with a CTE, also showing the average and the difference. (3) How many months beat the average and what is that average?

Solution 1

(First 3 of 47 rows.) 56.77 + 27.79 + 15.44 = 100.00 and order 1's total is €42.10, as in 07-04. The 47 rows are preserved: PARTITION BY order_id computes the total per order and sticks it onto each of its lines. Here's where you see the gain: with GROUP BY there would be 20 rows and no detail at all.

Solution 2 — 1.

(6 of 7 rows; the one missing is Tiago Almeida Nunes, Portugal's 2nd with €44.60.) 2. Seven rows, not nine, because France and Portugal only have two customers with purchases each. ROW_NUMBER doesn't invent rows: if a group has fewer than N, it contributes what it has. (Spain has 8 customers with purchases and contributes 3.)

3. RANK(), not DENSE_RANK or ROW_NUMBER. With RANK, everyone tied on the third value gets a 3 and WHERE rank_ <= 3 returns all of them. ROW_NUMBER would have cut off arbitrarily at one; DENSE_RANK would have returned too many, because its "3" is the third distinct value, not the third position.

Solution 3 — 1. It gives ERROR: window functions are not allowed in WHERE. The engine evaluates the WHERE before computing the window functions, so at that moment AVG(revenue) OVER () doesn't exist yet; and it couldn't, because the window needs to know which rows pass the filter and the filter needs the window's value. 2 and 3: with WITH monthly AS (SELECT month, revenue, ROUND(AVG(revenue) OVER (), 2) AS annual_avg FROM mv_monthly_sales) and then SELECT month, revenue, annual_avg, ROUND(revenue - annual_avg, 2) AS difference FROM monthly WHERE revenue > annual_avg ORDER BY revenue DESC, six of the twelve months come out above an average of €60.66 (€727.95 across 12): 2025-10 with +€36.54, 2025-06 with +€34.82, 2026-01 with +€14.44, 2025-03 with +€8.14, 2025-12 with +€3.92 and 2025-04 with +€0.62. The average is computed only once in the CTE and stays available as an ordinary column: that's why you can filter by it and subtract it in the same step.

Window functions are the tool that's been missing since module 4:

• A window function computes over a group of rows without collapsing them: GreenStore's 47 lines come out intact, each with its total, its percentage or its position alongside. OVER () is the empty window —the grand total— and it's where you should start. The anatomy is OVER (PARTITION BY ... ORDER BY ... frame), with all three parts optional, and adding ORDER BY changes an aggregate's result: it switches on the "up to the current row" frame and turns the total into a running total.
• They're evaluated after WHERE, GROUP BY and HAVING, and that's why you can't filter by them in the WHERE (window functions are not allowed in WHERE). The universal solution: compute in a CTE and filter outside. You don't filter it, you wrap it.
• Ranking: ROW_NUMBER numbers without repeating (1, 2, 3), RANK ties and leaves a gap (1, 1, 3), DENSE_RANK ties without a gap (1, 1, 2) — demonstrated with the real tie between the rice and the tomato at 14 units. Plus NTILE for quartiles and PERCENT_RANK for relative positions. Offset: LAG and LEAD with their (column, n, default) arguments, and FIRST_VALUE / LAST_VALUE / NTH_VALUE, which demand an explicit frame.
• The frame: ROWS counts physical rows, RANGE groups rows with the same sort value (28 instead of 14 in the rice tie). The default frame with ORDER BY is RANGE UNBOUNDED PRECEDING AND CURRENT ROW —hence LAST_VALUE returning the current row and not the last one—; without ORDER BY, the whole group.
• The GreenStore cases, all verified: a monthly running total closing at €727.95 by way of €603.52 in December; a 3-month moving average between €41.88 and €71.87; monthly change with October's misleading +196.70 %; top 2 per category with ROW_NUMBER (9 rows) and the table setting it against DISTINCT ON and LATERAL; a customer ranking with the Pareto that gives 65.89 % cumulative in the top six; and each product against its category's average in a single pass, where 07-02 used a correlated subquery.

With this there's no analytical question about GreenStore you can't write. But everything you've done so far lives in one statement: you write it, you run it and you forget it. What comes next is storing logic, not just queries. In the next lesson, stored procedures, you'll see code that lives inside the database: the real difference between a function and a procedure in PostgreSQL, just enough PL/pgSQL to write something useful —variables, IF, loops, RAISE, exception handling— and three examples in order of difficulty ending in sp_confirm_order, the procedure where the transactional logic you built by hand in 09-03 finally lives. And, above all, the honest discussion about what's worth putting in there and what isn't.