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 |