🎯 Live-coding SQL часть 2: Ретеншн первой недели по когортам

Продолжаем тренироваться в секции live-coding по SQL от компании VK. Если пропустили, то первая часть по ссылке.

📋 Условие

Напоминаю: у нас таблица visits со схемой:

user_id    -- ID пользователя  
campaign   -- канал привлечения  
datetime   -- дата и время визита

🧠 Задача

Посчитать ретеншн первой недели по когортам:
Для каждой даты первого визита (когорты) — сколько пользователей вернулись хотя бы один раз в течение 7 дней после первого визита (не включая сам день прихода).

⚙️ Декомпозиция задачи

💠 Шаг 0 — план действий

Нам нужно:
• Определить дату первого визита (как в задаче 1)
• Найти все визиты, которые произошли строго после этой даты
• Оставить только те, что произошли в течение 7 дней
• Посчитать, сколько уникальных пользователей вернулось по каждой когорте


💠 Шаг 1 — CTE с первой датой визита (cohort_date)

Повторим из прошлого задания — база для когорт.

WITH first_visits AS (
    SELECT 
        user_id,
        MIN(DATE(datetime)) AS cohort_date
    FROM visits
    GROUP BY user_id
)

💠 Шаг 2 — джойним с исходной таблицей

Нам нужно сопоставить:
• когорта пользователя
• последующие визиты
• ограничение в 7 дней после первого прихода

, retention_visits AS (
    SELECT
        fv.user_id,
        fv.cohort_date,
        DATE(v.datetime) AS visit_date
    FROM first_visits fv
    JOIN visits v ON fv.user_id = v.user_id
    WHERE DATE(v.datetime) > fv.cohort_date  -- позже, чем первый визит
      AND DATE(v.datetime) <= fv.cohort_date + INTERVAL '7 day'  -- но не позже 7 дней
)

💠 Шаг 3 — считаем вернувшихся пользователей по когорте

, retained_users AS (
    SELECT 
        cohort_date,
        COUNT(DISTINCT user_id) AS retained_users_count
    FROM retention_visits
    GROUP BY cohort_date
)

💠 Шаг 4 — добавим размер когорт (из первого задания)

, cohort_sizes AS (
    SELECT 
        cohort_date,
        COUNT(*) AS users_count
    FROM first_visits
    GROUP BY cohort_date
)

💠 Шаг 5 — собираем результат

WITH first_visits AS (
    SELECT 
        user_id,
        MIN(DATE(datetime)) AS cohort_date
    FROM visits
    GROUP BY user_id
),
retention_visits AS (
    SELECT
        fv.user_id,
        fv.cohort_date,
        DATE(v.datetime) AS visit_date
    FROM first_visits fv
    JOIN visits v ON fv.user_id = v.user_id
    WHERE DATE(v.datetime) > fv.cohort_date
      AND DATE(v.datetime) <= fv.cohort_date + INTERVAL '7 day'
),
retained_users AS (
    SELECT 
        cohort_date,
        COUNT(DISTINCT user_id) AS retained_users_count
    FROM retention_visits
    GROUP BY cohort_date
),
cohort_sizes AS (
    SELECT 
        cohort_date,
        COUNT(*) AS users_count
    FROM first_visits
    GROUP BY cohort_date
)
SELECT 
    cs.cohort_date,
    cs.users_count,
    COALESCE(ru.retained_users_count, 0) AS retained_users,
    ROUND(COALESCE(ru.retained_users_count, 0)::numeric / cs.users_count, 2) AS retention_rate
FROM cohort_sizes cs
LEFT JOIN retained_users ru ON cs.cohort_date = ru.cohort_date
ORDER BY cs.cohort_date;

💡 Советы для секции live-coding (часть 2):
1. Переиспользуй. Продемонстрирую структурное мышление и внимательность возвращаясь к прошлым результатам, нежели писать все с 0 каждый раз.
2. Формулируй вслух, что проверяешь. Например: «Сейчас я фильтрую визиты, произошедшие после первого визита, но не позже 7 дней — для метрики раннего ретеншна».
3. Добавляй защиту от NULL-ов. Используй COALESCE, если есть LEFT JOIN — это демонстрирует внимание к деталям.
4. Поясняй математику. Даже если A / B, проговори: «делю число вернувшихся на размер когорты, чтобы получить процент ретеншна».

📎 В следующем посте: ретеншн первой недели по каналам привлечения.

Интересные задачи присылайте мне в личку - разберем. @onlyanalyst

Вопросы по прохождению такой секции, то задавайте в комментариях.

😀 @onlyanalystgroup
💬 @onlyanalystchat