Order vs payment reconciliation

SQL data engineering interview problem. Difficulty: advanced. Pattern: Reconciliation. About 40 minutes. Part of the Pro drill bank.

Finance closes the books each day. They need to know which orders do not match the money that was actually received. Orders can have several payment attempts (some failed, some refunded), so you must add up only the captured payments, which are the ones where money really arrived.

Join orders to payments and build a break table, a list of orders where the money does not match. For each order with order_status of paid, pending or refunded, add up the amounts of its payments with status = 'captured'. If the order has no captured payment, the captured amount is 0. Return the order only when the order total and the captured amount are different (after rounding to 2 decimals). Return order_id, order_status, order_total, captured_amount and delta. delta is ROUND(order_total - captured_amount, 2). Order by the size of the delta (ABS(delta)) from large to small, then by order_id.

Requirements

• Only look at orders with status paid, pending or refunded.
• Sum only the payments with status captured.
• Use 0 when an order has no captured payment.
• Return only orders where the rounded difference is not zero.
• Order by ABS(delta) descending, then order_id.

Constraints

• Start from orders and use a LEFT JOIN, so orders with no payment at all are still counted.
• Add up the captured payments first, one number for each order, then join. This avoids repeating the order total.
• Use COALESCE(captured, 0) so a missing sum becomes 0.

Examples

Input: -- orders order_id | order_status | order_total ORD-0101 | pending | 1271.19 ORD-0114 | paid | 96.18 -- payments order_id | amount | status ORD-0101 | 1276.50 | failed ORD-0114 | 94.73 | captured Output: order_id | order_status | order_total | captured_amount | delta ORD-0101 | pending | 1271.19 | 0.0 | 1271.19 ORD-0114 | paid | 96.18 | 94.73 | 1.45 Why this passes: ORD-0101's only payment attempt failed (not captured), so captured_amount is COALESCE'd to 0 and the full order_total is the break. ORD-0114 has a captured payment, but it's 1.45 short of order_total.

Topics: finance, joins, data quality.

More SQL interview questions · All interview problems · Learn data engineering