Broken reconcile: cancelled orders vanish

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Joins. About 20 minutes. Part of the Pro drill bank.

Ticket from finance: 'The reconcile report is missing all cancelled orders. We expect to see every order, with the payment amount if there was one.' The cause is a join that only keeps orders that have a payment. Cancelled orders never got a payment, so they disappear.

The reconcile query joins orders to payments with an INNER JOIN, and that removes any order without a payment. Fix it so the report lists every order, with the payment amount when one exists and NULL when it does not. Return order_id, order_status, order_total and payment_amount (from payments.amount), with 1 row for each order, ordered by order_id.

Requirements

  • Include every order, also those with no payment.
  • Show payments.amount as payment_amount, or NULL when no payment exists.
  • Return 1 row per order_id.
  • Order by order_id.

Constraints

  • Start from orders, and use a LEFT JOIN to payments on order_id.
  • Do not add a WHERE filter on the payments table, as it would turn the LEFT JOIN back into an inner join.
  • Return only the 4 columns listed.

Examples

Input: order_id | order_status | order_total | payment row ORD-0115 | cancelled | 741.80 | (none) ORD-0001 | paid | 612.07 | 612.07 Output: order_id | order_status | order_total | payment_amount ORD-0001 | paid | 612.07 | 612.07 ORD-0115 | cancelled | 741.80 | NULL Why this passes: INNER JOIN drops ORD-0115 because cancelled orders never get a payment row. Reconcile needs the order with NULL payment_amount.

Topics: debugging, joins, reconciliation.

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

intermediate

Broken reconcile: cancelled orders vanish

Production ticket: The order-payment reconcile report uses INNER JOIN and drops orders with no payment row.

The reconcile job must list **every order** with an optional payment amount. Cancelled orders have no row in `payments`; the shipped query uses INNER JOIN and they disappear from the report. Fix the SQL so: - order_id, order_status, order_total, payment_amount - 1 row per order_id - payment_amount is NULL when no payment exists - ordered by order_id Start from the buggy INNER JOIN in the editor.