Напишите SQL-запрос, который считает rolling N-day retention с помощью оконных функций?

Короткий ответ

Rolling N-day retention для пользователя — это признак "была ли хотя бы одна активность в день N или позже"; его удобно получить через оконную функцию, находящую минимальную разницу в днях между регистрацией и каждым последующим событием, и сравнивающую её с порогом N.

Как это работает подробнее

для каждого пользователя вычисляем MAX(event_date) (или проверяем EXISTS событие с day_diff >= N) — если последняя активность наступила не раньше дня N, пользователь считается "rolling retained" на день N.

Пример кода

rolling_retention.sqlКопировать

-- PostgreSQL
WITH user_activity AS (
  SELECT
    u.user_id,
    u.reg_date,
    MAX(e.event_date) OVER (PARTITION BY u.user_id) AS last_active_date
  FROM users u
  JOIN events e ON e.user_id = u.user_id
)
SELECT
  reg_date,
  COUNT(DISTINCT user_id) AS cohort_size,
  COUNT(DISTINCT user_id) FILTER (WHERE last_active_date >= reg_date + 7) AS rolling_d7_retained
FROM user_activity
WHERE reg_date <= CURRENT_DATE - 7  -- исключаем когорты, для которых день 7 ещё не наступил
GROUP BY reg_date;

Что использовать на практике

• Практика. в ClickHouse для больших объёмов эффективнее использовать maxIf/minIf агрегатные функции с условием вместо JOIN; в PostgreSQL можно использовать оконную функцию MAX(event_date) OVER (PARTITION BY user_id) для избежания повторного сканирования таблицы.

Подводные камни

• Частые ошибки. вычисляют rolling retention как "была ли активность именно в день N или позже" только для одной конкретной даты в прошлом, забывая, что для свежих когорт день N+ ещё не наступил — таких пользователей нужно исключать из расчёта (right-censoring), а не считать их "не retained".