Task
A financial analysis team needs to assess the variance drivers affecting quarterly performance for different product categories. Write a SQL query to summarize the total revenue and compare it to the previous quarter's revenue to identify variance drivers.
Requirements
- Calculate total revenue for each product category for the current and previous quarters.
- Compute the variance in revenue between the two quarters.
- Rank categories based on the variance amount.
- Filter results to show only categories with a variance greater than a specified threshold.
Schema
sales (id, product_category, transaction_date, amount)
| id | product_category | transaction_date | amount |
|---|
| 1 | Electronics | 2024-01-15 | 300 |
| 2 | Clothing | 2024-01-20 | 150 |
| 3 | Electronics | 2024-02-15 | 400 |
| 4 | Clothing | 2024-02-10 | 250 |
| 5 | Books | 2024-01-05 | 200 |
| 6 | Electronics | 2024-03-01 | 500 |
| 7 | Books | 2024-03-15 | 300 |
| 8 | Clothing | 2024-03-20 | 350 |
Expected Output
| product_category | current_quarter_revenue | previous_quarter_revenue | variance | rank |
|---|
| Electronics | 1200 | 700 | 500 | 1 |
| Clothing | 800 | 400 | 400 | 2 |
| Books | 500 | 300 | 200 | 3 |
