A retail analytics team wants to analyze monthly sales performance by product category. You are given two tables: sales records individual sales transactions, and products contains product details.
Write a SQL query to calculate the total sales amount for each product category and month.
sales and products to associate each sale with its category.YYYY-MM format).sales
| Column | Type | Description |
|---|---|---|
| id | INT | Primary key for sales records |
| product_id | INT | Foreign key referencing products |
| sale_date | DATE | Date of the sale |
| amount | DECIMAL(10,2) | Sale amount |
products
| Column | Type | Description |
|---|---|---|
| id | INT | Primary key for products |
| name | VARCHAR(255) | Name of the product |
| category | VARCHAR(255) | Category of the product |
sales
| id | product_id | sale_date | amount |
|---|---|---|---|
| 1 | 101 | 2024-01-15 | 200 |
| 2 | 102 | 2024-01-20 | 150 |
| 3 | 101 | 2024-02-05 | 300 |
| 4 | 103 | 2024-02-10 | 250 |
| 5 | 101 | 2024-01-25 | 100 |
| 6 | 102 | 2024-01-30 | 50 |
| 7 | 103 | 2024-02-15 | 400 |
| 8 | 104 | 2024-01-10 | 0 |
| 9 | 105 | 2024-01-12 | NULL |
products
| id | name | category |
|---|---|---|
| 101 | Widget A | Electronics |
| 102 | Widget B | Home Goods |
| 103 | Widget C | Electronics |
| 104 | Widget D | Toys |
| 105 | Widget E | Electronics |
| 106 | Widget F | Furniture |
| category | month | total_sales |
|---|---|---|
| Electronics | 2024-01 | 300 |
| Electronics | 2024-02 | 650 |
| Home Goods | 2024-01 | 150 |
Only include category-month combinations with total sales greater than zero.