Interview › Find duplicates

SQL interview: find duplicate emails (GROUP BY HAVING)

The hunt is a GROUP BY. The rows are a join back to that set.

Prompt. List every customer whose email appears more than once.

Hint: GROUP BY email HAVING COUNT(*) > 1

Your T-SQL

SELECT email, COUNT(*) AS times
FROM Customers
GROUP BY email
HAVING COUNT(*) > 1;

SELECT c.*
FROM Customers c
JOIN (
  SELECT email FROM Customers GROUP BY email HAVING COUNT(*) > 1
) d ON d.email = c.email;

Uniqueidentifier keys do not make the email unique. UNIQUE on email does.

Then write it on a ticket

Monday is free. A colleague asks, you type, we mark it.

Clock in free