Потерянные пользователи (Churned Users)

Решение

Задача требует подсчета пользователей, которые ушли из сервиса (churn) — были активны в предыдущем месяце, но перестали логиниться в текущем месяце.

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

Логика:

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

Вариант 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 prev.user_id) AS churned_users
FROM monthly_users prev
LEFT JOIN monthly_users curr 
  ON prev.user_id = curr.user_id 
  AND curr.month = prev.month + INTERVAL '1 month'
WHERE curr.month IS NOT NULL  -- Был в предыдущем месяце
  AND curr.user_id IS NULL  -- И отсутствует в текущем месяце
GROUP BY curr.month
ORDER BY curr.month;

Как работает:

• monthly_users — уникальные пользователи по месяцам
• LEFT JOIN соединяет пользователя из месяца N с месяцем N+1
• WHERE условие: был в месяце N, но отсутствует в месяце N+1

Вариант 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
),
user_months_ranked AS (
  SELECT 
    month,
    user_id,
    LEAD(month) OVER (PARTITION BY user_id ORDER BY month) AS next_month
  FROM monthly_users
)
SELECT 
  month + INTERVAL '1 month' AS month,
  COUNT(DISTINCT user_id) AS churned_users
FROM user_months_ranked
WHERE next_month IS NULL  -- Нет следующего месяца = ушёл
  AND month < DATE_TRUNC('month', CURRENT_DATE)  -- Не последний месяц в данных
GROUP BY month
ORDER BY month;

Как работает:

• LEAD(month) получает следующий месяц активности пользователя
• Если LEAD вернул NULL, значит пользователь больше не активен
• month + INTERVAL '1 month' преобразует месяц появления в месяц churn

Вариант 3: С использованием NOT EXISTS (самый явный)

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 
  prev.month + INTERVAL '1 month' AS month,
  COUNT(DISTINCT prev.user_id) AS churned_users
FROM monthly_users prev
WHERE NOT EXISTS (
  SELECT 1
  FROM monthly_users curr
  WHERE curr.user_id = prev.user_id
  AND curr.month = prev.month + INTERVAL '1 month'
)
  AND prev.month < DATE_TRUNC('month', CURRENT_DATE)  -- Не последний месяц
GROUP BY prev.month
ORDER BY month;

Как работает:

• NOT EXISTS проверяет наличие пользователя в следующем месяце
• Если подзапрос не вернул строк, пользователь ушёл

Вариант 4: Для MySQL (с DATE_FORMAT)

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 
  DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(prev.month, '-01'), '%Y-%m-%d'), INTERVAL 1 MONTH), '%Y-%m') AS month,
  COUNT(DISTINCT prev.user_id) AS churned_users
FROM monthly_users prev
LEFT JOIN monthly_users curr 
  ON prev.user_id = curr.user_id
  AND DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(prev.month, '-01'), '%Y-%m-%d'), INTERVAL 1 MONTH), '%Y-%m') = curr.month
WHERE curr.month IS NULL
GROUP BY month
ORDER BY month;

Пошаговое объяснение (Вариант 2 — рекомендуемый)

1. CTE monthly_users

• DATE_TRUNC приводит даты к первому дню месяца
• GROUP BY удаляет дубликаты (один пользователь может логиниться несколько раз в месяц)
• Результат: одна строка на пользователя-месяц

2. CTE user_months_ranked

• LEAD(month) получает следующую дату активности для каждого пользователя
• PARTITION BY user_id — окно считается отдельно для каждого пользователя
• ORDER BY month — упорядочиваем по времени
• Если LEAD NULL — это последняя запись для пользователя

3. Финальный SELECT

• WHERE next_month IS NULL — пользователь больше не активен
• WHERE month < CURRENT_MONTH — исключаем текущий месяц (там может быть неполная информация)
• COUNT(DISTINCT user_id) — подсчитываем потерянных
• month + INTERVAL '1 month' — преобразуем в месяц churn

Пример выполнения

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

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

Этап 1: monthly_users

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

Этап 2: user_months_ranked (с LEAD)

month    | user_id | next_month
---------|---------|----------
2024-01  | 1       | 2024-02   (был в январе, есть в феврале → не churn)
2024-01  | 2       | NULL      (был в январе, нет в феврале → churn в феврале)
2024-02  | 1       | NULL      (был в феврале, последний → может быть churn в марте)
2024-02  | 3       | NULL      (был в феврале, последний → может быть churn в марте)

Этап 3: Подсчёт churned

Где next_month IS NULL и month != 2024-02 (последний месяц):
- user_id=2 в месяце 2024-01 → churned в 2024-02

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

month    | churned_users
---------|---------------
2024-02  | 1

Объяснение: Только пользователь 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
),
monthly_stats AS (
  SELECT 
    month,
    COUNT(DISTINCT user_id) AS active_users
  FROM monthly_users
  GROUP BY month
),
user_churn_analysis AS (
  SELECT 
    prev.month,
    COUNT(DISTINCT prev.user_id) AS prev_month_users,
    COUNT(DISTINCT CASE WHEN curr.user_id IS NOT NULL THEN curr.user_id END) AS retained_users,
    COUNT(DISTINCT CASE WHEN curr.user_id IS NULL THEN prev.user_id END) AS churned_users
  FROM monthly_users prev
  LEFT JOIN monthly_users curr 
    ON prev.user_id = curr.user_id 
    AND curr.month = prev.month + INTERVAL '1 month'
  GROUP BY prev.month
)
SELECT 
  month + INTERVAL '1 month' AS month,
  churned_users,
  retained_users,
  prev_month_users,
  ROUND(churned_users::NUMERIC / prev_month_users * 100, 2) AS churn_rate_pct
FROM user_churn_analysis
WHERE month < DATE_TRUNC('month', CURRENT_DATE)
ORDER BY month;

Это добавляет retention и churn rate.

Вариант с анализом причин churn

WITH monthly_users AS (
  SELECT 
    DATE_TRUNC('month', login_date) AS month,
    user_id,
    MAX(login_date) AS last_login_in_month
  FROM logins
  GROUP BY DATE_TRUNC('month', login_date), user_id
),
churn_analysis AS (
  SELECT 
    prev.month + INTERVAL '1 month' AS churn_month,
    COUNT(DISTINCT prev.user_id) AS churned_users,
    COUNT(DISTINCT CASE WHEN EXTRACT(DAY FROM prev.last_login_in_month) < 15 THEN prev.user_id END) AS early_month_churn,
    COUNT(DISTINCT CASE WHEN EXTRACT(DAY FROM prev.last_login_in_month) >= 15 THEN prev.user_id END) AS late_month_churn
  FROM monthly_users prev
  LEFT JOIN monthly_users curr 
    ON prev.user_id = curr.user_id 
    AND curr.month = prev.month + INTERVAL '1 month'
  WHERE curr.user_id IS NULL
  GROUP BY prev.month
)
SELECT 
  churn_month,
  churned_users,
  early_month_churn,
  late_month_churn,
  ROUND(100.0 * early_month_churn / NULLIF(churned_users, 0), 2) AS early_churn_pct
FROM churn_analysis
ORDER BY churn_month;

Это показывает, ушли ли пользователи в начале или в конце месяца.

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

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

✓ WHERE month < CURRENT_MONTH — критично исключить текущий месяц, так как информация неполная

✓ LEAD vs LEFT JOIN — оба работают, но LEAD более эффективен для больших объёмов

✓ Churn Rate — можно вычислить как: churned / previous_active_users * 100%

✓ Сравнение с retention — retention + churn должны быть близки к 100% (для месяца N)

✓ Новые пользователи — не считаются как churn (они не были в предыдущем месяце)

✓ Временной фрейм — можно менять на другие периоды (неделя, квартал, год)