Find duplicate emails, then remove the duplicates

Step 1 Solve it yourself first

Tables you will use

customers

What it really 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".

Picture it

flowchart LR
  A["customers grouped by email"] --> T["tom@mail.com<br/>id 2 · id 6"]
  A --> O["other emails<br/>one row each"]
  T -->|"COUNT is 2"| D["duplicate:<br/>keep id 2, remove id 6"]

The approach, step by step

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.

SELECT email, COUNT(*) AS sign_ups
FROM customers
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;

WITH numbered AS (
  SELECT customer_id, name, email,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY customer_id) AS rn
  FROM customers
)
SELECT customer_id, name, email
FROM numbered
WHERE rn > 1
ORDER BY customer_id;

DELETE FROM customers
WHERE customer_id NOT IN (
  SELECT MIN(customer_id)
  FROM customers
  GROUP BY email
);
SELECT customer_id, name, email
FROM customers
ORDER BY customer_id;

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Practice

  LeetCode — Top SQL 50 study plan

  free account

• Practice

  DataLemur — duplicate rows questions

  real interview questions, many free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain why HAVING and not WHERE is used to find duplicates.

   Show answer

   A duplicate email is a fact about a group of rows ("this email appears more than once"), not about any single row. Looking at one row on its own, you can't tell whether its email is repeated.

   WHERE runs before GROUP BY, one row at a time, so COUNT(*) doesn't exist yet there. GROUP BY email makes one group per email, and HAVING runs after it, so HAVING COUNT(*) > 1 can keep only the groups with more than one row.

2. Writefrom a blank page, then run it

   Find customers who share the same name and city, and how many rows each pair has.

   Show answer

   Group by both columns and keep groups with more than one row. Only Tom in Leeds appears twice.

   Note: GROUP BY puts NULLs together, so two people with the same name and no city would count as a pair. Usually that is what you want for finding duplicates, but say it out loud.

   sqlCopy

   SELECT name, city, COUNT(*) AS sign_ups
   FROM customers
   GROUP BY name, city
   HAVING COUNT(*) > 1
   ORDER BY name, city;

   Try it

   1 row

   name	city	sign_ups
   Tom	Leeds	2

3. Predictsay it before you run it

   What would the first query return if Zoe's email were changed to 'priya@mail.com'?

   Show answer

   Two rows: priya@mail.com with 2 sign-ups (Priya and now Zoe), and tom@mail.com with 2, as before. The query doesn't care that the names differ; it only groups by email.

   sqlCopy

   UPDATE customers SET email = 'priya@mail.com' WHERE name = 'Zoe';
   SELECT email, COUNT(*) AS sign_ups
   FROM customers
   GROUP BY email
   HAVING COUNT(*) > 1
   ORDER BY email;

   Try it

   2 rows

   email	sign_ups
   priya@mail.com	2
   tom@mail.com	2

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   Rewrite the duplicate check so 'TOM@mail.com' and 'tom@mail.com' count as the same email.

   Show answer

   Group by a cleaned-up version of the email: LOWER(TRIM(email)). LOWER makes the case the same; TRIM removes stray spaces, another common cause of fake "different" emails. Below, Tom's second sign-up is changed to 'TOM@mail.com' and the two still count as one.

   To stop it happening again, store emails lower-cased, and put a unique index on the lower-cased value (CREATE UNIQUE INDEX … ON customers (LOWER(email)), or a COLLATE NOCASE column in SQLite).

   sqlCopy

   UPDATE customers SET email = 'TOM@mail.com' WHERE customer_id = 6;
   SELECT LOWER(TRIM(email)) AS email, COUNT(*) AS sign_ups
   FROM customers
   GROUP BY LOWER(TRIM(email))
   HAVING COUNT(*) > 1
   ORDER BY 1;

   Try it

   1 row

   email	sign_ups
   tom@mail.com	2

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

To find duplicates I group by the column that should be unique and keep groups with HAVING COUNT star greater than one. To remove them I number the rows within each group with ROW_NUMBER — or take the minimum id per group — and delete every row that isn't the keeper, checking it as a SELECT first. Then I'd add a unique constraint, and I'd ask whether emails need normalising for case and spaces before comparing.

Follow-up questions

Tick each check when you can do it without notes