15 апреля 2026 г.

SQL-запросы для продуктового аналитика

Q: Какие индексы нужны для продуктовой аналитики?

`events(user_id, event_time, event_name)` — покрывает 80% запросов.

Схема данных

users (user_id, registered_at, country, platform)
events (user_id, event_name, event_time, properties)
ab_test_users (user_id, ab_group, test_id)

1. DAU за 30 дней

SELECT event_time::DATE AS day, COUNT(DISTINCT user_id) AS dau
FROM events
WHERE event_name = 'app_open'
  AND event_time >= CURRENT_DATE - INTERVAL '30 day'
GROUP BY 1 ORDER BY 1;

2. WAU по неделям

SELECT DATE_TRUNC('week', event_time) AS week,
    COUNT(DISTINCT user_id) AS wau
FROM events
WHERE event_name = 'app_open'
GROUP BY 1 ORDER BY 1;

3. Stickiness (DAU/MAU)

WITH daily AS (
    SELECT DISTINCT event_time::DATE AS day, user_id
    FROM events WHERE event_name = 'app_open'
),
dau AS (SELECT day, COUNT(DISTINCT user_id) AS dau FROM daily GROUP BY day),
mau AS (
    SELECT day,
        COUNT(DISTINCT user_id) OVER (
            ORDER BY day RANGE BETWEEN INTERVAL '27 day' PRECEDING AND CURRENT ROW
        ) AS mau
    FROM (SELECT DISTINCT day, user_id FROM daily) d
)
SELECT d.day, d.dau, m.mau, ROUND(d.dau * 1.0 / m.mau, 3) AS stickiness
FROM dau d JOIN mau m USING (day);

4. Retention D7

WITH cohort AS (
    SELECT DISTINCT user_id
    FROM events
    WHERE event_time::DATE = '2026-04-01'
),
d7 AS (
    SELECT DISTINCT user_id
    FROM events
    WHERE event_time::DATE = '2026-04-08'
)
SELECT COUNT(*) * 100.0 / (SELECT COUNT(*) FROM cohort) AS retention_d7
FROM cohort INTERSECT SELECT user_id FROM d7;

5. Воронка регистрации

SELECT
    COUNT(DISTINCT user_id) FILTER (WHERE event = 'landing_visit') AS visits,
    COUNT(DISTINCT user_id) FILTER (WHERE event = 'signup_start') AS starts,
    COUNT(DISTINCT user_id) FILTER (WHERE event = 'signup_complete') AS signups,
    COUNT(DISTINCT user_id) FILTER (WHERE event = 'email_confirm') AS confirmed
FROM events
WHERE event_time >= CURRENT_DATE - INTERVAL '30 day';

Если хочется сразу закрепить тему на практике — открой тренажёр в Telegram. 10 минут в день — и синтаксис в пальцах.

6. Activation rate (из регистрации в первое действие)

SELECT
    COUNT(DISTINCT user_id) FILTER (WHERE registered) AS regs,
    COUNT(DISTINCT user_id) FILTER (WHERE registered AND first_action) AS activated,
    ROUND(
        COUNT(DISTINCT user_id) FILTER (WHERE registered AND first_action) * 100.0 /
        NULLIF(COUNT(DISTINCT user_id) FILTER (WHERE registered), 0), 2
    ) AS activation_rate
FROM user_flags
WHERE registered_at >= CURRENT_DATE - INTERVAL '30 day';

7. Когортная retention таблица

WITH cohort AS (
    SELECT user_id, DATE_TRUNC('week', MIN(event_time))::DATE AS cohort_week
    FROM events GROUP BY user_id
),
activity AS (
    SELECT c.user_id, c.cohort_week,
        (DATE_TRUNC('week', e.event_time)::DATE - c.cohort_week) / 7 AS week_num
    FROM cohort c JOIN events e USING (user_id)
)
SELECT cohort_week, week_num, COUNT(DISTINCT user_id) AS active
FROM activity GROUP BY 1, 2 ORDER BY 1, 2;

8. Anti-funnel: где отваливаются пользователи

SELECT
    step,
    users,
    LAG(users) OVER (ORDER BY step_num) AS prev,
    LAG(users) OVER (ORDER BY step_num) - users AS dropoff,
    ROUND((LAG(users) OVER (ORDER BY step_num) - users) * 100.0 /
        LAG(users) OVER (ORDER BY step_num), 2) AS dropoff_pct
FROM (
    SELECT 1 AS step_num, 'signup' AS step,
        COUNT(DISTINCT user_id) FILTER (WHERE event = 'signup') AS users
    FROM events
    UNION ALL
    SELECT 2, 'activation',
        COUNT(DISTINCT user_id) FILTER (WHERE event = 'first_action')
    FROM events
    -- И так далее
) steps;

9. Session identification (30-min timeout)

WITH diffs AS (
    SELECT user_id, event_time,
        EXTRACT(EPOCH FROM (event_time -
            LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time)
        )) / 60 AS min_gap
    FROM events
)
SELECT user_id, event_time,
    SUM(CASE WHEN min_gap > 30 OR min_gap IS NULL THEN 1 ELSE 0 END)
        OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
FROM diffs;

10. A/B-тест: конверсия по группам

SELECT a.ab_group,
    COUNT(DISTINCT a.user_id) AS users,
    COUNT(DISTINCT e.user_id) FILTER (WHERE e.event = 'purchase') AS converters,
    ROUND(
        COUNT(DISTINCT e.user_id) FILTER (WHERE e.event = 'purchase') * 100.0 /
        COUNT(DISTINCT a.user_id), 2
    ) AS conv_pct
FROM ab_test_users a
LEFT JOIN events e USING (user_id)
WHERE a.test_id = 'new_onboarding_v2'
GROUP BY a.ab_group;

11. Power users (топ 10% активных)

WITH activity AS (
    SELECT user_id, COUNT(*) AS event_count
    FROM events
    WHERE event_time >= CURRENT_DATE - INTERVAL '30 day'
    GROUP BY user_id
),
percentiles AS (
    SELECT user_id, event_count,
        PERCENT_RANK() OVER (ORDER BY event_count) AS prank
    FROM activity
)
SELECT user_id, event_count
FROM percentiles
WHERE prank >= 0.9;

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

12. Feature adoption

SELECT feature, COUNT(DISTINCT user_id) AS users,
    COUNT(DISTINCT user_id) * 100.0 / (SELECT COUNT(DISTINCT user_id) FROM events) AS adoption_pct
FROM events
WHERE event = 'feature_use'
GROUP BY feature
ORDER BY users DESC;

13. Session length distribution

WITH sessions AS (
    SELECT user_id, session_id,
        MAX(event_time) - MIN(event_time) AS duration
    FROM events_with_session
    GROUP BY user_id, session_id
)
SELECT
    AVG(EXTRACT(EPOCH FROM duration) / 60) AS avg_min,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY duration) AS median
FROM sessions;

14. D1 retention по фичам онбординга

WITH users_by_step AS (
    SELECT user_id,
        MAX(CASE WHEN event = 'onboarding_step_3' THEN 1 ELSE 0 END) AS completed_onb
    FROM events WHERE registered_at >= CURRENT_DATE - INTERVAL '30 day'
    GROUP BY user_id
),
d1 AS (
    SELECT DISTINCT user_id
    FROM events
    WHERE event_time::DATE = registered_at::DATE + 1
)
SELECT
    u.completed_onb,
    COUNT(*) AS cohort,
    COUNT(d1.user_id) AS retained,
    ROUND(COUNT(d1.user_id) * 100.0 / COUNT(*), 2) AS d1_pct
FROM users_by_step u
LEFT JOIN d1 USING (user_id)
GROUP BY u.completed_onb;

15. Engagement score

WITH user_metrics AS (
    SELECT user_id,
        COUNT(DISTINCT event_time::DATE) AS active_days,
        COUNT(*) AS total_events,
        COUNT(DISTINCT event_name) AS unique_events
    FROM events
    WHERE event_time >= CURRENT_DATE - INTERVAL '30 day'
    GROUP BY user_id
)
SELECT user_id,
    (active_days * 3 + total_events * 0.1 + unique_events * 2) AS engagement_score
FROM user_metrics
ORDER BY engagement_score DESC
LIMIT 100;

Custom формула, зависит от продукта.

FAQ

Какие индексы нужны для продуктовой аналитики?

events(user_id, event_time, event_name) — покрывает 80% запросов.

PostgreSQL или ClickHouse?

Для продуктовой — ClickHouse. Миллиарды событий, агрегаты быстрые.

Как ускорить когортные запросы?

Pre-aggregate в materialized view или Data Mart, обновляйте по расписанию.

Когда оконная, когда JOIN?

Для расчётов «рядом» (сравнение с предыдущим) — оконная. Для обогащения — JOIN.

Тренироваться в Telegram