Prevent Fan-Out in an Order Reconciliation
Return each order with item_total, successful_payment_total, and balance_due without multiplying either measure.
- Joins
- Subqueries
- Aggregation
- NULL handling
- Filtering
Exercise brief
Understand the request
Portfolio finance analyst An order reconciliation combines two independent one-to-many populations: line items and payment attempts.
For every project, return total_allocation (SUM of allocation_pct from all assignments) and headcount (number of distinct employees on the project). The trap: SUM(employee.salary) at this grain double-counts employees on multiple projects. The fix: aggregate at the JUNCTION grain BEFORE joining back. Return project_id, project_name, total_allocation, headcount — ordered by project_id.
Return
- Return one row per order.
- Treat a missing item or successful-payment total as zero.
- Calculate balance_due as item_total minus successful_payment_total.
- Order by order_id.
Constraints
- Aggregate join_order_items to order_id before joining.
- Aggregate only payment_status = 'succeeded' rows to order_id before joining.
- Do not join both raw child tables to the same order row.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
join_orders
order_idINTEGERcustomer_nameVARCHAR(50)
join_order_items
item_idINTEGERorder_idINTEGERitem_amountINTEGER
join_payments
payment_idINTEGERorder_idINTEGERpayment_statusVARCHAR(20)payment_amountINTEGER
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
Two raw one-to-many joins create an items × payments multiplication for the same order.
Hint 2
Build item_totals and payment_totals CTEs with one row per order_id first.
Hint 3
LEFT JOIN both aggregates to join_orders and COALESCE missing totals to zero.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH item_totals AS (SELECT order_id, SUM(item_amount) AS item_total FROM join_order_items GROUP BY order_id), payment_totals AS (SELECT order_id, SUM(payment_amount) AS successful_payment_total FROM join_payments WHERE payment_status = 'succeeded' GROUP BY order_id) SELECT o.order_id, COALESCE(i.item_total, 0) AS item_total, COALESCE(p.successful_payment_total, 0) AS successful_payment_total, COALESCE(i.item_total, 0) - COALESCE(p.successful_payment_total, 0) AS balance_due FROM join_orders o LEFT JOIN item_totals i ON i.order_id = o.order_id LEFT JOIN payment_totals p ON p.order_id = o.order_id ORDER BY o.order_id;Why this works
Independent child tables must be reduced to the requested business grain before they are combined. Order 9001 has two items and three payment attempts, so a raw three-table join would create six rows and inflate both sums.
Success check
All four orders appear once with correct totals despite multiple items, multiple payment attempts, failed payments, and missing child rows.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| order_id | item_total | successful_payment_total | balance_due |
|---|---|---|---|
| 9001 | 100 | 100 | 0 |
| 9002 | 80 | 0 | 80 |
| 9003 | 50 | 50 | 0 |
| 9004 | 0 | 20 | -20 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
Self Joins & Hierarchical Queries
Query organization charts, trees, and parent-child relationships.
SQL Subqueries
Practice scalar, derived-table, correlated, EXISTS, NULL-safe anti-subquery, quantified, and row-subquery patterns.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
Open the interactive workspace and practice across SQL topics.