PrepBro
← Назад к вопросам

SQL: Расчет Retention Day 1, Day 7, Day 30

2.3 Middle🔥 241 комментариев
#SQL и базы данных#Метрики продукта

Условие

У вас есть таблица user_sessions:

  • user_id (integer)
  • session_date (date)
  • session_start (timestamp)

И таблица user_registrations:

  • user_id (integer)
  • registration_date (date)

Рассчитайте Retention Day 1, Day 7 и Day 30 для пользователей, зарегистрировавшихся в январе 2024 года.

Определения:

  • Retention Day N = % пользователей, которые вернулись на N-й день после регистрации

Ожидаемый результат:

Три числа: retention_d1, retention_d7, retention_d30 в процентах.

Источник: типовая задача на собеседованиях продуктовых аналитиков

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI23 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Решение: Расчет Retention Day 1, Day 7, Day 30

Понимание метрики

Retention Day N — это процент пользователей, которые имели хотя бы одну сессию на N-й день после регистрации.

Пример:

  • Пользователь зарегистрировался 5 января 2024
  • Retention Day 1 = был ли он 6 января (на следующий день)?
  • Retention Day 7 = был ли он 12 января (через неделю)?
  • Retention Day 30 = был ли он 4 февраля (через месяц)?

SQL-запрос (базовый вариант)

WITH jan_users AS (
  -- Пользователи, зарегистрировавшиеся в январе 2024
  SELECT
    user_id,
    registration_date
  FROM user_registrations
  WHERE EXTRACT(YEAR FROM registration_date) = 2024
    AND EXTRACT(MONTH FROM registration_date) = 1
),
user_retention AS (
  -- Определяем, в какие дни после регистрации пользователь был активен
  SELECT
    ju.user_id,
    ju.registration_date,
    us.session_date,
    (us.session_date - ju.registration_date) as days_since_registration
  FROM jan_users ju
  LEFT JOIN user_sessions us ON ju.user_id = us.user_id
    AND us.session_date > ju.registration_date -- активность ПОСЛЕ регистрации
)
SELECT
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN days_since_registration = 1 THEN user_id END) / 
    COUNT(DISTINCT user_id), 2) as retention_d1,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN days_since_registration = 7 THEN user_id END) / 
    COUNT(DISTINCT user_id), 2) as retention_d7,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN days_since_registration = 30 THEN user_id END) / 
    COUNT(DISTINCT user_id), 2) as retention_d30
FROM user_retention;

Альтернативный вариант (более читаемый с CTE)

WITH jan_users AS (
  -- Пользователи январского когорта
  SELECT
    user_id,
    registration_date
  FROM user_registrations
  WHERE EXTRACT(YEAR FROM registration_date) = 2024
    AND EXTRACT(MONTH FROM registration_date) = 1
),
active_on_day AS (
  -- Для каждого пользователя определяем, был ли активен на D1, D7, D30
  SELECT
    ju.user_id,
    MAX(CASE WHEN us.session_date = ju.registration_date + INTERVAL '1 day' THEN 1 ELSE 0 END) as active_d1,
    MAX(CASE WHEN us.session_date = ju.registration_date + INTERVAL '7 day' THEN 1 ELSE 0 END) as active_d7,
    MAX(CASE WHEN us.session_date = ju.registration_date + INTERVAL '30 day' THEN 1 ELSE 0 END) as active_d30
  FROM jan_users ju
  LEFT JOIN user_sessions us ON ju.user_id = us.user_id
  GROUP BY ju.user_id
)
SELECT
  ROUND(100.0 * SUM(active_d1) / COUNT(*), 2) as retention_d1,
  ROUND(100.0 * SUM(active_d7) / COUNT(*), 2) as retention_d7,
  ROUND(100.0 * SUM(active_d30) / COUNT(*), 2) as retention_d30
FROM active_on_day;

Оптимальный вариант (производительность)

WITH jan_cohort AS (
  SELECT
    user_id,
    registration_date
  FROM user_registrations
  WHERE registration_date >= '2024-01-01'
    AND registration_date < '2024-02-01'
),
active_dates AS (
  SELECT
    ju.user_id,
    ju.registration_date,
    CASE
      WHEN us.session_date = ju.registration_date + 1 THEN 1 ELSE 0
    END as is_active_d1,
    CASE
      WHEN us.session_date = ju.registration_date + 7 THEN 1 ELSE 0
    END as is_active_d7,
    CASE
      WHEN us.session_date = ju.registration_date + 30 THEN 1 ELSE 0
    END as is_active_d30
  FROM jan_cohort ju
  LEFT JOIN user_sessions us ON ju.user_id = us.user_id
)
SELECT
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN is_active_d1 = 1 THEN user_id END) / 
    COUNT(DISTINCT user_id), 2) as retention_d1,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN is_active_d7 = 1 THEN user_id END) / 
    COUNT(DISTINCT user_id), 2) as retention_d7,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN is_active_d30 = 1 THEN user_id END) / 
    COUNT(DISTINCT user_id), 2) as retention_d30
FROM active_dates;

Пример вывода

retention_d1 | retention_d7 | retention_d30
-------------|--------------|---------------
65.42        | 52.15        | 38.73

Интерпретация:

  • 65.42% пользователей, зарегистрировавшихся в январе, вернулись на следующий день
  • 52.15% вернулись через неделю
  • 38.73% вернулись через месяц

Важные моменты

1. Определение дня активности

Вариант 1 (точный день):

WHERE us.session_date = ju.registration_date + INTERVAL '1 day'
-- Активен ровно в день D+1

Вариант 2 (в течение дня):

WHERE us.session_date BETWEEN ju.registration_date + INTERVAL '1 day' 
      AND ju.registration_date + INTERVAL '2 day' - INTERVAL '1 second'
-- Активен в течение дня D+1

2. Регистрация в день 0

  • Некоторые определяют: регистрация = день 0, следующий день = день 1
  • Другие: регистрация = день 1 (день регистрации считается днём активности)

В нашем запросе используется первый подход (регистрация = день 0, следующий день = день 1).

3. Обработка пропусков дней

Если пользователь был активен на день 7, но не на день 1:

  • Он всё равно считается в retention_d7
  • Это логично, так как он всё же вернулся в приложение

Вариант с использованием session_start

WITH jan_cohort AS (
  SELECT
    user_id,
    registration_date
  FROM user_registrations
  WHERE registration_date >= '2024-01-01'
    AND registration_date < '2024-02-01'
)
SELECT
  ROUND(100.0 * COUNT(DISTINCT CASE
    WHEN DATE(us.session_start) = ju.registration_date + 1 THEN ju.user_id
  END) / COUNT(DISTINCT ju.user_id), 2) as retention_d1,
  ROUND(100.0 * COUNT(DISTINCT CASE
    WHEN DATE(us.session_start) = ju.registration_date + 7 THEN ju.user_id
  END) / COUNT(DISTINCT ju.user_id), 2) as retention_d7,
  ROUND(100.0 * COUNT(DISTINCT CASE
    WHEN DATE(us.session_start) = ju.registration_date + 30 THEN ju.user_id
  END) / COUNT(DISTINCT ju.user_id), 2) as retention_d30
FROM jan_cohort ju
LEFT JOIN user_sessions us ON ju.user_id = us.user_id;

Расширенный отчёт (со всеми днями)

WITH jan_cohort AS (
  SELECT user_id, registration_date
  FROM user_registrations
  WHERE registration_date >= '2024-01-01' AND registration_date < '2024-02-01'
),
days_since_registration AS (
  SELECT
    ju.user_id,
    (DATE(us.session_start) - ju.registration_date) as day_number
  FROM jan_cohort ju
  LEFT JOIN user_sessions us ON ju.user_id = us.user_id
)
SELECT
  day_number,
  COUNT(DISTINCT user_id) as active_users,
  ROUND(100.0 * COUNT(DISTINCT user_id) / (SELECT COUNT(DISTINCT user_id) FROM jan_cohort), 2) as retention_pct
FROM days_since_registration
WHERE day_number IS NOT NULL
GROUP BY day_number
ORDER BY day_number;

Результат покажет retention для ВСЕХ дней, не только D1, D7, D30.

SQL: Расчет Retention Day 1, Day 7, Day 30 | PrepBro