What one row means: grain, duplicates and join fan-out

In a live SQL round, the first wrong number usually has nothing to do with syntax. A join quietly turns one row into three, every sum after it is inflated, and the query still runs. The interviewer is watching for exactly this, and the follow-up is predictable: "how do you know each order is counted once?" By the end of this lesson you will be able to state what one row of a table and of your result represents, prove a key is unique before you join on it, find and fix join fan-out, pick the right COUNT, and remove duplicate rows by a rule you can defend.

Say what one row is before you write

The grain of a table is what one row represents: one order, one package, one user per day. A primary key is the column, or set of columns, that is unique and never NULL at that grain, so every row has one and no two rows share it. A key can span two columns, such as (order_id, line_number) for order lines. The result of a query has a grain too, and the question sets it: "revenue per day" wants one row per day.

The examples in this lesson come from an online home-goods store. orders has one row per order:

shipments has one row per package. A large order ships in several packages, and shipped_on stays NULL until the package leaves the warehouse:

The ask: revenue per order date, with the number of packages next to it. Before writing the query, say the grain of each input and of the output.

"orders is one row per order and shipments is one row per package, so order_id is unique in orders but probably not in shipments. The answer is one row per order date. Before I join, I'll check how many packages one order can have."

The check is a GROUP BY on the column you think is a key, keeping only the values that appear more than once:

SELECT order_id, COUNT(*) AS rows_per_order
FROM shipments
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY order_id;

The same check on orders returns no rows, so order_id is unique in this data. In shipments it is not, and anything joined to that table on order_id will repeat for orders 102 and 104.

How a join multiplies rows

Here is the query that comes to mind first:

SELECT o.order_date,
       SUM(o.amount) AS revenue,
       COUNT(*)      AS packages
FROM orders AS o
JOIN shipments AS s ON s.order_id = o.order_id
GROUP BY o.order_date
ORDER BY o.order_date;

The package counts are right. The revenue is not: the five orders add up to 305.00, and this query reports 520.00. Order 102 matched two packages, so its 120.00 was added twice. Order 104 matched three, so its 60.00 was added three times. This is fan-out: when a key appears m times on one side of a join and n times on the other, the join returns m × n rows for that key, and each of those rows carries a full copy of the columns from both sides. Any SUM over a column from the "one" side is now multiplied.

There is a second, smaller error hiding in the same number. Order 105 has no package yet, so the inner join dropped it along with its 25.00. The next lesson covers which rows each kind of join keeps.

The habit that catches both problems is a row count after every join, compared with the grain you expect:

SELECT (SELECT COUNT(*) FROM orders) AS order_rows,
       (SELECT COUNT(*)
        FROM orders AS o
        JOIN shipments AS s ON s.order_id = o.order_id) AS joined_rows;

Seven rows from five orders means some orders now sit on more than one row. Equal counts would not prove the join is safe (a dropped row and a duplicated row cancel out), but unequal counts always mean you should look before you aggregate.

What the interviewer sees is a revenue figure that does not match SELECT SUM(amount) FROM orders, and then a patch: SUM(DISTINCT o.amount). It returns 160.00 for March 3, which is right by luck, and 60.00 for March 4, because orders 103 and 104 both cost 60.00 and DISTINCT keeps one copy of the value. DISTINCT removes repeated values, not repeated orders, so it does not fix a sum.

Aggregate first, then join

The fix is to bring shipments to the grain of orders before joining, so each order meets at most one row:

WITH packages_per_order AS (
    SELECT order_id, COUNT(*) AS packages
    FROM shipments
    GROUP BY order_id
)
SELECT o.order_date,
       SUM(o.amount)                AS revenue,
       COALESCE(SUM(p.packages), 0) AS packages
FROM orders AS o
LEFT JOIN packages_per_order AS p ON p.order_id = o.order_id
GROUP BY o.order_date
ORDER BY o.order_date;

Revenue now totals 305.00, which matches the orders table. The CTE has 4 rows, one per order that has a package, so the join returns exactly the 5 rows of orders. The LEFT JOIN keeps order 105, and COALESCE, which returns its first non-NULL argument, turns the package count into 0 on any day where no order has a package yet, instead of NULL.

"I'll collapse shipments to one row per order in a CTE first. Then the join to orders stays at one row per order, and the sum of amount can't be multiplied."

When all you need is a count of entities, COUNT(DISTINCT) after the join also survives fan-out: on the joined rows above, COUNT(DISTINCT o.order_id) is 4 while COUNT(*) is 7. Sums have no such shortcut, as SUM(DISTINCT) showed, so for money, aggregate first. The same rule applies when you join two "many" tables to one parent, such as packages and returns to orders: aggregate each to the parent's grain in its own CTE, then join the results.

Pick the COUNT that matches the question

The three forms of COUNT answer different questions:

• COUNT(*) counts rows.

• COUNT(column) counts rows where that column is not NULL.

• COUNT(DISTINCT column) counts the different non-NULL values.

SELECT COUNT(*)                 AS packages,
       COUNT(shipped_on)        AS packages_shipped,
       COUNT(DISTINCT order_id) AS orders_with_a_package
FROM shipments;

There are 7 packages. 6 have left the warehouse, because package 7 has no shipped_on yet. 4 orders have at least one package, so 1 of the 5 orders has none.

Run on orders, the same three forms finish the key check from the first section: COUNT(*), COUNT(order_id) and COUNT(DISTINCT order_id) all return 5, so in this data order_id is never NULL and never repeated.

Notice what none of these numbers answers: "how many orders have shipped?" Order 104 has two packages with a date and one without. Whether it counts as shipped is a definition, and the interviewer expects you to ask for it rather than pick one silently. In general, before you choose a COUNT, say what you are counting (rows, filled-in values or distinct things) and what a NULL means in that column.

Remove duplicates by a stated rule

The store's customer list is loaded every night from two systems: the app, where customers edit their own profile, and the support tool, where agents type changes during a call. customers is supposed to have one row per customer:

The uniqueness check from the first section flags customers 7, 8 and 10. Duplicates in a lookup table fan out every table joined to it: joined on customer_id, the five orders become 9 rows and revenue grows from 305.00 to 550.00. Customer 7 alone has 2 orders and 2 customer rows, so the join gives 2 × 2 = 4 rows for that customer.

An exact duplicate repeats every column; customer 7's two rows are the same row loaded twice. SELECT DISTINCT * FROM customers removes those and returns 6 rows. A near duplicate shares the key but differs elsewhere, like customer 8's two cities, and DISTINCT keeps both. For those you need a rule, and the rule is a business decision, not a SQL one. Here: trust what the customer typed in the app over what an agent typed, and among rows from the same source, keep the most recently updated one.

WITH ranked AS (
    SELECT c.*,
           ROW_NUMBER() OVER (
               PARTITION BY customer_id
               ORDER BY CASE WHEN source = 'app' THEN 0 ELSE 1 END,
                        updated_at DESC NULLS LAST
           ) AS rn
    FROM customers AS c
)
SELECT customer_id, city, source, updated_at
FROM ranked
WHERE rn = 1
ORDER BY customer_id;

ROW_NUMBER() numbers the rows within each customer_id (the PARTITION BY) in the order the ORDER BY gives, and rn = 1 keeps the first. NULLS LAST matters: in PostgreSQL a descending sort puts NULLs first, so a row with no updated_at would otherwise count as the newest. Customer 8 keeps Denver even though Boulder is newer, because the rule ranks the source first. "Keep the newest row" would have chosen Boulder. Neither rule is wrong in general, which is why you say yours out loud: the interviewer may want the other one. The two rows for customer 7 tie on both sort keys, so ROW_NUMBER picks one of them; they are identical, so it does not matter which. When tied rows could differ, add a column that breaks the tie.

Finish by running the uniqueness check on the result. It returns no rows, and the join to orders is back to 5 rows and 305.00. Ties and the other ranking functions get their own lesson, ranking and top N per group.

Practicing with the questions on this page

Each question above opens with its own tables and a SQL console. Before you type a query:

1. Write one line per input table saying what a row is, and one line for the output.

2. Run the uniqueness check on every column you plan to join on.

3. Name the duplicates and NULLs that could exist, and decide what your query does with each.

Then build the query one join at a time. After each join, compare the row count with what the grain predicts, and before you finish, check one total against a query on a single table, the way the revenue here was checked against SUM(amount) on orders. If the two disagree, find the rows that were counted twice before you change anything else.

The next lesson, joins without surprises, covers which rows each join keeps, the filter placement that silently drops rows, and finding rows with no match.