Пересечение аудиторий в SQL: клиенты двух каналов без тестовых аккаунтов

Нужно выгрузить клиентов, которые в октябре пользовались и приложением, и сайтом, исключив тестовые аккаунты. В одной таблице несколько десятков событий на человека. В другой лежит список внутренних пользователей. Если начать с JOIN двух журналов, легко получить сотни одинаковых сочетаний и потом долго убирать дубли.

У задачи естественная форма: найти общих участников двух множеств, затем вычесть запрещённые идентификаторы. SQL умеет выражать это через INTERSECT и EXCEPT. Приём полезен для рекламных аудиторий, продуктовых исследований и контроля прав, когда важен факт присутствия, а не число событий.

Сравнивать нужно одинаковые объекты

В обоих множествах должна остаться одна колонка — идентификатор клиента. Если во вторую колонку добавить дату посещения, пересекаться будут пары «клиент–дата». Человек, заходивший на сайт в понедельник, а в приложение во вторник, исчезнет из результата. Это уже другой вопрос: пользовался ли он обоими каналами в один день.

Поэтому даты применяем как фильтр внутри источника, а наружу отдаём только client_id. Тестовые аккаунты тоже должны использовать тот же идентификатор. Email из одного источника нельзя напрямую сравнивать с числовым ключом из другого.

Полный пример для SQLite

WITH visits(client_id, channel) AS (
  VALUES (1, 'web'), (1, 'app'), (1, 'app'),
         (2, 'web'), (3, 'app'),
         (4, 'web'), (4, 'app')
), excluded(client_id) AS (
  VALUES (4)
), both_channels AS (
  SELECT client_id FROM visits WHERE channel = 'web'
  INTERSECT
  SELECT client_id FROM visits WHERE channel = 'app'
)
SELECT client_id FROM both_channels
EXCEPT
SELECT client_id FROM excluded
ORDER BY client_id;

Ответ — одна строка с клиентом 1. Два события приложения не удвоили результат. Клиент 2 присутствует только на сайте, клиент 3 — только в приложении. Клиент 4 проходит пересечение, затем исключается как внутренний.

Промежуточный CTE задаёт последовательность явно. Это удобно при расширении запроса: добавление UNION или второго EXCEPT в длинную цепочку без ясных границ может изменить логику. Сначала проверьте both_channels отдельно: в нём должны быть клиенты 1 и 4. Затем посмотрите итог.

Что проверить перед передачей аудитории

В рабочей таблице добавьте в обе части одинаковые границы периода. Не ограничивайте датой только сайт, оставляя приложение за всю историю. Иначе в аудиторию попадёт человек, который открыл приложение несколько лет назад и недавно пришёл на сайт.

Пропущенные идентификаторы лучше вынести в отдельный контроль качества. В операциях над множествами SQLite два NULL рассматриваются как равные при устранении повторов. Это не делает двух неизвестных посетителей одним человеком. Для персональной выгрузки исключите строки без ключа и проверьте их долю, чтобы заметить поломку авторизации или трекинга.

Не складывайте размеры отдельных аудиторий для получения общего охвата. Один клиент встречается в обоих каналах. Для объединённого охвата нужна операция UNION по идентификаторам. А для покупателей только одного канала понадобится направленная разность. Перед запросом полезно нарисовать два круга и отметить, какую область бизнес хочет получить.

Полученную выборку можно сверить с ручным списком нескольких известных аккаунтов. Особенно полезны аккаунт с повторными событиями, участник только одного канала и исключённый сотрудник. Такие примеры гораздо быстрее обнаруживают ошибку смысла, чем проверка одной общей цифры.

Практика на похожих условиях

В подборке EXISTS, INTERSECT и EXCEPT есть задания про покупателей двух категорий и участников нескольких занятий. Там можно проверить ту же логику на новых условиях, а затем перейти к более сложному требованию «выполнил все обязательные условия».

В курсе «Продвинутый SQL» операции над множествами связываются с подзапросами, JOIN, когортами и проверкой результатов. Откройте программу: она поможет перейти от разовой выгрузки к запросам, которые сохраняют правильный смысл при дублях, пропусках и изменении входных данных.