Task
NovaCart wants a simple monthly sales summary from a single transactions table. Write a SQL query to aggregate completed sales for January 2024 by product category.
Requirements
- Return
category, transaction_count, and total_sales for rows where status = 'completed' and transaction_date is in January 2024.
- Group results by
category and order by total_sales descending.
Table Definition
transactions
| column_name | type | description |
|---|
| id | INT | Unique transaction ID |
| category | VARCHAR(50) | Product category |
| amount | DECIMAL(10,2) | Transaction amount |
| status | VARCHAR(20) | Transaction status |
| transaction_date | DATE | Date of transaction |
| sales_rep | VARCHAR(100) | Sales representative name |
Sample Data
| id | category | amount | status | transaction_date | sales_rep |
|---|
| 1 | Electronics | 120.00 | completed | 2024-01-12 | Maya Chen |
| 2 | Books | 35.50 | completed | 2024-01-03 | Liam Patel |
| 3 | Electronics | 80.00 | pending | 2024-01-15 | Maya Chen |
| 4 | Home | 220.00 | completed | 2024-01-20 | Ava Brooks |
| 5 | Books | 15.00 | completed | 2024-01-28 | Noah Kim |
| 6 | Home | NULL | completed | 2024-01-25 | Ava Brooks |
| 7 | Electronics | 60.00 | completed | 2024-02-01 | Maya Chen |
| 8 | Toys | 0.00 | completed | 2024-01-10 | Ethan Cole |
Expected Output
| category | transaction_count | total_sales |
|---|
| Home | 2 | 220.00 |
| Electronics | 2 | 170.00 |
| Books | 2 | 50.50 |
| Toys | 1 | 0.00 |
