Вернувшиеся пользователи

Решение

WITH first_visit AS (
    SELECT
        user_id,
        MIN(event_at)::date AS cohort_day
    FROM events
    GROUP BY user_id
),
activity AS (
    SELECT DISTINCT user_id, event_at::date AS active_day
    FROM events
)
SELECT
    f.cohort_day,
    COUNT(*) AS users,
    COUNT(a.user_id) AS returned,
    ROUND(COUNT(a.user_id)::numeric / COUNT(*), 2) AS retention
FROM first_visit f
LEFT JOIN activity a
       ON a.user_id = f.user_id
      AND a.active_day = f.cohort_day + 1
GROUP BY f.cohort_day
ORDER BY f.cohort_day;

Почему так

Почему COUNT(*) и COUNT(a.user_id) в одном запросе

• COUNT(*) считает все строки группы — это размер когорты
• COUNT(колонка) пропускает NULL, а после LEFT JOIN они как раз у невернувшихся
• Разница между COUNT(*) и COUNT(col) — любимый вопрос, и здесь он решает задачу

Зачем DISTINCT в активности

• У пользователя за день десятки событий: без DISTINCT LEFT JOIN размножит строки когорты
• Размноженные строки испортят и COUNT(*), и долю — метрика станет больше единицы
• Сведение к паре «пользователь, день» делает соединение однозначным

Почему LEFT JOIN, а не INNER

• INNER выкинул бы невернувшихся, и знаменатель посчитался бы только по вернувшимся — retention всегда 100%
• Нам нужна вся когорта в знаменателе, поэтому соединение только добавляет признак возврата
• Ошибка со знаменателем в метриках — самая дорогая: цифра выглядит правдоподобно и никто не перепроверяет