Как посчитать долю новых пользователей, пришедших в день X, которые вернулись в течение 7 дней?

Что проверяют

Умеете ли вы превратить расплывчатую метрику в точное определение до того, как писать SQL. Следом спрашивают, чем такой возврат отличается от классического retention седьмого дня и как посчитать метрику сразу по всем когортам.

Как отвечать

Сначала определения, их стоит проговорить вслух:

• новый пользователь: регистрация или первое событие в день X. Они могут не совпадать, выберите одно;
• вернулся: любое событие или только значимое, например сессия или заказ;
• окно: дни с X+1 по X+7. Сам день X не считаем, иначе вернувшимися окажутся все, кто зашёл дважды в первый день;
• часовой пояс: день X по времени сервера или пользователя.

Затем один запрос вместо двух:

WITH cohort AS (
  SELECT user_id, first_ts::date AS d0
  FROM users WHERE first_ts::date = DATE '2026-09-01'
)
SELECT COUNT(DISTINCT e.user_id)::numeric
     / NULLIF(COUNT(DISTINCT c.user_id), 0)
FROM cohort c
LEFT JOIN events e ON e.user_id = c.user_id
 AND e.event_ts >= c.d0 + 1 AND e.event_ts < c.d0 + 8;

LEFT JOIN оставляет невернувшихся в знаменателе, а полуоткрытый интервал >= ... < не теряет последний день.

Оговорите разницу: «вернулся в течение 7 дней» (rolling) и «был активен ровно на 7-й день» (классический D7) дают сильно разные цифры. Для всех когорт фильтр заменяется на GROUP BY d0, но свежие когорты с неполным окном нужно исключить.

Где спотыкаются

Пишут INNER JOIN и получают долю 100%: невернувшиеся пропадают из знаменателя.

Фильтруют BETWEEN '2026-09-02' AND '2026-09-08' по колонке timestamp. Верхняя граница становится полуночью 8-го, и весь последний день выпадает.

Считают когорту, у которой семь дней ещё не прошли, и делают вывод о падении удержания.