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

> Joining two child tables of the same parent multiplies rows. Learn to state the grain of every table and aggregate before you join.

- Source: https://akshaythakare.com/notes/sql/join-fan-out-and-grain
- Topic: SQL
- Level: intermediate
- Reading time: 3 min
- Tags: sql, data-modeling, grain, interview
- Updated: 2026-10-11

## The bug

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

```sql
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.

*(Interactive illustration “Join Fan Out”: see the web page.)*

## 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.

```mermaid
erDiagram
  ORDERS ||--o{ ORDER_LINES : has
  ORDERS ||--o{ PAYMENTS : has
```

## The fix: state the grain, then aggregate to it

```sql
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.

> **Note: COUNT(DISTINCT) does not save you**
>
> `COUNT(DISTINCT order_id)` hides the duplication for counts, but `SUM(line_revenue)` is still multiplied. Fix the grain, not the aggregate.

## 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.