SQL-задачи на retention, воронки и когорты — с решениями

Зачем решать задачи, а не просто читать теорию

На собеседовании аналитика дают таблицы и просят написать запрос. Не объяснить концепцию, не нарисовать схему — а выдать рабочий SQL. Теория retention, воронок и когорт помогает понять, что считать. Но без практики руки не напишут WITH ... AS за 15 минут под давлением. Здесь — 5 задач уровня middle, которые регулярно встречаются на собесах. PostgreSQL, с решениями и разборами.

Если хотите сначала разобраться в теории — начните с гайдов: retention в SQL, воронка конверсии, когортный анализ.

---

Задача 1. D7 retention по недельным когортам

Условие. Таблица activity(user_id, event_date). Посчитайте D7 retention по недельным когортам: сгруппируйте пользователей по неделе первого визита, найдите долю тех, кто вернулся ровно через 7 дней.

Подсказка. date_trunc('week', ...) превращает дату в начало недели. Когорта — это MIN(event_date) каждого пользователя.

Решение

WITH cohorts AS (
    SELECT
        user_id,
        date_trunc('week', MIN(event_date))::DATE AS cohort_week
    FROM activity
    GROUP BY user_id
),
cohort_size AS (
    SELECT cohort_week, COUNT(*) AS total_users
    FROM cohorts
    GROUP BY cohort_week
),
first_visit AS (
    SELECT user_id, MIN(event_date) AS first_date
    FROM activity
    GROUP BY user_id
),
returned AS (
    SELECT DISTINCT c.cohort_week, f.user_id
    FROM first_visit f
    JOIN cohorts c ON c.user_id = f.user_id
    JOIN activity a ON a.user_id = f.user_id
        AND a.event_date = f.first_date + 7
)
SELECT
    cs.cohort_week,
    cs.total_users,
    COUNT(r.user_id) AS returned_d7,
    ROUND(100.0 * COUNT(r.user_id) / cs.total_users, 1) AS retention_d7_pct
FROM cohort_size cs
LEFT JOIN returned r ON r.cohort_week = cs.cohort_week
GROUP BY cs.cohort_week, cs.total_users
ORDER BY cs.cohort_week;

Проще и надёжнее — через day_offset:

WITH cohorts AS (
    SELECT user_id, MIN(event_date) AS first_date
    FROM activity
    GROUP BY user_id
),
cohort_week AS (
    SELECT
        user_id,
        first_date,
        date_trunc('week', first_date)::DATE AS cohort_week
    FROM cohorts
),
cohort_size AS (
    SELECT cohort_week, COUNT(*) AS total_users
    FROM cohort_week
    GROUP BY cohort_week
)
SELECT
    cw.cohort_week,
    cs.total_users,
    COUNT(DISTINCT CASE
        WHEN a.event_date - cw.first_date = 7 THEN cw.user_id
    END) AS returned_d7,
    ROUND(100.0 *
        COUNT(DISTINCT CASE
            WHEN a.event_date - cw.first_date = 7 THEN cw.user_id
        END) / cs.total_users, 1
    ) AS retention_d7_pct
FROM cohort_week cw
JOIN activity a ON a.user_id = cw.user_id
JOIN cohort_size cs ON cs.cohort_week = cw.cohort_week
GROUP BY cw.cohort_week, cs.total_users
ORDER BY cw.cohort_week;

Разбор. Ключевой момент — first_date определяется для каждого пользователя индивидуально, а cohort_week — это группировка для отчёта. D7 retention = «вернулся ровно через 7 дней после своего первого визита». CASE WHEN ... THEN user_id END внутри COUNT(DISTINCT ...) — стандартный приём для условного подсчёта.

---

Задача 2. Воронка: визит -> корзина -> покупка

Условие. Таблица events(user_id, event_type, created_at). Значения event_type: 'visit', 'cart', 'purchase'. Постройте воронку: сколько уникальных пользователей на каждом шаге и конверсия относительно предыдущего шага.

Подсказка. Каждый шаг — отдельный COUNT(DISTINCT ...). Конверсия = текущий шаг / предыдущий шаг.

Решение

WITH steps AS (
    SELECT
        COUNT(DISTINCT CASE WHEN event_type = 'visit'    THEN user_id END) AS visits,
        COUNT(DISTINCT CASE WHEN event_type = 'cart'      THEN user_id END) AS carts,
        COUNT(DISTINCT CASE WHEN event_type = 'purchase'  THEN user_id END) AS purchases
    FROM events
)
SELECT
    visits,
    carts,
    purchases,
    ROUND(100.0 * carts / NULLIF(visits, 0), 1)    AS visit_to_cart_pct,
    ROUND(100.0 * purchases / NULLIF(carts, 0), 1) AS cart_to_purchase_pct,
    ROUND(100.0 * purchases / NULLIF(visits, 0), 1) AS overall_conversion_pct
FROM steps;

Разбор. NULLIF(visits, 0) защищает от деления на ноль. Воронка здесь «нестрогая»: пользователь считается на шаге, даже если попал туда без предыдущего шага. Для строгой воронки (cart только если был visit) нужен JOIN между шагами. Подробнее — в статье воронка конверсии в SQL.

---

Задача 3. Month-over-month рост выручки

Условие. Таблица orders(order_id, user_id, amount, created_at). Посчитайте выручку по месяцам и процент изменения относительно предыдущего месяца (MoM growth).

Подсказка. Оконная функция LAG() даёт доступ к значению предыдущей строки.

Решение

WITH monthly_revenue AS (
    SELECT
        date_trunc('month', created_at)::DATE AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY date_trunc('month', created_at)
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
        1
    ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY month;

Разбор. LAG(revenue) OVER (ORDER BY month) возвращает выручку предыдущего месяца. Первая строка получит NULL — предыдущего месяца нет, и это корректное поведение. MoM growth = (текущий - предыдущий) / предыдущий * 100. На собеседовании могут попросить добавить фильтр «только последние 12 месяцев» — это простой WHERE created_at >= now() - interval '12 months'.

---

Задача 4. Покупка в течение 24 часов после регистрации

Условие. Таблицы users(user_id, registered_at) и orders(order_id, user_id, created_at). Найдите пользователей, которые совершили покупку в течение 24 часов после регистрации. Выведите user_id, время регистрации, время первой покупки и разницу в часах.

Подсказка. EXTRACT(EPOCH FROM ...) переводит интервал в секунды. Делим на 3600 — получаем часы.

Решение

WITH first_purchase AS (
    SELECT
        user_id,
        MIN(created_at) AS first_order_at
    FROM orders
    GROUP BY user_id
)
SELECT
    u.user_id,
    u.registered_at,
    fp.first_order_at,
    ROUND(
        EXTRACT(EPOCH FROM fp.first_order_at - u.registered_at) / 3600.0,
        1
    ) AS hours_to_purchase
FROM users u
JOIN first_purchase fp ON fp.user_id = u.user_id
WHERE fp.first_order_at <= u.registered_at + INTERVAL '24 hours'
ORDER BY hours_to_purchase;

Разбор. MIN(created_at) — берём именно первую покупку, а не любую. Условие <= registered_at + interval '24 hours' отсекает тех, кто купил позже. EXTRACT(EPOCH FROM ...) — стандартный способ получить секунды из интервала в PostgreSQL. Эту задачу часто дают, чтобы проверить, умеете ли вы работать с timestamp-арифметикой.

---

Задача 5. Кумулятивная выручка по когортам

Условие. Таблицы users(user_id, registered_at) и orders(order_id, user_id, amount, created_at). Для каждой месячной когорты регистрации посчитайте кумулятивную выручку по месяцам жизни когорты (месяц 0, 1, 2, ...).

Подсказка. «Месяц жизни» когорты — разница между месяцем заказа и месяцем регистрации. Кумулятивная сумма — SUM() OVER (PARTITION BY ... ORDER BY ...).

Решение

WITH user_cohort AS (
    SELECT
        user_id,
        date_trunc('month', registered_at)::DATE AS cohort_month
    FROM users
),
cohort_orders AS (
    SELECT
        uc.cohort_month,
        (EXTRACT(YEAR FROM age(date_trunc('month', o.created_at), uc.cohort_month)) * 12
         + EXTRACT(MONTH FROM age(date_trunc('month', o.created_at), uc.cohort_month))
        )::int AS lifetime_month,
        o.amount
    FROM user_cohort uc
    JOIN orders o ON o.user_id = uc.user_id
    WHERE o.created_at >= uc.cohort_month
),
monthly_rev AS (
    SELECT
        cohort_month,
        lifetime_month,
        SUM(amount) AS revenue
    FROM cohort_orders
    GROUP BY cohort_month, lifetime_month
)
SELECT
    cohort_month,
    lifetime_month,
    revenue,
    SUM(revenue) OVER (
        PARTITION BY cohort_month
        ORDER BY lifetime_month
    ) AS cumulative_revenue
FROM monthly_rev
ORDER BY cohort_month, lifetime_month;

Разбор. age() возвращает интервал между двумя датами. Извлекаем из него годы и месяцы, чтобы получить целое число — номер месяца жизни когорты. SUM(...) OVER (PARTITION BY cohort_month ORDER BY lifetime_month) — это кумулятивная (нарастающая) сумма внутри каждой когорты. Результат показывает, сколько денег принесла каждая когорта к концу каждого месяца — основа для расчёта LTV. Подробнее о когортах — в статье когортный анализ.

---

Что дальше

Эти 5 задач покрывают самые частые темы на собеседованиях: retention, воронки, MoM-динамику, временные интервалы и когортный LTV. Если хотите больше задач — в тренажёре есть 300+ SQL-вопросов разного уровня, а полный список тем — на странице примеры вопросов.

Лучший способ закрепить — решать руками. Откройте тренажёр и прорешайте SQL-блок — там есть задачи на все темы из этой статьи.