Analyse real data

In the chapters so far the lessons were laid out in the order of "here is a way of writing, learn it". This chapter is different. The question comes first.

"What sold best last month?" "What share of our customers come back?" "Which day of the week are sales falling on?" You answer questions like these by picking and combining the tools you already have. There is almost nothing new in the way of syntax. This chapter trains the putting-together.

Get your hands on it and you will notice that what stops you is rarely the syntax. What stops you is nearly always turning the question into something you can ask. Nobody can write "what share of our customers come back?" straight into SQL. First you restate it: "a returning customer is somebody who bought twice or more". Then again: "count the purchases per person and count the people at two or more". Once you are there, all that is left is the writing.

Try to take that restating on deliberately.

• settle what counts as one (an order? an item? a person?)
• find which table holds what you need
• decide whether the narrowing comes before or after the aggregating
• write something small, run it, and add to it once you have seen the result

That last one matters most. Write a long query in one breath, get an answer that does not add up, and you have no idea which part is at fault. Write only the narrowing and look at the result. If that is right, add the aggregating. If that is right too, add the sorting. Building a step at a time, checking as you go turns out to be the quickest way after all.

Something else worth keeping: doubt the numbers that come out. SQL does exactly what you wrote, so a wrong query returns its numbers just as confidently. No error does not mean the answer is right. When the count is an order of magnitude off what you expected, suspect the join. When it is short, suspect rows that the join threw away.

It helps to have a few ways of checking. Count the whole thing first and compare it with the count after narrowing. When a total comes out, add a few rows up by hand and see whether it matches. It is dull work, and it catches the mistakes of scale.

There is one more habit to build: once you have an answer, go on to ask what it lets you say. Suppose the number tells you sales are low on Thursdays. What you should do about it is not settled by the number. Perhaps most of the shops close on Thursdays. Data is material for a judgement, not the judgement itself.

The lessons in this chapter will have you rewriting your query several times before you reach an answer. That is normal, and worth knowing. Nobody writes the right query first time. Write, look, and fix what is off. It is the number of times round that becomes experience.

When a question has an answer, check as well whether you can say it in one sentence. If you can come out with "Thursday sales are thirty per cent below the other days", the analysis is in a shape somebody can hear. A table on its own is not yet an answer.

You will be reading your own queries back later. Putting in line breaks and indentation, one statement per line, changes how readable they are entirely. A query crammed onto one line defeats even the person who wrote it, by the next day.

The data is closer to the real thing than the practice sets so far. There are more fields, some rows have nothing in them, and it will not give up an answer as it stands. Tidy data is rare in the world, and experiencing that is another thing this chapter is for.

Come out the far side of this chapter and SQL turns from "something studied" into something you use. Handed some data and asked "what can we learn from this?", you can frame the question and produce the answer yourself. That is what we are aiming at.