SQL Interview Prep · Урок

Удержание на N-й день и скользящее удержание

Разница между классическими, скользящими и ограниченными определениями удержания

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

Почему у удержания несколько определений

Интервьюер редко просто скажет: «вычислите удержание». Точный дополнительный вопрос звучит так: какое именно удержание? Одни и те же данные дают очень разные числа в зависимости от определения.

Вам необходимо знать три варианта: классическое удержание в день N, скользящее удержание без ограничения и удержание в ограниченном окне. Умение различать их и уточнять, какой вариант нужен бизнесу, — это и есть проверяемый навык.

Классическое удержание в день N

Удержание в день N отвечает на вопрос: проявлял ли пользователь активность ровно в день N после своего первого действия? Удержание в день 1, день 7 и день 30 — классические показатели мобильных приложений.

Ключевое слово — ровно. Пользователь, активный в дни 6 и 8, но не в день 7, при классическом определении не считается удержанным в день 7. Именно эта точность делает подсчёт строгим, а кривую — неровной.

Вычисление разницы в днях

Удержание в день N зависит от числа дней между началом когорты и каждым днём активности. В Postgres вычитание двух дат сразу даёт целое число дней.

В других диалектах используются следующие варианты: DATEDIFF(day, start, d) в SQL Server и DATEDIFF(d, start) в MySQL. Назовите используемый диалект; суть — смещение в днях — остаётся одинаковой.

WITH cohort AS (
  SELECT user_id, MIN(event_at::date) AS day0
  FROM events GROUP BY user_id
),
act AS (
  SELECT DISTINCT user_id, event_at::date AS day
  FROM events
)
SELECT c.user_id, (a.day - c.day0) AS day_n
FROM cohort c
JOIN act a ON a.user_id = c.user_id;

Запрос для удержания в день 7

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

Равенство = 7, а не >= 7, является признаком классического удержания. Замена его неравенством незаметно меняет определение.

WITH dn AS (
  SELECT c.user_id, (a.day - c.day0) AS day_n
  FROM cohort c JOIN act a ON a.user_id = c.user_id
)
SELECT
  COUNT(DISTINCT CASE WHEN day_n = 7 THEN user_id END) AS d7_retained,
  COUNT(DISTINCT user_id)                               AS cohort_size,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN day_n = 7 THEN user_id END)
        / NULLIF(COUNT(DISTINCT user_id), 0), 1)         AS d7_pct
FROM dn;

Скользящее удержание без ограничения

Скользящее удержание в день N отвечает на более мягкий вопрос: проявлял ли пользователь активность в день N или в любой последующий день? Пользователь считается удержанным в день 7, если он вернулся в день 7, день 20 или в любой более поздний день.

Такая метрика даёт более плавную и высокую кривую и часто используется для измерения долгосрочной устойчивости активности. Отличительный признак — замена = N на >= N для максимального дня активности.

Скользящее удержание с максимальным днём

Удобный способ вычислить скользящее удержание — найти для каждого пользователя последнее смещение активного дня с помощью MAX. Затем пользователь считается удержанным в скользящем смысле в день N, если это максимальное значение >= N.

После MAX один пользователь соответствует одной строке, поэтому подсчёт становится простым. Кроме того, сразу видно, что скользящее удержание монотонно: если пользователь удержан в день 30, то он удержан и в любой меньший день N.

WITH last_day AS (
  SELECT c.user_id, MAX(a.day - c.day0) AS max_day_n
  FROM cohort c JOIN act a ON a.user_id = c.user_id
  GROUP BY c.user_id
)
SELECT
  COUNT(*)                                          AS cohort_size,
  COUNT(*) FILTER (WHERE max_day_n >= 7)            AS rolling_d7,
  ROUND(100.0 * COUNT(*) FILTER (WHERE max_day_n >= 7)
        / NULLIF(COUNT(*), 0), 1)                    AS rolling_d7_pct
FROM last_day;

Удержание в ограниченном окне

Промежуточный вариант — удержание в ограниченном окне: пользователь проявил активность хотя бы один раз в пределах окна вокруг дня N, например с 5-го по 9-й день для показателя «1-я неделя». Такой подход допускает отсутствие активности в конкретный день, но сглаживает результат не так сильно, как полностью скользящее удержание.

Это наиболее реалистичное для бизнеса определение, поскольку реальное использование продукта происходит всплесками. В запросе используется BETWEEN для смещения в днях.

SELECT
  COUNT(DISTINCT CASE WHEN day_n BETWEEN 5 AND 9
                      THEN user_id END) AS week1_retained,
  COUNT(DISTINCT user_id)               AS cohort_size
FROM dn;

Три определения, один пользователь

Рассмотрим различие на конкретном примере. Пользователь начинает в день 0 и затем проявляет активность только в день 9.

Классическое удержание в день 7: НЕ удержан (активности ровно в день 7 не было).
Скользящее удержание в день 7: удержан (максимальный день 9 >= 7).
Удержание в ограниченном окне 5–9 для первой недели: удержан (день 9 попадает в окно).

Одни и те же данные, три ответа. На собеседовании приведите такой пример, чтобы показать, что Вы понимаете смысл определений, а не только синтаксис.

Детализация периода: день, неделя или месяц

«День N» обобщается до понятия периода N. Для продукта B2B с ежемесячным использованием удержание на уровне дней создаёт шум; в таком случае следует группировать данные по месяцам и анализировать месяц N. Механика остаётся одинаковой, меняется только уровень детализации усечения.

Выбирайте уровень детализации в соответствии с естественной частотой использования продукта и озвучивайте этот выбор. Для потребительских мобильных приложений подходит день, а для более медленных инструментов B2B — неделя или месяц.

-- weekly grain: offset in whole weeks
SELECT
  c.user_id,
  FLOOR((a.day - c.day0) / 7) AS week_n
FROM cohort c
JOIN act a ON a.user_id = c.user_id;

Ловушка выживаемости и зрелости когорт

Тонкий вопрос для опытного специалиста: не следует сообщать удержание в день 30 для когорты, которой всего 10 дней. У неё ещё не было возможности проявить активность на 30-й день, поэтому её значение искусственно равно 0 и не означает действительно низкое удержание.

Для защиты учитывайте при расчёте удержания в день N только когорты, возраст которых >= N. Фильтруйте по условию CURRENT_DATE - day0 >= N. Если забыть об этом, недавние когорты будут выглядеть катастрофически плохо.

WITH cohort AS (
  SELECT user_id, MIN(event_at::date) AS day0
  FROM events GROUP BY user_id
)
SELECT *
FROM cohort
WHERE (CURRENT_DATE - day0) >= 30;  -- mature enough for Day-30

Как озвучить выбор определения

Лучший ответ на собеседовании — не запрос, а встречный вопрос: «Вам нужно классическое удержание в день N, скользящее удержание или удержание в ограниченном окне? И каков естественный период?»

Затем обозначьте компромисс: классическое удержание строго и хорошо подходит для точных продуктовых точек возврата; скользящее завышает краткосрочный показатель, но отражает долгосрочную устойчивость активности; ограниченное окно представляет собой реалистичный компромисс. Главное в этом уроке — показать, что Вы осознанно выбираете показатель.

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

Первое действие пользователя произошло в день 0, а единственная последующая активность — в день 12. Считается ли он удержанным в день 7 при каждом из определений?

Итоги: определения удержания

Основные выводы о классическом и скользящем удержании:

Классическое удержание в день N: активность ровно в день N (смещение = N) — строгая метрика с неровной кривой.
Скользящее удержание: активность в день N или позже (MAX offset >= N) — более плавная и монотонная метрика, измеряющая устойчивость активности.
Удержание в ограниченном окне: активность внутри окна (BETWEEN) — реалистичный компромисс.
Обобщайте день до любого уровня детализации периода, соответствующего частоте использования продукта.
Сообщайте показатель удержания в день N только для зрелых когорт (возраст >= N), чтобы избежать ловушки выживаемости, и всегда уточняйте, какое определение имеет в виду бизнес.

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

Урок «Удержание на N-й день и скользящее удержание» бесплатный?

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

Чему я научусь в уроке «Удержание на N-й день и скользящее удержание»?

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

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

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

Сколько времени занимает урок «Удержание на N-й день и скользящее удержание»?

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

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

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

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

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

← Назад к SQL Interview Prep