Профессия:
Задачи с собеседований на продуктового аналитика: SQL на воронку конверсии, retention и когорты, LTV и ARPU, оконные функции и сессионизацию событий, продуктовые кейсы про падение конверсии и рост DAU, дизайн A/B-теста и размер выборки. К каждой задаче есть разбор с запросом или расчётом.
Анализ push-уведомлений: метрики и оптимизация
1. Метрики push-уведомлений
Delivery метрики:
Engagement метрики:
Качество и безопасность:
2. Измерение влияния push на retention
Анализ онбординга: Оптимизация воронки активации
Проблема 70% отсева на регистрации критична — это означает потерю 7 из 10 потенциальных пользователей на самом раннем этапе. Систематизирую подход анализа.
1. Данные для анализа
Запрошу иерархический набор данных:
На уровне воронки (aggregate):
На уровне шагов:
На уровне когорт:
Решение
Понимание задачи
Воронка конверсии показывает, на каком шаге пользователи отсеиваются:
SQL-запрос
Расчет среднего чека по дням недели
Решение на PostgreSQL:
SELECT
TO_CHAR(order_date, 'Day') AS day_of_week,
COUNT(*) AS orders_count,
ROUND(AVG(total_amount)::numeric, 2) AS avg_order_amount,
ROUND(SUM(total_amount)::numeric, 2) AS total_revenue
FROM orders
GROUP BY TO_CHAR(order_date, 'Day')
ORDER BY
CASE WHEN TO_CHAR(order_date, 'Day') = 'Monday' THEN 1
WHEN TO_CHAR(order_date, 'Day') = 'Tuesday' THEN 2
WHEN TO_CHAR(order_date, 'Day') = 'Wednesday' THEN 3
WHEN TO_CHAR(order_date, 'Day') = 'Thursday' THEN 4
WHEN TO_CHAR(order_date, 'Day') = 'Friday' THEN 5
WHEN TO_CHAR(order_date, 'Day') = 'Saturday' THEN 6
WHEN TO_CHAR(order_date, 'Day') = 'Sunday' THEN 7
END;
Лучше с EXTRACT:
Решение
Это задача на сравнительный анализ по типам устройств — критична для оптимизации мобильного опыта.
SQL запрос для сравнения по устройствам:
SELECT
s.device_type,
COUNT(DISTINCT s.session_id) as sessions_count,
COUNT(DISTINCT CASE WHEN c.session_id IS NOT NULL THEN s.session_id END) as conversions_count,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN c.session_id IS NOT NULL THEN s.session_id END) / COUNT(DISTINCT s.session_id), 2) as conversion_rate,
ROUND(AVG(c.revenue), 2) as avg_revenue,
ROUND(SUM(c.revenue) / COUNT(DISTINCT s.session_id), 2) as revenue_per_session
FROM sessions s
LEFT JOIN conversions c ON s.session_id = c.session_id
GROUP BY s.device_type
ORDER BY conversion_rate DESC;
Вариант с дополнительной аналитикой:
SQL: Найти отток клиентов (Churn)
Определение Churn
Churn Rate — доля пользователей, которые не были активны в течение 30 дней от последней активности.
Решение 1: Базовый churn (пользователи неактивные 30+ дней)
WITH last_activity AS (
SELECT user_id, MAX(activity_date) as last_active_date
FROM user_activity
GROUP BY user_id
)
SELECT
DATE_TRUNC(NOW(), MONTH) as month,
COUNT(*) as active_users,
SUM(CASE WHEN DATE_DIFF(NOW(), last_active_date) >= 30 THEN 1 ELSE 0 END) as churned_users,
ROUND(100.0 * SUM(CASE WHEN DATE_DIFF(NOW(), last_active_date) >= 30 THEN 1 ELSE 0 END) / COUNT(*), 2) as churn_rate
FROM last_activity;
Решение 2: Месячный когортный churn (более точный)
SQL: Определить самый популярный день для покупок
Анализ по дням недели
SELECT
DAYNAME(order_date) as day_of_week,
DAYOFWEEK(order_date) as day_num,
COUNT(*) as orders_count,
ROUND(SUM(amount), 2) as total_revenue,
ROUND(AVG(amount), 2) as avg_order_value,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) as pct_of_total
FROM orders
GROUP BY DAYOFWEEK(order_date), DAYNAME(order_date)
ORDER BY day_num;
Анализ по часам суток
SELECT
HOUR(order_date) as hour_of_day,
COUNT(*) as orders_count,
ROUND(SUM(amount), 2) as total_revenue,
ROUND(AVG(amount), 2) as avg_order_value,
ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 2) as pct_of_total
FROM orders
GROUP BY HOUR(order_date)
ORDER BY hour_of_day;
Комбинированный анализ (день × час)
Решение: Пользователи, купившие товар с id=5
Базовый SQL-запрос
SELECT COUNT(DISTINCT user_id) as unique_users
FROM purchases
WHERE product_id = 5
AND purchase_date >= '2021-10-10';
Объяснение
Ключевые элементы:
COUNT(DISTINCT user_id) — считаем уникальных пользователей
WHERE product_id = 5 — фильтруем только товар с id=5
WHERE purchase_date >= '2021-10-10' — дата 10 октября 2021 включительно
Пример
Решение
Это задача на анализ параметров заказов (basket size) — критична для e-commerce аналитики и оптимизации среднего чека.
Логика решения:
SQL запрос для общих метрик:
SQL: Посчитать DAU и WAU
Объяснение метрик
DAU (Daily Active Users) — количество уникальных пользователей, выполнивших хотя бы одно действие в течение дня. Это базовая метрика для оценки ежедневной активности сервиса.
WAU (Weekly Active Users) — количество уникальных пользователей, выполнивших хотя бы одно действие на протяжении последних 7 дней (включая текущий день). Эта метрика показывает масштаб аудитории с недельным горизонтом и менее подвержена ежедневным колебаниям.
Решение с оконными функциями
SELECT
activity_date as date,
COUNT(DISTINCT user_id) as dau,
COUNT(DISTINCT user_id) OVER (
ORDER BY activity_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) as wau
FROM user_activity
GROUP BY activity_date
ORDER BY activity_date;
Решение
Это задача на анализ сочетаний товаров (market basket analysis) — критична для e-commerce для рекомендаций и кросс-селлинга.
Логика решения:
SQL запрос:
SELECT
oi1.product_id as product_id_1,
oi2.product_id as product_id_2,
COUNT(DISTINCT oi1.order_id) as times_bought_together
FROM order_items oi1
INNER JOIN order_items oi2
ON oi1.order_id = oi2.order_id
AND oi1.product_id < oi2.product_id
GROUP BY oi1.product_id, oi2.product_id
ORDER BY times_bought_together DESC
LIMIT 10;
Альтернативный подход (более явный):
Решение: Скользящее среднее за 7 дней
SQL-запрос (базовый вариант)
SELECT
date,
revenue,
ROUND(
AVG(revenue) OVER (
ORDER BY date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
), 2
) as moving_avg_7d
FROM daily_metrics
ORDER BY date;
Объяснение
Оконная функция AVG() OVER():
AVG(revenue) OVER (
ORDER BY date -- Сортируем по дате
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW -- Берём текущую и 6 предыдущих строк
)
Как это работает:
Почему "6 PRECEDING"?
Пример
Решение: Клиенты с более чем 3 покупками за месяц
SQL-запрос
SELECT
customer_id,
COUNT(order_id) as orders_count,
ROUND(SUM(amount), 2) as total_amount
FROM orders
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '1 month'
AND order_date < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
GROUP BY customer_id
HAVING COUNT(order_id) > 3
ORDER BY orders_count DESC;
Альтернативный вариант (более универсальный)
WITH last_month AS (
SELECT
customer_id,
COUNT(order_id) as orders_count,
SUM(amount) as total_amount
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id
HAVING COUNT(order_id) > 3
)
SELECT
customer_id,
orders_count,
ROUND(total_amount, 2) as total_amount
FROM last_month
ORDER BY orders_count DESC;
Объяснение
Ключевые элементы:
Анализ email рассылки: вероятность и конверсия
1. Вероятность покупки для случайного получателя
Покупка произойдёт, если выполнены ВСЕ условия по цепочке:
Цепочка действий:
Расчёт (правило произведения вероятностей):
P(покупка) = P(открыт) × P(клик|открыт) × P(покупка|клик)
P(покупка) = 0.25 × 0.10 × 0.05 = 0.000125 = 0.0125%
Вероятность того, что случайный получатель совершит покупку = 0.0125% или 1 из 8000
2. Ожидаемое количество покупок
Расчёт по цепочке:
Этап 1: Открытия писем
10,000 × 0.25 = 2,500 пользователей открыли письмо
Этап 2: Клики
2,500 × 0.10 = 250 пользователей кликнули по ссылке
Этап 3: Покупки
250 × 0.05 = 12.5 ≈ 12-13 покупок ожидается
Или прямая формула:
Покупки = 10,000 × 0.25 × 0.10 × 0.05 = 12.5 ≈ 12 покупок
Ожидаемое количество покупок = 12-13 покупок
Анализ повторных покупок
1. Один заказ
Количество клиентов, сделавших только одну покупку:
SELECT COUNT(customer_id) as one_time_buyers
FROM (SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id HAVING COUNT(*) = 1) t;
2. Повторные покупки
Процент клиентов с повторными покупками:
WITH customer_counts AS (
SELECT customer_id, COUNT(*) as cnt FROM orders GROUP BY customer_id
)
SELECT
ROUND(100.0 * SUM(CASE WHEN cnt > 1 THEN 1 ELSE 0 END) / COUNT(*), 2) as repeat_rate
FROM customer_counts;
3. Среднее время между заказами
WITH ranked AS (
SELECT customer_id, order_date, LAG(order_date) OVER (PARTITION BY customer_id ORDER BY order_date) as prev_date
FROM orders
),
intervals AS (
SELECT EXTRACT(DAY FROM (order_date - prev_date))::int as days_between
FROM ranked WHERE prev_date IS NOT NULL
)
SELECT ROUND(AVG(days_between), 1) as avg_days_between_orders
FROM intervals;
Решение
Дано:
Формула размера выборки:
n = (z_alpha + z_beta)² × [p0(1-p0) + p1(1-p1)] / (p1 - p0)²
Расчет:
Итоговые результаты:
1. Минимальный размер выборки:
2. Параметры статистической значимости:
3. Длительность теста:
Дни = 62,320 пользователей / 10,000 пользователей в день = 6.2 дня
Рекомендуемая длительность: 7 дней
Метрики мессенджера (Telegram-like)
1. Ключевые метрики роста и активности
Пользователи:
Общение:
Рост:
2. Вовлеченность (Engagement)
Интенсивность:
SQL: Найти пользователей с непрерывной активностью N дней
Объяснение подхода "Gaps and Islands"
Основная идея — сгруппировать непрерывные даты в "острова" (islands), отделённые "разрывами" (gap). Для этого используем хитрость: вычитаем из даты номер строки ROW_NUMBER(), получая "группу дней".
Логика: если дни идут подряд, разница между датой и ROW_NUMBER() будет одинаковой для всей последовательности.
Решение
Решение: Расчет Retention Day 1, Day 7, Day 30
Понимание метрики
Retention Day N — это процент пользователей, которые имели хотя бы одну сессию на N-й день после регистрации.
Пример:
SQL-запрос (базовый вариант)
Решение
Это задача на расчет rolling retention — метрика, которая показывает, какой % пользователей был активен в день N или позже.
Разница между обычной и rolling retention:
SQL запрос для rolling retention:
Решение
Free-to-play игры — это уникальная категория, где нужно балансировать между монетизацией (заработок) и удержанием (размер аудитории). Приведу комплексный анализ.
1. Ключевые метрики для отслеживания
Метрики активности:
Решение
Это задача на расчет медианы (50-го процентиля) выручки по месяцам. Медиана часто информативнее среднего, так как не подвергается влиянию выбросов.
Логика решения:
SQL запрос с PERCENTILE_CONT (PostgreSQL):
SELECT
DATE_TRUNC('month', date)::date as month,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue) as median_revenue
FROM daily_sales
GROUP BY DATE_TRUNC('month', date)
ORDER BY month;
Альтернативный подход с оконными функциями (универсальный):
Решение: A/B тест для цвета кнопки оформления заказа
1. Проектирование теста
Нулевая гипотеза (H0): Цвет кнопки (зелёный vs оранжевый) не влияет на конверсию до покупки
Альтернативная гипотеза (H1): Оранжевая кнопка увеличивает конверсию до покупки на ≥5%
Обоснование 5% MDE:
ТЕСТ: Button Color Optimization
GROUPS:
├─ Control (A): Зелёная кнопка #4CAF50
├─ Treatment (B): Оранжевая кнопка #FF9800
└─ Размер групп: 50/50 (рандомизация)
LEVEL OF RANDOMIZATION: User ID
├─ Не session (иначе один пользователь может быть в обеих группах)
└─ Sticky assignment: один пользователь в одной группе всегда
TARGET AUDIENCE: Все пользователи, прошедшие до checkout
Решение: Анализ аномалии в метриках
Фреймворк анализа аномалий
При обнаружении аномалии в метриках нужно следовать структурированному подходу: сначала исключить технические и процессные причины, потом проверить внешние факторы, и только потом делать выводы о продукте.
1. Гипотезы для объяснения аномалии
Категория 1: Технические и операционные проблемы
Гипотеза 1.1: Технический сбой (downtime)
Гипотеза 1.2: Проблемы с отслеживанием данных
Гипотеза 1.3: Изменение в расчётах метрик
Категория 2: Маркетинг и бизнес-события
Решение
###概念理解
Когортная таблица retention показывает, какой процент пользователей, зарегистрировавшихся в одном месяце (когорта), остались активны в последующие месяцы.
SQL-запрос
Решение: SQL-анализ Customer Journey
Бизнес-задача
Анализ путей, по которым пользователи приходят к конверсии. Это критично для оптимизации маркетинга: нужно понять, какие каналы и последовательности наиболее эффективны.
1. Самые частые последовательности касаний
Year-over-Year сравнение выручки
Решение на PostgreSQL:
SELECT
DATE_TRUNC('month', month)::date AS month,
revenue,
LAG(revenue) OVER (
ORDER BY DATE_TRUNC('month', month)
RANGE BETWEEN INTERVAL '1 year' PRECEDING AND INTERVAL '1 year' PRECEDING
) AS prev_year_revenue,
ROUND((
(revenue - LAG(revenue) OVER (
ORDER BY DATE_TRUNC('month', month)
RANGE BETWEEN INTERVAL '1 year' PRECEDING AND INTERVAL '1 year' PRECEDING
)) / LAG(revenue) OVER (
ORDER BY DATE_TRUNC('month', month)
RANGE BETWEEN INTERVAL '1 year' PRECEDING AND INTERVAL '1 year' PRECEDING
) * 100
), 2) AS yoy_change_pct
FROM monthly_revenue
ORDER BY month;
Более простой вариант с подзапросом:
Продуктовый кейс: Рост DAU e-commerce приложения на 20%
1. Декомпозиция цели
DAU = Новые пользователи (D0→D1) + Вернувшиеся (Retention)
DAU_target = DAU_current × 1.20
Варианты декомпозиции:
2. Рычаги влияния на DAU
Входящий поток (Acquisition):
Удерживающий поток (Retention):
Реактивация:
3. Гипотезы для роста
H1: Улучшение онбординга → D1 retention +8%
Решение: Telegram как Product Analyst
1. Бизнес-модель
Telegram использует комбинированную модель с несколькими потоками дохода:
Основные источники выручки:
Косвенные выгоды:
2. Ключевые метрики по бизнес-целям
Решение: SQL-запрос для анализа падения продаж
Постановка задачи
Необходимо найти товары, у которых еженедельные продажи упали более чем на 30% между двумя последовательными неделями. Это критический метрика для выявления проблемных товаров, требующих немедленного внимания.
Подход
Используем оконные функции (Window Functions) для сравнения данных разных недель. Этот подход позволяет вычислить суммы продаж за каждую неделю и сравнить их без сложных JOIN-ов.
SQL-запрос
Ранжирование и сравнение заказов SQL
SELECT customer_id, order_id, order_date, amount, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date) as order_number, LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) as prev_order_amount, ROUND(amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date), 2) as amount_change, ROUND(100.0 * (amount - LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date)) / LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date), 1) as percent_change FROM orders ORDER BY customer_id, order_date
Объяснение:
Решение
Это финальный комплексный кейс на улучшение D7 retention с 15% до бенчмарка 25%.
1. Данные для анализа
Минимальный набор:
2. Сегментация пользователей
По источнику трафика:
Organic: D7 = 20% ← Хорошо
Paid (Google): D7 = 12% ← ПРОБЛЕМА
Paid (Facebook): D7 = 14% ← ПРОБЛЕМА
Referral: D7 = 28% ← Отлично
По полноте онбординга:
Completed: D7 = 25% ← Хорошо
Partial: D7 = 10% ← КРИТИЧНО
No onboarding: D7 = 5% ← ОЧЕНЬ ПЛОХО
По активности день 1-3:
3+ сессии день 1: D7 = 40% ← Вовлечённые
1-2 сессии день 1: D7 = 18% ← Средние
0 сессий день 1: D7 = 2% ← Совсем ушли
Метрики для подписочного сервиса (Netflix/Spotify)
Подписочная модель отличается от других моделей монетизации: доход предсказуем, но нужно постоянно удерживать пользователей. Расскажу про систему метрик для такого сервиса.
1. Ключевые метрики для мониторинга
Subscribers (Active Subscribers)
New Subscriptions (New Sign-ups)
Monthly Recurring Revenue (MRR)
Решение
Задача на расчет фанел-метрик (funnel analytics) — одна из самых важных в Product Analytics. Воронка показывает, на каком этапе процесса пользователи выпадают.
Логика решения:
SQL запрос:
Решение: Падение конверсии в e-commerce
Шаг 1: Выдвижение гипотез
Падение конверсии на 5 процентных пункта (с 15% до 10%) — существенное снижение. Причины могут быть технические, продуктовые, платёжные или внешние.
Решение
Это задача на расчет time-to-conversion — критична для понимания скорости монетизации и эффективности онбординга.
Логика решения:
SQL запрос для общей статистики:
Метрики приложения заказа такси
1. Ключевые метрики роста
Пассажиры:
Водители:
2. Качество сервиса
3. Метрики по сторонам
Решение
Описание подхода
Для решения этой задачи используем оконные функции (window functions) в SQL. Ключевая идея: присвоить каждой странице пользователя номер в хронологическом порядке и отфильтровать только те строки, где номер равен 3.
SQL-запрос
WITH ranked_pages AS (
SELECT
user_id,
page,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY time ASC) as page_number
FROM log
)
SELECT user_id, page
FROM ranked_pages
WHERE page_number = 3
Почему этот подход работает
ROW_NUMBER() — оконная функция, которая нумерует строки в порядке, определённом в ORDER BY:
PARTITION BY user_id — нумерация независима для каждого пользователяORDER BY time ASC — нумерация в хронологическом порядке (от старых событий к новым)WHERE page_number = 3 — берём только третьи строкиАльтернативный синтаксис (без CTE)
Если СУБД позволяет использовать WHERE на результаты window-функций:
Аналитика запуска в новой стране: метрики и бенчмарки
1. Метрики для отслеживания запуска
Фаза 1: Тестирование и софт-запуск (1-2 недели)
Фаза 2: Полный запуск (неделя 3-12)
Фаза 3: Оптимизация (месяц 3+)
2. Определение успешности запуска
Критерии успеха (KPI по фазам):
Решение
Это задача на применение оконных функций для сравнения значений со статистикой по группам.
SQL запрос:
SELECT
session_id,
user_id,
duration_seconds,
ROUND(AVG(duration_seconds) OVER (PARTITION BY user_id), 2) as user_avg_duration,
duration_seconds > AVG(duration_seconds) OVER (PARTITION BY user_id) as is_above_average
FROM sessions
ORDER BY user_id, session_date;
Как это работает:
AVG(duration_seconds) OVER (PARTITION BY user_id) вычисляет среднюю длительность сессии для каждого пользователя. Это оконная функция, которая сохраняет детальность данных — не агрегирует все в одну строку, а применяет функцию в контексте каждой строки.
Сравнение duration_seconds > AVG(...) OVER (...) возвращает TRUE если сессия длиннее среднего, FALSE иначе.
Альтернативный вариант с CASE:
Решение: Оценка успешности новой фичи
1. Как измерить успешность: Иерархия метрик
Выбор зависит от типа фичи, но принцип всегда одинаков: метрик должен быть связан с бизнес-целью.
Примеры:
Почему именно первичный метрик? Он напрямую влияет на стратегическую цель продукта. Если первичный метрик растёт, фича работает. Остальные метрики — для диагностики.
Решение
Это задача на выявление регулярных плательщиков (recurring revenue users) — критична для MRR прогнозов в подписочных моделях.
Логика решения:
SQL запрос для выявления регулярных плательщиков:
Решение: Первая и последняя покупка клиента
SQL-запрос (базовый вариант)
SELECT
customer_id,
COUNT(*) as total_purchases,
MIN(purchase_date) as first_purchase_date,
MAX(purchase_date) as last_purchase_date
FROM purchases
GROUP BY customer_id
ORDER BY customer_id;
Это найдёт даты, но без информации о продуктах.
Вариант со страницей (с использованием оконных функций)
Сессионизация событий SQL
WITH events_ranked AS ( SELECT user_id, event_id, event_time, LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) as prev_time, CASE WHEN prev_time IS NULL THEN 1 WHEN EXTRACT(EPOCH FROM (event_time - prev_time)) / 60 > 30 THEN 1 ELSE 0 END as session_start FROM user_events ),
sessions AS ( SELECT user_id, event_id, event_time, CONCAT(user_id, '_', SUM(session_start) OVER (PARTITION BY user_id ORDER BY event_time)) as session_id FROM events_ranked )
SELECT user_id, session_id, MIN(event_time) as session_start, MAX(event_time) as session_end, COUNT(*) as events_in_session FROM sessions GROUP BY user_id, session_id
Объяснение:
Найти максимальный чек в каждом месяце
Используем MAX() window function для нахождения максимума в каждом месяце:
WITH max_monthly AS (
SELECT
DATE_TRUNC('month', order_date) as month,
user_id,
total_amount,
MAX(total_amount) OVER (PARTITION BY DATE_TRUNC('month', order_date)) as max_amt
FROM orders
)
SELECT
month::date,
user_id,
total_amount as max_order_amount
FROM max_monthly
WHERE total_amount = max_amt
ORDER BY month, user_id;
Ключевые части:
Альтернатива с RANK():
Метрики трехстороннего маркетплейса доставки
1. Метрики ресторанов
2. Метрики курьеров
3. Метрики клиентов
4. Здоровье экосистемы
5. Guardrail метрики
SQL: Расчет ARPU по источникам трафика
Базовый расчет ARPU
SELECT
u.traffic_source,
COUNT(DISTINCT u.user_id) as users_count,
ROUND(SUM(p.amount), 2) as total_revenue,
ROUND(SUM(p.amount) / COUNT(DISTINCT u.user_id), 2) as arpu
FROM users u
LEFT JOIN payments p ON u.user_id = p.user_id
GROUP BY u.traffic_source
ORDER BY arpu DESC;
С учетом конверсии платящих пользователей
SELECT
u.traffic_source,
COUNT(DISTINCT u.user_id) as total_users,
COUNT(DISTINCT p.user_id) as paying_users,
ROUND(100.0 * COUNT(DISTINCT p.user_id) / COUNT(DISTINCT u.user_id), 2) as conversion_pct,
ROUND(SUM(COALESCE(p.amount, 0)), 2) as total_revenue,
ROUND(SUM(COALESCE(p.amount, 0)) / COUNT(DISTINCT u.user_id), 2) as arpu,
ROUND(SUM(COALESCE(p.amount, 0)) / COUNT(DISTINCT p.user_id), 2) as arppu
FROM users u
LEFT JOIN payments p ON u.user_id = p.user_id
GROUP BY u.traffic_source
ORDER BY arpu DESC;
Решение
Это задача на разбиение клиентов по величине выручки с помощью оконных функций. Типичная сегментация для анализа VIP-клиентов.
Логика решения:
SQL запрос с NTILE:
WITH customer_totals AS (
SELECT
customer_id,
SUM(amount) as total_amount
FROM orders
GROUP BY customer_id
)
SELECT
customer_id,
total_amount,
NTILE(4) OVER (ORDER BY total_amount) as percentile
FROM customer_totals
ORDER BY total_amount DESC;
Альтернативный подход с PERCENT_RANK:
Решение: Расчет LTV по когортам
Понимание метрики
LTV (Lifetime Value) — это общая выручка, которую принёс один пользователь за всё время.
LTV по когортам — средняя выручка на пользователя по месяцам его регистрации.
Пример:
SQL-запрос (базовый вариант)
SELECT
DATE_TRUNC('month', u.registration_date)::date as registration_month,
COUNT(DISTINCT u.user_id) as users_count,
ROUND(SUM(COALESCE(p.amount, 0)), 2) as total_revenue,
ROUND(SUM(COALESCE(p.amount, 0)) / COUNT(DISTINCT u.user_id), 2) as ltv
FROM users u
LEFT JOIN payments p ON u.user_id = p.user_id
GROUP BY DATE_TRUNC('month', u.registration_date)
ORDER BY registration_month;
Объяснение
Ключевые элементы:
Решение: Cumulative Sum (нарастающий итог)
Объяснение задачи
Нужно для каждого пользователя посчитать running total — сумму всех транзакций с начала периода до текущей даты. Это классическая задача аналитиков для анализа накопления денег, расходов и т.д.
Синтаксис: Window Function
SQL предоставляет window function SUM() OVER() — идеальный инструмент для нарастающих итогов:
SELECT
user_id,
transaction_date,
amount,
SUM(amount) OVER (
PARTITION BY user_id
ORDER BY transaction_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS cumulative_sum
FROM transactions
ORDER BY user_id, transaction_date;
Разбор синтаксиса
PARTITION BY user_id — разделяет данные по пользователям. Каждый пользователь считается отдельно.
ORDER BY transaction_date — упорядочивает строки в партиции. Без этого cumulative sum не имеет смысла.