Top N per group in two lines

The question that used to be hard

“The three best-selling products in each category.” Before window functions this took a correlated subquery that counted how many products in the same category outsold this one, and it ran once per row. With a window it is a rank and a filter:

WITH ranked AS (
  SELECT category, product_id, sales,
         row_number() OVER (PARTITION BY category
                            ORDER BY sales DESC, product_id) AS rn
  FROM product_sales
)
SELECT category, product_id, sales
FROM ranked
WHERE rn <= 3
ORDER BY category, rn;

Read the window aloud: within each category, order by sales descending and by product id to break ties, and number the rows from 1. Then keep the first three of each. The previous lesson explained why the filter has to live in a second layer, and why product_id is in the ORDER BY.

Which N, and which ties

row_number returns exactly three per category, and when two products tie for third, one is dropped by the tiebreaker. That is right for “show me three”. It is wrong for “show me everything that made the top three”, where dense_rank() <= 3 returns both tied products, and possibly four or five rows for that category. rank() <= 3 is the third option: it returns the tied rows but, if two products tie for first, it returns only ranks 1, 1 and 3. Decide which the reader wants before choosing the function, and say which you chose in the column name or a comment.

The one-row case

When N is 1 the previous module’s DISTINCT ON, arg_max and join-back patterns all compete with this one. The window version is the most portable and the only one that extends to N of 2 without rewriting, so it is the default; DISTINCT ON is worth keeping for Postgres-only code where it reads more clearly.

Percent of the group, not a fixed count

Sometimes the ask is “the top 10% of customers in each region”. ntile(10) gets close but produces equal-count buckets, which is what “top 10% by count” means. For “customers who together make up the top 10% of revenue” you want a running share:

WITH s AS (
  SELECT region, customer_id, revenue,
         sum(revenue) OVER (PARTITION BY region ORDER BY revenue DESC)
           / sum(revenue) OVER (PARTITION BY region) AS cum_share
  FROM customer_revenue
)
SELECT * FROM s WHERE cum_share <= 0.10;

Two windows with the same partition, one ordered and one not, and the division gives each customer’s position in the cumulative curve. The engine sorts once for both because the partition matches.

Performance, briefly

The window has to sort each category’s rows by sales. For a product table that is nothing. For “the latest three events per device” over a billion-row events table it is the whole cost, and there are two things that help. An index on (device_id, event_time DESC) lets the engine read rows already in window order and skip the sort. And Postgres from version 15 recognises a row_number() <= N filter on the outer query and stops scanning each partition after N rows, so the query no longer numbers a million events per device to keep three. On older versions, and on engines without that optimisation, a LATERAL join that fetches LIMIT 3 per device from the index is faster, at the cost of reading less naturally.

The trap in the tiebreaker

If the ORDER BY inside the window is not complete, the rows that make it into the top N can change between runs, and a daily report will show a different “third-best product” on days when nothing changed. The fix is the same as before: end the window’s ORDER BY with a column that cannot tie. If the report’s audience will notice a product appearing and disappearing, this is not a nicety.

The habit

Every top-N query should be able to say, in one sentence, what happens to ties, and that sentence should match the function used. “Three per category, ties broken by product id” is row_number. “Everything in the top three positions” is dense_rank. If you cannot say the sentence, the report is going to surprise somebody.