Finding Duplicate Emails

SQLAggregationMidSeniorUpdated Nov 10, 2026

Return every email address that is associated with more than one user row, along with how many times it appears.

Schema

CREATE TABLE users (
  id    BIGINT PRIMARY KEY,
  email TEXT NOT NULL
);

Drill This: Your Query First

Answer, Then Reveal

Write the query before you look at the answer.

Skip to Reading

The Correct Query

Option (b)

SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

GROUP BY email + HAVING COUNT(*) > 1 is the canonical duplicate-finder. (a) puts the aggregate filter in WHERE, which is a syntax error. (c) returns every distinct email, not just duplicates, and the DISTINCT is redundant once GROUP BY is present. (d) duplicates rows by self-join and hardcodes n=2, wrong for emails that appear 3+ times.

The Options That Fail

The explanation above says why each of these breaks.

Option (a)

SELECT email, COUNT(*) AS n
FROM users
WHERE COUNT(*) > 1
GROUP BY email;

Option (c)

SELECT DISTINCT email, COUNT(*) AS n
FROM users
GROUP BY email;

Option (d)

SELECT u1.email, 2 AS n
FROM users u1
JOIN users u2 ON u1.email = u2.email AND u1.id <> u2.id;