Task
NovaCart wants a quick view of product performance trends from a single sales table. Write a SQL query to summarize monthly sales activity and highlight the strongest categories.
Requirements
- Return total revenue and total orders for each
sale_month and category.
- Only include rows where
status = 'completed' and sale_date is not NULL.
- Group by month and category.
- Order the result by
sale_month ascending, then total_revenue descending.
Table Definition
sales
| column | type | description |
|---|
| sale_id | INT | Primary key for each sale |
| sale_date | DATE | Date the sale was recorded |
| category | VARCHAR(50) | Product category |
| amount | DECIMAL(10,2) | Sale amount |
| status | VARCHAR(20) | Sale status such as completed, returned, or pending |
Sample Data
| sale_id | sale_date | category | amount | status |
|---|
| 1 | 2024-02-10 | Electronics | 300.00 | completed |
| 2 | 2024-01-05 | Books | 40.00 | completed |
| 3 | 2024-01-20 | Electronics | 120.00 | completed |
| 4 | 2024-01-25 | Books | 15.00 | returned |
| 5 | 2024-02-02 | Home | 80.00 | completed |
| 6 | 2024-02-18 | Electronics | 220.00 | pending |
| 7 | 2024-01-12 | Home | 60.00 | completed |
| 8 | 2024-03-01 | Books | 55.00 | completed |
| 9 | 2024-03-03 | Electronics | 500.00 | completed |
| 10 | NULL | Home | 90.00 | completed |
Expected Output
| sale_month | category | total_orders | total_revenue |
|---|
| 2024-01 | Electronics | 1 | 120.00 |
| 2024-01 | Home | 1 | 60.00 |
| 2024-01 | Books | 1 | 40.00 |
| 2024-02 | Electronics | 1 | 300.00 |
| 2024-02 | Home | 1 | 80.00 |
| 2024-03 | Electronics | 1 | 500.00 |
| 2024-03 | Books | 1 | 55.00 |
