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. The browser checker seeds this data when you click Check Answer.

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 5001 net_revenue 130

Your attempt

Explain and fix the mismatch

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

Saved locally

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.