Unit 10.03: Explaining a variance instead of rounding it away

Unit ID: SQL-M10-U04 - Estimated active time: 13-16 minutes Objective: investigate a difference between two figures until it is fully accounted for.

A small variance is information, not noise

Your revenue figure is ₹26,85,905. Finance reports ₹27,01,463. The gap is ₹15,558 - 0.58%.

Small enough to round away. Do not.

SELECT ROUND(100.0 *
  (SELECT SUM(order_total) FROM orders WHERE status='pending') /
  (SELECT SUM(order_total) FROM orders), 2) AS pending_pct_of_total;
-- 0.58

The variance is exactly the pending orders. Once identified, it is not a discrepancy at all - it is a definition difference: you counted completed revenue, finance counted booked revenue. Both are right.

Why explaining beats adjusting

An unexplained 0.58% gap has two possible futures:

• Explained: "we report completed orders; finance reports booked, which includes 12 pending orders

worth ₹15,558." Everyone agrees, and the definitions are now documented.

• Adjusted: someone nudges a filter until the numbers match. The gap closes and the *reason*

disappears - along with any chance of noticing when the same 0.58% later means something different.

The second is how reporting systems accumulate untraceable fudges.

Decomposing a variance

Work through the candidates in order:

-- 1. Different population?
SELECT status, COUNT(*) AS orders, SUM(order_total) AS revenue
FROM orders GROUP BY status;
-- completed 988 / 2685905.00 - pending 12 / 15558.00

-- 2. Different period boundary?  (Module 8)
SELECT COUNT(*) FROM orders WHERE placed_at BETWEEN '2026-06-01' AND '2026-06-30';   -- 967
SELECT COUNT(*) FROM orders WHERE placed_at >= '2026-06-01' AND placed_at < '2026-07-01'; -- 1000

-- 3. Different grain?  (Module 5)
SELECT COUNT(*) FROM orders;                                                          -- 1000
SELECT COUNT(*) FROM orders o JOIN order_items i ON i.order_id=o.order_id;            -- 3400

Population, period, grain. Nearly every real variance is one of those three, and each has a query that settles it.

When the variance does not resolve

If you cannot account for it, say so plainly:

Limitation: our figure differs from finance by ₹15,558 (0.58%). The pending-order
            hypothesis accounts for the full difference. No unexplained variance remains.

or, when it genuinely does not close:

Limitation: ₹4,200 (0.16%) of the variance is unexplained. Investigation continues.

An acknowledged unexplained gap is defensible. A hidden one is not.

Practice

Your figure is ₹26,85,905 and another team reports ₹27,01,463. Write the one-line explanation you would put in the evidence note.

Takeaway

Chase every variance to a cause. Population, period, and grain explain nearly all of them - and an explained difference is a definition, not an error.

---

Mark complete