NovaCart wants a quick view of product performance trends from a single sales table. Write a SQL query to summarize monthly sales activity and highlight the strongest categories.
sale_month and category.status = 'completed' and sale_date is not NULL.sale_month ascending, then total_revenue descending.sales
| column | type | description |
|---|---|---|
| sale_id | INT | Primary key for each sale |
| sale_date | DATE | Date the sale was recorded |
| category | VARCHAR(50) | Product category |
| amount | DECIMAL(10,2) | Sale amount |
| status | VARCHAR(20) | Sale status such as completed, returned, or pending |
| sale_id | sale_date | category | amount | status |
|---|---|---|---|---|
| 1 | 2024-02-10 | Electronics | 300.00 | completed |
| 2 | 2024-01-05 | Books | 40.00 | completed |
| 3 | 2024-01-20 | Electronics | 120.00 | completed |
| 4 | 2024-01-25 | Books | 15.00 | returned |
| 5 | 2024-02-02 | Home | 80.00 | completed |
| 6 | 2024-02-18 | Electronics | 220.00 | pending |
| 7 | 2024-01-12 | Home | 60.00 | completed |
| 8 | 2024-03-01 | Books | 55.00 | completed |
| 9 | 2024-03-03 | Electronics | 500.00 | completed |
| 10 | NULL | Home | 90.00 | completed |
| sale_month | category | total_orders | total_revenue |
|---|---|---|---|
| 2024-01 | Electronics | 1 | 120.00 |
| 2024-01 | Home | 1 | 60.00 |
| 2024-01 | Books | 1 | 40.00 |
| 2024-02 | Electronics | 1 | 300.00 |
| 2024-02 | Home | 1 | 80.00 |
| 2024-03 | Electronics | 1 | 500.00 |
| 2024-03 | Books | 1 | 55.00 |
| Column | Type | Description |
|---|---|---|
| sale_idPK | INT | Unique identifier for each sale |
| sale_date | DATE | Date the sale occurred |
| category | VARCHAR(50) | Product category |
| amount | DECIMAL(10,2) | Revenue amount for the sale |
| status | VARCHAR(20) | Sale processing status |
{"sales":[[1,"2024-02-10","Electronics","300.00","completed"],[2,"2024-01-05","Books","40.00","completed"],[3,"2024-01-20","Electronics","120.00","completed"],[4,"2024-01-25","Books","15.00","returned"],[5,"2024-02-02","Home","80.00","completed"],[6,"2024-02-18","Electronics","220.00","pending"],[7,"2024-01-12","Home","60.00","completed"],[8,"2024-03-01","Books","55.00","completed"],[9,"2024-03-03","Electronics","500.00","completed"],[10,null,"Home","90.00","completed"],[11,"2024-02-14","Books",Output[["2024-01","Electronics","1","120.00"],["2024-01","Home","1","60.00"],["2024-01","Books","1","40.00"],["2024-02","Electronics","1","300.00"],["2024-02","Home","1","80.00"],["2024-02","Books","1","0.00"],["2024-03","Electronics","1","500.00"],["2024-03","Books","1","55.00"]]