Task
FinSight tracks department spending in a single budgeting table. Write a SQL query to report departments where actual spending differs from projected budget.
Requirements
- Return each
department with its total projected budget, total actual spend, and the variance (actual_spend - projected_budget).
- Include only departments where the totals do not match.
- Add a
status column that shows Over Budget when actual spend is greater than projected budget, otherwise Under Budget.
- Order the results by absolute variance descending, then by department name.
Table Definition
department_budget_actuals
| Column | Type | Description |
|---|
| id | INT | Primary key |
| department | VARCHAR(100) | Department name |
| projected_budget | DECIMAL(12,2) | Planned budget amount for the entry |
| actual_spend | DECIMAL(12,2) | Actual spend amount for the entry |
| report_month | DATE | Month of the budget record |
| cost_center | VARCHAR(50) | Cost center label |
Sample Data
| id | department | projected_budget | actual_spend | report_month | cost_center |
|---|
| 1 | Engineering | 5000.00 | 5200.00 | 2024-01-01 | Platform |
| 2 | Marketing | 3000.00 | 2800.00 | 2024-01-01 | Growth |
| 3 | Finance | 2000.00 | 2000.00 | 2024-01-01 | Accounting |
| 4 | Engineering | 4500.00 | 4300.00 | 2024-02-01 | Platform |
| 5 | Marketing | 3200.00 | 3500.00 | 2024-02-01 | Brand |
| 6 | HR | 1500.00 | 1400.00 | 2024-01-01 | People Ops |
Expected Output
| department | total_projected_budget | total_actual_spend | variance | status |
|---|
| Sales | 0.00 | 500.00 | 500.00 | Over Budget |
| Operations | 1800.00 | 1400.00 | -400.00 | Under Budget |
| Engineering | 9500.00 | 9700.00 | 200.00 | Over Budget |
| Marketing | 6200.00 | 6300.00 | 100.00 | Over Budget |
| HR | 1500.00 | 1400.00 | -100.00 | Under Budget |
