Chapter 07 · 8 min · Basic
Duplicates, and which one you keep
A duplicate is not a bug in the database. It is usually a person who signed up twice — once with the app, once through a promo link, once after they forgot they already had an account.
That means two things. First, you cannot just delete duplicates, because each row is a real event that really happened. Second, every per-customer number you calculate is wrong until you decide what a customer is.
Three moves, in order: find them, look at them, decide which one survives and what that costs. The third is the one with your name on it.
The dataset
Twelve customer records. customer_id is the primary key, so every row is unique as far as the database is concerned — the repetition is in email, which is what a human would call the same person. Names are spelled slightly differently between a person's own rows, which is exactly why you match on email and not on name.
Schema
| customer_id | int |
| text | |
| full_name | text |
| plan | text |
| lifetime_myr | numeric(8,2) |
| created_on | date |
Example data
Find out whether you have a problem
One query tells you whether duplicates exist at all, and it is the same rows-versus-things comparison from first look at a table.
Twelve rows, eight distinct emails. The gap is four, so four rows are repeats of somebody already in the table.
Run this on the identifier that matters to the business, not the one that matters to the database. customer_id is unique by definition — it is the primary key, the database enforces it, and counting distinct values of it will always match the row count and always tell you nothing. Email is the column where two rows mean one person.
The general move: ask what a row is supposed to represent, find the column that identifies that thing, and compare its distinct count to the row count. If they match, you are done in ten seconds.
Twelve rows. How many distinct emails — and therefore how many rows are repeats?
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
- 02Find which ones
- 03Look at the rows before you decide anything
- 04Decide which one survives
- 05What the duplicates did to your average
- 06The version you would actually send