Unit 10.01: Magnitude checks that catch order-of-magnitude errors

Unit ID: SQL-M10-U02 - Estimated active time: 13-16 minutes Objective: use rough arithmetic to detect errors that detailed review misses.

Detailed review is bad at scale errors

Reviewers read logic carefully and numbers casually. A revenue figure ten times too large passes review routinely, because ₹2.7 crore and ₹27 crore look equally like "a revenue number".

A magnitude check catches it in seconds:

SELECT COUNT(*)                              AS orders,        -- 1000
       ROUND(AVG(order_total), 2)            AS avg_order,     -- 2701.46
       COUNT(*) * ROUND(AVG(order_total), 2) AS rough_estimate, -- 2701460.00
       SUM(order_total)                      AS actual         -- 2701463.00
FROM orders;

Estimate ₹27,01,460 against actual ₹27,01,463 - a ₹3 difference from rounding the average. Close enough to confirm the total is the right size.

Now recall the fan-out figure from Module 5: ₹91,47,789. Against a ₹27 lakh estimate, that is 3.4× too large - visible instantly with no knowledge of the query that produced it.

Bounds tell you what is plausible

SELECT MIN(order_total) AS smallest,   -- 501.00
       MAX(order_total) AS largest,    -- 4996.00
       COUNT(*)         AS orders      -- 1000
FROM orders;

Every order is between ₹501 and ₹4,996. So total revenue must fall between roughly ₹5.0L and ₹50.0L. ₹91.5L is outside the possible range - the fan-out bug is arithmetically impossible, not merely suspicious.

That is a stronger statement than "looks too big", and it takes one query.

Sanity checks worth running on any measure

SELECT
  COUNT(*) FILTER (WHERE order_total <= 0)     AS non_positive,   -- 0
  COUNT(*) FILTER (WHERE order_total IS NULL)  AS missing,        -- 0
  COUNT(*)                                     AS rows            -- 1000
FROM orders;

Zero non-positive and zero missing. Had either been non-zero, any average or total would need a caveat - and you would want to know before publishing, not after.

Practice

Someone reports average order value of ₹27,014.60. Without seeing their query, explain why it is wrong.

Takeaway

Estimate with count × average, and check the result against min/max bounds. An impossible number is identifiable without reading the query that produced it.

---

Mark complete