Highest Grossing Items
ASKED IN INTERVIEWAmazon
Problem Statement
Amazon needs to identify its best-performing products to optimize inventory.
Write an SQL query to find the top 2 highest-grossing products (by total revenue) for each category in each year.
Rules:
- Revenue is calculated as the sum of (unit_price * quantity) for all sales of a product.
- If there is a tie in revenue, the product with the lower product_id should be ranked higher.
- A year is defined by the transaction_date.
- Return columns: year, category_name, product_name, total_revenue.
- Results must be sorted by year in descending order, then by category_name in ascending order, and finally by total_revenue in descending order.
Tests your understanding of
Window Functions, Ranking, Subqueries and Aggregation
Input Tables
categories
| category_id(INTEGER) | category_name(VARCHAR) |
|---|---|
| 1 | Electronics |
| 2 | Home Decor |
products
| product_id(INTEGER) | product_name(VARCHAR) | category_id(INTEGER) |
|---|---|---|
| 101 | Laptop | 1 |
| 102 | Smartphone | 1 |
| 103 | Tablet | 1 |
| 201 | Lamp | 2 |
| 202 | Vase | 2 |
| 203 | Rug | 2 |
sales
| sale_id(INTEGER) | product_id(INTEGER) | unit_price(DECIMAL) | quantity(INTEGER) | transaction_date(TIMESTAMP) |
|---|---|---|---|---|
| 1 | 101 | 1000 | 2 | 2023-01-10 10:00:00 |
| 2 | 102 | 800 | 5 | 2023-02-15 11:00:00 |
| 3 | 103 | 500 | 1 | 2023-03-20 12:00:00 |
| 4 | 201 | 50 | 10 | 2023-01-05 09:00:00 |
| 5 | 202 | 40 | 20 | 2023-05-10 14:00:00 |
| 6 | 203 | 150 | 2 | 2023-06-01 15:00:00 |
| 7 | 101 | 1000 | 1 | 2024-01-10 10:00:00 |
| 8 | 102 | 800 | 1 | 2024-01-11 11:00:00 |
| 9 | 103 | 500 | 10 | 2024-01-12 12:00:00 |
| 10 | 201 | 50 | 5 | 2024-02-01 10:00:00 |
Expected Output
| year(INTEGER) | category_name(VARCHAR) | product_name(VARCHAR) | total_revenue(DECIMAL) |
|---|---|---|---|
| 2024 | Electronics | Tablet | 5000 |
| 2024 | Electronics | Laptop | 1000 |
| 2024 | Home Decor | Lamp | 250 |
| 2023 | Electronics | Smartphone | 4000 |
| 2023 | Electronics | Laptop | 2000 |
| 2023 | Home Decor | Vase | 800 |
| 2023 | Home Decor | Lamp | 500 |
Tags
MediumASKED IN INTERVIEWWindow FunctionsRankingSubqueriesAggregation
20-25 min
45%