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

  1. Group by the column that should be unique (email).
  2. Keep groups with more than one row: HAVING COUNT(*) > 1.
  3. To see the extra rows themselves, number the rows in each email group by customer_id and keep row numbers above 1.
  4. To delete them, keep the MIN(customer_id) per email and delete everything else. Run it as a SELECT first.

Edge cases to mention

Common mistakes

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.