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.
department_name, division_name, latest_month_spend, prior_month_spend, variance_amount, and variance_share_of_total.actual_spend values for each department and month before calculating variance.0.variance_share_of_total to two decimal places.variance_share_of_total using the sum of positive variance amounts as the denominator.department_name in ascending order after sorting variance_amount descending.| Column | Type | Description |
|---|---|---|
| spend_idPK | INT | Primary key for each spend record |
| department_id | INT | Department identifier |
| spend_month | DATE | First day of the spend month |
| actual_spend | DECIMAL(12,2) | Actual spend for the month |
| budget_spend | DECIMAL(12,2) | Budgeted spend for the month |
| Column | Type | Description |
|---|---|---|
| department_idPK | INT | Primary key for department metadata |
| department_name | VARCHAR(100) | Department name |
| division_name | VARCHAR(100) | Division name |