✏️ Explanatory Question
Level: Hard — A classic production bug that separates people who "know joins" from those who truly understand cardinality.
LEFT JOIN to the orders table to show order info, the report now shows 1,800 rows — and customer names are repeated. Marketing is complaining about inflated counts.
The cause: A JOIN multiplies rows based on matching cardinality. If one customer has 5 orders, that customer's row is repeated 5 times — one per matching order. This is a one-to-many relationship, and the JOIN correctly (but unexpectedly) fans out the rows.
DISTINCT on top is the junior fix — it hides the symptom, hurts performance, and can still give wrong aggregate numbers. They want to see if you understand grain/cardinality and fix it properly.
SELECT DISTINCT-- THE PROBLEM: one row per ORDER, not per customer (fan-out)
SELECT c.customer_id, c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- Customer with 5 orders appears 5 times
-- JUNIOR "FIX": DISTINCT hides it but is wrong for counts/sums
SELECT DISTINCT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- SENIOR FIX: aggregate to the correct grain FIRST, then join
SELECT c.customer_id, c.name,
COALESCE(o.order_count, 0) AS order_count,
COALESCE(o.total_spent, 0) AS total_spent
FROM customers c
LEFT JOIN (
SELECT customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
) o ON c.customer_id = o.customer_id;
-- Exactly one row per customer, with correct totalsCOUNT(*) OVER (PARTITION BY customer_id)) to keep detail rows while showing the correct count — no fan-out in the aggregate.