Task
Nimbus Retail wants to identify the highest-performing products within each category based on completed order revenue. Write a SQL query to return the top 2 products per category.
Requirements
- Use only orders with
status = 'completed'.
- Calculate each product's total revenue as
quantity * unit_price.
- Rank products within each category by total revenue, highest first.
- Return only the top 2 products per category.
- Include
category_name, product_name, total_revenue, and revenue_rank.
- Sort the final output by
category_name, then revenue_rank, then product_name.
Table Definitions
categories
| column | type | description |
|---|
| category_id | INT | Primary key for the category |
| category_name | VARCHAR(100) | Category name |
products
| column | type | description |
|---|
| product_id | INT | Primary key for the product |
| product_name | VARCHAR(100) | Product name |
| category_id | INT | References categories.category_id |
order_items
| column | type | description |
|---|
| order_item_id | INT | Primary key for the order item |
| product_id | INT | References products.product_id |
| quantity | INT | Number of units sold |
| unit_price | NUMERIC(10,2) | Price per unit |
| status | VARCHAR(20) | Order item status |
| ordered_at | DATE | Order date |
Sample Data
Representative rows are shown below.
categories
| category_id | category_name |
|---|
| 2 | Books |
| 1 | Electronics |
| 3 | Home |
| 4 | Toys |
products
| product_id | product_name | category_id |
|---|
| 101 | Laptop | 1 |
| 102 | Headphones | 1 |
| 103 | Mouse | 1 |
| 201 | Novel | 2 |
| 202 | Cookbook | 2 |
| 203 | Notebook | 2 |
| 301 | Blender | 3 |
| 302 | Lamp | 3 |
| 303 | Chair | 3 |
| 401 | Puzzle | 4 |
order_items
| order_item_id | product_id | quantity | unit_price | status | ordered_at |
|---|
| 8 | 201 | 5 | 20.00 | completed | 2024-02-01 |
| 2 | 101 | 1 | 1200.00 | pending | 2024-01-11 |
| 5 | 102 | 3 | 150.00 | completed | 2024-01-15 |
| 11 | 202 | 1 | 35.00 | completed | 2024-02-03 |
| 1 | 101 | 2 | 1200.00 | completed | 2024-01-10 |
| 14 | 301 | 1 | 90.00 | completed | 2024-02-06 |
| 18 | 303 | 1 | 200.00 | cancelled | 2024-02-10 |
| 20 | 999 | 1 | 50.00 | completed | 2024-02-12 |
Expected Output
| category_name | product_name | total_revenue | revenue_rank |
|---|
| Books | Novel | 100.00 | 1 |
| Books | Cookbook | 70.00 | 2 |
| Electronics | Laptop | 2400.00 | 1 |
| Electronics | Headphones | 600.00 | 2 |
| Home | Chair | 600.00 | 1 |
| Home | Lamp | 100.00 | 2 |
