Task
ShopSphere wants to identify its highest-revenue products from recent sales activity. Write a SQL query to find the top 5 products by revenue in the last 30 days, excluding any returned items.
Assume the query is run relative to the latest date in the dataset, and revenue should be calculated as quantity * unit_price.
Requirements
- Use only order items from the last 30 days based on
orders.order_date.
- Exclude items where
order_items.is_returned = true.
- Join the necessary tables to return product names.
- Aggregate revenue by product.
- Return the top 5 products ordered by revenue descending.
Table Definitions
products
| column | type | description |
|---|
| product_id | INT | Primary key for each product |
| product_name | VARCHAR(100) | Product display name |
| category | VARCHAR(50) | Product category |
orders
| column | type | description |
|---|
| order_id | INT | Primary key for each order |
| customer_id | INT | Customer placing the order |
| order_date | DATE | Date the order was placed |
order_items
| column | type | description |
|---|
| order_item_id | INT | Primary key for each line item |
| order_id | INT | References orders.order_id |
| product_id | INT | References products.product_id |
| quantity | INT | Number of units purchased |
| unit_price | DECIMAL(10,2) | Price per unit at purchase time |
| is_returned | BOOLEAN | Whether the item was returned |
Sample Data
products
| product_id | product_name | category |
|---|
| 101 | Wireless Mouse | Electronics |
| 102 | Mechanical Keyboard | Electronics |
| 103 | USB-C Hub | Accessories |
orders
| order_id | customer_id | order_date |
|---|
| 1001 | 501 | 2024-06-28 |
| 1002 | 502 | 2024-07-02 |
| 1003 | 503 | 2024-07-10 |
order_items
| order_item_id | order_id | product_id | quantity | unit_price | is_returned |
|---|
| 1 | 1001 | 101 | 2 | 25.00 | false |
| 2 | 1002 | 102 | 1 | 90.00 | false |
| 3 | 1003 | 101 | 1 | 25.00 | true |
Expected Output
| product_id | product_name | total_revenue |
|---|
| 106 | 4K Monitor | 600.00 |
| 102 | Mechanical Keyboard | 360.00 |
| 108 | Laptop Stand | 240.00 |
| 101 | Wireless Mouse | 150.00 |
| 107 | Noise Cancelling Headphones | 150.00 |
