A
A financial analytics platform needs to analyze revenue trends by product category over the past year. Write a SQL query to calculate the monthly total revenue for each category, along with the cumulative revenue for each category ordered by month.
category and transaction_monthcategory and transaction_monthtransactions (id, category, transaction_date, amount)
| id | category | transaction_date | amount |
|---|---|---|---|
| 1 | Electronics | 2023-01-05 | 500 |
| 2 | Books | 2023-01-12 | 150 |
| 3 | Electronics | 2023-02-10 | 300 |
| 4 | Clothing | 2023-02-15 | 200 |
| 5 | Books | 2023-03-20 | 100 |
| 6 | Clothing | 2023-03-25 | 250 |
| 7 | Electronics | 2023-03-30 | 400 |
| 8 | Books | 2023-04-05 | 200 |
| 9 | Clothing | 2023-04-10 | 300 |
| 10 | Electronics | 2023-04-15 | 700 |
| category | transaction_month | monthly_revenue | cumulative_revenue |
|---|---|---|---|
| Books | 2023-01 | 150 | 150 |
| Books | 2023-02 | 0 | 150 |
| Books | 2023-03 | 100 | 250 |
| Books | 2023-04 | 200 | 450 |
| Clothing | 2023-01 | 0 | 0 |
| Clothing | 2023-02 | 200 | 200 |
| Clothing | 2023-03 | 250 | 450 |
| Clothing | 2023-04 | 300 | 750 |
| Electronics | 2023-01 | 500 | 500 |
| Electronics | 2023-02 | 300 | 800 |
| Electronics | 2023-03 | 400 | 1200 |
| Electronics | 2023-04 | 700 | 1900 |
| Column | Type | Description |
|---|---|---|
| idPK | INT | Primary key |
| category | VARCHAR | Product category |
| transaction_date | DATE | Date of transaction |
| amount | DECIMAL | Transaction amount |
{"transactions":[[1,"Electronics","2023-01-05",500],[2,"Books","2023-01-12",150],[3,"Electronics","2023-02-10",300],[4,"Clothing","2023-02-15",200],[5,"Books","2023-03-20",100],[6,"Clothing","2023-03-25",250],[7,"Electronics","2023-03-30",400],[8,"Books","2023-04-05",200],[9,"Clothing","2023-04-10",300],[10,"Electronics","2023-04-15",700]]}Output[["Books","2023-01","150","150"],["Books","2023-03","100","250"],["Books","2023-04","200","450"],["Clothing","2023-02","200","200"],["Clothing","2023-03","250","450"],["Clothing","2023-04","300","750"],["Electronics","2023-01","500","500"],["Electronics","2023-02","300","800"],["Electronics","2023-03","400","1200"],["Electronics","2023-04","700","1900"]]
