SQL Variance Analysis by Region | Dataford Interview Questions

Write a SQL query to analyze monthly operational spend by region, compare it to budget, and flag variances greater than 10%.

Use the provided regional budget and operational spend data. Treat a variance as the percentage difference between actual spend and budget, using the budget as the denominator. Include budgeted region-months even when no spend exists.

Output

One row per budgeted region and month
Columns: region_name, spend_month, actual_spend, budget_amount, variance_amount, variance_pct, and variance_flag
Flag rows when the absolute variance exceeds 10%; order by region name and month

Schema

regions

Column	Type	Description
region_idPK	INT	Unique regional identifier
region_name	VARCHAR(100)	Operational region name

regional_budgets

Column	Type	Description
budget_idPK	INT	Unique budget record identifier
region_id	INT	References the budgeted region
month_start	DATE	First day of the budget month
budget_amount	NUMERIC(12,2)	Approved operational budget for the region and month

operational_spend

Column	Type	Description
spend_idPK	INT	Unique operational spend record identifier
region_id	INT	Region associated with the spend
month_start	DATE	First day of the spend month
amount	NUMERIC(12,2)	Operational spend amount

Your question is SQL Variance Analysis by Region. 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.

Problem