Reconcile orders with payments

advanced Reconciliation Challenge 11 of 13

Finance asked QA to confirm that every paid or shipped order was paid exactly what its items cost. Find the orders where the amounts differ.

Requirements

Consider orders whose status is 'paid' or 'shipped'.
items_total is the sum of quantity * unit_price over the order’s items. paid_total is the sum of its payments, or 0 if there are none.
Compare both totals rounded to 2 decimal places, and return only orders where they differ.
Return order_id, items_total and paid_total (both rounded to 2 decimal places).

Expected output

order_id | items_total | paid_total 2039 | 399 | 0 2041 | 238.5 | 238.05

Checks (3)

○Sample data
○The missing payment for 2039 arrives
○Order 2031 is paid in two parts

Each check builds a fresh database and compares your result with the reference solution's: same number of columns in the same order (names are not checked), and the same rows in any order.

query.sqlExecutes on SQLite in your browser

Ctrl/⌘ + Enter to check query · your code is saved in this browser

Results

Use Run query to see your rows, then Check query to grade it.

Schema · Acme Store

SQLite

Loading the database…

“null” marks columns that allow NULL. Foreign keys are not enforced, as in many real test databases.

Reconcile orders with payments

Requirements

Expected output

Checks (3)

Related interview questions