Context

Finance and operations teams often need a repeatable way to compare budgeted spend against actual spend each month. Interviewers ask this to assess whether you can translate a business reporting process into clear SQL logic.

Core question

Walk through how you would conduct a monthly budget variance analysis for a large department using SQL. In your answer, explain:

1. How you would structure the source data for budgets and actuals
2. How you would aggregate data to the monthly level
3. How you would calculate dollar and percentage variance
4. How you would handle missing values, late postings, and unusual transactions
5. How you would present the final output so finance leaders can review it quickly

Scope guidance

Keep the answer practical and SQL-focused. The interviewer is not looking for advanced optimization or complex modeling here; they want a clean process, sensible assumptions, and a clear explanation of the core SQL steps and checks.

Monthly aggregation

The first step is to roll detailed transactions up to a consistent monthly grain. In PostgreSQL, this is commonly done with DATE_TRUNC('month', date_column) so budget and actual data can be compared on the same timeline.

SELECT DATE_TRUNC('month', transaction_date) AS month_start, SUM(amount) AS actual_spend
FROM department_expenses
GROUP BY DATE_TRUNC('month', transaction_date);

Variance calculation

Budget variance is usually measured in both absolute dollars and percentage terms. Dollar variance shows the size of the gap, while percentage variance helps compare performance across cost centers with different budget sizes.

SELECT budget_amount, actual_amount,
       actual_amount - budget_amount AS variance_amount,
       CASE
         WHEN budget_amount = 0 OR budget_amount IS NULL THEN NULL
         ELSE ROUND(((actual_amount - budget_amount) / budget_amount) * 100, 2)
       END AS variance_pct
FROM monthly_budget_summary;

NULL and data quality handling

Budget or actual values may be missing for some months, especially when data arrives late or a budget was not loaded. COALESCE is used to avoid incorrect NULL arithmetic and to make reporting behavior explicit.

SELECT month_start,
       COALESCE(budget_amount, 0) AS budget_amount,
       COALESCE(actual_amount, 0) AS actual_amount
FROM monthly_budget_summary;

Categorizing results with CASE

A CASE expression helps classify each month as over budget, under budget, or on target. This makes the output easier for stakeholders to scan without interpreting raw numbers alone.

SELECT month_start,
       CASE
         WHEN actual_amount > budget_amount THEN 'Over Budget'
         WHEN actual_amount < budget_amount THEN 'Under Budget'
         ELSE 'On Budget'
       END AS variance_status
FROM monthly_budget_summary;