Top N per group in SQL: number the rows, keep the first three

"Top 3 products per category, by revenue" is a SQL interview classic. GROUP BY stops at one row per group, so it cannot give you three. The answer is to number the rows inside each group and keep the first three. This page covers the window, the filter that has to live in a CTE, and the tie question. The print-ready A4 PDF is at the bottom.

The question

• Top 3 products per category, by revenue.
• GROUP BY gives one row per group, not three.

The answer

• ROW_NUMBER() OVER (PARTITION BY category restarts the count in each category.
• ORDER BY revenue DESC) gives the best product the number 1.
• WHERE rn <= 3 keeps the first three. It goes in a CTE, not in the same SELECT.

The query

WITH ranked AS (
  SELECT category, product, revenue,
         ROW_NUMBER() OVER (
           PARTITION BY category
           ORDER BY revenue DESC) AS rn
  FROM sales
)
SELECT * FROM ranked WHERE rn <= 3;

Number the products inside each category, best first. Then keep the numbers up to 3.

The WHERE cannot see rn in the same SELECT: window functions run after WHERE. Hence the CTE. BigQuery and Snowflake have QUALIFY rn <= 3 for exactly this.

Remember

• DENSE_RANK: use it when ties should all stay.
• QUALIFY: BigQuery and Snowflake, no CTE needed.
• LIMIT 3: works for one group only, because it cuts the whole result.

The trap: two products tie for third

Three functions, three answers to the same tie.

ROW_NUMBER breaks the tie in an arbitrary way, so the product that is dropped may change between runs. If that matters, add a second column to the ORDER BY, such as the product name, so the result is stable.

With RANK or DENSE_RANK you can get more than three rows in a category, because every tied product stays. That is often the honest answer.

Interview phrasing worth memorizing: I use ROW_NUMBER for exactly N rows, DENSE_RANK when tied values must all be kept, and I say which one the question needs.