SQL Challenger
0 of 250 complete

Lessons

0 of 250 complete

Saved in this browser

Lesson 239 of 250

Hard

Find payment reconciliation discrepancies

Your task

Return order_id, expected_cents, recorded_cents, and issue for discrepancies only. Completed orders expect their full amount; refunded and cancelled orders expect zero. Net ledger charges minus refunds. Use orphan_payment with NULL expected cents if the order is missing, missing_payment with zero recorded cents for a positive expected amount with no ledger, and amount_mismatch for any other nonmatching amount. Sort by order ID.

A reconciliation should reveal unexpected ledger entries, missing payments, and incorrect net amounts.

Sample data

Choose a table
idcustomer_idordered_onchannelstatustotal_cents
20112025-01-04onlinecompleted2500
20222025-01-10storecompleted1800
20322025-01-10storecompleted3200
20432025-01-18marketplacerefunded5000
20542025-02-02storecompleted2700
20612025-02-04onlinecompleted4000
20752025-02-18onlinecancelled2400
20862025-02-28onlinecompleted1700
20942025-03-16onlinecompleted2900
21082025-03-09storecompleted3100

Expected output

order_idexpected_centsrecorded_centsissue
20527002500amount_mismatch
20640003500amount_mismatch
2070900amount_mismatch
21031000missing_payment
999NULL800orphan_payment
Show a hint

Full outer join orders with a ledger summary, then classify discrepancies in that order.

Data quality and reconciliation guide

Keep raw values available while you clean them

The staged_orders table is an import, so references, statuses, and amounts are text and may contain missing or malformed values. Trim whitespace before testing a field. An empty string after trimming can be turned into NULL to give missing data one representation.

SELECT import_id, NULLIF(TRIM(order_ref), '') AS clean_ref
FROM staged_orders
ORDER BY import_id;

Compare records at the same grain

The payment ledger may have several charges or refunds for one order. Aggregate that ledger to one row per order before comparing it with shop_orders. Keep unmatched keys visible so a reconciliation reveals missing orders and missing payments.

SELECT order_id,
  SUM(CASE WHEN kind = 'charge' THEN amount_cents ELSE -amount_cents END) AS net_cents
FROM payment_ledger
GROUP BY order_id
ORDER BY order_id;
SQLiteCtrl / ⌘ + Enter

Press Control+Space for SQL suggestions. Use the arrow keys to select an option and Enter to insert it.

Results

Loading SQLite…