“Grain” is the word data people use for what one row means. One row per order line. One row per order. One row per customer per month. Every query has a grain at every stage, and most wrong totals in SQL come from a stage whose grain is different from what the author assumed.
Interviewers like this topic because the broken query looks perfectly reasonable, runs without errors and returns numbers in the right ballpark. The skill they’re checking is whether you reason about row counts before you trust a SUM.
The query that reports 75 when the answer is 45
Here’s a small shop schema. An order has line items and, separately, payments (a customer can split a payment between a card and a gift card).
CREATE TABLE orders (id int PRIMARY KEY, customer_id int NOT NULL);
CREATE TABLE order_items (order_id int REFERENCES orders, sku text, amount numeric(10,2));
CREATE TABLE payments (order_id int REFERENCES orders, method text, paid numeric(10,2));
INSERT INTO orders VALUES (1, 100), (2, 100), (3, 200);
INSERT INTO order_items VALUES (1,'mug',10), (1,'tee',20), (2,'cap',15), (3,'mug',10);
INSERT INTO payments VALUES (1,'card',25), (1,'gift',5), (2,'card',15), (3,'card',10);Customer 100 bought 45.00 of goods across two orders and paid 45.00. The task: one row per customer with item total and paid total. The first draft most people write joins everything and groups once:
SELECT o.customer_id, sum(i.amount) AS item_total, sum(p.paid) AS paid_total
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN payments p ON p.order_id = o.id
GROUP BY o.customer_id
ORDER BY 1;On PostgreSQL 16 this returns:
customer_id | item_total | paid_total
-------------+------------+------------
100 | 75.00 | 75.00
200 | 10.00 | 10.00Customer 200 looks fine because order 3 has exactly one item and one payment. Customer 100 is inflated by 30, and both columns are wrong by the same amount, which makes the mistake easy to miss if you only check that “items equal payments”.
Look at the rows before the GROUP BY
The fastest way to see the problem is to drop the aggregation and look at what SUM is about to add up. For order 1:
SELECT o.id, i.sku, i.amount, p.method, p.paid
FROM orders o
JOIN order_items i ON i.order_id = o.id
JOIN payments p ON p.order_id = o.id
WHERE o.id = 1
ORDER BY i.sku, p.method; id | sku | amount | method | paid
----+-----+--------+--------+-------
1 | mug | 10.00 | card | 25.00
1 | mug | 10.00 | gift | 5.00
1 | tee | 20.00 | card | 25.00
1 | tee | 20.00 | gift | 5.00Two items times two payments gives four rows. The join has no relationship between an item and a payment; it pairs every item of the order with every payment of the order. So each item amount appears twice (once per payment) and each payment appears twice (once per item). Item total for order 1 becomes 60 instead of 30, and the paid total also becomes 60. Add order 2’s 15 and you get the 75.
The grain of that intermediate result is “one row per (item, payment) pair within an order”, which is meaningless for both sums. Nothing in SQL warns you, because a join that multiplies rows is a valid join.
This is sometimes called a fan trap. You’ll hit it any time one parent has two or more independent one-to-many children and you join them all in the same FROM.
Reduce each child to the parent’s key, then combine
The fix is to bring each child table down to one row per order before it meets the other one. After that, joining on order_id is one-to-one and can’t multiply anything.
WITH items AS (
SELECT order_id, sum(amount) AS item_total
FROM order_items
GROUP BY order_id
),
pays AS (
SELECT order_id, sum(paid) AS paid_total
FROM payments
GROUP BY order_id
)
SELECT o.customer_id,
sum(items.item_total) AS item_total,
sum(pays.paid_total) AS paid_total
FROM orders o
LEFT JOIN items ON items.order_id = o.id
LEFT JOIN pays ON pays.order_id = o.id
GROUP BY o.customer_id
ORDER BY 1; customer_id | item_total | paid_total
-------------+------------+------------
100 | 45.00 | 45.00
200 | 10.00 | 10.00A few decisions in that query are worth saying out loud in an interview:
- Each CTE has a stated grain.
itemsis one row per order. You can check that withGROUP BY order_idalone: the output can’t have duplicate keys. - The joins are
LEFT JOIN. An order with no payments yet should still count its items. With an inner join, an unpaid order would vanish from the item total too. - The outer
GROUP BYrolls orders up to customers. That’s a second, deliberate change of grain, from order to customer, and it’s safe because every input row is now exactly one order.
A correlated subquery in the SELECT list ((SELECT sum(amount) FROM order_items WHERE order_id = o.id)) also works and is fine for small result sets. The CTE version tends to read better once you have several children, and PostgreSQL 12 and later can inline simple CTEs into the main query anyway.
Fixes that look right and aren’t
SUM(DISTINCT amount). It appears to remove the duplicates, and on some data it even gives the right answer. It deduplicates values, not rows. In this dataset two different orders contain a 10.00 mug, so:
SELECT sum(DISTINCT amount) FROM order_items; -- 45.00, real total is 55.00The second mug disappears because its amount matches the first. Distinct on money is almost always a bug.
COUNT(DISTINCT o.id). Counting distinct parent keys after a fan-out does give the right count of orders, because ids are unique. That’s a legitimate tool for counts, and it’s a decent quick check, but it doesn’t help sums or averages. Here’s the difference after joining just the items:
SELECT o.customer_id, count(*) AS rows_seen, count(DISTINCT o.id) AS orders
FROM orders o JOIN order_items i ON i.order_id = o.id
GROUP BY 1 ORDER BY 1; customer_id | rows_seen | orders
-------------+-----------+--------
100 | 3 | 2
200 | 1 | 1Dividing by the number of payments. Some people patch the inflated sum by dividing by count(DISTINCT p.method) or similar. It works until one order has a different multiplier from another, and then the report is wrong in a way nobody can explain.
Averages make grain visible
Grain matters even without a join. “Average order value” and “average item price” come from the same table and give different answers:
SELECT avg(amount) FROM order_items; -- 13.75 (per item)
SELECT avg(t) FROM (SELECT sum(amount) AS t
FROM order_items GROUP BY order_id) s; -- 18.33 (per order)Four items averaging 13.75, three orders averaging 18.33. Both are correct answers to different questions. When a stakeholder asks for “the average”, the first follow-up is “average per what?”, and you’ll sound more experienced if you ask it before writing any SQL.
A habit that catches this early
Before you write the final query, write one sentence: “This returns one row per customer, with the sum of item amounts and the sum of payments across their orders.” Then, for every table in the FROM, ask how many rows it contributes per output row. If two tables each contribute “many” relative to the same parent, aggregate at least one of them first.
When you’re debugging an existing report, compare count(*) before and after each join. If adding a join changes the row count and you didn’t expect it to, you’ve found the stage where the grain shifted. The join cardinality guide goes deeper on predicting those counts.
Interview practice: the dashboard that disagrees with finance
A product dashboard shows revenue per customer as 12% higher than the finance export. The dashboard query joins orders, order_items and shipments (an order can ship in several parcels) and sums order_items.amount. How would you find and fix the problem?
Answer
The 12% gap and the presence of a second one-to-many child (shipments) point straight at a fan-out. Orders shipped in one parcel are counted once; orders shipped in two parcels have every item counted twice. Since most orders ship in one parcel, the inflation is modest, which is why nobody caught it.
To confirm, pick an order that has two shipments and run the join without GROUP BY. You’ll see each item repeated per shipment. Then compare count(*) with and without the shipments join over a day of data.
The fix is to stop joining shipments at item grain. If the dashboard only needs items, remove the join. If it needs something from shipments (say, the first ship date), reduce shipments to one row per order in a CTE (min(shipped_at) GROUP BY order_id) and join that. Then reconcile against finance on a handful of hand-checked orders, including one with multiple shipments and one with none. I’d also mention that SUM(DISTINCT) would hide the symptom on some days and not others, so it’s not an acceptable patch.