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.
status = 'completed'.quantity * unit_price.category_name, product_name, total_revenue, and revenue_rank.category_name, then revenue_rank, then product_name.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 |
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 |
| 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 |
| Column | Type | Description |
|---|---|---|
| category_idPK | INT | Primary key for the category |
| category_name | VARCHAR(100) | Category name |
| Column | Type | Description |
|---|---|---|
| product_idPK | INT | Primary key for the product |
| product_name | VARCHAR(100) | Product name |
| category_id | INT | References categories.category_id |
| Column | Type | Description |
|---|---|---|
| order_item_idPK | 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 such as completed, pending, or cancelled |
| ordered_at | DATE | Date the order item was created |
{"products":[[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],[402,"Drone",null],[501,"Shovel",5],["1","Mouse","100"],["2","Lamp","42"],["3","Blender","1"],["4","Laptop","46"],["5","Notebook","57"],["6","Puzzle","37"],["7","Notebook","83"],["8","Laptop","84"],["9","Chair","18"],["10","Notebook","14"],["11","Cookbook","15"],["12","Notebook","78"],["13","Puzzle","89"],["14",Output[["Books","Notebook","null","1"],["Books","Novel","100.00","2"],["Electronics","Laptop","2400.00","1"],["Electronics","Headphones","600.00","2"],["Home","Chair","600.00","1"],["Home","Lamp","100.00","2"],["Toys","Puzzle","60.00","1"]]
