Variance Analysis in SQL Reporting

Context

Variance analysis is a common analytics task in finance, operations, and product reporting. Interviewers ask this to see whether you can turn a business question like “why did performance change?” into a structured SQL workflow.

Core question

Explain how you would approach variance analysis using SQL. Your answer should cover:

1. how you define the baseline for comparison (budget, prior month, prior year, forecast, or target),
2. how you calculate absolute and percentage variance,
3. how you break variance down by dimensions such as product, region, or month, and
4. what SQL features or supporting tools you would use to make the analysis reliable and repeatable.

Scope guidance

The interviewer is not looking for a single query only. They want a practical explanation of the analysis process, the SQL patterns you would use, common pitfalls such as nulls or divide-by-zero, and how you would communicate findings from the output.

Baseline Definition

Variance analysis starts by choosing the correct comparison point. Common baselines include budget, forecast, prior period, or target, and the choice changes the business meaning of the result.

SELECT month, actual_revenue, budget_revenue, actual_revenue - budget_revenue AS variance
FROM monthly_finance;

Absolute vs Percentage Variance

Absolute variance shows the raw difference between two values, while percentage variance normalizes that difference relative to the baseline. Both are useful because a large dollar change may be small in percentage terms, and vice versa.

SELECT actual_amount - target_amount AS abs_variance,
       CASE WHEN target_amount = 0 OR target_amount IS NULL THEN NULL
            ELSE ROUND((actual_amount - target_amount) * 100.0 / target_amount, 2)
       END AS pct_variance
FROM metrics;

Aggregation by Business Dimension

Variance becomes actionable when broken down by dimensions such as month, region, product, or channel. SQL aggregation with GROUP BY helps identify where the largest positive or negative changes are occurring.

SELECT region, SUM(actual_sales) AS actual_sales, SUM(target_sales) AS target_sales,
       SUM(actual_sales) - SUM(target_sales) AS variance
FROM sales_summary
GROUP BY region;

Data Quality and Null Handling

Variance calculations can be misleading if missing values, duplicate rows, or zero baselines are not handled carefully. SQL functions such as COALESCE and CASE are important for producing stable calculations and avoiding divide-by-zero errors.

SELECT COALESCE(actual_amount, 0) - COALESCE(plan_amount, 0) AS variance
FROM plan_vs_actual;

Tooling for Repeatable Analysis

SQL is usually the core tool for extracting and aggregating data, but analysts often pair it with BI dashboards, spreadsheets, or notebooks for visualization and commentary. The strongest approach is to keep the metric logic in SQL and use downstream tools mainly for presentation.