Your question is Find Spending Variance Drivers by Department. Start with the requirements and the two tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
| 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 |