← All practice problems

Find payment reconciliation breaks

senior · sql🔒Walkthroughs

NEW

Find payment reconciliation breaks

Reconcile every order's amount_usd against the money actually captured for it in payments. Report the orders that don't reconcile.

Rules: - Only payments with status = 'captured' count. A 'failed' attempt contributes nothing (note one order has both a failed and a captured row). - An order with no captured payment has a captured total of 0 and is a break (don't let it vanish). - Exclude cancelled orders entirely — they're not expected to be paid. - A break is any remaining order where captured_total <> amount_usd.

Columns: order_id, amount_usd, captured_total, diff (= amount_usd - captured_total). Sort by order_id.

Tables & sample data

orders

Loading preview…

payments

Loading preview…

query.sql

Loading…

Loading editor…