Кандидаты с повторными откликами

Кандидаты с повторными откликами

Контекст

Рекрутеры «Пульса» жалуются: один и тот же человек подаётся по нескольку раз, и его карточка всплывает в воронке дважды. Прежде чем чистить базу, нужно понять масштаб — с каких почт пришло больше одного отклика и как далеко разъехались даты.

Задача

Верните почты, с которых пришло больше одного отклика, со счётчиком и датами первого и последнего.

Данные

Таблица applications — отклики кандидатов:

Примерные строки:

Правила

1. Колонки: email, applications_count, first_applied, last_applied.
2. Выводим почту, если с неё пришло два отклика или больше.
3. Отклики без почты (email IS NULL) не участвуют: контакта нет, склеивать такие отклики между собой нельзя.
4. Почта сравнивается как есть, посимвольно: Sokolov@mail.example и sokolov@mail.example — разные кандидаты, приводить регистр не нужно.
5. first_applied — самая ранняя дата отклика с этой почты, last_applied — самая поздняя.
6. Сортировка: счётчик по убыванию, при равенстве — почта по алфавиту.

Примеры

-- Выдача на данных «Пульса»:
-- email                | applications_count | first_applied | last_applied
-- sokolov@mail.example | 2                  | 2026-03-02    | 2026-04-15
-- vetrova@mail.example | 2                  | 2026-03-19    | 2026-06-27

Где это пригодится

Поиск повторов по ключу — первый шаг любой чистки данных: одинаковые заказы, задвоенные платежи, один и тот же клиент под двумя записями. Ловушка всегда одна и та же: NULL (значение «неизвестно»; два NULL не равны друг другу) в ключе группировки собирается в одну кучу, хотя за ним стоят разные люди.

CREATE TABLE applications (
  id INTEGER PRIMARY KEY,
  candidate TEXT NOT NULL,
  email TEXT,
  department_id INTEGER NOT NULL,
  applied_at DATE NOT NULL,
  stage TEXT NOT NULL,
  source TEXT NOT NULL
);

INSERT INTO applications
  (id, candidate, email, department_id, applied_at, stage, source)
VALUES
  (1, 'Анна', 'anna@example.test', 1, DATE '2026-01-03', 'new', 'site'),
  (2, 'Борис', 'boris@example.test', 2, DATE '2026-01-04', 'new', 'referral'),
  (3, 'Анна', 'anna@example.test', 1, DATE '2026-01-10', 'screening', 'site'),
  (4, 'Анна', 'anna@example.test', 1, DATE '2026-01-18', 'tech', 'site'),
  (5, 'Борис', 'boris@example.test', 2, DATE '2026-01-15', 'tech', 'referral'),
  (6, 'Соло', 'solo@example.test', 3, DATE '2026-01-09', 'new', 'hh'),
  (7, 'Без почты А', NULL, 1, DATE '2026-01-11', 'new', 'site'),
  (8, 'Без почты Б', NULL, 2, DATE '2026-01-12', 'new', 'site');