Level 1 · Top 3 products in every category

A window function computes a value for each row using a set of related rows, its “window”, without collapsing them. The window is defined by OVER (...):

ROW_NUMBER() OVER (PARTITION BY category ORDER BY net_revenue DESC)

• PARTITION BY category splits the rows into groups, like GROUP BY, but keeps every row.
• ORDER BY net_revenue DESC orders rows inside each partition.
• ROW_NUMBER() numbers them 1, 2, 3, … and restarts at 1 in each partition.

So every product gets its position within its own category, and every product row is still there.

Top N per group

“Top 3 per category” is the classic use. With LIMIT you can only take the top 3 overall. With ROW_NUMBER you number within each category, then keep numbers 1 to 3:

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY category
                            ORDER BY net_revenue DESC, product_id) AS category_rank
  FROM product_revenue
)
SELECT *
FROM ranked
WHERE category_rank <= 3;

Why the extra CTE

Window functions are evaluated at the SELECT step, after WHERE, GROUP BY and HAVING (chapter 4’s logical order). So WHERE ROW_NUMBER() OVER (...) <= 3 is not allowed: the number does not exist yet when WHERE runs. Compute it in a CTE or subquery, then filter in the outer query. This works in every database.

DuckDB, BigQuery and Snowflake also have QUALIFY, a filter that runs after window functions:

SELECT category, product_id, net_revenue
FROM product_revenue
QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY net_revenue DESC, product_id) <= 3;

Postgres and MySQL do not support QUALIFY, so know the CTE version for interviews.

Window functions on top of GROUP BY

You can apply a window function to grouped results in the same SELECT, because aggregates are computed before windows. RANK() OVER (ORDER BY SUM(revenue) DESC) is valid. Splitting it into a CTE, as here, is usually easier to read and debug.

Make the order unique

ROW_NUMBER always gives distinct numbers, even for ties. If two products have identical revenue, which one is 3rd and which is 4th is arbitrary, and could change between runs, unless you add a tie-breaker (product_id). For “top N with ties included”, use RANK instead, covered next.

Interview note

“Return the top 2 highest-paid employees in each department” is perhaps the most asked SQL interview question. The expected answer is exactly this pattern: a window function partitioned by the group, ordered by the measure, in a CTE, filtered outside. Mention how you handle ties.