A
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.
| Column | Type | Description |
|---|---|---|
| idPK | 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 |
| Column | Type | Description |
|---|---|---|
| idPK | INT | Primary key for product records |
| name | VARCHAR(255) | Name of the product |
| category | VARCHAR(255) | Category of the product |
{"sales":[[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":[[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"]]}Output[["Electronics","2024-01","300"],["Electronics","2024-02","650"],["Home Goods","2024-01","150"]]
