Find duplicate emails in SQL (and the upper-case trap)
The question: Find the email addresses used by more than one account, with how many accounts use each. Emails ignore case: Asha@Mail.com and asha@mail.com are the same inbox.
users
| user_id | |
|---|---|
| 1 | asha@mail.com |
| 2 | ravi@mail.com |
| 3 | Asha@Mail.com |
| 4 | neha@mail.com |
| 5 | neha@mail.com |
| 6 | kabir@mail.com |
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 email, COUNT(*) AS accounts FROM users GROUP BY email HAVING COUNT(*) > 1;
GROUP BY treats asha@mail.com and Asha@Mail.com as two different emails, so Asha's duplicate is missed and only Neha's shows up.
The answer
SELECT LOWER(email) AS email, COUNT(*) AS accounts FROM users GROUP BY LOWER(email) HAVING COUNT(*) > 1;
Group by the email in one case, so every way of typing the same address lands in one group. HAVING then keeps the groups with more than one account.
Real data has more of this: spaces at the ends, a dot in a Gmail name. Ask what counts as the same before you count.
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.