Window Functions

GROUP BY answers "what is the total" by destroying the detail. Twenty-four orders go in, ten country rows come out, and no individual order survives to be looked at.

A window function computes the same kind of summary without collapsing anything. Add OVER () to an aggregate and every row stays, each one now carrying the total alongside its own values.

SELECT order_id, amount,
       SUM(amount) OVER () AS all_orders
FROM orders;   -- 24 rows, every one showing the same grand total

The empty brackets are the window: which rows this calculation may look at. Empty means "all of them". That is the whole idea — a second pass over the same rows, reported next to the first.

Your task: return order_id, amount, and the total of all order amounts as all_orders, for every order, ordered by order_id.

"What percentage of revenue is each country?" needs two numbers at different levels at once: the country's revenue, and everyone's. Before windows that meant computing the total separately and joining it back.

A window can wrap an aggregate. It looks wrong the first time:

SELECT u.country,
       SUM(o.amount)                  AS revenue,
       SUM(SUM(o.amount)) OVER ()     AS all_revenue
FROM orders o JOIN users u ON u.user_id = o.user_id
GROUP BY u.country;

The inner SUM is the ordinary aggregate, computed per country. The outer one is a window over the grouped rows — the GROUP BY has already happened, so the window sees nine country rows and totals those. It is not a typo, and it is the standard way to write a share.

Your task: for paid orders, return each country, its revenue, and its pct of total revenue rounded to one decimal place. Order by revenue descending, then country.

OVER () looked at everything. PARTITION BY narrows the window to rows sharing a value — so each row is compared against its own group rather than the whole table.

AVG(o.amount) OVER (PARTITION BY u.country)

Read it as "the average, restarted for each country". Every order still appears; each now carries the average for the country it belongs to.

This is the same question the correlated subquery answered in module 3-05 — compare a row against an aggregate of its own group — written in one line and computed in one pass rather than re-running an inner query per row.

Your task: for paid orders, return order_id, country, amount, and that country's average order value rounded to two decimals as country_avg. Order by country, then order_id.

ROW_NUMBER() hands each row a position: 1, 2, 3. It takes no argument, and it needs an ORDER BY inside the OVER to know what order it is numbering.

ROW_NUMBER() OVER (PARTITION BY u.country ORDER BY o.amount DESC)

Two different ORDER BYs are now in play and they do different jobs. The one inside OVER decides what gets number 1. The one at the end of the query decides what order the rows are printed in. They are frequently the same and do not have to be.

Your task: for paid orders, return country, order_id, amount, and a rn numbering that country's orders from most to least valuable. Break amount ties with the lower order_id first. Print ordered by country, then rn.

With no ties the three ranking functions are identical, which is why the difference between them is usually learned the hard way. Here there are real ties: five users have 3 orders each, and four have 2.

Which one is right depends on the question. "Who is in the top 3?" with ROW_NUMBER silently cuts off two of five equal leaders and the report will not mention it.

Your task: return every user's name, their orders count, and all three rankings by order count descending, as rnk, dense and rn. Break the ROW_NUMBER tie by name. Order by orders descending, then name.

The rule that catches everyone, and Postgres is blunt about it:

WHERE ROW_NUMBER() OVER (ORDER BY o.order_id) <= 3
ERROR:  window functions are not allowed in WHERE

Not a limitation so much as a consequence of ordering. Window functions are evaluated after WHERE, GROUP BY and HAVING — they run on whatever survived those. So at the moment WHERE is deciding, no window has been computed yet. The same error appears for GROUP BY and HAVING.

It does mean a WHERE in the same query filters the rows the window then sees. Filter to paid orders and the running totals and averages are over paid orders only, which is usually what you wanted.

To filter on a window value, compute it in a CTE and filter outside — a second pass, and the standard shape.

Your task: return the order_id and amount of the three largest paid orders, with their rn, using a CTE and ROW_NUMBER. Break ties by the lower order_id. Order by rn.

Now the pattern this all builds to, and probably the single most useful thing in Part 4: the top N rows within each group.

"The biggest order in each country" resists everything so far. MAX(amount) gives the number but not the order it belongs to. Grouping and then joining back is possible and fiddly, and duplicates when two orders tie.

Number within the partition, then keep number one:

WITH ranked AS (
  SELECT …, ROW_NUMBER() OVER (PARTITION BY u.country
                               ORDER BY o.amount DESC, o.order_id) AS rn
  FROM …
)
SELECT … FROM ranked WHERE rn = 1;

Change = 1 to <= 3 for a top three. Use RANK() instead if genuine ties should all be kept — that is the whole decision.

Your task: return country, order_id and amount for each country's largest paid order, breaking ties by the lower order_id, ordered by country.

NTILE(n) splits the window into n buckets of as-equal-as-possible size and labels each row with its bucket number. It is how quartiles, deciles and "top 20% of customers" get built.

NTILE(4) OVER (ORDER BY o.amount DESC)   -- 1 = the biggest quarter

It divides by position, not by value. Rows with equal values can land in different buckets, because NTILE's job is to make the buckets the same size. If the boundary must respect ties, that is RANK or an explicit range, not NTILE.

When the count does not divide evenly the earlier buckets get the extra row each — 21 orders into 4 buckets gives 6, 5, 5, 5.

Your task: for paid orders, return order_id, amount and a quartile from 1 (largest) to 4, breaking ties by the lower order_id. Order by amount descending, then order_id.

Module 3-05 asked for every order worth more than the average order in its own country, and answered it with a correlated subquery that re-ran once per row.

A window computes every country's average in one pass. The only wrinkle is the rule from two stations ago — the comparison cannot live in WHERE, so the window goes in a CTE and the filter goes outside it.

Both are correct. The window version is usually faster, and it is certainly easier to read once the shape is familiar. The correlated form is worth keeping in hand for engines that do not support windows, which by now are few.

Your task: return order_id, country and amount for every order worth more than the average order in its own country — using a window rather than a correlated subquery. Order by order_id.