Многошаговая воронка конверсии с временными ограничениями

Условие задания

**Контекст:** Ecom-платформа анализирует воронку от просмотра товара до покупки. Каждый шаг должен произойти в строгом порядке и в пределах временного окна.

**Данные:**

[см. код в задании]

**Задание:**

Постройте воронку конверсии за последние 30 дней:
1. Шаги: view_product → add_to_cart → begin_checkout → add_payment → purchase
2. Каждый следующий шаг должен произойти **после предыдущего** и **в пределах 24 часов** от первого шага (view_product)
3. Для каждого шага выведите: количество уникальных пользователей, конверсию от предыдущего шага и от первого шага
4. Шаги должны быть для **одного и того же** `product_id`

Пример данных

Структура для ориентира — реальные значения из эталонного решения.

WITH step1_view AS (
    SELECT DISTINCT ON (user_id, product_id)
        user_id,
        product_id,
        event_ts AS view_ts
    FROM user_events
    WHERE event_type = 'view_product'
      AND event_ts >= CURRENT_DATE - INTERVAL '30 days'
    ORDER BY user_id, product_id, event_ts
),

step2_cart AS (
    SELECT
        s1.user_id,
        s1.product_id,
        s1.view_ts,
        MIN(e.event_ts) AS cart_ts
    FROM step1_view s1
    JOIN user_events e
        ON  e.user_id = s1.user_id
        AND e.product_id = s1.product_id
        AND e.event_type = 'add_to_cart'
        AND e.event_ts > s1.view_ts
        AND e.event_ts <= s1.view_ts + INTERVAL '24 hours'
    GROUP BY s1.user_id, s1.product_id, s1.view_ts
),

step3_checkout AS (
    SELECT
        s2.user_id,
        s2.product_id,
        s2.view_ts,
        MIN(e.event_ts) AS checkout_ts
    FROM step2_cart s2
    JOIN user_events e
        ON  e.user_id = s2.user_id
        AND e.product_id = s2.product_id
        AND e.event_type = 'begin_checkout'
        AND e.event_ts > s2.cart_ts
        AND e.event_ts <= s2.view_ts + INTERVAL '24 hours'
    GROUP BY s2.user_id, s2.product_id, s2.view_ts
),

step4_payment AS (
    SELECT
        s3.user_id,
        s3.product_id,
        s3.view_ts,
        MIN(e.event_ts) AS payment_ts
    FROM step3_checkout s3
    JOIN user_events e
        ON  e.user_id = s3.user_id
        AND e.product_id = s3.product_id
        AND e.event_type = 'add_payment'
        AND e.event_ts > s3.checkout_ts
        AND e.event_ts <= s3.view_ts + INTERVAL '24 hours'
    GROUP BY s3.user_id, s3.product_id, s3.view_ts
),

step5_purchase AS (
    SELECT
        s4.user_id,
        s4.product_id,
        s4.view_ts,
        MIN(e.event_ts) AS purchase_ts
    FROM step4_payment s4
    JOIN user_events e
        ON  e.user_id = s4.user_id
        AND e.product_id = s4.product_id
        AND e.event_type = 'purchase'
        AND e.event_ts > s4.payment_ts
        AND e.event_ts <= s4.view_ts + INTERVAL '24 hours'
    GROUP BY s4.user_id, s4.product_id, s4.view_ts
),

counts AS (
    SELECT
        (SELECT COUNT(DISTINCT user_id) FROM step1_view)     AS s1,
        (SELECT COUNT(DISTINCT user_id) FROM step2_cart)     AS s2,
        (SELECT COUNT(DISTINCT user_id) FROM step3_checkout) AS s3,
        (SELECT COUNT(DISTINCT user_id) FROM step4_payment)  AS s4,
        (SELECT COUNT(DISTINCT user_id) FROM step5_purchase) AS s5
)

SELECT
    step_name,
    users,
    ROUND(100.0 * users / NULLIF(prev_users, 0), 1) AS cvr_from_prev,
    ROUND(100.0 * users / NULLIF(s1, 0), 1)          AS cvr_from_start
FROM counts,
LATERAL (VALUES
    ('1. View Product',    s1, NULL::BIGINT, s1),
    ('2. Add to Cart',     s2, s1,           s1),
    ('3. Begin Checkout',  s3, s2,           s1),
    ('4. Add Payment',     s4, s3,           s1),
    ('5. Purchase',        s5, s4,           s1)
) AS steps(step_name, users, prev_users, s1);

Темы

воронка CTE оконные функции конверсия многошаговый анализ

Подсказки

• Для каждого пользователя и продукта найдите первый view_product через ROW_NUMBER
• Последовательность шагов: каждый следующий event_ts > предыдущий event_ts
• LATERAL JOIN или цепочка CTE для поиска следующего шага после предыдущего
• Временное ограничение: event_ts <= view_ts + INTERVAL '24 hours'

Все тестовые задания →

Частые вопросы

Какой уровень знаний нужен для задачи "Многошаговая воронка конверсии с временными ограничениями"?

Это задание для уровня Senior. Senior-уровень — глубокое понимание темы, опыт решения нестандартных задач, обсуждение trade-off на собеседовании.

На каких собеседованиях встречается такая задача?

Подобные задания в категории «SQL» регулярно дают на собеседованиях аналитика данных в Яндекс, Сбер, Ozon, Авито, Тинькофф, Wildberries, T-Bank, X5, ВТБ и других крупных IT-компаниях. Тематика: воронка, CTE, оконные функции, конверсия, многошаговый анализ.

Сколько времени даётся на решение?

На реальном собеседовании на подобную задачу отводится 30-60 минут с обсуждением подходов, оптимизаций и trade-off. Для тренировки рекомендуем сначала решить самостоятельно, потом сверить с эталонным решением и подсказками.

Где ещё потренироваться по теме «SQL»?

На zasqlpython.ru есть 425 SQL задач в песочнице с автопроверкой кода, конспекты SQL для аналитика, AI мок-собеседование с разбором ваших ответов.

← Все задания