SQL для Product Analyst: что проверять при найме

Продуктовый аналитик — роль на стыке бизнеса, данных и продуктовой разработки. SQL-задачи для него специфичны: упор на продуктовые метрики, воронки и поведенческий анализ. Универсальный тест не всегда подходит.

Что отличает продуктового аналитика

Продуктовый аналитик работает с:

• Событийными данными (клики, просмотры, конверсии)
• Пользовательскими сессиями и воронками
• Результатами A/B тестов
• Когортным анализом и retention
• Продуктовыми метриками: DAU, MAU, LTV, Churn Rate

SQL для продуктовой аналитики — это прежде всего агрегация событий и оконные функции для работы с пользовательскими траекториями.

Задача 1: Активные пользователи (DAU/MAU)

-- Таблица: events(user_id, event_type, event_date)
-- Задача: рассчитайте DAU и MAU за последние 30 дней,
-- а также соотношение DAU/MAU (индикатор «липкости» продукта)

WITH daily AS (
    SELECT
        event_date,
        COUNT(DISTINCT user_id) AS dau
    FROM events
    WHERE event_date >= CURRENT_DATE - 30
    GROUP BY event_date
),
monthly_window AS (
    SELECT
        event_date,
        dau,
        COUNT(DISTINCT user_id) OVER (
            ORDER BY event_date
            ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
        ) AS mau
    FROM events
    JOIN daily USING (event_date)
    WHERE event_date >= CURRENT_DATE - 30
    GROUP BY event_date, dau
)
SELECT
    event_date,
    dau,
    mau,
    ROUND(100.0 * dau / NULLIF(mau, 0), 1) AS dau_mau_ratio
FROM monthly_window
ORDER BY event_date;

Задача 2: Конверсионная воронка

-- Задача: постройте воронку по шагам онбординга
-- Шаги: registration → email_confirmed → first_login → first_purchase

WITH steps AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_type = 'registration'     THEN 1 END) AS step1,
        MAX(CASE WHEN event_type = 'email_confirmed'  THEN 1 END) AS step2,
        MAX(CASE WHEN event_type = 'first_login'      THEN 1 END) AS step3,
        MAX(CASE WHEN event_type = 'first_purchase'   THEN 1 END) AS step4
    FROM events
    GROUP BY user_id
)
SELECT
    COUNT(*)                    AS total_registered,
    SUM(step2)                  AS email_confirmed,
    SUM(step3)                  AS first_login,
    SUM(step4)                  AS first_purchase,
    ROUND(100.0 * SUM(step2) / COUNT(*), 1)   AS pct_confirmed,
    ROUND(100.0 * SUM(step3) / COUNT(*), 1)   AS pct_logged_in,
    ROUND(100.0 * SUM(step4) / COUNT(*), 1)   AS pct_purchased
FROM steps;

Что проверяем: CASE WHEN внутри MAX для сводки, умение строить воронки.

Задача 3: Когортный анализ Retention

-- Задача: retention по неделям (W0, W1, W2, W3, W4)
-- W0 = неделя регистрации, W1 = следующая неделя и т.д.

WITH registrations AS (
    SELECT
        user_id,
        DATE_TRUNC('week', event_date) AS reg_week
    FROM events
    WHERE event_type = 'registration'
),
activity AS (
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('week', event_date) AS activity_week
    FROM events
)
SELECT
    r.reg_week                                              AS cohort,
    COUNT(DISTINCT r.user_id)                               AS cohort_size,
    SUM(CASE WHEN a.activity_week = r.reg_week             THEN 1 END) AS w0,
    SUM(CASE WHEN a.activity_week = r.reg_week + '1 week'  THEN 1 END) AS w1,
    SUM(CASE WHEN a.activity_week = r.reg_week + '2 weeks' THEN 1 END) AS w2,
    SUM(CASE WHEN a.activity_week = r.reg_week + '3 weeks' THEN 1 END) AS w3,
    SUM(CASE WHEN a.activity_week = r.reg_week + '4 weeks' THEN 1 END) AS w4
FROM registrations r
LEFT JOIN activity a ON a.user_id = r.user_id
GROUP BY r.reg_week
ORDER BY r.reg_week;

Задача 4: Сессионный анализ

-- Задача: найдите среднюю длину сессии и bounce rate
-- Сессия = последовательность событий пользователя с перерывами < 30 минут
-- Bounce = сессия из одного события

WITH events_with_gap AS (
    SELECT
        user_id,
        event_time,
        LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event,
        event_time - LAG(event_time) OVER (
            PARTITION BY user_id ORDER BY event_time
        ) AS time_since_prev
    FROM events
),
session_starts AS (
    SELECT *,
           SUM(CASE WHEN time_since_prev IS NULL
                      OR time_since_prev > INTERVAL '30 minutes'
                    THEN 1 ELSE 0 END)
               OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
    FROM events_with_gap
),
session_stats AS (
    SELECT
        user_id,
        session_id,
        COUNT(*)                                            AS events_in_session,
        MAX(event_time) - MIN(event_time)                  AS session_duration
    FROM session_starts
    GROUP BY user_id, session_id
)
SELECT
    ROUND(AVG(EXTRACT(EPOCH FROM session_duration) / 60), 1) AS avg_session_min,
    ROUND(100.0 * SUM(CASE WHEN events_in_session = 1 THEN 1 END) / COUNT(*), 1) AS bounce_rate_pct
FROM session_stats;

Это сложная задача уровня Senior. Если кандидат решает её правильно — это сильный специалист.

Теоретические вопросы для продуктового аналитика

• «Как посчитать LTV через SQL?»
• «Что такое Churn Rate и как его рассчитать?»
• «Как правильно определить контрольную и тестовую группы для A/B теста в SQL?»
• «Что такое rolling retention vs classic retention?»

Шкала оценки

Итого 20. Продуктовый аналитик Middle: ≥ 10. Senior: ≥ 16.

Для практики именно продуктовых аналитических задач рекомендуйте кандидатам SQLlab.ru — там есть раздел по аналитике с реальными бизнес-сценариями.