SQL for Spending Analysis | Dataford Interview Questions

Write a SQL query to analyze departmental spending by month, joining budget and transaction tables and handling missing values.

Return monthly results for every department-month appearing in either table. Use the monthly_budgets and transactions tables.

Output

One row per department and month
Columns: department_id, spending_month, budget_amount, actual_spending, and variance
Treat missing budget or spending amounts as zero, and calculate variance as actual spending minus budget
Order by spending_month, then department_id

monthly_budgets

Column	Type	Description
budget_idPK	INT	Unique budget record identifier
department_id	INT	Department receiving the budget
budget_month	DATE	First day of the budget month
budget_amount	NUMERIC(12,2)	Approved budget amount for the month

transactions

Column	Type	Description
transaction_idPK	INT	Unique transaction identifier
department_id	INT	Department charged for the transaction
transaction_date	DATE	Date on which the transaction occurred
amount	NUMERIC(12,2)	Transaction amount

Your question is SQL for Spending Analysis. Start with the requirements and the two 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.