Chapter 09 · 12 min · Basic
Deriving the column that isn't there
"How many of our deliveries were late?" There is no late column. There is a promised time and an arrival time, and the gap between them is where you are about to invent a fact.
Every derived column is an assertion made on the reader's behalf: this delivery was late, this customer is high value, this account is dormant. Written carelessly it is an assertion nobody can check, made with a threshold nobody agreed to, applied to rows that should never have been classified at all.
Ten deliveries here. Depending on two decisions — what happens to the rows with no arrival time, and whether a five-minute grace period counts — the late rate is 25%, 50%, or 62.5%. All three come from correct SQL.
The dataset
Ten food deliveries with a promised duration and an actual one. Two rows have no arrival time: one delivery never completed, one is simply a gap in the record — and no column tells you which is which. One delivery arrived at exactly the promised minute.
Schema
| delivery_id | int |
| restaurant | text |
| promised_min | int |
| actual_min | int |
| tip_myr | numeric(6,2) |
Example data
The column they asked for is not in the table
There is no is_late column. There is a promised duration and an actual one, and the word "late" is a comparison somebody has to define.
Here is the reflex version, and it is wrong in a way that will never announce itself. Two deliveries have no actual_min — one never arrived, one simply has a gap in the record. CASE WHEN actual_min > promised_min THEN true ELSE false END sends both of them to false, because a comparison against NULL is not false, it is unknown, and ELSE catches unknown along with everything else.
So the query reports five on-time deliveries out of ten. Two of those five are deliveries nobody can say anything about — and the late rate it implies, five in ten, is computed over a denominator that includes them. The ELSE branch quietly converted "we do not know" into "it was fine", which is the single most common way a derived column lies.
Two rows have no actual_min. What will is_late_naive say about them?
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.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02A derived flag has three outcomes, not two
- 03The threshold is the assumption, so put it where it can be argued with
- 04Name it so somebody else can check it
- 05Now count on it, and watch the denominator
- 06Now derive it where the rule has to be reused