Skip to course content
Free SQL course

SQL for Data Analysis and AI

Unit 01.00: What one row means: finding the grain

Unit ID: SQL-M01-U01 - Estimated active time: 15-18 minutes Objective: state the grain of any table in one sentence, and detect when a query has changed it.

The question that prevents most SQL bugs

Before writing any query, answer this: "What does one row in this table represent?"

That answer is called the grain. Analysts who skip it produce numbers that look plausible and are wrong.

Look at two tables that both describe orders:

TableOne row is…Grain
ordersone order placed by one customerone order
order_itemsone product line inside an orderone order-item

They sound similar. They are not. orders has 1,000 rows for 1,000 orders. order_items might have 3,400 rows for those same 1,000 orders. If you SUM(price) on the wrong one, you get a different revenue number - and both look like "revenue".

Why this is the #1 source of wrong answers

Two tables at different grain: one order row and three line-item rows The orders table holds one row for order 501 with an order total of 1000.00 rupees. The order_items table holds three rows for that same order: SKU-A at 400.00, SKU-B at 350.00 and SKU-C at 250.00, which together sum to 1000.00. Because one order corresponds to three line items, joining the two tables changes the grain from one row per order to one row per line item, turning 1,000 order rows into 3,400 joined rows. One row does not mean the same thing in both tables Order 501, as it is actually stored orders one row = one order order_id order_total 501 1000.00 1 row order_id order_items one row = one line within an order order_id sku price 501 SKU-A 400.00 501 SKU-B 350.00 501 SKU-C 250.00 3 rows for the same order Join them and the grain changes: 1,000 orders becomes 3,400 rows, one per line item.
Order 501 as it is actually stored: one row in orders, three in order_items.

A revenue figure is only meaningful once you know what you counted. Consider:

-- Grain: one row per order
SELECT COUNT(*) AS order_count
FROM orders;
-- 1,000

-- Grain: one row per order LINE
SELECT COUNT(*) AS line_count
FROM order_items;
-- 3,400

Neither is "the number of sales". One is orders, one is lines. The word "sales" is ambiguous; the grain is not.

Worked example

A manager asks: *"What was our average order value last month?"*

The trap: order_items has a price column, so it is tempting to write AVG(price). That returns the average line price (₹531.91 in our data), not the average order value (₹2,718.53). The manager asked about orders. Run both against the starter database and compare.

Correct approach - collapse to the order grain first, then average:

SELECT AVG(order_total) AS avg_order_value
FROM (
  SELECT order_id, SUM(price * quantity) AS order_total
  FROM order_items
  GROUP BY order_id
) AS per_order;

The inner query changes the grain from *order-item* to *order*. The outer query averages at the grain the question actually asked about.

Non-example

This is not a grain problem:

SELECT COUNT(*) FROM customers WHERE country = 'IN';

One row is one customer, you are counting customers, and the filter does not change the grain. Naming the grain here takes two seconds and confirms there is nothing to worry about. That is the point - it is a cheap check.

Practice

For each table, write the grain as a single sentence beginning "One row is…":

  1. payments(payment_id, order_id, amount, paid_at)
  2. page_views(view_id, user_id, url, viewed_at)
  3. daily_active_users(date, user_count)

Then answer: which of the three can you SUM a money column on without any further work?

Check your answer
  1. One row is one payment against one order. (An order may have several payments.)
  2. One row is one page view by one user at one moment.
  3. One row is one day, already aggregated.

You can SUM(amount) on payments directly to get total money received - the grain matches the question. Table 3 is already aggregated, so summing user_count across days double-counts people who returned.

Takeaway

State the grain before you write the query. If a query changes the grain (most JOINs and GROUP BYs do), say the new grain out loud before trusting any number from it.

---