✏️ Explanatory Question
Level: Coding Round — A LeetCode warm-up classic; the foundation for data-cleaning and dedup tasks.
The Puzzle: Given a person table, report all email addresses that appear more than once. This is the classic first step before de-duplicating records.
person| id | |
|---|---|
| 1 | rumman@x.com |
| 2 | krushna@x.com |
| 3 | rumman@x.com |
| 4 | swetha@x.com |
| 5 | krushna@x.com |
| rumman@x.com |
| krushna@x.com |
rumman@x.com appears twice (ids 1, 3) and krushna@x.com twice (ids 2, 5). swetha@x.com is unique → excluded.
The core technique — GROUP BY + HAVING: Group rows by email, then use HAVING COUNT(*) > 1 to keep only groups with more than one row. Remember: WHERE filters individual rows (before grouping), while HAVING filters the grouped result — and only HAVING can use aggregate functions like COUNT().
SELECT email AS Email
FROM person
GROUP BY email
HAVING COUNT(*) > 1;SELECT DISTINCT p1.email AS Email
FROM person p1
WHERE (SELECT COUNT(*) FROM person p2 WHERE p2.email = p1.email) > 1;
-- Works, but re-counts per row; GROUP BY is cleaner and fasterSELECT DISTINCT p1.email AS Email
FROM person p1
JOIN person p2 ON p1.email = p2.email AND p1.id <> p2.id;
-- If an email matches another row with a different id, it's a duplicate Case-sensitivity gotcha: By default, MySQL string comparison depends on the column's collation. With a case-insensitive collation (like utf8mb4_0900_ai_ci), Rumman@x.com and rumman@x.com are treated as the same email. If you need exact case matching, group on BINARY email or a case-sensitive collation. Mention this — email dedup is a real-world case-sensitivity trap.
-- Natural extension: remove the duplicate rows, keeping one per email
DELETE p1
FROM person p1
JOIN person p2 ON p1.email = p2.email AND p1.id > p2.id;
-- Keeps the smallest id for each email; deletes the rest
-- Then prevent future duplicates
ALTER TABLE person ADD UNIQUE (email); Interviewer follow-up: "Return each duplicate email along with how many times it appears and the list of ids." → Extend the GROUP BY: SELECT email, COUNT(*) AS cnt, GROUP_CONCAT(id ORDER BY id) AS ids FROM person GROUP BY email HAVING COUNT(*) > 1. GROUP_CONCAT gathers all the ids per group into one field — useful for reporting exactly which rows are duplicated.