alexeygrigorev/data-science-interviews: дубликаты email

Условие

В таблице users(id, email) найдите все email-адреса, которые встречаются больше одного раза.

Решение

Подход

Группировка по email и фильтрация через HAVING COUNT(*) > 1. Альтернатива — оконная функция COUNT() OVER, она удобнее, когда хочется сразу видеть и сами строки с дубликатами, а не только email-ы.

Реализация

-- Просто email-ы с дубликатами
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY cnt DESC;
 
-- Все строки, у которых email — дубликат
SELECT id, email
FROM (
    SELECT id, email, COUNT(*) OVER (PARTITION BY email) AS cnt
    FROM users
) t
WHERE cnt > 1
ORDER BY email;

Если задача — удалить дубликаты, оставив самую старую запись:

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

Подводные камни

1. Чувствительность к регистру. Vasya@mail.ru и vasya@mail.ru — формально разные email, но фактически один. Используйте LOWER(email) в группировке.
2. Пробелы по краям. ' vasya@mail.ru' ≠ 'vasya@mail.ru'. Применяйте TRIM.
3. NULL-email. COUNT(*) посчитает строки с NULL-email вместе. Если хотите исключить — WHERE email IS NOT NULL.
4. HAVING COUNT(*) > 1 нельзя писать без GROUP BY в большинстве СУБД — частая ошибка джунов.

Эталонный ответ

SELECT email FROM users GROUP BY email HAVING COUNT(*) > 1. Перед группировкой полезно нормализовать email через LOWER(TRIM(email)).