Grain (of a table)

What one row of a table represents: one order, one order line, one customer per day. Knowing the grain tells you what a join and an aggregate will do.

Formulaone row = one [what]?

Try it · 30 seconds · from Atterna’s practice

orders.id	amount	items in the order
1	€50	3
2	€30	1

SELECT SUM(o.amount)
FROM orders o
JOIN items i ON i.order_id = o.id;

What does this query return?

Preparing the practice…

How we work it through

1
Say what one row is before writing a join.
2
Check it: COUNT(*) against COUNT(DISTINCT the key you think it is).
3
Aggregate to the grain you need before joining.

On the practice dataset

The query we run on Meridian, Atterna’s practice marketplace, and what it returns.

SELECT COUNT(*) AS orders,
       COUNT(DISTINCT checkout_id) AS checkouts,
       (SELECT COUNT(*) FROM meridian.order_items) AS lines
FROM meridian.orders;

Result9,320 orders from 7,138 checkouts, with 16,650 item lines — three grains in one purchase

The common trap

Summing a column after joining to a table with a finer grain.

When an AI answers

An AI assistant

“There were 16,650 orders.”

What it didn’t know: It counted rows in order_items, where one row is an item line, not an order.
Ask back: “What does one row of the table you counted represent?”

Paste a real AI answer into the checker — it writes these questions for you →