Y

SQL · Beginner

SQL join fan-out: avoid double counting rows

13 min read

Why it matters

Duplicate rows after a SQL join are the most expensive bug an analyst can ship, because nothing errors: the query runs, the dashboard renders, and revenue is quietly overstated by a quarter. It happens whenever one table is joined to two "many" tables at once, and it is the reason finance teams stop trusting a report. SQL appears in 88% of Data Analyst job descriptions we read, and "your revenue doubled after you added a join, why?" is a standard interview question because it separates people who have written real reports from people who have only done exercises.

The concept

Grain: what one row means

Every table has a grain: the thing one row represents. In an online shop:

tablegrainkey
ordersone orderorder_id
order_itemsone item line in an orderorder_id, order_item_id
order_paymentsone payment in an order (card, voucher, ...)order_id, payment_sequential

Before any join, say the grain of each table out loud, and say the grain you want in the result.

Join cardinality

The relationship between two tables on the join key is its cardinality:

  • one-to-one: each order has at most one shipping address row. Joining never adds rows.

  • one-to-many: each order has many items. Joining orders to items gives one row per item; order-level columns repeat on each item row. That is correct, as long as you remember order-level numbers now appear several times.

  • many-to-many: products and categories when a product can be in several categories. You model these with a bridge table (product_category(product_id, category_id)), and joins through it need the same care.

Fan-out: two "many" sides at once

Join one parent to two different one-to-many children, and every child row from one side pairs with every child row from the other side for the same parent. An order with 3 items and 2 payments becomes 3 × 2 = 6 rows. Each payment appears 3 times; each item appears twice. Any SUM over either child is now wrong.

This is called fan-out, or a "chasm trap" in BI tools. It does not need a bug in your keys: every join condition is correct, and the result is still wrong.

How to spot it

  1. The SUM after the join is larger than the SUM before. Compute the total from the source table alone, then from the joined result. They must match.

  2. COUNT(*) differs from COUNT(DISTINCT key) at the grain you expect. If you expect one row per order, those two counts must be equal.

  3. The numbers are suspiciously large for some groups: customers who pay in several instalments, orders with many lines.

The fix: aggregate each child to the parent grain first

Bring each child table down to the grain of the parent (one row per order) in its own subquery or CTE, then join the results. Now every join is one-to-one and nothing multiplies.

sql
WITH item_totals AS (SELECT order_id, SUM(price) AS item_value FROM items GROUP BY order_id),
     pay_totals  AS (SELECT order_id, SUM(payment_value) AS paid FROM payments GROUP BY order_id)
SELECT ...
FROM orders o
LEFT JOIN item_totals it ON it.order_id = o.order_id
LEFT JOIN pay_totals  pt ON pt.order_id = o.order_id;

COUNT(DISTINCT) fixes counts, not sums

COUNT(DISTINCT o.order_id) after a fanned-out join still gives the right number of orders, because duplicates collapse. There is no equivalent for sums: SUM(DISTINCT payment_value) collapses two different payments that happen to have the same value, so it is wrong in a different way. Never "fix" an inflated sum with DISTINCT.

Assert the grain

In production SQL (a dbt model, a scheduled job), add a check that fails when the grain breaks:

sql
SELECT COUNT(*) - COUNT(DISTINCT order_id) AS extra_rows FROM result;   -- must be 0

dbt's unique test on the key column does exactly this.

BI tools have the same bug

Power BI, Tableau and Looker build joins for you from the relationships you define. If a model joins orders to items and payments, a revenue measure can double count the same way. Their solutions mirror the SQL one: keep fact tables at their own grain and relate them through shared dimensions (a star schema), rather than joining facts to facts.

Worked example: Olist orders, items and payments

The data is the Olist Brazilian e-commerce dataset (CC BY-NC-SA 4.0): about 100,000 real orders from 2016 to 2018, amounts in Brazilian reais. Download olist_orders_dataset.csv, olist_order_items_dataset.csv and olist_order_payments_dataset.csv from the datasets folder of olist/work-at-olist-data, then load them into DuckDB:

setup.py
import duckdb
con = duckdb.connect("olist.duckdb")
con.sql("CREATE OR REPLACE TABLE orders   AS SELECT * FROM read_csv('olist_orders_dataset.csv')")
con.sql("CREATE OR REPLACE TABLE items    AS SELECT * FROM read_csv('olist_order_items_dataset.csv')")
con.sql("CREATE OR REPLACE TABLE payments AS SELECT * FROM read_csv('olist_order_payments_dataset.csv')")

Run each query with con.sql("...").show().

Step 1: know the grain and the true totals

sql
SELECT (SELECT COUNT(*) FROM orders) AS orders, (SELECT COUNT(*) FROM items) AS item_rows,
       (SELECT COUNT(*) FROM payments) AS payment_rows;
SELECT COUNT(*) AS rows, COUNT(DISTINCT order_id) AS distinct_orders FROM items;
SELECT COUNT(*) AS rows, COUNT(DISTINCT order_id) AS distinct_orders FROM payments;
SELECT ROUND(SUM(price + freight_value), 2) AS item_total FROM items;
SELECT ROUND(SUM(payment_value), 2) AS paid_total FROM payments;
text
┌────────┬───────────┬──────────────┐
│ orders │ item_rows │ payment_rows │
│ int64  │   int64   │    int64     │
├────────┼───────────┼──────────────┤
│  99441 │    112650 │       103886 │
└────────┴───────────┴──────────────┘
 
┌────────┬─────────────────┐
│  rows  │ distinct_orders │
│ int64  │      int64      │
├────────┼─────────────────┤
│ 112650 │           98666 │
└────────┴─────────────────┘
 
┌────────┬─────────────────┐
│  rows  │ distinct_orders │
│ int64  │      int64      │
├────────┼─────────────────┤
│ 103886 │           99440 │
└────────┴─────────────────┘
 
┌─────────────┐
│ item_total  │
│   double    │
├─────────────┤
│ 15848243.14 │
└─────────────┘
 
┌─────────────┐
│ paid_total  │
│   double    │
├─────────────┤
│ 16008872.12 │
└─────────────┘

Both child tables have more rows than distinct orders, so both are one-to-many from orders. Write down the two true totals: items plus freight 15,848,243.14, payments 16,008,872.12. Any later query must reproduce them.

Step 2: the naive join

"Show item value and amount paid per order" is usually first written like this:

sql
SELECT COUNT(*) AS joined_rows,
       ROUND(SUM(i.price + i.freight_value), 2) AS item_total_after_join,
       ROUND(SUM(p.payment_value), 2) AS paid_total_after_join
FROM orders o
JOIN items i ON i.order_id = o.order_id
JOIN payments p ON p.order_id = o.order_id;
text
┌─────────────┬───────────────────────┬───────────────────────┐
│ joined_rows │ item_total_after_join │ paid_total_after_join │
│    int64    │        double         │        double         │
├─────────────┼───────────────────────┼───────────────────────┤
│      117601 │           16571528.43 │           20308134.71 │
└─────────────┴───────────────────────┴───────────────────────┘

Payments jumped from 16.0 million to 20.3 million, an overstatement of about 27%. Item value is inflated too, by about 723,000. Every join condition is correct. The problem is the shape.

Step 3: watch it happen on one order

Order 0420dbc50fc554e303e4b2d6b39063f6 has 3 items and was paid in 2 parts:

sql
SELECT order_item_id, product_id, price, freight_value FROM items
WHERE order_id = '0420dbc50fc554e303e4b2d6b39063f6' ORDER BY order_item_id;
SELECT payment_sequential, payment_type, payment_value FROM payments
WHERE order_id = '0420dbc50fc554e303e4b2d6b39063f6' ORDER BY payment_sequential;
text
┌───────────────┬──────────────────────────────────┬────────┬───────────────┐
│ order_item_id │            product_id            │ price  │ freight_value │
│     int64     │             varchar              │ double │    double     │
├───────────────┼──────────────────────────────────┼────────┼───────────────┤
│             1 │ 7d854ab97c64ef9df2bbfaf332765786 │   64.9 │         26.98 │
│             2 │ 84a87daa85c8b432d90bc1baa0cb4388 │   59.9 │         15.94 │
│             3 │ fdba6f37e0258dbca54eb0ed8b293ae0 │   59.9 │         14.72 │
└───────────────┴──────────────────────────────────┴────────┴───────────────┘
 
┌────────────────────┬──────────────┬───────────────┐
│ payment_sequential │ payment_type │ payment_value │
│       int64        │   varchar    │    double     │
├────────────────────┼──────────────┼───────────────┤
│                  1 │ credit_card  │         48.61 │
│                  2 │ voucher      │        193.73 │
└────────────────────┴──────────────┴───────────────┘

Items plus freight come to 242.34, and the two payments add up to exactly 242.34. Now join them:

sql
SELECT i.order_item_id, i.price, p.payment_sequential, p.payment_type, p.payment_value
FROM items i JOIN payments p ON p.order_id = i.order_id
WHERE i.order_id = '0420dbc50fc554e303e4b2d6b39063f6'
ORDER BY i.order_item_id, p.payment_sequential;
text
┌───────────────┬────────┬────────────────────┬──────────────┬───────────────┐
│ order_item_id │ price  │ payment_sequential │ payment_type │ payment_value │
│     int64     │ double │       int64        │   varchar    │    double     │
├───────────────┼────────┼────────────────────┼──────────────┼───────────────┤
│             1 │   64.9 │                  1 │ credit_card  │         48.61 │
│             1 │   64.9 │                  2 │ voucher      │        193.73 │
│             2 │   59.9 │                  1 │ credit_card  │         48.61 │
│             2 │   59.9 │                  2 │ voucher      │        193.73 │
│             3 │   59.9 │                  1 │ credit_card  │         48.61 │
│             3 │   59.9 │                  2 │ voucher      │        193.73 │
└───────────────┴────────┴────────────────────┴──────────────┴───────────────┘

3 items × 2 payments = 6 rows. Summing payment_value here gives 727.02, three times what the customer paid; summing price counts every item twice. Once you have seen this table, you will never forget fan-out.

Step 4: the grain check catches it automatically

sql
WITH joined AS (
  SELECT o.order_id FROM orders o
  JOIN items i ON i.order_id = o.order_id
  JOIN payments p ON p.order_id = o.order_id)
SELECT CASE WHEN COUNT(*) = COUNT(DISTINCT order_id) THEN 'ok'
            ELSE 'FAN-OUT: ' || (COUNT(*) - COUNT(DISTINCT order_id)) || ' extra rows' END AS grain_check
FROM joined;
text
┌───────────────────────────┐
│        grain_check        │
│          varchar          │
├───────────────────────────┤
│ FAN-OUT: 18936 extra rows │
└───────────────────────────┘

Put a check like this at the end of any query that is meant to return one row per order, customer or day.

Notice what COUNT(DISTINCT) can and cannot do on the broken join:

sql
SELECT COUNT(DISTINCT o.order_id) AS orders_distinct_after_bad_join,
       COUNT(*) AS rows_after_bad_join
FROM orders o
JOIN items i ON i.order_id = o.order_id
JOIN payments p ON p.order_id = o.order_id;
text
┌────────────────────────────────┬─────────────────────┐
│ orders_distinct_after_bad_join │ rows_after_bad_join │
│             int64              │        int64        │
├────────────────────────────────┼─────────────────────┤
│                          98665 │              117601 │
└────────────────────────────────┴─────────────────────┘

The distinct order count is right (98,665 orders have both items and payments). The sums from Step 2 are still wrong, and no DISTINCT will repair them.

Step 5: pre-aggregate, then join

sql
WITH item_totals AS (
  SELECT order_id, COUNT(*) AS n_items, SUM(price + freight_value) AS item_value
  FROM items GROUP BY order_id),
pay_totals AS (
  SELECT order_id, SUM(payment_value) AS paid
  FROM payments GROUP BY order_id)
SELECT COUNT(*) AS rows, COUNT(DISTINCT o.order_id) AS orders,
       ROUND(SUM(it.item_value), 2) AS item_total, ROUND(SUM(pt.paid), 2) AS paid_total
FROM orders o
LEFT JOIN item_totals it ON it.order_id = o.order_id
LEFT JOIN pay_totals pt ON pt.order_id = o.order_id;
text
┌───────┬────────┬─────────────┬─────────────┐
│ rows  │ orders │ item_total  │ paid_total  │
│ int64 │ int64  │   double    │   double    │
├───────┼────────┼─────────────┼─────────────┤
│ 99441 │  99441 │ 15848243.14 │ 16008872.12 │
└───────┴────────┴─────────────┴─────────────┘

One row per order (99,441 rows, 99,441 orders) and both totals match Step 1 to the cent. The joins are LEFT JOINs so orders without items or payments stay in the result; SQL INNER JOIN vs LEFT JOIN with real examples explains why that matters.

Step 6: reconcile items against payments

Now that both are at order grain, you can do the check finance actually wants: did customers pay what the items cost?

sql
WITH item_totals AS (SELECT order_id, SUM(price + freight_value) AS item_value FROM items GROUP BY order_id),
pay_totals AS (SELECT order_id, SUM(payment_value) AS paid FROM payments GROUP BY order_id)
SELECT COUNT(*) AS orders_with_both,
       COUNT(*) FILTER (WHERE ABS(item_value - paid) <= 0.01) AS match_to_cent,
       COUNT(*) FILTER (WHERE paid - item_value > 0.01) AS paid_more,
       COUNT(*) FILTER (WHERE item_value - paid > 0.01) AS paid_less
FROM item_totals JOIN pay_totals USING (order_id);
text
┌──────────────────┬───────────────┬───────────┬───────────┐
│ orders_with_both │ match_to_cent │ paid_more │ paid_less │
│      int64       │     int64     │   int64   │   int64   │
├──────────────────┼───────────────┼───────────┼───────────┤
│            98665 │         98222 │       296 │       147 │
└──────────────────┴───────────────┴───────────┴───────────┘

98,222 of 98,665 orders (99.6%) reconcile to the cent. The 443 that do not are the interesting ones to investigate (instalment interest, partial refunds, data errors). The gap between the two grand totals has a second source too: payments exist for orders with no items at all.

sql
SELECT COUNT(DISTINCT p.order_id) AS orders, ROUND(SUM(p.payment_value), 2) AS paid
FROM payments p LEFT JOIN items i ON i.order_id = p.order_id WHERE i.order_id IS NULL;
text
┌────────┬───────────┐
│ orders │   paid    │
│ int64  │  double   │
├────────┼───────────┤
│    775 │ 162591.95 │
└────────┴───────────┘

775 orders were paid but have no item rows (most are orders Olist marked unavailable or canceled). Whether those count as revenue is a business decision; your job is to surface the 162,591.95 rather than let a join hide it.

Common mistakes

  • Joining facts to facts. Orders, items, payments, shipments and refunds are all "many" tables. Two of them in one join multiplies rows. Pre-aggregate each.

  • Trusting a total you did not reconcile. Always compute the total from the source table alone and compare.

  • "Fixing" sums with SUM(DISTINCT ...). Two genuine payments of 48.61 become one. Fix the grain instead.

  • Fixing counts but not sums. COUNT(DISTINCT order_id) looks right, so the inflated revenue next to it looks right too. Check every measure.

  • Joining a lookup that is not unique. If products had two rows for one product_id, every item would double. Check COUNT(*) against COUNT(DISTINCT key) on lookups too.

  • Averages over fanned-out rows. An average order value computed after the join weights orders with many items and payments more heavily. Compute averages at the right grain.

Interview angle

Your revenue doubled after adding a join to the payments table. Why, and how do you fix it?

The join changed the grain. If orders were already joined to items, adding payments pairs every item with every payment of the same order, so an order with 3 items and 2 payments becomes 6 rows and each payment is counted 3 times. I would confirm it by comparing COUNT(*) with COUNT(DISTINCT order_id) and the joined sum with the sum from the payments table alone. The fix is to aggregate items and payments to one row per order in separate CTEs, then join those to orders, and reconcile the totals.

Can't you just use DISTINCT?

For counts, COUNT(DISTINCT order_id) gives the right number. For sums it does not: SUM(DISTINCT amount) drops genuine payments that happen to have the same value, and SELECT DISTINCT on the rows only removes exact duplicates, which fan-out rows are not. The reliable fix is to aggregate to the correct grain before joining.

What is the difference between one-to-many and many-to-many joins?

One-to-many: each row on one side matches many on the other, like orders to items; the result has the grain of the many side. Many-to-many: rows match many on both sides, like products and categories where products can have several categories; you resolve it with a bridge table and must decide how to allocate measures, or you double count across categories.

Practice task

  1. Load olist_products_dataset.csv as products and prove product_id is unique.

  2. Compute item revenue (price only) by product category. Check the category totals add up to SUM(price) from items.

  3. Write the wrong version of "revenue by payment type" that joins items and payments, and the right version that uses payments only. Explain the difference in one sentence.

  4. At order grain, compare orders that used a voucher with those that did not: count, total paid and average paid.

  5. Write a grain check for each of your final queries.

Done when every query reconciles against a total computed from a single source table.

Show a solution
sql
CREATE OR REPLACE TABLE products AS SELECT * FROM read_csv('olist_products_dataset.csv');
SELECT COUNT(*) AS rows, COUNT(DISTINCT product_id) AS keys FROM products;          -- 32951 and 32951

SELECT p.product_category_name AS category, ROUND(SUM(i.price), 2) AS item_revenue
FROM items i LEFT JOIN products p ON p.product_id = i.product_id
GROUP BY 1 ORDER BY 2 DESC;      -- top: beleza_saude 1258681.34, relogios_presentes 1205005.68

-- right: payments are already at payment grain, so no join is needed
SELECT payment_type, ROUND(SUM(payment_value), 2) AS paid FROM payments GROUP BY 1 ORDER BY 2 DESC;

WITH order_pay AS (
  SELECT order_id, SUM(payment_value) AS paid,
         BOOL_OR(payment_type = 'voucher') AS used_voucher
  FROM payments GROUP BY order_id)
SELECT used_voucher, COUNT(*) AS orders, ROUND(SUM(paid), 2) AS paid, ROUND(AVG(paid), 2) AS avg_paid
FROM order_pay GROUP BY 1 ORDER BY 1;

The voucher query returns 3,866 orders that used a voucher (average paid 135.58) and 95,574 that did not (average 162.02); together they are the 99,440 orders in payments. Category names are in Portuguese; product_category_name_translation.csv in the same folder maps them to English.

Further reading