SQL для аналитиков: что нужно знать и уметь

SQL — главный инструмент аналитика. Не Python, не Excel, именно SQL. Вот что нужно уметь, чтобы работать с данными эффективно.

Чем SQL аналитика отличается от SQL разработчика

Разработчик пишет SQL для CRUD — создать, прочитать, изменить, удалить. Аналитик использует SQL для исследования: найти паттерны, посчитать метрики, ответить на бизнес-вопрос.

Аналитику нужны:

• Сложные агрегации и GROUP BY
• Оконные функции (без них никуда)
• CTE для читаемых многошаговых запросов
• DATE_TRUNC и работа с временными рядами
• Условная агрегация через CASE WHEN

Основные аналитические задачи

Когортный анализ

Разбивка пользователей по времени регистрации и отслеживание их поведения:

WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', registered_at) AS cohort_month
    FROM users
),
orders_with_cohort AS (
    SELECT
        o.user_id,
        c.cohort_month,
        DATE_TRUNC('month', o.created_at) AS order_month
    FROM orders o
    JOIN cohorts c ON o.user_id = c.user_id
)
SELECT
    cohort_month,
    COUNT(DISTINCT user_id)                  AS cohort_size,
    COUNT(DISTINCT CASE WHEN order_month = cohort_month THEN user_id END)                  AS month_0,
    COUNT(DISTINCT CASE WHEN order_month = cohort_month + INTERVAL '1 month' THEN user_id END) AS month_1,
    COUNT(DISTINCT CASE WHEN order_month = cohort_month + INTERVAL '2 month' THEN user_id END) AS month_2
FROM orders_with_cohort
GROUP BY cohort_month
ORDER BY cohort_month;

Воронка конверсии

SELECT
    COUNT(DISTINCT CASE WHEN step = 'view'     THEN session_id END) AS views,
    COUNT(DISTINCT CASE WHEN step = 'cart'     THEN session_id END) AS add_to_cart,
    COUNT(DISTINCT CASE WHEN step = 'checkout' THEN session_id END) AS checkout,
    COUNT(DISTINCT CASE WHEN step = 'purchase' THEN session_id END) AS purchases,
    ROUND(100.0 *
        COUNT(DISTINCT CASE WHEN step = 'cart' THEN session_id END) /
        NULLIF(COUNT(DISTINCT CASE WHEN step = 'view' THEN session_id END), 0), 1
    ) AS view_to_cart_pct
FROM funnel_events
WHERE event_date >= CURRENT_DATE - 30;

Скользящие метрики

-- 7-дневный скользящий DAU
SELECT
    event_date,
    COUNT(DISTINCT user_id) AS dau,
    AVG(COUNT(DISTINCT user_id)) OVER (
        ORDER BY event_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS dau_7d_avg
FROM events
GROUP BY event_date
ORDER BY event_date;

Ранжирование

-- Топ продуктов внутри каждой категории
SELECT
    category,
    product_name,
    revenue,
    RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rank_in_category
FROM products p
JOIN sales s ON p.id = s.product_id
GROUP BY category, product_name, revenue
HAVING RANK() OVER (PARTITION BY category ORDER BY revenue DESC) <= 3;

Оконные функции — основа аналитического SQL

Без оконных функций большинство аналитических задач решаются через самосоединения или подзапросы — громоздко и медленно.

ROW_NUMBER — уникальный номер строки в группе:

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at)

LAG / LEAD — предыдущее/следующее значение:

-- Рост выручки относительно предыдущего месяца
revenue - LAG(revenue) OVER (ORDER BY month) AS revenue_growth

SUM OVER — нарастающий итог:

SUM(revenue) OVER (ORDER BY month) AS cumulative_revenue

PERCENTILE — медиана и перцентили:

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY check_amount) AS median_check

Паттерны работы с данными

Дедупликация

-- Оставить только первую запись для каждого пользователя
SELECT DISTINCT ON (user_id) *
FROM events
ORDER BY user_id, created_at;

Поиск разрывов в последовательности

-- Дни без продаж
SELECT generate_series::date AS missing_date
FROM generate_series('2024-01-01'::date, '2024-12-31'::date, '1 day')
WHERE generate_series::date NOT IN (SELECT DATE(created_at) FROM orders);

Pivot — строки в колонки

SELECT
    user_id,
    MAX(CASE WHEN month = 1 THEN revenue END) AS jan,
    MAX(CASE WHEN month = 2 THEN revenue END) AS feb,
    MAX(CASE WHEN month = 3 THEN revenue END) AS mar
FROM monthly_revenue
GROUP BY user_id;

Что изучать аналитику

Приоритет:

1. Оконные функции — ROW_NUMBER, RANK, LAG/LEAD, SUM/AVG OVER
2. CTE — структурированные многошаговые запросы
3. DATE_TRUNC / EXTRACT — работа с временными рядами
4. Условная агрегация — CASE WHEN внутри COUNT/SUM
5. EXPLAIN ANALYZE — понимать почему запрос медленный

SQL — это не просто инструмент получения данных. Это язык для формулировки аналитических гипотез и их проверки.