Как посчитать когортный retention на SQL?

Зачем когорты. Общий DAU растёт вместе с притоком новых пользователей и маскирует то, что люди уходят. Когортная таблица показывает, как долго остаются пользователи, пришедшие в разное время, — видно влияние изменений продукта на свежие когорты.

Решения, которые нужно оговорить:

• что считается активностью (любое событие или ключевое действие);
• календарные недели или окна от даты регистрации;
• классический (активен именно в периоде N) или rolling (активен в периоде N или позже) retention;
• часовой пояс для границ дней.

Чтение таблицы: по строке — как когорта затухает; по диагонали — эффект календарных событий (праздники, сбой) на все когорты сразу; по столбцу — стали ли новые когорты удерживаться лучше.

WITH first_seen AS (
  SELECT user_id, DATE_TRUNC('week', MIN(event_time)) AS cohort
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT e.user_id, f.cohort,
         (DATE_TRUNC('week', e.event_time)::date - f.cohort::date) / 7 AS week_no
  FROM events e JOIN first_seen f USING (user_id)
),
sizes AS (
  SELECT cohort, COUNT(*) AS cohort_size FROM first_seen GROUP BY cohort
)
SELECT a.cohort, a.week_no,
       COUNT(*) AS active,
       ROUND(100.0 * COUNT(*) / s.cohort_size, 1) AS retention_pct
FROM activity a JOIN sizes s USING (cohort)
GROUP BY a.cohort, a.week_no, s.cohort_size
ORDER BY a.cohort, a.week_no;