How do you reconcile two tables in SQL?

Define the matching key and row grain, validate duplicates, aggregate each side to the same grain, then preserve unmatched records from both sides. Compare amounts and reconcile the final totals back to the inputs. A matching grand total alone does not prove every record is correct.

This synthetic e-commerce case is useful practice for data analyst roles in the US, UK and Europe. All amounts are illustrative USD values. It is a data-quality exercise, not accounting advice or a claim about a named employer’s interview.

Agree on what should match

Our orders table contains one row per order and an expected amount. The payments table contains successful captured payments only. One order can have multiple legitimate payment records. The order amounts and payment amounts use the same currency and include the same components. Refunds, fees, tax adjustments and exchange-rate differences are excluded from this small example.

Before applying this pattern to a real system, select a reporting cutoff and allow for payment arrival delays. An order placed moments before the cutoff may not yet have a corresponding captured payment. Label that timing difference rather than automatically calling it lost revenue.

We store integer cents to make the sample arithmetic exact: 10,000 cents means $100.00. In a multi-currency system, retain a currency column and reconcile within currency. Do not add USD, GBP and EUR amounts together or assume every currency has two decimal places.

Understand the expected exceptions

• A: a $100 order with one $100 payment should match.
• B: a $50 order has two different payments of $20 and $30. Both are legitimate and together should match.
• C: a $75 order has no payment in the extract.
• D: a $20 order has only $15 captured, leaving a $5 difference.
• X: a $40 payment references an order absent from the order extract. Its exact source row appears twice.

The repeated X row is an ingestion duplicate in this controlled dataset. Two different payment IDs for the same order are not duplicates merely because they share an order ID.

Run the complete SQLite example

Paste this query into SQLite, or execute it with Python’s built-in sqlite3 module. It includes its own input data and needs no external files.

WITH orders(order_id, expected_cents) AS (
 VALUES ('A',10000),('B',5000),('C',7500),('D',2000)
), payments(payment_id, order_id, amount_cents) AS (
 VALUES ('p1','A',10000),('p2','B',2000),('p3','B',3000),
        ('p4','D',1500),('p5','X',4000),('p5','X',4000)
), clean_payments AS (
 SELECT DISTINCT payment_id, order_id, amount_cents FROM payments
), paid AS (
 SELECT order_id, SUM(amount_cents) AS paid_cents
 FROM clean_payments GROUP BY order_id
)
SELECT o.order_id AS order_id, o.expected_cents,
       COALESCE(p.paid_cents,0) AS paid_cents,
       COALESCE(p.paid_cents,0)-o.expected_cents AS difference_cents,
       CASE WHEN p.order_id IS NULL THEN 'missing'
            WHEN p.paid_cents=o.expected_cents THEN 'matched'
            ELSE 'amount mismatch' END AS result
FROM orders o LEFT JOIN paid p ON p.order_id=o.order_id
UNION ALL
SELECT p.order_id, 0, p.paid_cents, p.paid_cents, 'orphan payment'
FROM paid p LEFT JOIN orders o ON o.order_id=p.order_id
WHERE o.order_id IS NULL
ORDER BY order_id;

Verified output: A and B are matched; C is missing with a difference of −7,500 cents; D has an amount mismatch of −500 cents; X is an orphan payment with a difference of +4,000 cents.

The first left join retains every order. The second branch adds payments that have no matching order. UNION ALL combines these disjoint groups. The result contains one row for each order ID present on either side, provided the input assumptions hold.

SQLite’s SELECT reference documents joins, DISTINCT and compound queries. Its aggregate reference explains SUM. The query groups payment amounts before joining so order B does not create two copies of its expected amount.

Reconcile the reconciliation

The expected orders total is $245. After removing the exact repeated row, all payments total $205. The signed difference is −$40: −$75 for C, −$5 for D and +$40 for X.

That net difference hides three separate exceptions. Reporting only “$40 short” loses the missing-order reference and understates the amount that needs investigation. Show the exception count and the amounts by category, alongside the net total.

If you join raw payments to orders and sum expected order amounts, B’s $50 expectation appears twice. If you use only an inner join, C and X disappear. Both errors can make a report look tidy while removing the records that reconciliation was meant to find.

When DISTINCT is not enough

The sample removes an exact duplicate across payment ID, order ID and amount. That is safe only because the exercise explicitly identifies it as an ingestion duplicate. In a real feed, first count repeated payment IDs and inspect whether their attributes disagree.

If the same payment ID has both $40 and $45, DISTINCT keeps both rows. Summing them would be wrong. Quarantine the conflicting ID or apply the source system’s documented version rule using a reliable update sequence. Do not pick the largest amount or the latest timestamp simply to make the query run.

Also reject or separately report null IDs and unknown amounts. SUM can ignore null amounts; a plausible total can therefore conceal incomplete input. Converting a missing joined row to zero is a presentation choice here, not permission to treat unknown payment amounts as known zero values.

Checks to explain in an interview

1. Confirm order IDs are unique and non-null before the join.
2. Validate payment IDs and distinguish legitimate split payments from duplicated extraction rows.
3. Reconcile source row counts and amounts before and after deduplication.
4. Keep unmatched records on both sides; verify C and X remain visible.
5. Document currency, payment status, cutoff and exclusions next to the result.

A clear interview answer is: “I would aggregate valid payment records to order level, retain exceptions from both systems and tie totals back to the extracts. Then I would investigate timing, mapping and status differences before describing an exception as a financial loss.”

Practice the next step

Add a valid $5 payment for D: D should become matched and total payments should become $210. Remove the repeated p5 row: the result should stay unchanged. Add a conflicting amount for p5: the data-quality gate should fail before reconciliation rather than silently combining the rows.

Continue with SQL joins, pandas merge validation and business case studies. For coached practice, review interview plans and availability. All interview preparation sessions and feedback are conducted in English.