Chapter 01 · 7 min
You have a dataset. Now what?
Somebody drops a table in front of you and asks a question about it. That moment is the actual job, and it is the one thing no SQL tutorial covers — you finish the tutorial knowing what GROUP BY does and still have no idea what to type first.
This chapter is the whole flow, start to finish, on twelve rows of orders. Six queries: look at it, size it up, see what is in the columns, fix what is mechanically broken, answer the question, and write the answer down so it survives being questioned.
Nothing here is hard. That is on purpose — the point is the order of operations, not the SQL. Every chapter after this one takes a single step from this page and spends a chapter on the decision hidden inside it.
The dataset
Twelve orders from a small online shop, one row per order. Twelve rows is small enough to read with your eyes — do read them, because every answer on this page is checkable by counting, and you will still miss at least one of the problems hiding in there.
Schema
| order_id | int |
| customer_name | text |
| city | text |
| amount_myr | numeric(7,2) |
| status | text |
| ordered_on | date |
Example data
The question you were asked
Here is the ask, in the words it usually arrives in — "which city is doing best?"
You could answer that in about thirty seconds. That is the trap. "Doing best" is not a number — it could be most orders, most revenue, biggest average order, fastest growth. And "city" turns out not to be a settled thing in this table either.
So the flow is not write the query. It is:
- look at the rows
- size the table up
- look inside the columns you are about to trust
- fix what is mechanically broken
- answer the question
- write down what the number does and does not cover
Six steps, six queries. Start by just looking.
Look at it
Always run this first. Not because it tells you much, but because it takes four seconds and it is how you notice the things you would never have thought to check.
Read the twelve rows now. Three things should catch your eye, and if they do not, that is exactly why this step exists:
- one city is written four different ways
- one order has no amount at all
- not every order is
delivered— some were cancelled, one was refunded
None of those is a bug in the data. They are all normal. They are also every single problem you will spend the rest of this chapter dealing with.
Before you run it: how many different spellings of Kuala Lumpur can you find?
Size it up
Now three numbers that tell you how much of this table is actually usable.
count(*) counts rows. count(amount_myr) counts rows where that column is not null — every aggregate in SQL except count(*) quietly skips the blanks. So the gap between those two numbers is the size of the hole in your data, and it is one line of SQL to find.
count(distinct customer_name) tells you something different again: twelve orders did not come from twelve people. Somebody ordered twice.
You now know the shape of the table: how big, how complete, and how repeated.
Twelve rows. Will rows_with_an_amount and customers both be twelve?
Look inside the columns
Two columns decide the answer: status, because a cancelled order is not revenue, and city, because it is what you are grouping by. Look at both before trusting either.
Counting the distinct values of a column is the cheapest, highest-value query in this entire track. It fits on four lines, it runs on anything, and it is how you find out that the column you were about to GROUP BY has four spellings of one city in it.
Run it on status first. Nine delivered, two cancelled, one refunded — so three of the twelve orders should probably not count as sales at all. Then run it on city by editing the query, and look at what comes back.
Now change status to city and run it again. How many rows come back — three, or more?
Fix what is mechanically broken
Grouping by city as it stands gives you six groups for three cities: Penang and ' Penang ' land apart because of a stray space, Kuala Lumpur and kuala lumpur because of capitals, and KL because somebody typed an abbreviation.
Two of those are mechanical — a computer can fix them with no judgement at all. trim() removes the spaces at the edges, initcap() puts each word into Capital Case, and the three variants collapse into one.
KL is not mechanical. No function knows that KL means Kuala Lumpur; you know that, because you know the country. That is a decision, it should be written down, and it is the difference between cleaning and guessing. Deciding it properly is what one city, four spellings is about.
Run this and you get four groups, not six. Better — and still not right.
Six spellings went in. How many rows come out, and which one still looks wrong?
Answer the question
Now you can write the query you would have written in the first thirty seconds — except this one is defensible.
Three decisions are baked into it, and you made every one of them on purpose:
where status = 'delivered'— cancelled and refunded orders are not revenue- the
case—KLis Kuala Lumpur, a human decision, written in the query where a reader can see and disagree with it count(amount_myr)besidecount(*)— so the reader can see that one delivered order has no amount, instead of finding out later
group by 1 just means group by the first column in the select list, which saves repeating that long case expression twice.
And look what the cleaning did. Group by the raw column and Penang is the top city on revenue. Merge KL into Kuala Lumpur — one line, one judgement call — and Kuala Lumpur takes the top spot by a distance. The decision you made about a two-letter abbreviation changed the answer to the manager's question.
Which city comes top on revenue — and would it have been the same city before the KL line?
Write down what it covers
The query is done. The work is not, and this last part takes one sentence.
Compare two ways of reporting the identical result:
- Without it. Kuala Lumpur is our best city, RM 125.30.
- With it. Kuala Lumpur leads on delivered revenue, RM 125.30 across 4 orders in the first ten days of March. That counts KL and Kuala Lumpur as one city, excludes cancelled and refunded orders, and one of those 4 orders has no amount recorded, so the real figure is a little higher.
The second one survives a meeting. The first one gets quietly overturned in a meeting you are not in, by somebody who ran a slightly different query.
That sentence is not decoration and it is not modesty. It is the definition of the number — the population, the period, and the exclusions — and without it nobody can reproduce your figure or tell whether it answers their question.
A habit worth forming from today: never send a number without its denominator. Same message, same paragraph, not a footnote.
What the rest of the track does
You have just done the entire job. Everything left is depth, one step at a time:
- Look at it — what the question really was, and what one row of a table actually stands for
- Clean it — counting correctly, blanks that are not zeroes, one thing spelled four ways, and which duplicate you keep
- Work with it — sorting rows into buckets, and bringing in a second table without doubling your total
- Deliver it — a number someone can act on
No subqueries. No window functions. One flat query at a time, on tables you can check by counting, all the way to the end.
The practice questions below are the easy, free ones that drill exactly this chapter's moves — counting rows by a category, and filtering to the rows that count.