Когорты пользователей по дате регистрации и их возвраты

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

Задача

Есть лог событий продукта events(user_id, event_name, event_time), где event_name — это registration или visit.

Построй когорты пользователей по дате регистрации и посчитай, какая доля когорты возвращалась в последующие дни. Формат вывода:

cohort_date | date | cohort_size | returned_users | share

Схема данных

CREATE TABLE events (user_id int, event_name text, event_time timestamp);

Событий два типа: registration (дата регистрации → когорта пользователя) и visit (заход). Возврат — это визит в один из последующих дней.

Решение

WITH reg AS (
  SELECT user_id, MIN(event_time::date) AS cohort_date
  FROM events
  WHERE event_name = 'registration'
  GROUP BY user_id
),
sizes AS (
  SELECT cohort_date, COUNT(*) AS cohort_size
  FROM reg
  GROUP BY cohort_date
),
ret AS (
  SELECT r.cohort_date,
         e.event_time::date AS date,
         COUNT(DISTINCT e.user_id) AS returned_users
  FROM reg r
  JOIN events e
    ON e.user_id = r.user_id
   AND e.event_name = 'visit'
   AND e.event_time::date > r.cohort_date
  GROUP BY 1, 2
)
SELECT to_char(s.cohort_date, 'YYYY-MM-DD') AS cohort_date,
       to_char(ret.date, 'YYYY-MM-DD') AS date,
       s.cohort_size, ret.returned_users,
       ROUND(ret.returned_users * 1.0 / s.cohort_size, 4) AS share
FROM ret
JOIN sizes s USING (cohort_date)
ORDER BY s.cohort_date, ret.date;

Разбор

Разбиваем на три CTE — так задача читается по частям.

reg — дата регистрации каждого пользователя. Беру MIN(event_time::date) на случай, если событие регистрации задвоено: когорта пользователя одна, самая ранняя. sizes — размер каждой когорты, считается отдельно, до всяких возвратов.

ret — возвраты. Джойню регистрации на визиты того же пользователя, и вот два нюанса, которые проверяют:

• Условие e.event_time::date > r.cohort_date — строгое «больше». Визит в день регистрации возвратом не считается: человек только что пришёл, это ещё не «вернулся». Поставишь >= — завысишь удержание.
• COUNT(DISTINCT e.user_id) — если пользователь заходил за день несколько раз, он всё равно один вернувшийся.

Финальная доля — returned_users * 1.0 / cohort_size. Множитель 1.0 спасает от целочисленного деления: без него 1 / 3 дало бы 0. Соединяю возвраты с размерами когорт по cohort_date и сортирую по когорте и дню — сортировка идёт по исходным датам, а в SELECT обе колонки, cohort_date и date, оборачиваю в to_char(..., 'YYYY-MM-DD') только для читаемого вывода. Про то, почему для такой цепочки удобнее CTE, чем вложенные подзапросы, — в вопросе подзапрос, CTE или временная таблица.

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

cohort_date | date | cohort_size | returned_users | share
--- | --- | --- | --- | ---
2026-06-01 | 2026-06-02 | 3 | 1 | 0.3333
2026-06-01 | 2026-06-03 | 3 | 2 | 0.6667
2026-06-02 | 2026-06-03 | 2 | 1 | 0.5000