Какие задачи решал при помощи SQL?

Примеры задач, решённых с помощью SQL

SQL — это мой основной инструмент. За 10+ лет я использовал его для решения сотен аналитических задач. Выберу несколько ярких примеров, которые показывают разные аспекты работы Data Analyst.

Задача 1: Анализ поведения пользователей (Cohort Analysis)

Контекст: Нужно было понять, какой процент пользователей, зарегистрировавшихся в разные месяцы, совершил покупку через N дней.

WITH cohorts AS (
    -- Когда каждый пользователь зарегистрировался
    SELECT 
        user_id,
        DATE_TRUNC('month', created_at)::date as cohort_month,
        DATE_PART('day', MAX(purchase_date) - created_at) as days_to_purchase
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, DATE_TRUNC('month', u.created_at)
)
SELECT 
    cohort_month,
    COUNT(DISTINCT user_id) as cohort_size,
    COUNT(DISTINCT CASE WHEN days_to_purchase <= 7 THEN user_id END) as purchased_7d,
    COUNT(DISTINCT CASE WHEN days_to_purchase <= 30 THEN user_id END) as purchased_30d,
    COUNT(DISTINCT CASE WHEN days_to_purchase <= 90 THEN user_id END) as purchased_90d,
    ROUND(100.0 * COUNT(DISTINCT CASE WHEN days_to_purchase IS NOT NULL THEN user_id END) / 
          NULLIF(COUNT(DISTINCT user_id), 0), 2) as conversion_rate_pct
FROM cohorts
GROUP BY cohort_month
ORDER BY cohort_month DESC;

Результат: Обнаружили, что cohort с июня 2023 имеет 45% конверсию, а cohort с июля — только 12%. Это привело к расследованию и обнаружению проблемы в onboarding процессе.

Задача 2: Выявление пользователей на пороге churn (RFM анализ)

Контекст: В SaaS компании нужно было выявить пользователей, которые вероятно уходят, чтобы их сохранить.

WITH user_metrics AS (
    SELECT 
        u.id,
        u.email,
        u.company_name,
        -- Recency: дни с последнего действия
        DATE_PART('day', CURRENT_TIMESTAMP - MAX(la.last_action_at)) as recency_days,
        -- Frequency: кол-во действий за последний месяц
        COUNT(DISTINCT CASE WHEN la.last_action_at >= CURRENT_DATE - INTERVAL '30 days' 
                           THEN la.action_id END) as actions_30d,
        -- Monetary: MRR пользователя
        COALESCE(SUM(CASE WHEN s.status = 'active' THEN s.monthly_amount ELSE 0 END), 0) as mrr
    FROM users u
    LEFT JOIN last_actions la ON u.id = la.user_id
    LEFT JOIN subscriptions s ON u.id = s.user_id
    GROUP BY u.id, u.email, u.company_name
)
SELECT 
    id,
    email,
    company_name,
    recency_days,
    actions_30d,
    mrr,
    CASE 
        WHEN recency_days > 30 AND actions_30d = 0 AND mrr > 100 THEN 'CRITICAL'
        WHEN recency_days > 14 AND actions_30d < 5 THEN 'AT_RISK'
        WHEN recency_days > 7 AND mrr > 500 THEN 'HIGH_VALUE_WATCH'
        ELSE 'HEALTHY'
    END as churn_risk
FROM user_metrics
WHERE CASE 
        WHEN recency_days > 30 AND actions_30d = 0 AND mrr > 100 THEN TRUE
        WHEN recency_days > 14 AND actions_30d < 5 THEN TRUE
        WHEN recency_days > 7 AND mrr > 500 THEN TRUE
        ELSE FALSE
    END
ORDER BY mrr DESC;

Результат: Выявили 47 пользователей в статусе CRITICAL. После целевого retention-программы удалось сохранить 34 из них (72%), что спасло $156K ARR.

Задача 3: Расчёт LTV и CAC для оптимизации маркетинга

Контекст: Маркетинг команда нужно было понять, какие каналы привлечения прибыльны на самом деле.

WITH customer_lifetime AS (
    SELECT 
        u.id as user_id,
        u.utm_source,
        u.utm_campaign,
        -- Общая сумма, потраченная клиентом
        COALESCE(SUM(o.amount), 0) as total_spent,
        -- Кол-во заказов
        COUNT(DISTINCT o.id) as order_count,
        -- Дни от регистрации до последнего заказа
        EXTRACT(DAY FROM MAX(o.created_at) - u.created_at) as customer_lifetime_days,
        -- Средний интервал между заказами
        EXTRACT(DAY FROM AVG(o.created_at - LAG(o.created_at) OVER (PARTITION BY u.id ORDER BY o.created_at))) as avg_order_interval_days
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'completed'
    WHERE u.created_at >= CURRENT_DATE - INTERVAL '24 months'
    GROUP BY u.id, u.utm_source, u.utm_campaign
),
marketing_spend AS (
    SELECT 
        utm_source,
        utm_campaign,
        SUM(spend) as total_spend,
        COUNT(DISTINCT user_id) as users_acquired
    FROM marketing_campaigns
    WHERE created_at >= CURRENT_DATE - INTERVAL '24 months'
    GROUP BY utm_source, utm_campaign
)
SELECT 
    cl.utm_source,
    cl.utm_campaign,
    COUNT(DISTINCT cl.user_id) as customer_count,
    ROUND(AVG(cl.total_spent), 2) as avg_ltv,
    ROUND(MAX(cl.total_spent), 2) as max_ltv,
    ROUND(AVG(cl.order_count), 2) as avg_orders_per_customer,
    ROUND(AVG(cl.customer_lifetime_days), 0) as avg_lifetime_days,
    ROUND(ms.total_spend / NULLIF(ms.users_acquired, 0), 2) as cac,
    ROUND(AVG(cl.total_spent) / NULLIF(ms.total_spend / NULLIF(ms.users_acquired, 0), 0), 2) as ltv_to_cac_ratio
FROM customer_lifetime cl
LEFT JOIN marketing_spend ms 
    ON cl.utm_source = ms.utm_source 
    AND cl.utm_campaign = ms.utm_campaign
GROUP BY cl.utm_source, cl.utm_campaign, ms.total_spend, ms.users_acquired
HAVING COUNT(DISTINCT cl.user_id) >= 50  -- фильтруем маленькие когорты
ORDER BY ltv_to_cac_ratio DESC;

Результат: Выявили, что PPC кампании из Google имели LTV:CAC = 8:1, а из Facebook — 2:1. Перераспределили бюджет в пользу Google, что увеличило прибыль на 34%.

Задача 4: Анализ воронки с расчётом drop-off

Контекст: Нужно было найти, где в процессе покупки пользователи уходят.

WITH funnel_steps AS (
    SELECT 
        DATE(event_time) as event_date,
        session_id,
        MAX(CASE WHEN event_type = 'view_product' THEN 1 ELSE 0 END) as step_1_view,
        MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) as step_2_cart,
        MAX(CASE WHEN event_type = 'enter_checkout' THEN 1 ELSE 0 END) as step_3_checkout,
        MAX(CASE WHEN event_type = 'payment_complete' THEN 1 ELSE 0 END) as step_4_payment,
        COUNT(DISTINCT CASE WHEN event_type IN ('view_product', 'add_to_cart') THEN user_id END) as engaged_users
    FROM events
    WHERE event_time >= CURRENT_DATE - INTERVAL '90 days'
    GROUP BY DATE(event_time), session_id
)
SELECT 
    event_date,
    -- Шаг 1: Просмотры
    COUNT(*) as step_1_visitors,
    -- Шаг 2: В корзину
    SUM(step_2_cart) as step_2_added_cart,
    ROUND(100.0 * SUM(step_2_cart) / NULLIF(COUNT(*), 0), 2) as step_1_to_2_pct,
    -- Шаг 3: Оформление
    SUM(step_3_checkout) as step_3_checkout,
    ROUND(100.0 * SUM(step_3_checkout) / NULLIF(SUM(step_2_cart), 0), 2) as step_2_to_3_pct,
    -- Шаг 4: Оплата
    SUM(step_4_payment) as step_4_completed,
    ROUND(100.0 * SUM(step_4_payment) / NULLIF(SUM(step_3_checkout), 0), 2) as step_3_to_4_pct,
    -- Итоговая конверсия
    ROUND(100.0 * SUM(step_4_payment) / NULLIF(COUNT(*), 0), 2) as overall_conversion_pct
FROM funnel_steps
GROUP BY event_date
ORDER BY event_date DESC;

Результат: Обнаружили, что 68% пользователей бросают корзину на шаге "Enter Checkout". Добавление пошагового прогресс-бара и сокращение формы увеличило конверсию на 23%.

Задача 5: Сегментация пользователей для персонализации

Контекст: Нужно было разбить пользователей на сегменты для целевых email-кампаний.

WITH user_segments AS (
    SELECT 
        u.id,
        u.email,
        COUNT(DISTINCT o.id) as total_orders,
        ROUND(AVG(o.amount), 2) as avg_order_value,
        MAX(o.created_at) as last_order_date,
        DATEDIFF(day, MAX(o.created_at), CURRENT_DATE) as days_since_last_order,
        SUM(o.amount) as lifetime_value,
        CASE 
            WHEN COUNT(DISTINCT o.id) >= 10 AND SUM(o.amount) > 1000 THEN 'VIP'
            WHEN COUNT(DISTINCT o.id) >= 5 AND SUM(o.amount) > 500 THEN 'LOYAL'
            WHEN DATEDIFF(day, MAX(o.created_at), CURRENT_DATE) > 180 THEN 'DORMANT'
            WHEN DATEDIFF(day, MAX(o.created_at), CURRENT_DATE) < 30 THEN 'ACTIVE'
            ELSE 'AT_RISK'
        END as segment
    FROM users u
    LEFT JOIN orders o ON u.id = o.user_id
    GROUP BY u.id, u.email
)
SELECT 
    segment,
    COUNT(*) as user_count,
    ROUND(AVG(avg_order_value), 2) as avg_aov,
    ROUND(AVG(lifetime_value), 2) as avg_ltv,
    COUNT(DISTINCT CASE WHEN days_since_last_order > 90 THEN id END) as at_churn_risk
FROM user_segments
GROUP BY segment
ORDER BY avg_ltv DESC;

Результат: Создали 5 сегментов и запустили специальные email-кампании. VIP-сегмент дал 40% увеличение repeat-покупок.

Ключевые техники SQL, которые использую

• Window Functions — LAG, LEAD, ROW_NUMBER, RANK, SUM OVER
• CTEs (WITH) — для читаемости и переиспользования подзапросов
• Aggregations — GROUP BY, HAVING, FILTER
• Date Functions — DATE_TRUNC, EXTRACT, DATEDIFF
• Conditional Logic — CASE WHEN для создания сегментов
• Joins — LEFT JOIN для сохранения всех данных

Каждый запрос решает конкретную бизнес-задачу и приводит к действию, а не просто выдаёт цифры.