learn deep. ship safe.Practice testsFollow ByteMunSubscribe

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?

Written by Published Tests checked by Deepak
SQL · standard syntax, runs on SQLite
Case 1
Table accounts
idemail
1a@x.com
2b@x.com
3a@x.com
Expected (1 row)
a@x.com
Case 2
Table accounts
idemail
1u@y.org
2v@y.org
Expected (0 rows)

Run checks these 2 samples. Submit checks all 5 tests, including hidden ones.

  1. Daily Sales Running TotalWindow functions · GROUP BY and aggregates · SQL
    medium
  2. Highest Paid Employee in Each DepartmentJoins · GROUP BY and aggregates · Window functions · SQL
    medium
  3. easy
  4. easy