Fan-Out and Double Counting

Joining to a finer grain multiplies rows

Joining orders to order_items repeats each order once per item line, so sum(o.amount) counts an order's amount several times. This fan-out is the most common cause of inflated metrics. Fix it by aggregating at the correct grain first (sum items per order in a subquery or CTE, then join), by not joining tables you do not need, or by using EXISTS for filters. Compare row counts before and after a join to catch it.

Inflated revenue from a join, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). Asha's paid revenue is 2,200, but joining to items reports 3,400 because order 101 has two item lines; Ravi shows 900 instead of 450.

-- Wrong: joining items repeats each order once per item line
SELECT c.name, sum(o.amount) AS revenue, count(*) AS rows_summed
FROM customers c JOIN orders o ON o.customer_id = c.id
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'paid' GROUP BY c.name ORDER BY c.name;

-- Right: aggregate at the order grain first
SELECT c.name, sum(o.amount) AS revenue, count(*) AS orders
FROM customers c JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'paid' GROUP BY c.name ORDER BY c.name;

Output:

 name  | revenue | rows_summed 
-------+---------+-------------
 Asha  | 3400.00 |           4
 Meera | 2000.00 |           1
 Ravi  |  900.00 |           2
(3 rows)

 name  | revenue | orders 
-------+---------+--------
 Asha  | 2200.00 |      3
 Meera | 2000.00 |      1
 Ravi  |  450.00 |      1
(3 rows)

Counting guests by plates

Counting a party's guests by counting plates gives the wrong answer when each guest uses three plates.

Quick check: How do you fix revenue doubled by joining order items?

• Switch to a LEFT JOIN
• Use DISTINCT on the revenue sum
• Add ORDER BY
• Aggregate at the order grain before joining, or avoid the unneeded join