Chapter 08 · 12 min · Basic
Where you put the bucket boundaries
Somebody asks for an ageing report on unpaid invoices. Current, 30 days, 60 days, 90-plus. It sounds like a CASE expression and ten minutes.
The ten minutes are real. The part that takes judgment is that the bands do not exist in the data — you are about to invent four categories, and every number in the report is a consequence of where you put the lines. Two of the twelve invoices in this table sit exactly on a boundary, and they carry RM 8,000 between them.
This chapter is about three choices: how to write a band so it cannot overlap or leak, who owns the value on the line, and when to let the data draw the boundaries instead of drawing them yourself.
The dataset
Twelve unpaid invoices with a days-past-due count and an amount. The days are chosen deliberately: some sit exactly on 30, 60 and 90, and one very large invoice sits far out in the tail. Both facts change the report.
Schema
| invoice_id | int |
| customer | text |
| days_past_due | int |
| amount_myr | numeric(10,2) |
Example data
The bands are the analysis
Twelve unpaid invoices and a days-past-due column. Nobody wants the list; they want the ageing report — how much is current, how much is a month late, how much is in trouble.
That report is entirely made of decisions you are about to make silently: how many bands, where they start, and who owns the value that lands exactly on a line. None of those are in the data. The data has twelve integers. Everything else is a rule you invented, and the rule is what the meeting will argue about.
Read the raw numbers first, and specifically look at the ones sitting on round numbers. Days 30, 60 and 90 are not coincidences — they are the values a finance system produces, which is exactly why they are the values a naive band puts in the wrong place.
Which of these twelve rows will change bucket depending on how the rule is written?
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
- 02Write the bands half-open, always
- 03The boundary case is a real decision, not a detail
- 04Fixed bands or quantiles: a different thing to hold still
- 05Count the money, then count the rows
- 06Now do it where you cannot see the distribution