SQL-паттерны аналитики
Повторяющиеся SQL-паттерны аналитика — сессии, gaps-and-islands, retention, когорты и воронки.
11 вопросов
JuniorКодОчень частоТрёхшаговая воронка — пользователи на каждом шаге и пошаговая конверсия
Трёхшаговая воронка — пользователи на каждом шаге и пошаговая конверсия
Считают уникальных пользователей на шаг условной агрегацией — COUNT(DISTINCT user_id) FILTER (WHERE step = 'view') и так же для cart и purchase. Каждая конверсия делит пользователей следующего шага на предыдущий: cart на view, purchase на cart — не на общий итог.
Типичные ошибки
- ✗Делить каждый шаг на всю базу, а не на предыдущий шаг
- ✗Брать строки COUNT(*) вместо COUNT(DISTINCT user_id)
- ✗Путать пошаговую конверсию со сквозной
Уточняющие вопросы
- →Как ещё вывести сквозную конверсию view→purchase?
- →Почему FILTER лучше трёх отдельных сгруппированных подзапросов?
MiddleКодОчень частоRetention-треугольник когорт — когорты по месяцу регистрации на месяцы с регистрации
Retention-треугольник когорт — когорты по месяцу регистрации на месяцы с регистрации
Относят регистрацию к месяцу когорты и считают размер COUNT(*). months_since — разница года и месяца между месяцем активности и когорты; делят уникальных активных на (когорту, months_since) на этот фиксированный размер когорты, а не на скользящую базу.
Типичные ошибки
- ✗Брать активных прошлого месяца как скользящий знаменатель
- ✗Приближать прошедшие месяцы как разницу дней делить на 30
- ✗Считать размер когорты по активным месяца 0, а не по всем регистрациям
Уточняющие вопросы
- →Почему фиксированный знаменатель по размеру когорты лучше скользящего?
- →Как заполнить месяцы, где у когорты было ноль активных?
JuniorКодЧастоRetention первого дня в SQL — доля регистраций, активных на следующий день
Retention первого дня в SQL — доля регистраций, активных на следующий день
LEFT JOIN activity по activity_date = signup_date + 1, затем делят число совпавших уникальных пользователей на все регистрации. LEFT JOIN держит невернувшихся в знаменателе, поэтому это доля вернувшихся от всей когорты регистраций, а не только от вернувшихся.
Типичные ошибки
- ✗Брать INNER JOIN, который убирает невернувшихся из знаменателя
- ✗Считать любую позднюю активность вместо ровно следующего дня
- ✗Делить строки активности, а не уникальных пользователей
Уточняющие вопросы
- →Как расширить это до retention дня 7 или дня 30?
- →Почему INNER JOIN незаметно меняет здесь знаменатель?
JuniorКодЧастоКлассифицировать каждого активного в этом месяце пользователя как new или returning одним запросом
Классифицировать каждого активного в этом месяце пользователя как new или returning одним запросом
GROUP BY user_id по всей активности. Пользователь new, когда MIN(activity_date) попадает в текущий месяц, иначе returning. Оставляют активных в этом месяце через HAVING MAX(activity_date) >= date_trunc('month', CURRENT_DATE).
Типичные ошибки
- ✗Решать new или returning по числу визитов за месяц
- ✗Брать MAX(activity_date) вместо MIN для проверки первого визита
- ✗Фильтровать на этот месяц до расчёта первой активности за всё время
Уточняющие вопросы
- →Почему проверку первого визита ведут по всей истории, а не только за месяц?
- →Как добавить третью метку
resurrectedдля вернувшихся после паузы?
MiddleДебаггингЧастоСобытия с доставкой at-least-once завышают дневную активность — дедуплицируйте идемпотентно
События с доставкой at-least-once завышают дневную активность — дедуплицируйте идемпотентно
COUNT(user_id) учитывает каждую доставленную строку, поэтому дубли завышают его. Сначала схлопывают до одной строки на event_id — DISTINCT ON (event_id) или ROW_NUMBER() = 1 — затем COUNT(DISTINCT user_id). Дедуп по уникальному id доставки идемпотентен.
Типичные ошибки
- ✗Дедуплицировать агрегированный вывод, а не сырые строки событий
- ✗Считать, что COUNT(*) или COUNT(DISTINCT user_id) сами убирают повторы доставки
- ✗Винить пустые ключи, а не законную повторную доставку at-least-once
Уточняющие вопросы
- →Почему дедуп по event_id идемпотентен, а COUNT(DISTINCT user_id) недостаточно?
- →Как выбрать, какую из дублей оставить, если их данные различаются?
MiddleКодЧастоВоронка с 24-часовым окном — покупка должна следовать за просмотром в течение 24ч
Воронка с 24-часовым окном — покупка должна следовать за просмотром в течение 24ч
Соединяют покупки с просмотрами по purchase_time > view_time AND purchase_time <= view_time + interval '24 hours'. При такой паре пользователь конвертирован, поэтому считают DISTINCT конвертированных на смотревших — раз на пользователя.
Типичные ошибки
- ✗Игнорировать окно и считать любого смотревшего-и-купившего
- ✗Брать рамку первый просмотр — последняя покупка, а не разрыв на пару
- ✗Брать разрыв по модулю, допуская покупку раньше просмотра
Уточняющие вопросы
- →Как гарантировать, что каждый конвертированный посчитан один раз?
- →Как отнести покупку к самому свежему подходящему просмотру?
MiddleКодИногдаСамая длинная серия подряд активных дней на пользователя
Самая длинная серия подряд активных дней на пользователя
Приём gaps-and-islands: на пользователя вычитают из даты ROW_NUMBER() с сортировкой по дате — подряд идущие дни делят одно смещение, постоянный ключ острова. Группируют по пользователю и ключу, COUNT(*) дней в серии, затем MAX длины на пользователя.
Типичные ошибки
- ✗Считать все строки день-за-днём вместо самой длинной одной серии
- ✗Брать интервал первый-последний, будто активность непрерывна
- ✗Считать все уникальные активные дни как длиннейшую серию
Уточняющие вопросы
- →Почему
activity_date - ROW_NUMBER()постоянен внутри серии? - →Как ещё вернуть дату начала и конца этой серии?
MiddleКодИногдаРазбить поток событий на сессии по 30-минутному разрыву неактивности
Разбить поток событий на сессии по 30-минутному разрыву неактивности
На пользователя берут LAG(event_time) с сортировкой по времени. Помечают новую сессию, когда разрыв до предыдущего события больше 30 минут или предыдущего события нет (LAG равен NULL). Сумма этих флагов старта на пользователя и есть число сессий.
Типичные ошибки
- ✗Делить весь активный интервал на 30 минут вместо анализа разрывов
- ✗Бить на окна по часам, а не по разрыву между событиями
- ✗Считать близкие события как сессии, а не считать старты сессий
Уточняющие вопросы
- →Как присвоить каждому отдельному событию id сессии?
- →Как первое событие пользователя не теряется как старт сессии?
SeniorКодИногдаПометить каждого пользователя в каждом месяце как new, retained, churned или resurrected
Пометить каждого пользователя в каждом месяце как new, retained, churned или resurrected
Cross-join пользователей со шкалой месяцев и left-join активности дают флаг active на пользователя-месяц. LAG(active) даёт прошлый месяц, нарастающий SUM(active) — прошлые активности. Четыре метки жизненного цикла выводят, сравнивая флаг этого месяца с прошлым.
Типичные ошибки
- ✗Метить по числу визитов текущего месяца без контекста прошлого месяца
- ✗Считать, что churn требует отдельного anti-join, ведь у него нет строки
- ✗Брать дневной разрыв событий вместо помесячного наличия
Уточняющие вопросы
- →Как шкала месяцев позволяет классифицировать churned-месяц без строки?
- →Как из этих меток получить помесячную сводку growth accounting?
SeniorКодРедкоВоронка с повторным входом — отнести каждого пользователя раз к его дальнему шагу в окне
Воронка с повторным входом — отнести каждого пользователя раз к его дальнему шагу в окне
Ранжируют шаги (view=1, cart=2, purchase=3) и берут MAX(step_rank) на пользователя в окне — это схлопывает повторный вход в один дальний шаг. Затем считают через FILTER (WHERE max_step >= k) для каждого k — кумулятивная воронка, где каждый посчитан раз.
Типичные ошибки
- ✗Считать, что distinct-на-шаг уже верно учитывает повторный вход
- ✗Брать хронологически последнее событие как дальний шаг
- ✗Суммировать ранги шагов, так что повторы обгоняют один глубокий проход
Уточняющие вопросы
- →Почему обычная distinct-на-шаг воронка может дважды счесть вернувшегося?
- →Как ограничить дальний шаг одной сессией, а не всем окном?
SeniorКодРедкоN-дневный vs rolling retention — напишите оба и покажите, где числа расходятся
N-дневный vs rolling retention — напишите оба и покажите, где числа расходятся
Bracket сопоставляет activity_date = signup_date + 7; rolling — activity_date >= signup_date + 7. Оба делят уникальных совпавших на когорту. Rolling всегда не меньше bracket: активный в день 10, но не в день 7, проходит rolling, но не точный день.
Типичные ошибки
- ✗Путать, какое определение берёт точный день, а какое день-и-позже
- ✗Считать, что оба определения дают одно число retention
- ✗Читать rolling как активность до дня 7, а не с дня 7 и далее
Уточняющие вопросы
- →Почему rolling retention всегда не меньше bracket retention?
- →Какое определение лучше для продукта, которым пользуются пару раз в месяц?
