SQL Interview Prep · Урок

Запросы для оттока и возвращения пользователей

Выявление пользователей, которые ушли, и тех, кто вернулся после перерыва

«Запросы для оттока и возвращения пользователей» — бесплатный урок SQL Interview Prep на CoddyKit. Это урок 4 из 4. Ты можешь прочитать весь урок бесплатно ниже — а потом практиковать его прямо в браузере с встроенным редактором кода и ИИ-репетитором 24/7. Это часть пути обучения SQL Interview Prep, и твой прогресс синхронизируется между веб-версией и приложением CoddyKit. Курс SQL Interview Prep содержит 4 уроков всего.

Обратная сторона удержания

Если удержание показывает, кто остался, то отток показывает, кто ушёл, а возвращение — кто вернулся. Интервьюеры рассматривают эти показатели вместе с удержанием, поскольку они показывают, умеете ли Вы рассуждать об отсутствии активности, что сложнее, чем подсчитывать её наличие.

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

Точное определение оттока

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

Для продуктов по подписке отток может означать отменённую или истёкшую подписку — изменение статуса, а не промежуток без активности. До написания запроса SQL уточните, какая модель применяется.

Последняя активность каждого пользователя

Основа оттока, определяемого по промежутку без активности, — это самое недавнее событие каждого пользователя. Сгруппируйте данные по пользователю и возьмите MAX даты события.

Это единственное значение в сравнении с сегодняшней датой показывает, как долго пользователь не проявлял активности. Все последующие действия сводятся к сравнению с этой датой последнего появления.

SELECT
  user_id,
  MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id;

Запрос для пользователей, ушедших из продукта

Пользователь считается ушедшим, если его последняя активность была более 30 дней назад. Сравните last_active с CURRENT_DATE - 30. Любой пользователь, чьё самое недавнее событие произошло до этой границы, перестал проявлять активность.

Обратите внимание: работа выполняется после агрегации. Сначала Вы сводите данные к одной строке на пользователя, а затем проверяете промежуток. Фильтрация исходных событий по дате покажет только тех, кто не проявлял активности в заданном окне, но не определит общий отток.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events
  GROUP BY user_id
)
SELECT user_id, last_active
FROM last_seen
WHERE last_active < CURRENT_DATE - INTERVAL '30 days';

Подсчёт показателя оттока

Показатель оттока — это число ушедших пользователей, делённое на соответствующую базу, часто на пользователей, проявлявших активность в начале периода. Используйте условную агрегацию, чтобы за один проход подсчитать ушедших пользователей и общее количество, затем аккуратно выполните деление с помощью 100.0 и NULLIF.

На собеседовании чётко назовите знаменатель: отток среди всех пользователей за всё время и отток среди пользователей, ранее проявлявших активность, — это разные показатели.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days'
  ) AS churned,
  COUNT(*) AS total_users,
  ROUND(100.0 * COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days')
    / NULLIF(COUNT(*), 0), 1) AS churn_pct
FROM last_seen;

Отток между периодами с помощью логики множеств

Можно сформулировать задачу иначе: кто проявлял активность в прошлом месяце, но не в этом? Это разность множеств. Сформируйте множество активных пользователей прошлого месяца и множество активных пользователей текущего месяца, затем найдите элементы первого множества, отсутствующие во втором.

Это можно выразить через EXCEPT, анти-соединение с помощью LEFT JOIN / IS NULL или NOT EXISTS. Анти-соединение наиболее переносимо и именно его интервьюеры чаще всего хотят увидеть.

WITH last_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-04-01' AND event_at < DATE '2024-05-01'
),
this_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-05-01' AND event_at < DATE '2024-06-01'
)
SELECT user_id FROM last_month
EXCEPT
SELECT user_id FROM this_month;

Форма антисоединения

Тот же запрос для выявления оттока за этот период, но в форме антисоединения: присоедините активных пользователей этого месяца с помощью LEFT JOIN к пользователям прошлого месяца, а затем оставьте строки, где совпадение равно NULL. Это пользователи, присутствовавшие в прошлом месяце, но отсутствующие в этом, — пользователи, ушедшие в отток.

NOT EXISTS — столь же хороший вариант, и он безопасно обрабатывает значения NULL. Упомяните, что NOT IN может быть опасным, если внутреннее множество способно содержать NULL, — это классическая ловушка.

SELECT lm.user_id
FROM last_month lm
LEFT JOIN this_month tm ON tm.user_id = lm.user_id
WHERE tm.user_id IS NULL;

Определение реактивации

Реактивация (также называемая повторной активацией) — это возвращение пользователя, ушедшего в отток, к активности. Её характерный признак — разрыв в его временной шкале: активность, затем период молчания дольше порога оттока, а после этого — новая активность.

Таким образом, реактивированный в этом месяце пользователь активен сейчас, был неактивен в прошлом периоде, но проявлял активность в каком-либо более раннем периоде. Это противоположность оттока.

Выявление разрывов с помощью LAG

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

LAG избавляет от самосоединения, а запрос легко читается. Разделите данные по пользователю, упорядочьте их по периоду активности и сравните каждый период с предыдущим.

WITH monthly AS (
  SELECT DISTINCT user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
),
gaps AS (
  SELECT user_id, active_month,
    LAG(active_month) OVER (
      PARTITION BY user_id ORDER BY active_month
    ) AS prev_month
  FROM monthly
)
SELECT user_id, active_month AS resurrected_month
FROM gaps
WHERE prev_month IS NOT NULL
  AND active_month > prev_month + INTERVAL '1 month';

Новые, реактивированные и удержанные

Полный запрос для классификации активности относит каждого активного в этом периоде пользователя к одному из трёх типов: новый (ранее активности не было), удержанный (активен и в прошлом периоде) или реактивированный (активность была раньше, но между периодами возник разрыв). Значение prev_month из LAG определяет все три категории.

prev_month IS NULL → новый
prev_month = active_month - 1 → удержанный
иначе (разрыв) → реактивированный

Такое разбиение — сильный и полный ответ.

SELECT user_id, active_month,
  CASE
    WHEN prev_month IS NULL THEN 'new'
    WHEN active_month = prev_month + INTERVAL '1 month' THEN 'retained'
    ELSE 'resurrected'
  END AS user_state
FROM gaps;

Ловушка NOT IN с NULL

И наконец, последняя ловушка. Если записать отток так: WHERE user_id NOT IN (SELECT user_id FROM this_month), а этот подзапрос вернёт хотя бы одно значение NULL, весь результат окажется пустым, потому что NOT IN при сравнении с NULL даёт значение UNKNOWN.

Предпочитайте NOT EXISTS или антисоединение через LEFT JOIN / IS NULL — они корректно работают со значениями NULL. Если без подсказки отметить это различие, на собеседовании по удержанию это надёжно покажет высокий уровень.

-- safe anti-join instead of NOT IN
SELECT lm.user_id
FROM last_month lm
WHERE NOT EXISTS (
  SELECT 1 FROM this_month tm
  WHERE tm.user_id = lm.user_id
);

Быстрая проверка

Вам нужны пользователи, активные в прошлом месяце, но не в этом. Коллега написал WHERE user_id NOT IN (SELECT user_id FROM this_month), и запрос возвращает ноль строк, хотя очевидно, что часть пользователей ушла в отток. Какое исправление будет самым безопасным?

Итоги: отток и реактивация

Основные сведения об оттоке и реактивации:

Определяйте отток по порогу неактивности (например, отсутствию активности в течение 30 дней) или по изменению статуса подписки — уточните, какое определение используется.
Вычисляйте для каждого пользователя его MAX(последняя активность), затем сравнивайте с CURRENT_DATE - threshold.
Отток от периода к периоду — это разность множеств: используйте EXCEPT, NOT EXISTS или антисоединение через LEFT JOIN / IS NULL.
Реактивация — это разрыв во временной шкале; выявляйте её с помощью LAG, чтобы классифицировать пользователей как новых, удержанных или реактивированных.
Избегайте NOT IN, если возможны значения NULL, — этот оператор незаметно делает результат пустым.

Часто задаваемые вопросы

Урок «Запросы для оттока и возвращения пользователей» бесплатный?

Да — полный текст урока «Запросы для оттока и возвращения пользователей» бесплатно доступен здесь в веб-версии. Чтобы практиковать его интерактивно (встроенный редактор кода и ИИ-репетитор 24/7) и разблокировать остальной курс SQL Interview Prep, подпишись на CoddyKit PRO. Курс SQL Interview Prep содержит 4 уроков всего.

Чему я научусь в уроке «Запросы для оттока и возвращения пользователей»?

Выявление пользователей, которые ушли, и тех, кто вернулся после перерыва Ты практикуешь SQL Interview Prep с помощью реального кода, который запускаешь прямо в браузере, и ИИ-репетитор 24/7 отвечает на твои вопросы во время урока.

Нужен ли мне опыт, чтобы начать SQL Interview Prep?

Предыдущий опыт не требуется. SQL Interview Prep на CoddyKit структурирован для всех уровней — от новичков до продвинутых, поэтому ты можешь начать отсюда или с самого начала и учиться в своем темпе. Это урок 4 из 4.

Сколько времени занимает урок «Запросы для оттока и возвращения пользователей»?

Большинство уроков CoddyKit занимают около 5–10 минут. Каждый из них компактный и интерактивный, поэтому ты постоянно делаешь прогресс и продолжаешь с того же места в веб-версии и приложении.

Можно ли писать и запускать код в этом уроке SQL Interview Prep?

Да. Каждый урок SQL Interview Prep включает встроенный редактор кода, поэтому ты пишешь и запускаешь реальный код прямо в браузере и получаешь моментальную обратную связь от AI — локальная установка не требуется.

Все уроки этого курса

Определение когорты по первому действию
Построение матрицы удержания
Удержание на N-й день и скользящее удержание
Запросы для оттока и возвращения пользователей

← Назад к SQL Interview Prep