SQLIntermediateOutput Mismatch DebuggingFree

Duplicate Revenue from Joining Orders to Multiple Payments and Refunds

The CFO dashboard is showing inflated net revenue for a subset of orders. The issue only appears on orders that have multiple successful payment captures or multiple refund records.

Scenario context

The mart joins orders directly to payments and refunds at row level. Because both child tables can have multiple rows per order, the join multiplies records before aggregation.

Business requirement

Return one row per order with paid_amount, refunded_amount, and net_revenue. Aggregate each child table to order_id before joining so payment and refund rows cannot multiply each other.

Sample production data

Use these small tables to reason about the bug before writing the fix. This data is used for the visible sample check when you click Run.

orders

order_id	order_amount
5001	150
5002	80

payments

payment_id	order_id	payment_amount	status
1	5001	100	SUCCESS
2	5001	50	SUCCESS
3	5002	80	SUCCESS
4	5002	80	FAILED

refunds

refund_id	order_id	refund_amount
10	5001	20
11	5002	5

Schema

orders(order_id, order_amount)
payments(payment_id, order_id, payment_amount, status)
refunds(refund_id, order_id, refund_amount)

Broken logic / code

SELECT
  o.order_id,
  SUM(p.payment_amount) - COALESCE(SUM(r.refund_amount), 0) AS net_revenue
FROM orders o
LEFT JOIN payments p ON o.order_id = p.order_id AND p.status = 'SUCCESS'
LEFT JOIN refunds r ON o.order_id = r.order_id
GROUP BY 1;

Actual output

order_id 5001 net_revenue 260

Expected output / expected logic

order_id  paid_amount  refunded_amount  net_revenue
5001      150          20               130
5002      80           5                75

Your attempt

Explain and fix the mismatch

Write the corrected query, run it against the sample tables, and compare the result with the expected output.

Saved

Interview-style explanation

Now explain your solution as if you are in an interview: symptom, root cause, fix, edge cases, trade-offs, monitoring, and prevention.