How can aggregating after a JOIN produce inflated (double-counted) totals, and how do you fix it?

How to think about it

The interviewer is checking whether you understand why a query that looks perfectly correct can quietly return wrong numbers. This is one of the most common bugs in analytics SQL, and it’s invisible — no error, no warning, just a total that’s two or three times too big.

The trap: a JOIN that fans out rows

Say each order can carry several tags — promotions, campaigns, categories — and you want each customer’s total revenue and a tag count. The intuitive query joins first, then aggregates:

-- orders(order_id, customer_id, revenue) — one row per order
-- order_tags(order_id, tag)             — MANY rows per order

SELECT o.customer_id,
       SUM(o.revenue) AS total_revenue,   -- counted once per TAG, not per order
       COUNT(t.tag)   AS tag_count
FROM orders o
JOIN order_tags t ON o.order_id = t.order_id
GROUP BY o.customer_id;

With the data below — order 1 has revenue 100 and three tags — that join produces three rows for order 1, so SUM(revenue) adds 100 three times:

sample data
  orders:      (1, cust 10, $100)  (2, cust 10, $200)  (3, cust 20, $150)
  order_tags:  1 → promo, email, social    2 → promo    3 → social

customer_id	total_revenue	tag_count
10	500	4
20	150	1

Customer 10’s real revenue is 100 + 200 = 300, but the query reports 500 — order 1’s $100 was counted once per tag. No error, no warning; just a believable, wrong number. That is exactly what makes it dangerous.

The fix: aggregate the many-side first

Collapse order_tags to one row per order_id before the join, so nothing fans out:

SELECT o.customer_id,
       SUM(o.revenue)   AS total_revenue,
       SUM(t.tag_count) AS total_tags
FROM orders o
JOIN (
    SELECT order_id, COUNT(*) AS tag_count
    FROM order_tags
    GROUP BY order_id
) t ON o.order_id = t.order_id
GROUP BY o.customer_id;

customer_id	total_revenue	total_tags
10	300	4
20	150	1

Now each order matches exactly one row of the subquery, revenue is added once, and customer 10 reads the correct 300 — while the tag total (SUM(t.tag_count)) still comes out to 4. The fan-out is gone because the many-side was reduced before it ever met orders.

Why DISTINCT is a fragile patch

SUM(DISTINCT o.revenue)     -- dangerous: dedupes by VALUE, not by order
COUNT(DISTINCT o.order_id)  -- safe, but only works for counts

SUM(DISTINCT ...) deduplicates by amount, so two legitimate $100 orders collapse into a single $100. It silences the symptom by corrupting the meaning. Pre-aggregating upstream is the only robust fix.

The rule to carry away

The instant a JOIN multiplies rows on the driving table’s side, every aggregate on that side is wrong. So make it a reflex: compare COUNT(*) before and after the join. If the row count grew, you have a fan-out, and the many-side needs aggregating first.