Join Fan-Out: How a Correct Query Returns Wrong Revenue

The bug

An order has one line worth ₹300 and two payment attempts (one failed, one succeeded).

SELECT SUM(l.line_revenue) AS revenue
FROM order_lines l
JOIN payments p ON p.order_id = l.order_id;
-- returns ₹600 — the single line appears once per payment attempt

Nothing errors. The query is syntactically and relationally "correct". The answer is wrong.

Why it happens

order_lines and payments are both children of orders. Joining two one-to-many children on the parent key produces a many-to-many result: every line is paired with every payment of the same order.

The fix: state the grain, then aggregate to it

WITH paid_orders AS (
  SELECT order_id
  FROM payments
  GROUP BY order_id
  HAVING MAX(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) = 1   -- one row per order
)
SELECT SUM(l.line_revenue) AS revenue
FROM order_lines l
JOIN paid_orders po ON po.order_id = l.order_id;

Now the right side has exactly one row per order, so lines are not duplicated.

A habit that prevents it

Before writing a join, write one sentence per table: "one row per order line", "one row per payment attempt". If the result of your join has no single-sentence grain, you are about to fan out.