Chapter 08 · 7 min · Basic
Putting things in buckets
Nobody wants a list of twelve balances. They want to know how many customers are small, medium and large — because you cannot make a decision about twelve individual numbers, and you can make one about three groups.
Turning a continuous number into a handful of named buckets is one of the most useful things you can do with CASE, and one of the easiest to get quietly wrong. Two failures do almost all the damage: a gap between bands that swallows rows, and a boundary you chose without noticing you were choosing it.
Both are visible in twelve rows, which is why this chapter uses twelve.
The dataset
Twelve e-wallet balances. Four values were placed on purpose and you should find them before you start: a balance of exactly zero, one of exactly 50.00, one of exactly 200.00, and one that is null. Each of them lands on or beside a line you are about to draw.
Schema
| wallet_id | int |
| owner_name | text |
| balance_myr | numeric(8,2) |
| opened_on | date |
Example data
Look at the distribution first
Before drawing any lines, sort the column and look at where the values actually sit. It takes one query and it is what tells you whether your intended bands describe this data or some imaginary data.
Read the sorted list. Half the wallets are under RM 50. There is a long thin tail — one wallet at RM 1,250 holds more than everything below RM 200 put together. And there is a null, parked at the end by nulls last, belonging to no band at all until you decide otherwise.
This is also where you notice values sitting exactly on round numbers: 50.00 and 200.00 are precisely the kind of number a person picks as a boundary. That is not a coincidence in this table and it is not one in real data either — round numbers attract both the values and the boundaries.
Twelve wallets. How many are under RM 50, and how many are over RM 200?
Basic
The rest of this chapter is Basic
Learning SQL is free here, forever. This track is the paid half: what to actually do with a dataset once somebody hands you one, from the first look to a number you can defend.
RM 25/mo · cancel anytime
Still to come in this chapter
- 02The bands that eat three rows
- 03Bands that actually touch
- 04Where the boundary case belongs
- 05Every downstream number moves with the boundary