Write a SQL query to find duplicate emails in a users table.

Group by the email and keep only the groups that contain more than one row:

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

HAVING filters groups after aggregation, whereas WHERE filters rows before grouping. You cannot write WHERE COUNT(*) > 1 because the count does not exist yet at that stage.

Real data needs normalisation

Asha@Example.com and asha@example.com are the same address to a person but different strings to the database:

SELECT LOWER(TRIM(email)) AS normalised_email, COUNT(*) AS occurrences
FROM users
WHERE email IS NOT NULL
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

Mentioning this unprompted shows you think about data quality, not just syntax. (COUNT(email) ignores NULLs while COUNT(*) counts rows; GROUP BY puts all NULLs in one group, so filter them out explicitly if they are not interesting.)

Show the complete duplicate rows

Often you need the IDs, not just the email, to investigate or clean up. A window function keeps every row and adds the group count:

SELECT id, email, created_at
FROM (
  SELECT u.*,
         COUNT(*) OVER (PARTITION BY LOWER(email)) AS dup_count
  FROM users u
) t
WHERE dup_count > 1
ORDER BY LOWER(email), created_at;

The equivalent without window functions joins back to the grouped result:

SELECT u.id, u.email, u.created_at
FROM users u
JOIN (
  SELECT LOWER(email) AS e
  FROM users
  GROUP BY LOWER(email)
  HAVING COUNT(*) > 1
) d ON LOWER(u.email) = d.e;

Keep the first, flag the rest

To identify which rows a cleanup would delete (keeping the oldest account):

SELECT id, email
FROM (
  SELECT id, email,
         ROW_NUMBER() OVER (PARTITION BY LOWER(email) ORDER BY created_at, id) AS rn
  FROM users
) t
WHERE rn > 1;

In a test I would only SELECT, never delete, and I would not run cleanup against shared data without agreement.

Why a tester runs this

• A signup or import feature should prevent duplicates. This query verifies it after a bulk import or a concurrency test (two simultaneous signups with the same email).
• If duplicates exist, the real bug is often a missing unique constraint, such as a unique index on LOWER(email) in PostgreSQL or using the citext type. Application-level checks alone are vulnerable to race conditions.

What interviewers look for

• Correct GROUP BY plus HAVING COUNT(*) > 1.
• A clear explanation of WHERE versus HAVING.
• Normalising case and whitespace.
• Retrieving full rows with a window function, and connecting the result to a missing constraint.