NovaCart wants a monthly sales report by product category. Write a SQL query to calculate total sales per category for each month and show the month-over-month change within each category.
sales to products using product_id.amount is NULL.category and month formatted as YYYY-MM.category, month, total_sales, and sales_trend.sales_trend should be the current month's total minus the previous month's total in the same category. For the first month in a category, return NULL.category, then month.sales| column | type | description |
|---|---|---|
| id | INT | Primary key for each sale |
| product_id | INT | Product sold |
| sale_date | DATE | Date of the sale |
| amount | DECIMAL(10,2) | Sale amount; may be NULL |
products| column | type | description |
|---|---|---|
| id | INT | Primary key for each product |
| category | VARCHAR(255) | Product category |
| name | VARCHAR(255) | Product name |
products: (1, Electronics, Laptop), (3, Home Appliances, Vacuum Cleaner), (7, Home Appliances, Air Purifier)
sales: (1, 1, 2024-01-05, 200.00), (4, 3, 2024-02-15, 400.00), (11, 9, 2024-01-15, NULL)
| category | month | total_sales | sales_trend |
|---|---|---|---|
| Electronics | 2024-01 | 700.00 | NULL |
| Electronics | 2024-02 | 300.00 | -400.00 |
| Electronics | 2024-03 | 100.00 | -200.00 |
| Home Appliances | 2024-02 | 950.00 | NULL |
| Home Appliances | 2024-03 | 100.00 | -850.00 |
| 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 |
| category | VARCHAR(255) | Category of the product |
| name | VARCHAR(255) | Name of the product |
{"sales":[[8,6,"2024-01-25","350.00"],[2,2,"2024-01-10","150.00"],[11,9,"2024-01-15",null],[6,4,"2024-01-20","0.00"],[4,3,"2024-02-15","400.00"],[1,1,"2024-01-05","200.00"],[10,8,"2024-03-01","100.00"],[7,5,"2024-02-10","250.00"],[12,10,"2024-02-05","200.00"],[5,2,"2024-03-05","100.00"],[9,7,"2024-02-28","300.00"],[3,1,"2024-02-01","300.00"],["1","34","2024-02-22","204"],["2","61","2024-01-27","221"],["3","4","2024-02-19","411"],["4","21","2024-01-24","225"],["5","82","2024-02-21","255"],["6","1Output[["Electronics","2024-01","700.00",null],["Electronics","2024-02","300.00","-400.00"],["Electronics","2024-03","100.00","-200.00"],["Home Appliances","2024-02","950.00",null],["Home Appliances","2024-03","100.00","-850.00"]]
