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 |
