Your question is Top Product Revenue Last 30 Days. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
orders.order_date.order_items.is_returned = true.| Column | Type | Description |
|---|---|---|
| product_idPK | INT | Unique product identifier |
| product_name | VARCHAR(100) | Product display name |
| category | VARCHAR(50) | Product category |
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| customer_id | INT | Customer who placed the order |
| order_date | DATE | Date the order was placed |
| Column | Type | Description |
|---|---|---|
| order_item_idPK | INT | Unique line item identifier |
| 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) | Unit sale price at the time of purchase |
| is_returned | BOOLEAN | Whether the line item was returned |