Task
You are given monthly sales and cost data for product lines. Write a PostgreSQL query that breaks the month-over-month change in revenue and cost into key components: price effect, volume effect, and mix effect for each product line. Return the current month, prior month, product line, and the component values needed to explain the change.
Use the provided tables and keep the result at the product-line level. The query should compare each month to the immediately previous month for the same product line.
Schema
| Table | Column | Type | Description |
|---|
product_lines | product_line_id | INT | Primary key for each product line |
product_lines | product_line_name | VARCHAR(100) | Business-facing product line name |
monthly_financials | financial_id | INT | Primary key for the monthly record |
monthly_financials | product_line_id | INT | Foreign key to product_lines.product_line_id |
monthly_financials | month_start | DATE | First day of the month |
monthly_financials | units_sold | INT | Units sold in the month |
monthly_financials | revenue | DECIMAL(12,2) | Monthly revenue |
monthly_financials | cost | DECIMAL(12,2) | Monthly cost |
Sample data
| product_line_id | product_line_name |
|---|
| 1 | Advisory Services |
| 2 | Audit Support |
| 3 | Tax Compliance |
| financial_id | product_line_id | month_start | units_sold | revenue | cost |
|---|
| 1 | 1 | 2024-01-01 | 100 | 20000.00 | 12000.00 |
| 2 | 1 | 2024-02-01 | 120 | 25200.00 | 14160.00 |
| 3 | 1 | 2024-03-01 | 110 | 24750.00 | 13860.00 |
| 4 | 2 | 2024-01-01 | 80 | 16000.00 | 9800.00 |
| 5 | 2 | 2024-02-01 | 0 | 0.00 | 1500.00 |
| 6 | 2 | 2024-03-01 | 95 | 19950.00 | 11875.00 |
| 7 | 3 | 2024-01-01 | 60 | 9000.00 | 5400.00 |
| 8 | 3 | 2024-03-01 | 70 | 11200.00 | 6650.00 |
Expected output
| product_line_name | current_month | prior_month | revenue_change | cost_change |
|---|
| Advisory Services | 2024-02-01 | 2024-01-01 | 5200.00 | 2160.00 |
| Advisory Services | 2024-03-01 | 2024-02-01 | -450.00 | -300.00 |
| Audit Support | 2024-02-01 | 2024-01-01 | -16000.00 | -8300.00 |
| Audit Support | 2024-03-01 | 2024-02-01 | 19950.00 | 10375.00 |
