SQL: Расчет rolling retention

Решение

Это задача на расчет rolling retention — метрика, которая показывает, какой % пользователей был активен в день N или позже.

Разница между обычной и rolling retention:

• Normal Retention (Day 7): Был активен в точно день 7 (не раньше, не позже)
• Rolling Retention (Day 7): Был активен где-то между днём 0 и днём 7

SQL запрос для rolling retention:

WITH user_activation_dates AS (
  SELECT 
    u.user_id,
    u.registration_date,
    MAX(CASE WHEN ua.activity_date IS NOT NULL THEN ua.activity_date END) as last_activity_date,
    COUNT(DISTINCT ua.activity_date) as activity_days
  FROM users u
  LEFT JOIN user_activity ua ON u.user_id = ua.user_id
  GROUP BY u.user_id, u.registration_date
),
day_cohorts AS (
  SELECT DISTINCT
    (ua2.activity_date - ua1.registration_date) as day_number,
    COUNT(DISTINCT ua1.user_id) FILTER (WHERE ua2.activity_date IS NOT NULL) as users_retained
  FROM user_activation_dates ua1
  LEFT JOIN user_activity ua2 ON ua1.user_id = ua2.user_id
  WHERE (ua2.activity_date - ua1.registration_date) >= 0
    AND (ua2.activity_date - ua1.registration_date) <= 30
  GROUP BY (ua2.activity_date - ua1.registration_date)
)
SELECT 
  day_number,
  users_retained,
  ROUND(100.0 * users_retained / (SELECT COUNT(*) FROM users), 2) as rolling_retention_pct
FROM day_cohorts
WHERE day_number IS NOT NULL
ORDER BY day_number;

Более правильный вариант (rolling = активен В ДЕНЬ N ИЛИ ПОЗЖЕ):

WITH user_days AS (
  SELECT 
    u.user_id,
    u.registration_date,
    GENERATE_SERIES(0, 30) as day_number,
    (u.registration_date + GENERATE_SERIES(0, 30))::date as target_date
  FROM users u
),
user_day_activity AS (
  SELECT 
    ud.user_id,
    ud.registration_date,
    ud.day_number,
    MAX(CASE WHEN ua.activity_date <= ud.target_date THEN 1 ELSE 0 END) as was_active_on_or_before
  FROM user_days ud
  LEFT JOIN user_activity ua ON ud.user_id = ua.user_id
    AND ua.activity_date >= ud.registration_date
    AND ua.activity_date <= ud.target_date
  GROUP BY ud.user_id, ud.registration_date, ud.day_number, ud.target_date
)
SELECT 
  day_number,
  COUNT(DISTINCT user_id) as total_users,
  COUNT(DISTINCT CASE WHEN was_active_on_or_before = 1 THEN user_id END) as users_retained,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN was_active_on_or_before = 1 THEN user_id END) / COUNT(DISTINCT user_id), 2) as rolling_retention_pct
FROM user_day_activity
GROUP BY day_number
ORDER BY day_number;

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

WITH user_base AS (
  SELECT 
    u.user_id,
    u.registration_date,
    MAX(ua.activity_date) as last_activity_date
  FROM users u
  LEFT JOIN user_activity ua ON u.user_id = ua.user_id
  GROUP BY u.user_id, u.registration_date
),
rolling_by_day AS (
  SELECT 
    day_number,
    COUNT(*) as total_users,
    COUNT(CASE 
      WHEN last_activity_date >= (registration_date + day_number) 
      THEN 1 
    END) as users_retained_on_or_after
  FROM user_base
  CROSS JOIN LATERAL GENERATE_SERIES(0, 30) as day_number
  WHERE (registration_date + day_number) <= CURRENT_DATE
  GROUP BY day_number
)
SELECT 
  day_number,
  total_users,
  users_retained_on_or_after as users_retained,
  ROUND(100.0 * users_retained_on_or_after / total_users, 2) as rolling_retention_pct
FROM rolling_by_day
ORDER BY day_number;

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

day_number | users_retained | rolling_retention_pct
0          | 10,000         | 100.00%
1          | 5,500          | 55.00%
2          | 4,800          | 48.00%
3          | 4,200          | 42.00%
4          | 3,850          | 38.50%
5          | 3,500          | 35.00%
6          | 3,200          | 32.00%
7          | 3,000          | 30.00% ← D7 rolling
...
30         | 1,500          | 15.00%

Ключевые моменты:

GENERATE_SERIES(0, 30) — генерирует дни 0-30

CROSS JOIN LATERAL — декартово произведение каждого пользователя на каждый день

last_activity_date >= (registration_date + day_number) — был ли активен в этот день или позже

Rolling vs Normal: Rolling > Normal (потому что включает всех, кто вообще был активен)

Применение:

• Rolling retention выше обычной (30% rolling vs 15% normal в день 7)
• Используется для более оптимистичного view на retention
• Главная метрика в бизнесе: обычная (точный день), для planning: rolling