Compare Department Spend to Budget

MediumSQL · PostgreSQL

Problem

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

departments

Column	Type	Description
department_idPK	INT	Primary key for the department
department_name	VARCHAR(100)	Department name

budgets

Column	Type	Description
budget_idPK	INT	Primary key for the budget record
department_id	INT	References departments.department_id
budget_month	DATE	Month the budget applies to
budget_amount	DECIMAL(12,2)	Approved budget amount for the month

expenses

Column	Type	Description
expense_idPK	INT	Primary key for the expense transaction
department_id	INT	References departments.department_id
expense_date	DATE	Date the expense was incurred
amount	DECIMAL(12,2)	Expense amount
status	VARCHAR(20)	Expense status such as approved, pending, or rejected

Tablesdepartmentsbudgetsexpenses

Your question is Compare Department Spend to Budget. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.