Write a query to return the top 3 selling products per category using a subquery (no window functions allowed).

Answer

Without window functions, ranking within a group requires a correlated subquery that counts how many products in the same category outsell each candidate row.

Solution

SELECT p1.category, p1.product_id, p1.total_sales
FROM Products p1
WHERE (
  SELECT COUNT(*) FROM Products p2
  WHERE p2.category = p1.category AND p2.total_sales > p1.total_sales
) < 3
ORDER BY p1.category, p1.total_sales DESC;

Step by Step

1. For every product row (p1), the correlated subquery counts how many other products in the same category (p2.category = p1.category) have strictly higher sales.
2. If fewer than 3 products outsell it, that product is by definition in the top 3 for its category (0, 1, or 2 products beat it).
3. The outer query keeps only rows satisfying that condition.

Alternative Method: Self-Join

SELECT p1.category, p1.product_id, p1.total_sales
FROM Products p1
LEFT JOIN Products p2
  ON p1.category = p2.category AND p2.total_sales > p1.total_sales
GROUP BY p1.category, p1.product_id, p1.total_sales
HAVING COUNT(p2.product_id) < 3
ORDER BY p1.category, p1.total_sales DESC;

This self-join achieves the same result by counting matching "higher sales" rows per product via GROUP BY/HAVING instead of a subquery - often faster on engines that optimize joins better than correlated subqueries.

Key Exam Traps

• Ties at the boundary: If two products tie for 3rd place, this logic returns both (since neither is beaten by 3+ products) - a "top 3" query can legitimately return more than 3 rows per category when ties exist. Clarify with the interviewer whether ties should be included or arbitrarily broken.
• Performance: A correlated subquery re-executes per outer row, which can be slow on large tables - the self-join alternative is often the better answer to mention when asked about scaling.
• "No window functions allowed" is the actual constraint being tested here - reaching for RANK() reflexively would miss the point of the question entirely.