task Join fan-out & cardinality · medium
Orders whose payments don't match
For delivered or shipped orders, return id, order_total (sum of quantity × unit_price), paid_total (sum of payments) and balance (order_total − paid_total) where paid_total differs from order_total.
what to return
- Columns
idorder_totalpaid_totalbalancein this order- Rows
- 3 rows · any order
- Watch for
- Orders with no payment have paid_total 0.
- Round all three amounts to 2 decimals and compare the rounded totals.
- Skill
- Compare totals from two child tables without fan-out. Refresher: Keep unmatched rows →
expected result
Your query should return exactly these columns and values. Run it and compare.
expected result3 rows · any order
| id | order_total | paid_total | balance |
|---|---|---|---|
| 119 | 55 | 45 | 10 |
| 121 | 45 | 0 | 45 |
| 125 | 178 | 183 | -5 |
all 3 rows
data you can query
Sample rows from each table. Each column shows its type underneath; → marks a link to another table.
sales_orders5 columns · 33 rows
| idinteger · key | shopper_idinteger → shoppers | order_datetext | statustext | delivered_datetext |
|---|---|---|---|---|
| 101 | 1 | 2025-01-04 | delivered | 2025-01-08 |
| 102 | 2 | 2025-01-06 | delivered | 2025-01-07 |
| 103 | 1 | 2025-01-15 | delivered | 2025-01-20 |
| 104 | 3 | 2025-01-18 | cancelled | NULL |
| 105 | 4 | 2025-01-22 | delivered | 2025-01-31 |
showing 5 of 33 rows
order_lines4 columns · 48 rows
| order_idinteger → sales_orders | item_idinteger → catalog_items | quantityinteger | unit_pricereal |
|---|---|---|---|
| 101 | 1 | 1 | 39 |
| 101 | 3 | 2 | 19.5 |
| 102 | 4 | 1 | 59 |
| 103 | 5 | 1 | 45 |
| 103 | 10 | 3 | 12 |
showing 5 of 48 rows
order_payments5 columns · 29 rows
| idinteger · key | order_idinteger | paid_attext | amountreal | methodtext |
|---|---|---|---|---|
| 1 | 101 | 2025-01-04 | 78 | card |
| 2 | 102 | 2025-01-06 | 59 | paypal |
| 3 | 103 | 2025-01-15 | 81 | card |
| 4 | 105 | 2025-01-22 | 90 | card |
| 5 | 105 | 2025-01-23 | 38 | gift_card |
showing 5 of 29 rows
solution.sql
Hints and the answer are always available. Using them counts the question as solved with help.