Duplicate Emails SQL Query | SQL Interview Question

Problem statement

Prompt

Given the person table below, write a query that returns the email address of every person whose email appears more than once.

Each duplicate email should be returned once, regardless of how many times it appears.
Only the email column should be in the result.
person.email may be NULL; a NULL value is not an email and must not be treated as a duplicate.

Example

id	email
1	a@example.com
2	b@example.com
3	a@example.com
4	c@example.com

Expected result:

email
a@example.com

Constraints

person.id is the primary key.
person.email is nullable.

Tips

GROUP BY email then filter the groups with HAVING COUNT(*) > 1.
Remember that HAVING filters groups _after_ aggregation, while WHERE filters rows _before_.

Schema

CREATE TABLE person (
    id    INTEGER PRIMARY KEY,
    email TEXT  -- NULL allowed
);

Sample data

INSERT INTO person (id, email) VALUES
  (1, 'a@example.com'),
  (2, 'b@example.com'),
  (3, 'a@example.com'),
  (4, 'c@example.com');

Additional hidden fixtures are applied during validation to test edge cases.

Solution

One correct approach — try solving it yourself in the practice editor first, then compare. There is usually more than one valid solution.

-- Reference: one row per email appearing more than once.
SELECT email
FROM person
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY email;

Key concepts

GROUP BY
HAVING
COUNT
NULL handling

Duplicate Emails