Task
You are given departmental budget data and actual expense transactions. Write a PostgreSQL query that compares actual spend versus budget by department and returns each department’s budget, actual spend, variance, and variance percentage.
Schema
| table | column | type | description |
|---|
| departments | department_id | INT | Primary key for each department |
| departments | department_name | VARCHAR(100) | Department name |
| budgets | budget_id | INT | Primary key for each budget record |
| budgets | department_id | INT | Foreign key to departments.department_id |
| budgets | budget_month | DATE | Month the budget applies to |
| budgets | budget_amount | DECIMAL(12,2) | Approved budget amount |
| expenses | expense_id | INT | Primary key for each expense transaction |
| expenses | department_id | INT | Foreign key to departments.department_id |
| expenses | expense_date | DATE | Date of the expense |
| expenses | amount | DECIMAL(12,2) | Expense amount |
| expenses | status | VARCHAR(20) | Expense status, such as approved or pending |
Sample data
| departments | | |
|---|
| 1 | Finance | |
| 2 | People Ops | |
| 3 | IT | |
| 4 | Sales | |
| budgets | | | |
|---|
| 101 | 1 | 2024-04-01 | 12000.00 |
| 102 | 2 | 2024-04-01 | 8000.00 |
| 103 | 3 | 2024-04-01 | 15000.00 |
| 104 | 4 | 2024-04-01 | 10000.00 |
| expenses | | | | |
|---|
| 201 | 1 | 2024-04-03 | 2500.00 | approved |
| 202 | 1 | 2024-04-18 | 4100.00 | approved |
| 203 | 2 | 2024-04-05 | 3000.00 | approved |
| 204 | 2 | 2024-04-20 | 2800.00 | pending |
| 205 | 3 | 2024-04-10 | 9000.00 | approved |
| 206 | 3 | 2024-04-22 | 2400.00 | approved |
| 207 | 4 | 2024-04-12 | 1200.00 | approved |
| 208 | 4 | 2024-04-25 | 1500.00 | approved |
| 209 | 4 | 2024-04-27 | 700.00 | rejected |
Expected output
| department_name | budget_amount | actual_spend | variance | variance_pct |
|---|
| Finance | 12000.00 | 6600.00 | -5400.00 | -45.00 |
| IT | 15000.00 | 11400.00 | -3600.00 | -24.00 |
| People Ops | 8000.00 | 3000.00 | -5000.00 | -62.50 |
| Sales | 10000.00 | 2700.00 | -7300.00 | -73.00 |
