task Reconciliation & quality gates · hard

Reconcile orders, payments and refunds

For every non-cancelled order, compute order_total (lines), paid (payments) and refunded (refunds), and classify payment_check: 'unpaid' when nothing was paid, 'underpaid' when paid < order_total, 'overpaid' when paid > order_total, otherwise 'ok'. Return id, order_total, paid, refunded and payment_check for orders that are not 'ok'.

what to return

Columns

idorder_totalpaidrefundedpayment_check in this order

Rows

5 rows · any order

Watch for

Missing payments or refunds count as 0.

Round order_total, paid and refunded to 2 decimals and classify using the rounded values.

Refunds don't change payment_check (a refund is a separate event).

Skill

Reconcile several sources without double counting and classify mismatches. Refresher: Multi-grain joins without fan-out →

expected result

Your query should return exactly these columns and values. Run it and compare.

data you can query

Sample rows from each table. Each column shows its type underneath; → marks a link to another table.