easyGROUP BY and aggregatesSQL
Find Duplicate Emails
Write a SQL query that finds every email address used by more than one account, using GROUP BY and HAVING: a common first SQL interview question.
SQL
The table accounts(id, email) stores one row per account. Email addresses were not always checked for uniqueness, so some are used by more than one account.
Write a query that returns each email that appears in two or more rows. Return one column, email, each duplicated address once, in any order. Emails are already lower-case, so compare them exactly.
Examples
Example 1
Input: accounts: (1, a@x.com), (2, b@x.com), (3, a@x.com) Output: email a@x.com Why: a@x.com is used by accounts 1 and 3; b@x.com only once.
Constraints
- email is never NULL.
- An email can appear any number of times.
- Return each duplicated email once, however many times it repeats.
Hints
Hint 1
Group the rows so there is one group per email.
Hint 2
Which clause filters groups by an aggregate such as COUNT(*)?
Solution
Show the solution (try it yourself first)
Approach: group, then filter the groups
GROUP BY email makes one group per address and COUNT(*) says how many accounts share it. WHERE runs before grouping, so it can't see the count; HAVING runs after grouping and keeps only the groups with two or more rows.
SELECT email
FROM accounts
GROUP BY email
HAVING COUNT(*) > 1;A self-join (a.email = b.email AND a.id <> b.id) with DISTINCT also works but produces many pairs for an email used often. To stop new duplicates, clean the data and then add a UNIQUE index on email.
Complexity
O(n log n) for the grouping sort, or O(n) with a hash aggregate; with an index on email, MySQL can group by reading the index in order.
Found a mistake in this problem, its tests or the solution?