SQL · medium

Find the top 3 products by revenue within each category.

Asked in Data Analyst interviews, in the SQL round.

Short answer

RANK()/ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) <= 3.

Sample data

These tables are loaded into an in-browser engine on this page, so you can run your own answer against them. No account needed.

products

How to answer it

Definition

A top-N-per-group problem. One global ORDER BY ... LIMIT 3 gives the top three overall; the ranking has to restart inside every category, which is what PARTITION BY does.

Example

WITH ranked AS (
  SELECT category,
         product_id,
         revenue,
         DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
  FROM   products
  WHERE  revenue IS NOT NULL
)
SELECT category, product_id, revenue
FROM   ranked
WHERE  rnk <= 3
ORDER BY category, revenue DESC, product_id;

Output

category    | product_id | revenue
------------+------------+--------
electronics |          2 |    1290
electronics |          1 |     890
electronics |          3 |     430
electronics |          4 |     430
home        |          8 |     950
home        |          6 |     480
home        |          9 |     320
travel      |         10 |     740
travel      |         11 |     150

Run the query against the products sample table above. Electronics returns four products because Mouse and Webcam tie at the third revenue level. Travel has only two products, so both qualify. NULL revenue is excluded; decide whether unknown revenue should instead be treated as zero.

Which ranking function

revenue   ROW_NUMBER   RANK   DENSE_RANK
  91200            1      1            1
  77400            2      2            2
  77400            3      2            2
  64100            4      4            3

For the four-row comparison above, filtering each function to <= 3 gives: ROW_NUMBER: three rows; RANK: three rows with ranks 1, 2, 2 (the next rank is 4); DENSE_RANK: all four rows, covering three distinct revenue levels. Categories with fewer than three products return fewer than three rows.

Exactly three products per category

Use ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC, product_id ASC) in place of DENSE_RANK when the business wants at most three products, with product_id as a stable tie-breaker. In the sample this chooses Mouse ahead of Webcam. Do not add product_id to DENSE_RANK's window ordering: that would separate products with equal revenue and defeat tie preservation.

When the input contains individual orders

The sample already has one revenue total per product. For transaction data, first SUM revenue grouped by category and product_id, then rank those totals. Define the reporting period and treatment of refunds before aggregating. Ranking individual order rows answers a different question.

Interview follow-ups

• Ask whether top three means three products, competition ranks, or three revenue levels.
• Test ties at first and third place, a category with two products, and NULL revenue.
• For a large dataset, inspect the execution plan: aggregation and sorting within categories usually dominate the work.

Key points

• PARTITION BY category is what makes it per group rather than overall.
• Ties change the row count. Decide whether "top 3" means three rows or three levels.
• If revenue is not pre-aggregated, aggregate first and rank the aggregate.
• Filter on the rank outside the window. A window function cannot appear in WHERE, for the same reason an aggregate cannot.

FAQ

• Why can I not write WHERE rnk <= 3 directly? Window functions are computed after WHERE. Wrap it in a CTE, or use QUALIFY where available.
• What about ties on the boundary? DENSE_RANK keeps all products in the top three revenue levels. ROW_NUMBER limits the count; add a unique tie-breaker to make selection repeatable.
• Does this work for bottom 3? Same query, ORDER BY revenue ASC.

The 30-second answer

A window function partitioned by category and ordered by revenue descending, filtered to rank three or below in an outer query, since a window function cannot go in WHERE.

The choice worth narrating is which ranking function. ROW_NUMBER gives at most three rows; a unique tie-breaker makes selection stable. RANK keeps ties and skips subsequent numbers. DENSE_RANK gives the top three revenue levels, which may be more than three products. I would default to DENSE_RANK and ask what should happen when two products tie on the boundary, because that is a product decision rather than a SQL one.

In the interview

• What they are checking: the PARTITION BY, and whether you ask about ties.
• Common mistake: ORDER BY revenue DESC LIMIT 3, which is top three overall, not per category.

Practice this for real

• Check your resume against a Data Analyst job description, free, no account needed.
• Every data interview question, by role and topic
• Scored AI mock interviews, two free, no card