Find duplicate emails, then remove the duplicates
Find every email that belongs to more than one customer, with how many sign-ups it has. Then delete the duplicates, keeping the row with the lowest customer_id.
What it tests
GROUP BY with HAVING, the ROW_NUMBER pattern for picking one row per group, and writing a safe DELETE.
The pattern
Duplicates (GROUP BY … HAVING COUNT(*) > 1). "Duplicate", "appears more than once", "keep only one", "de-duplicate".
How to approach it
- Group by the column that should be unique (email).
- Keep groups with more than one row: HAVING
COUNT(*) > 1. - To see the extra rows themselves, number the rows in each email group by customer_id and keep row numbers above 1.
- To delete them, keep the
MIN(customer_id)per email and delete everything else. Run it as a SELECT first.
Edge cases to mention
- Case and spaces: 'Tom@mail.com ' and 'tom@mail.com' are different strings — normalise with
LOWER(TRIM(email))if they should match. - NULL emails group together in GROUP BY: decide whether they count as duplicates.
- If the duplicate rows have orders, the foreign key blocks the delete — re-point those orders to the kept id first.
Common mistakes
- Using WHERE
COUNT(*) > 1instead of HAVING: WHERE runs before the groups exist, so it can't see a count. - Deleting without checking the rows first with a SELECT.
- Fixing the data but not adding a UNIQUE constraint, so duplicates come straight back.
Not true: After the delete: the duplicates are gone for good, so no constraint is needed.
In fact: Without a rule in the database, the next double sign-up creates a duplicate again. Add a UNIQUE constraint on the email after cleaning up.
Part of Unit 8: SQL interview problems, from easy to hard in the free SQL course.