Удержание пользователей по месяцам (Retention)

Решение

Задача требует подсчета удержанных пользователей (retained users) — тех, кто авторизовался и в текущем, и в предыдущем месяце. Это классический метрический расчёт для оценки retention rate.

Подход к решению

Этап 1: Определяем уникальных пользователей по месяцам.

Этап 2: Для каждого месяца находим пересечение пользователей с предыдущим месяцем.

Этап 3: Подсчитываем количество удержанных пользователей.

Вариант 1: С использованием self-join (самообъединение)

WITH monthly_users AS (
  SELECT 
    DATE_TRUNC('month', login_date) AS month,
    user_id
  FROM logins
  GROUP BY DATE_TRUNC('month', login_date), user_id
)
SELECT 
  curr.month,
  COUNT(DISTINCT curr.user_id) AS retained_users
FROM monthly_users curr
INNER JOIN monthly_users prev 
  ON curr.user_id = prev.user_id 
  AND curr.month = prev.month + INTERVAL '1 month'
GROUP BY curr.month
ORDER BY curr.month;

Вариант 2: С использованием оконных функций (более эффективный)

WITH monthly_users AS (
  SELECT 
    DATE_TRUNC('month', login_date) AS month,
    user_id
  FROM logins
  GROUP BY DATE_TRUNC('month', login_date), user_id
),
users_with_prev AS (
  SELECT 
    month,
    user_id,
    LAG(user_id) OVER (PARTITION BY user_id ORDER BY month) AS prev_month_user
  FROM monthly_users
)
SELECT 
  month,
  COUNT(DISTINCT user_id) AS retained_users
FROM users_with_prev
WHERE prev_month_user IS NOT NULL
GROUP BY month
ORDER BY month;

Пошаговое объяснение (Вариант 2)

1. CTE monthly_users

• DATE_TRUNC('month', login_date) — приводит все даты месяца к первому дню
• GROUP BY month, user_id — получаем уникальных пользователей за каждый месяц
• Результат: одна строка на пользователя-месяц

2. CTE users_with_prev

• LAG(user_id) OVER (PARTITION BY user_id ORDER BY month) — получает user_id из предыдущей строки того же пользователя
• PARTITION BY user_id — считаем окно отдельно для каждого пользователя
• ORDER BY month — сортируем по времени
• Если prev_month_user IS NOT NULL, значит пользователь был активен и в текущем, и в предыдущем месяце

3. Финальный запрос

• WHERE prev_month_user IS NOT NULL — фильтруем только удержанных пользователей
• COUNT(DISTINCT user_id) — подсчитываем уникальных
• GROUP BY month — группируем по месяцам

Пример расчёта

Исходные данные:

user_id | login_date
--------|------------
1       | 2024-01-15
2       | 2024-01-20
1       | 2024-02-10
3       | 2024-02-15

После CTE monthly_users:

month    | user_id
---------|--------
2024-01  | 1
2024-01  | 2
2024-02  | 1
2024-02  | 3

После users_with_prev с LAG:

month    | user_id | prev_month_user
---------|---------|----------------
2024-01  | 1       | NULL
2024-01  | 2       | NULL
2024-02  | 1       | 1        ← удержан
2024-02  | 3       | NULL

Финальный результат:

month    | retained_users
---------|---------------
2024-02  | 1

Удержан только user_id=1 (был в январе и в феврале).

Вариант 3: С явным указанием месяца в результате (MySQL/SQLite)

WITH monthly_users AS (
  SELECT 
    DATE_FORMAT(login_date, '%Y-%m') AS month,
    user_id
  FROM logins
  GROUP BY DATE_FORMAT(login_date, '%Y-%m'), user_id
)
SELECT 
  curr.month,
  COUNT(DISTINCT curr.user_id) AS retained_users
FROM monthly_users curr
WHERE EXISTS (
  SELECT 1
  FROM monthly_users prev
  WHERE curr.user_id = prev.user_id
  AND DATE_ADD(prev.month, INTERVAL 1 MONTH) = curr.month
)
GROUP BY curr.month
ORDER BY curr.month;

Здесь используется подзапрос вместо JOIN для проверки наличия пользователя в предыдущем месяце.

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

✓ GROUP BY в первой CTE — критично для удаления дубликатов (один пользователь может логиниться несколько раз в месяц)

✓ LAG() функция — получает значение из предыдущей строки по упорядочиванию

✓ PARTITION BY user_id — окно счётное отдельно для каждого пользователя

✓ INNER JOIN vs EXISTS — оба подхода работают, но EXISTS часто быстрее на больших данных

✓ Вычисление Retention Rate — можно добавить метрику: (retained_users / LAG(total_users)) * 100

✓ Различие с DAU/MAU — retention измеряет переиспользование, а DAU/MAU — просто активность в период