SQL: remove duplicates and keep one row per email
The question: Some people signed up twice with the same email. Return one row per email: the account with the lowest user_id.
users
| user_id | name | |
|---|---|---|
| 1 | asha@mail.com | Asha |
| 2 | ravi@mail.com | Ravi |
| 3 | asha@mail.com | Asha S |
| 4 | neha@mail.com | Neha |
| 5 | ravi@mail.com | Ravi K |
This query looks right. Run it, decide if it is, and fix it.
Show the answer and why the first query is wrong
Why the usual answer is wrong
SELECT DISTINCT email, name FROM users;
DISTINCT only removes rows that are identical in every column. Asha and Asha S have the same email but different names, so both rows stay.
The answer
SELECT user_id, email, name
FROM (
SELECT user_id, email, name,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY user_id) AS rn
FROM users
)
WHERE rn = 1;Number the rows inside each email, oldest account first, and keep number 1. Every email appears once, with all its columns.
To actually delete the extras: DELETE FROM users WHERE user_id NOT IN (SELECT MIN(user_id) FROM users GROUP BY email). Interviewers like hearing "I'd check with a SELECT first".
With MIN and a join
SELECT u.user_id, u.email, u.name FROM users u JOIN (SELECT MIN(user_id) AS user_id FROM users GROUP BY email) k ON k.user_id = u.user_id;
Practise more like this, on real business data
324 interview-style questions on 35 tables with the duplicates, NULLs and missing weeks interviewers test. Free.