17. Reconcile orders against payments
Asked in screens shaped like: Stripe, Capital One, Wise
A revenue report and a settlement report disagree. Find the orders where the paid amount does not match the order amount — this is the reconciliation check that belongs in a data quality test, not a dashboard.
Requirements
- Columns: order_id, order_amount, paid_amount, diff
- Only orders that have a payment row and where the two amounts differ
- Compare both amounts rounded to 2 decimals
- diff is order_amount - paid_amount, ordered by diff descending then order_id
Expected output
order_id, order_amount, paid_amount, diff
Row order is graded, so ORDER BY matters here.
Tables this problem reads. The full warehouse is available in thesandbox.
orders
~200 rows
Order headers. One row per order, so amount is the order total — not a line total.
- order_idINTEGER— 1000–1199
- customer_idTEXT— C01–C12
- countryTEXT— US, DE, IN, UK, BR
- amountREAL
- statusTEXT— 'completed' or 'cancelled'
- ordered_atTEXT— YYYY-MM-DD HH:MM:SS
payments
~182 rows
Settlements. Cancelled orders have no payment row, which is the anti-join case.
- payment_idINTEGER
- order_idINTEGER— joins orders
- methodTEXT— card, ach, wallet
- amountREAL
- paid_atTEXT
Pick a warehouse dialect above the editor. The engine is still SQLite; common syntax is rewritten before it runs.
SQLite cheat sheet
- Truncate to day
DATE(ordered_at) - Current date
DATE('now') - Conditional
CASE WHEN … THEN … ELSE … END - String aggregate
GROUP_CONCAT(col, ", ") - JSON field
json_extract(payload, '$.source') - Date difference
JULIANDAY(a) - JULIANDAY(b) - Filter a window
CTE, then WHERE rn = 1 - Median
ROW_NUMBER + COUNT(*) OVER ()
Revealed one at a time. The reference query stays in the Solution tab.
Hint 1
An inner join is right here — missing payments are a different check.
Hint 2
Compare the rounded values so float noise does not flag every row.
Hint 3
Round the difference the same way you round the inputs.
SELECT o.order_id,
ROUND(o.amount, 2) AS order_amount,
ROUND(p.amount, 2) AS paid_amount,
ROUND(o.amount - p.amount, 2) AS diff
FROM orders o
JOIN payments p ON p.order_id = o.order_id
WHERE ROUND(o.amount, 2) <> ROUND(p.amount, 2)
ORDER BY diff DESC, o.order_id;Why it works
Never compare raw floats with = or <>. Round both sides to the business precision, or store money as integer minor units and compare exactly.
Run your query to see the result set.
Submit to run the checks.
Why it works
Never compare raw floats with = or <>. Round both sides to the business precision, or store money as integer minor units and compare exactly.
Pro problem
Hard problems unlock with Pro
Easy pads stay free. Medium and hard SQL and Python problems — editor, tests, hints, and solutions — open after you upgrade to Pro or coaching.
See plansPractice free easy problems