Your question is SQL Top Products Query. 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.
Cloud Software Group's product analytics team wants to identify the strongest-selling products within each product category. Revenue should reflect completed orders only.
Write a PostgreSQL query that returns the top three products by revenue for every product category.
quantity * unit_price.status = 'completed'.| Column | Type | Description |
|---|---|---|
| product_idPK | INT | Unique product identifier |
| product_name | VARCHAR(100) | Product name |
| category | VARCHAR(50) | Product category |
| Column | Type | Description |
|---|---|---|
| order_item_idPK | INT | Unique order-line identifier |
| order_id | INT | References orders.order_id |
| product_id | INT | References products.product_id |
| quantity | INT | Number of units purchased |
| unit_price | NUMERIC(10,2) | Price per unit at purchase time |
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| order_date | DATE | Date the order was placed |
| status | VARCHAR(20) | Order lifecycle status |