Chapter 14 · 13 min · Basic
Gaps, streaks, and who survived
Every chapter so far has been about the rows in front of you. This one is about the rows that are not.
A hub stops reporting. A driver goes quiet for nine days. A subscriber's last payment was in February. None of it produces a row, an error, or a null — the table just contains less than you assumed, and every aggregate over it runs clean.
Three moves handle almost all of it: generate the calendar so absence becomes visible, define what consecutive means before you count a streak, and check who left the population before you average across it.
The dataset
Daily parcel counts for three delivery hubs across 2 to 15 March 2026 — fourteen calendar days. Each hub is missing days for a different reason, and none of the reasons is recorded anywhere in the table.
Schema
| hub | text |
| day | date |
| parcels | int |
Example data
The summary that hides the whole story
Start where most people stop: group by hub, count the days, average the parcels.
Three tidy rows. Klang is the big hub, Ipoh is mid-sized, Kuantan is small. Nothing looks broken.
Kuantan's last_day is 7 March. The reporting window runs to the 15th. That hub has been silent for eight days and the summary reports it as an ordinary member of the set — with an average parcel count computed over exactly the six days it did report, which is the most flattering possible denominator.
This is the failure this whole chapter is about, and note that the evidence was on screen. max(day) was right there. A date column in a summary is not decoration — it is the row's expiry date, and the first thing to do with a MIN/MAX date pair is compare it against the window you were asked about.
The window is fourteen days long. Will any hub report fourteen days — and what is the lowest count you would accept without asking a question?
Basic
The rest of this chapter is Basic
Learning SQL is free here, forever. Both data-science tracks are the paid half — this one is the level-up: grain, reshaping, change over time, and whether the number really moved.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02Make the missing rows into rows
- 03Streaks: subtract the row number from the date
- 04"Consecutive" is a definition you choose
- 05A streak touching the edge of the window is a lower bound
- 06Who is still in the population
- 07Now do it where nobody has counted the hubs