Unit 13.02: Reconciling every figure you will report

Unit ID: SQL-M13-U03 - Estimated active time: 30-40 minutes Objective: prove each reported number before it enters the memo.

Reconcile before you write, not after someone asks

Module 10's checks, applied to each answer:

-- Parts sum to the whole (Q1, Q2)
SELECT 988 + 12 = 1000                                    AS counts_reconcile,
       2685905.00 + 15558.00 = 2701463.00                 AS revenue_reconciles;
-- true | true

-- Independent source agreement (Q8)
SELECT (SELECT SUM(amount) FROM payments)                             AS paid,       -- 2685905.00
       (SELECT SUM(order_total) FROM orders WHERE status='completed') AS owed,       -- 2685905.00
       (SELECT SUM(amount) FROM payments)
         - (SELECT SUM(order_total) FROM orders WHERE status='completed') AS diff;   -- 0.00

Zero difference between two independently populated tables. That is the strongest evidence in the whole capstone.

Magnitude and bounds

SELECT COUNT(*) * ROUND(AVG(order_total), 2) AS estimate,  -- 2701460.00
       SUM(order_total)                      AS actual,    -- 2701463.00
       MIN(order_total)                      AS floor,     -- 501.00
       MAX(order_total)                      AS ceiling    -- 4996.00
FROM orders;

Estimate within ₹3 of actual, and every order inside ₹501-₹4,996. Any figure outside those bounds is impossible rather than merely surprising (Module 10).

The completeness figures you must report

SELECT COUNT(*)                                     AS customers,       -- 4812
       COUNT(country)                               AS with_country,    -- 4512
       ROUND(100.0*COUNT(country)/COUNT(*), 1)      AS coverage_pct     -- 93.8
FROM customers;

Question 4's country split covers 93.8% of customers. Reporting the split without that number is the denominator failure from Module 3.

The reconciliation table for your memo

Every row has a value and the check that supports it.

Practice

Reconcile question 7. What two checks prove the item count is right?

-- 1. Every item belongs to a real order (Module 5)
SELECT COUNT(*) FROM order_items i
LEFT JOIN orders o ON o.order_id = i.order_id
WHERE o.order_id IS NULL;                             -- 0

-- 2. Every order has items, and the totals line up
SELECT COUNT(*) AS item_rows,                          -- 3400
       COUNT(DISTINCT order_id) AS orders_with_items   -- 1000
FROM order_items;

Takeaway

Every reported figure needs a check beside it. Build the reconciliation table as you go - reconstructing it afterwards is far harder.

---

Mark complete