Top 2 Items per Category

SQLMediumMid levelAmazon~15 min

Problem

**[Asked at Amazon]**

Amazon's category managers want to know which products generated the most revenue
per category. For each category, find the **top 2 highest-revenue products** in 2023.

Revenue = SUM of all individual purchase amounts for that product.

Return `category`, `product_name`, and `total_revenue`, ordered by category then
revenue descending.

Tables

Example rows — the live problem includes the full dataset.

purchases

purchase_id	category	product_name	buyer_id	amount	purchase_date

Expected output

Your answer should return 4 rows with the columns category, product_name, total_revenue.

Starter code (SQL)

SELECT *
FROM purchases;

Solve this SQL question free

Write SQL and run it instantly in your browser — even on your phone. No signup needed to try.

Solve it now → Create free account

Solution & explanation

Create a free account to unlock the optimal solution, a step-by-step explanation, and the hidden test cases that grade your answer.