You are given monthly spending data for several departments across two fiscal years. Write a PostgreSQL query that identifies the departments responsible for the largest increase in spending in the latest month compared with the prior month, and show each department’s variance contribution alongside the total variance across all departments.
Return only departments with a positive variance contribution, ranked from largest to smallest contribution. Use the latest month present in the data as the comparison point.
| Table | Column | Type | Description |
|---|---|---|---|
| department_spend | spend_id | INT | Primary key for each spend record |
| department_spend | department_id | INT | Department identifier |
| department_spend | spend_month | DATE | First day of the spend month |
| department_spend | actual_spend | DECIMAL(12,2) | Actual spend for the month |
| department_spend | budget_spend | DECIMAL(12,2) | Budgeted spend for the month |
| departments | department_id | INT | Primary key for department metadata |
| departments | department_name | VARCHAR(100) | Department name |
| departments | division_name | VARCHAR(100) | Division name |
department_spend
| spend_id | department_id | spend_month | actual_spend | budget_spend |
|---|---|---|---|---|
| 1 | 10 | 2024-05-01 | 120000.00 | 115000.00 |
| 2 | 20 | 2024-05-01 | 80000.00 | 82000.00 |
| 3 | 10 | 2024-06-01 | 150000.00 | 118000.00 |
| 4 | 20 | 2024-06-01 | 76000.00 | 83000.00 |
| 5 | 30 | 2024-05-01 | 45000.00 | 47000.00 |
| 6 | 30 | 2024-06-01 | 70000.00 | 48000.00 |
| 7 | 40 | 2024-06-01 | 30000.00 | 30000.00 |
| 8 | 50 | 2024-05-01 | 0.00 | 5000.00 |
| 9 | 50 | 2024-06-01 | 9000.00 | 5000.00 |
departments
| department_id | department_name | division_name |
|---|---|---|
| 10 | Health Services | Health and Human Services |
| 20 | Public Safety | Public Safety |
| 30 | Transportation | Transportation |
| 40 | Education Grants | Education |
| 50 | Administrative Services | Administration |
| department_name | division_name | latest_month_spend | prior_month_spend | variance_amount | variance_share_of_total |
|---|---|---|---|---|---|
| Health Services | Health and Human Services | 150000.00 | 120000.00 | 30000.00 | 0.48 |
| Transportation | Transportation | 70000.00 | 45000.00 | 25000.00 | 0.40 |
| Administrative Services | Administration | 9000.00 | 0.00 | 9000.00 | 0.14 |
