Top N Records Per Group Using PARTITION BY in SQL

Question

Given `products` (product_id, category, sales_volume), write a SQL query to select the top 3 products with the highest sales volume in each category.

Accepted Answer

Optimal Solution:
WITH ranked_products AS (
  SELECT 
    category,
    product_id,
    sales_volume,
    ROW_NUMBER() OVER (
      PARTITION BY category 
      ORDER BY sales_volume DESC
    ) AS category_rank
  FROM products
)
SELECT category, product_id, sales_volume, category_rank
FROM ranked_products
WHERE category_rank <= 3
ORDER BY category, category_rank;

Technical Explanation:
Using `ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales_volume DESC)` numbers products from 1 to N inside each category. Filtering `category_rank <= 3` extracts the top 3.

product_id	category	sales_volume
1	Laptops	500
2	Laptops	450
3	Laptops	400
4	Laptops	300
5	Phones	900

category	product_id	sales_volume	category_rank
Laptops	1	500	1
Laptops	2	450	2
Laptops	3	400	3
Phones	5	900	1