Task
NovaCart wants to identify its highest-revenue products from completed sales. Write a SQL query to return the top 10 products by total sales revenue.
Requirements
- Use only completed orders when calculating revenue.
- Compute product revenue as
quantity * unit_price from order line items.
- Return each product's
product_id, product_name, and total revenue.
- Sort by total revenue descending, then by
product_id ascending to break ties.
- Return only the top 10 rows.
Table Definitions
products
| column | type | description |
|---|
| product_id | INT | Primary key for each product |
| product_name | VARCHAR(100) | Product 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 | Order date |
| status | VARCHAR(20) | Order status |
order_items
| column | type | description |
|---|
| order_item_id | INT | Primary key for each order line |
| order_id | INT | References orders.order_id |
| product_id | INT | References products.product_id |
| quantity | INT | Units sold |
| unit_price | DECIMAL(10,2) | Price per unit at purchase time |
Sample Data
Representative rows are included below in the structured dataset.
Expected Output
| product_id | product_name | total_revenue |
|---|
| 3 | Noise-Canceling Headphones | 500.00 |
| 1 | Wireless Mouse | 300.00 |
| 4 | Standing Desk | 300.00 |
| 9 | Office Chair | 300.00 |
| 2 | Mechanical Keyboard | 240.00 |
| 7 | 4K Monitor | 220.00 |
| 12 | Webcam | 180.00 |
| 8 | USB-C Dock | 160.00 |
| 6 | Laptop Stand | 105.00 |
| 11 | Ethernet Adapter | 90.00 |
