Chapter 04 · 11 min · Basic

Pick the grain before you query

Revenue for March is RM 605. You add the product breakdown somebody asked for, rerun it, and revenue is RM 1,285. Nothing about the business changed in the ninety seconds between those two queries.

What changed is the grain — what one row of your result means. It started as one row per order. After the join it is one row per order, per item, per promo code, and SUM(order_total_myr) is now adding the same order total up to six times.

The free tutorial's joins that inflate your numbers shows you this happening and how to spot it afterwards. This chapter is about not needing to spot it: decide the grain in a sentence before you type FROM, then make the query prove it held.

The dataset

Five orders, seven order items and three promo rows. Order 101 has three items and two promo codes; order 105 has neither. Small enough that you can count the fan-out by hand, which is the point — every number below is one you can verify yourself.

Schema

▸orders

order_id	int
customer_id	int
order_date	date
order_total_myr	numeric(8,2)

▸order_items

item_id	int
order_id	int
product	text
line_total_myr	numeric(8,2)

▸order_promos

order_id	int
promo_code	text
discount_myr	numeric(8,2)

Example data

▸orders

▸order_items

▸order_promos

Write the sentence first

Before any join, finish this sentence out loud: "one row per ___".

For a March revenue report it is one row per order. That sentence is not documentation, it is a specification, and it fixes three things at once:

Which table you start from — the one that already has that grain. Here, orders.
Which column is unique in your result. Here, order_id.
What counts as a bug. Any row count above five is now wrong by definition, not by opinion.

Run the baseline. Five rows, five distinct orders, RM 605. Write those three numbers down. They are the thing every later query has to still agree with.

Five orders, one row each. Will rows and orders be the same number here — and will they still match after the next query joins a second table?

Editable, try changing it

Basic

The rest of this chapter is Basic

Learning SQL is free here, forever. This track is the paid half: what to do when the data is dirty, duplicated and undocumented, and somebody still wants a number.

See Basic plans

RM 25/mo · cancel anytime

Still to come in this chapter

02The join that quietly changed it
03Two joins, and the multiplication compounds
04Make the query prove the grain held
05Aggregate to the grain, then join
06Now do it where you cannot count the rows by hand