Know the Grain of Your Data

A new analyst reports that Pantry's average order value is ₹255. That is odd: the finance team is sure a typical basket is about twice that. Nobody made an arithmetic slip. The analyst averaged the right column at the wrong grain, and every number built on top of it inherited the mistake.

The rule

Say what one row is before you count, average or join.

The grain of a table is what a single row stands for. Here is a slice of Pantry's order_items table:

order_id	customer_id	item	price
5001	17	Rice, 5 kg	420
5001	17	Milk	60
5001	17	Eggs	90
5002	23	Atta, 10 kg	450

One row is one item in one order, not one order. Averaging price over rows gives the average item price: 1,020 ÷ 4 = ₹255. The average order value needs one number per order first: order 5001 totals ₹570 and order 5002 totals ₹450, so the answer is 1,020 ÷ 2 = ₹510.

Every question has a natural grain. "Average basket" is about orders. "Share of customers who came back" is about customers. "Most popular item" is about items. When the table's grain is finer than the question's, aggregate up first.

The fastest check is to compare row counts with distinct keys:

SELECT COUNT(*)                 AS item_rows,
       COUNT(DISTINCT order_id) AS orders
FROM order_items;

If the two numbers match, order_id is unique and the table has one row per order. If item_rows is bigger, several rows share an order, and the grain is finer.

The shortcut

Rows versus distinct keys, before every join. Joins are where grain mistakes multiply, literally.

The join that tripled revenueStep 1 of 4

Name each grain

orders has one row per order, with an order_total column. order_items has one row per item.

A join between tables at different grains does not raise an error. It quietly returns more rows, and every sum over those rows is inflated. Checking the row count before and after a join catches it.

Drills

Answer fast — trust the rule.

Using the four rows above, what is the average order value in rupees?

(420 + 60 + 90 + 450) \div 2 = \,?

quick drill

A table has COUNT(*) = 120,000 and COUNT(DISTINCT order_id) = 45,000. What is its grain?

After the careless join, order 5001's ₹570 total appears on 3 rows and order 5002's ₹450 on 1 row. What does the inflated sum come to?

570 \times 3 + 450 = \,?

quick drill

Which question is naturally asked at the customer grain?

Remember

Say what one row is before you count, average or join.
Compare COUNT(*) with COUNT(DISTINCT key), before and after every join.
Trap: joining tables of different grains, which silently repeats rows and inflates sums.

Know the Grain of Your Data

Know the Grain of Your Data

The rule

The shortcut

Name each grain

Drills

Remember

Practice — level up