Grain: the sentence to say before you write the query

One sentence, and most wrong answers do not happen

Before writing any query, finish this sentence out loud:

One row of my result is one ____.

One customer. One order. One customer-month. One payment attempt. If you cannot finish it, you are not ready to write the query, and the number you produce will be wrong in a way that does not look wrong.

That sentence names the grain of your result. Grain is the most useful idea in this course and almost nobody is taught it explicitly.

Every table has a grain too

Before combining tables you need to know the grain of each. It is a fact you can measure, not a matter of opinion:

SELECT count(*) AS rows,
       count(DISTINCT order_id) AS orders
FROM order_items;
-- rows 84,201 | orders 31,940  → one row per line item

Names lie. A table called daily_metrics may hold one row per day per country per product, which is not daily anything. A table called users may hold one row per user per sign-up source, so a user who arrived twice appears twice. Measure it once; write the answer down.

Joining changes the grain, and that is the entire hazard

Here is the mechanism stated as a rule you can apply without thinking:

Joining a table at grain A to a table at grain B gives you grain B, whenever many B rows belong to one A row.

So joining customers (one row per customer) to orders (one row per order) gives you one row per order, with customer details repeated. Every customer-level number in that result (credit limit, signup date, lifetime value) is now duplicated once per order and cannot be summed. The joins lesson shows exactly what that does to a SUM.

You have three moves, and picking the right one is the skill:

1. Aggregate the many side first, then join. One row per customer stays one row per customer.
2. Join, then group back up. Works, but any customer-level column you SUM after joining is wrong; only COUNT and the aggregates over the many side are safe.
3. Use a scalar subquery or a lateral join to attach one summarised value per row without changing grain at all.

-- Option 1: aggregate first, grain stays one row per customer
WITH per_customer AS (
  SELECT customer_id, sum(amount) AS spend, count(*) AS orders
  FROM orders GROUP BY customer_id
)
SELECT c.id, c.full_name, c.credit_limit,
       coalesce(p.spend, 0) AS spend
FROM customers c
LEFT JOIN per_customer p ON p.customer_id = c.id;

Read the grain at each step. The CTE is one row per customer. customers is one row per customer. Joining one-to-one preserves it. sum(credit_limit) over that result is now correct, and it was not correct before.

The grain of a report is a business decision

“Revenue by month” sounds unambiguous until you ask what a month is a property of. Order date? Payment settlement date? The month the subscription covers? Three different grains, three different tables, three different numbers, all defensible.

Similarly “one row per customer per month” needs a decision about customers with no activity that month. Do they appear with zero, or not appear at all? A grid that only contains rows where something happened will show a gap where a real zero should be, and the chart drawn from it will interpolate straight across the gap.

State the grain in words when you send the number:

One row is one customer-month, for every customer who existed at the start of the month, including months with no orders (shown as zero), by order placement date in Asia/Kolkata.

Nobody enjoys writing that sentence. It has ended more disagreements than any query I have seen.

A check you can run

After writing any query, before believing it, compare the row count against the grain you claimed:

SELECT count(*), count(DISTINCT customer_id) FROM my_result;

Claimed one row per customer? Those two numbers must match. If they do not, a join multiplied and every per-customer aggregate is inflated by an amount you cannot see in the output.

This takes eight seconds and it catches the majority of the errors that make it into meetings. It is the cheapest quality check in the whole of data work, and the reason it is not universal is that most people were never taught to name the grain in the first place.