Amazon: net revenue across two fan-outs

Constraints

1. 1orders is how many orders the customer placed — 2 each here.
2. 2gross is the sum of item_amount for those orders. refunded is the sum of refund_amount. net is gross minus refunded.
3. 3Order 5001 has three items and two refunds. Joining both children at once produces six rows for that one order, and every amount on both sides repeats.
4. 4A customer with no refunds still appears, with refunded of 0 rather than NULL.
5. 5Round the money columns to 2 decimal places and order by customer_id.

Worked example

Customer a1 placed orders 5001 (240, three items) and 5002 (90, one item), and was refunded 80 twice on 5001. The truth is gross 330, refunded 160, net 170. Join orders to items to refunds and 5001 becomes six rows. SUM(item_amount) then reports 570 because each of the three items is counted twice, and SUM(refund_amount) reports 480 because each of the two refunds is counted three times. COUNT(DISTINCT order_id) fixes the order count and does nothing for either total — which is what makes this dangerous.

What this tests

That COUNT(DISTINCT ...) is not a cure for fan-out. When a parent has two independent one-to-many children, the only correct shape is to collapse each child to the parent grain first and join the summaries.