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.

How to answer it

Rank within a partition, then keep ranks up to three.

SELECT category, product_id, revenue
FROM (
  SELECT
    category,
    product_id,
    revenue,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
  FROM product_revenue
) ranked
WHERE rnk <= 3
ORDER BY category, revenue DESC;

The ranking function is the decision. ROW_NUMBER gives exactly three rows per category and breaks ties arbitrarily; RANK includes ties but can skip ranks (1, 1, 3); DENSE_RANK includes ties and never skips, so "top 3" can return more than three rows when products tie. Say which behaviour the requester wants; there is no default.

If revenue has to be computed first, aggregate in a CTE and rank the aggregate; ranking before aggregating ranks line items, not products.

• 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.

Related questions

• Write a query to find the second highest salary from an Employees table.
• Given orders(user_id, order_date, amount), compute 7-day rolling revenue per day.
• Explain the difference between INNER, LEFT, and FULL OUTER JOIN.
• Calculate the median order value using SQL.
• Compute month-over-month user retention from an events table.
• Find users who logged in 3 or more consecutive days.

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