Find Duplicate Email Addresses
Reported by candidates from ZS's online assessment. Pattern, common pitfall, and the honest play if you blank under the timer.
With up to 10^5 rows in the employees table, a self-join comparing every pair of emails is the wrong instinct, and it's the first thing nervous candidates write. ZS reported this SQL question in September 2026: return every email that appears more than once, exact and case-sensitive, ordered ascending. It's a grouping problem, not a join problem. If you've got an OA invite and your mind goes blank on the syntax, StealthCoder runs invisibly on your desktop as a safety net during the live assessment. Know the shape of the query first and you probably won't need it.
The problem
The table employees stores one row per employee email record. Return every email address that appears more than once. Comparison is exact and case-sensitive. Do not count or list unique emails. Return one column named email. Order rows by email ascending. What the interview report shared The interviewer asked SQL to find duplicate email addresses. employeesColumnMeaning employee_idUnique employee identifier emailEmployee email address Tables employees: employee_id PK (Integer), email (Text) Constraints The employees table has between 1 and 10^5 rows. employee_id is unique and non-null. email is non-null and contains at most 200 characters.
Reported by candidates. Source: FastPrep
Pattern and pitfall
The trick is GROUP BY email with HAVING COUNT(*) > 1, then ORDER BY email ASC. That's one pass over the table, which handles 10^5 rows without trouble. The brute-force version joins employees to itself on email with different employee_id values. It works on small data, but it creates a row for every matching pair and still needs DISTINCT to avoid repeats. The common pitfalls are using WHERE instead of HAVING for the aggregate, wrapping email in LOWER() or TRIM() when the spec says exact and case-sensitive, and forgetting to alias the column as email. Email is non-null, so NULL handling isn't a concern here. Select only the email column, no count. If you freeze on HAVING versus WHERE during the ZS OA, StealthCoder can show the working query as a hedge.
Memorize the pattern. If you can't, run StealthCoder. The proctor sees the IDE. They don't see what's behind it.
You can drill Find Duplicate Email Addresses cold, or you can hedge it. StealthCoder runs invisibly during screen share and surfaces a working solution in under 2 seconds. The proctor sees the IDE. They don't see what's behind it. Made by an engineer who treats the OA as theater. If yours is tonight, you don't have time to grind. You have time to hedge.
Get StealthCoderRelated leaked OAs
This OA pattern shows up on LeetCode as duplicate emails. If you have time before the OA, drill that.
You've seen the question.
Make sure you actually pass ZS's OA.
ZS reuses patterns across OAs. Made by an engineer who treats the OA as theater. If yours is tonight, you don't have time to grind. You have time to hedge. Works on HackerRank, CodeSignal, CoderPad, and Karat.
Find Duplicate Email Addresses FAQ
How hard is the ZS duplicate email SQL question really?+
It's easy. One GROUP BY, one HAVING, one ORDER BY. The only way to miss is a syntax slip or over-engineering with joins. If you've written an aggregate filter before, you can finish it in a couple of minutes.
What's the trick to finding duplicate emails in SQL?+
Group rows by email and keep groups with COUNT(*) greater than 1. The filter must go in HAVING because it runs after aggregation. WHERE runs before grouping and can't see the count.
Should I lowercase the emails before comparing?+
No. The problem says comparison is exact and case-sensitive, so Alice@x.com and alice@x.com are different values. Adding LOWER() or TRIM() would change the answer and could fail hidden tests.
Can I use a self-join or a window function instead?+
You can, but you shouldn't. A self-join with a.email = b.email and a.employee_id <> b.employee_id needs DISTINCT and does more work. A window COUNT works too but needs a subquery. GROUP BY with HAVING is shorter and clearer.
How do I prepare for this in 48 hours?+
Write the query from memory three times on a small sample table. Then practice variations: duplicates by two columns, counts greater than 2, and ordering results. Check that your output column is named email and sorted ascending.