Task
You are given monthly spend and budget data. Write a PostgreSQL query that compares actual versus budgeted spend by department for the current month, returning each department’s actual spend, budgeted spend, variance, and variance percentage. Use the current month based on CURRENT_DATE.
Schema
| Table | Column | Type | Description |
|---|
departments | department_id | INT | Primary key for each department |
departments | department_name | VARCHAR(100) | Department name |
actual_spend | spend_id | INT | Primary key for each spend record |
actual_spend | department_id | INT | Department reference |
actual_spend | spend_date | DATE | Date of the actual spend |
actual_spend | amount | DECIMAL(12,2) | Actual spend amount |
budget_spend | budget_id | INT | Primary key for each budget record |
budget_spend | department_id | INT | Department reference |
budget_spend | budget_month | DATE | First day of the budget month |
budget_spend | budget_amount | DECIMAL(12,2) | Budgeted spend amount |
Sample data
departments
| department_id | department_name |
|---|
| 1 | Finance |
| 2 | Operations |
| 3 | Sales |
| 4 | HR |
actual_spend
| spend_id | department_id | spend_date | amount |
|---|
| 1 | 1 | 2026-06-02 | 1200.00 |
| 2 | 1 | 2026-06-18 | 800.00 |
| 3 | 2 | 2026-06-05 | 1500.00 |
| 4 | 2 | 2026-06-20 | 700.00 |
| 5 | 3 | 2026-06-10 | 900.00 |
| 6 | 3 | 2026-05-28 | 400.00 |
| 7 | 4 | 2026-06-12 | 0.00 |
| 8 | 4 | 2026-06-25 | 300.00 |
| 9 | 2 | 2026-04-15 | 200.00 |
| 10 | 3 | 2026-06-30 | NULL |
budget_spend
| budget_id | department_id | budget_month | budget_amount |
|---|
| 1 | 1 | 2026-06-01 | 2200.00 |
| 2 | 2 | 2026-06-01 | 1800.00 |
| 3 | 3 | 2026-06-01 | 1000.00 |
| 4 | 4 | 2026-06-01 | 250.00 |
| 5 | 1 | 2026-05-01 | 2100.00 |
| 6 | 2 | 2026-05-01 | 1700.00 |
| 7 | 5 | 2026-06-01 | 500.00 |
| 8 | 3 | 2026-04-01 | 950.00 |
Expected output
| department_name | actual_spend | budgeted_spend | variance | variance_pct |
|---|
| Finance | 2000.00 | 2200.00 | -200.00 | -9.09 |
| Operations | 2200.00 | 1800.00 | 400.00 | 22.22 |
| Sales | 900.00 | 1000.00 | -100.00 | -10.00 |
| HR | 300.00 | 250.00 | 50.00 | 20.00 |
