SQL для аналитика. 50 задач с решениями на PostgreSQL
Оконные функции – выполняют вычисления по набору строк, связанных с текущей строкой (см. главу 12). Ключевое слово OVER.
DATE_TRUNC () – обрезает дату до указанной точности (см. главу 11).
Совет
Retention считают не только по дням, но и по неделям, месяцам. Формула одна и та же, меняется только период группировки.
SQL-запросы с объяснением
Задача 1. Подготовка данных: уникальные действия пользователей по дням.
Сначала найдём все дни, когда пользователь был активен.
text
SELECT DISTINCTuser_id,DATE (dt_tm) AS activity_dateFROM users_logORDER BY user_id, activity_date;Как это работает– DATE (dt_tm) – отбрасываем время, оставляем только дату.– DISTINCT – убираем дубли (несколько действий в один день).Пример вывода (фрагмент):
text
user_id activity_date1 2024-01-151 2024-02-012 2024-02-102 2024-02-153 2024-01-20Задача 2. Добавляем дату регистрации.
Для расчёта retention нужно знать, когда пользователь зарегистрировался.
text
SELECTu.user_id,u.registered_at::date AS reg_date,DATE (l. dt_tm) AS activity_dateFROM users uJOIN users_log l ON u.user_id = l.user_idGROUP BY u.user_id, reg_date, activity_dateORDER BY u.user_id, activity_date;Как это работает
– Присоединяем логи к пользователям.
– GROUP BY убирает дубли (то же, что DISTINCT, но явно).
– registered_at::date – преобразуем в дату.
Задача 3. Считаем дни после регистрации.
Для каждого действия посчитаем, на какой день после регистрации оно произошло.
text
WITH user_activity AS (SELECTu.user_id,u.registered_at::date AS reg_date,DATE (l. dt_tm) AS activity_dateFROM users uJOIN users_log l ON u.user_id = l.user_idGROUP BY u.user_id, reg_date, activity_date)SELECTuser_id,reg_date,activity_date,(activity_date – reg_date) AS day_numberFROM user_activityORDER BY user_id, day_number;Как это работает
– activity_date – reg_date – разница в днях (0 – день регистрации, 1 – следующий день и т.д.).
– CTE user_activity – подготовленные данные.
Задача 4. Считаем retention 7 дней.
Для каждого дня регистрации посчитаем, сколько пользователей вернулось в каждый из следующих дней.
text
WITH user_activity AS (SELECTu.user_id,u.registered_at::date AS reg_date,DATE (l. dt_tm) AS activity_dateFROM users uJOIN users_log l ON u.user_id = l.user_idGROUP BY u.user_id, reg_date, activity_date),day_numbers AS (SELECTreg_date,(activity_date – reg_date) AS day_number,COUNT (DISTINCT user_id) AS usersFROM user_activityWHERE activity_date> = reg_dateAND activity_date <= reg_date +7GROUP BY reg_date, day_number)SELECTreg_date,day_number,users,FIRST_VALUE (users) OVER (PARTITION BY reg_date ORDER BY day_number) AS day0_users,ROUND (100.0 * users / FIRST_VALUE (users) OVER (PARTITION BY reg_date ORDER BY day_number), 2) AS retention_rateFROM day_numbersORDER BY reg_date, day_number;Как это работает
– user_activity – подготовленные данные (день регистрации и дни активностей).
– day_numbers – считаем количество уникальных пользователей для каждой пары (дата регистрации, день после регистрации).
– FIRST_VALUE (users) OVER (PARTITION BY reg_date ORDER BY day_number) – берём количество пользователей в день 0 (день регистрации).
– retention_rate – процент от дня 0.
Пример вывода (фрагмент):
text
reg_date day_number users day0_users retention_rate2024-01-15 0 1 1 100.002024-01-18 0 1 1 100.002024-01-18 2 1 1 100.002024-01-20 0 1 1 100.002024-01-20 5 1 1 100.00Типичная ошибка
Учитывать пользователей, у которых нет активности в конкретный день, как вернувшихся. Retention считается только по активным пользователям.
Вопросы и ответы
В: Почему retention считается в процентах от дня 0?
О: Чтобы сравнивать когорты с разным количеством пользователей. День 0 всегда 100%.
В: Что делать, если пользователь совершил несколько действий в один день?
О: Используем DISTINCT или GROUP BY – считаем только один раз.
В: Как считать retention не по покупкам, а по любым действиям?
О: Используйте users_log вместо orders. В нашей схеме так и сделано.
В: Какой retention считается хорошим?
О: Зависит от продукта. Для мобильных приложений хороший Day 1 retention – 40—60%, Day 7 – 20—30%.
Что в итоге
Мы научились:
– Подготавливать данные для расчёта когорт.
– Считать количество активных пользователей по дням после регистрации.
– Вычислять retention в процентах с помощью оконных функций.
Retention – ключевая метрика для оценки удержания пользователей.
* Задание со звёздочкой
Посчитайте retention за 7 дней, но используйте только покупки (users_log с action = ’buy’). Измените фильтр в user_activity.
Подсказка: добавьте AND l.action = ’buy’ в условие JOIN или WHERE.
Часть 3. Событийная аналитика. (7 задач)
Глава 14. COUNT (DISTINCT), CASE, CTE. Воронка событий
Описание задачи
Представьте, действия пользователя в интернет-магазине. Пользователь сначала заходит на сайт, смотрит товары, добавляет в корзину, покупает. Но на каждом шаге кто-то отсеивается, кто-то смотрит и не покупает, кто-то не доходит даже до просмотра товаров.
Воронка (funnel) – это последовательность шагов, которая показывает, сколько пользователей доходит до каждого этапа.
Почему воронка? Пришли много пользователей, посмотрели товары уже меньше, купило еще меньше, визуально можно представить, что это воронка.
Такая воронка – это один из самых важных отчётов в аналитике событий.
Мы построим воронку: enter → show → add_to_cart → buy.
Таблицы: users_log (логи действий пользователей).
Аналогия из жизни
Представьте торговый центр. На вход зашли 1000 человек. До витрины с телефонами дошли 600. В магазин зашли 300. Купили 100. Это и есть воронка. Наша задача – посчитать такие же цифры по логам.
Основные SQL-конструкции
COUNT (DISTINCT …) – считает уникальные значения. В воронке это важно, потому что один пользователь может совершить действие несколько раз.
CASE – условный оператор, работает как if-then-else (если правда, то одно, иначе другое). Позволяет вернуть одно значение, если условие истинно, и другое – если ложно.
text
CASEWHEN условие THEN значение1ELSE значение2ENDВ нашей задаче: если действие равно ’enter’, то 1, иначе 0.
CTE (WITH) – временная таблица (см. главу 6). Разбивает сложный запрос на части.
Почему мы не можем просто использовать GROUP BY?
GROUP BY сгруппирует всех пользователей, но не позволит легко посчитать, сколько пользователей выполнили несколько действий подряд. CTE с CASE даёт гибкость.
SQL-запросы с объяснением
Шаг 1. Что нам нужно посчитать?
Нам нужно для каждого пользователя определить по таблице логов:
– Был ли у него enter? Вошёл ли он на сайт?
– Был ли у него show? Посмотрел ли он товары?
– Был ли у него add_to_cart? Добавил ли товары в корзину?
– Был ли у него buy? Совершил покупку?
Если был – ставим 1, если нет – 0.
Аналогия: Как контрольный список. Был пользователь на этапе? Да/нет.
Шаг 2. Подготовим данные: уникальные действия пользователей.
Сначала найдём все уникальные действия каждого пользователя.
text
WITH user_actions AS (SELECT DISTINCTuser_id,actionFROM users_log)SELECT * FROM user_actions ORDER BY user_id, action;Как это работает
– DISTINCT убирает дубли (если пользователь сделал enter 5 раз, оставляем один).
– Теперь у нас есть список: пользователь X сделал действия A, B, C.
Пример вывода (реальные данные, фрагмент):
textuser_id action1 add_to_cart1 buy1 enter1 exit1 register1 show2 add_to_cart2 buyШаг 3. Добавим флаги для каждого действия с помощью CASE.
Теперь для каждого пользователя посчитаем флаги: 1 – если действие было, 0 – если нет. Маркируем действия пользователя 0 или 1 для удобного дальнейшего использования.
text
WITH user_actions AS (SELECT DISTINCTuser_id,actionFROM users_log),user_flags AS (SELECTuser_id,MAX (CASE WHEN action = ’enter’ THEN 1 ELSE 0 END) AS has_enter,MAX (CASE WHEN action = ’show’ THEN 1 ELSE 0 END) AS has_show,MAX (CASE WHEN action = ’add_to_cart’ THEN 1 ELSE 0 END) AS has_add_to_cart,MAX (CASE WHEN action = ’buy’ THEN 1 ELSE 0 END) AS has_buyFROM user_actionsGROUP BY user_id)SELECT * FROM user_flags ORDER BY user_id;Как это работает
– CASE WHEN action = ’enter’ THEN 1 ELSE 0 END – если действие равно enter, возвращаем 1, иначе 0.
– MAX (…) – если у пользователя была хотя бы одна строка с 1, MAX вернёт 1. Если не было – вернёт 0.
– GROUP BY user_id – сворачиваем все строки пользователя в одну.
Почему MAX, а не SUM?
SUM сложил бы 1+1+1 = 3, если пользователь сделал действие 3 раза. Нам важно только «было или нет», поэтому MAX (максимальное значение – 1).
Пример вывода (реальные данные, фрагмент):
text
user_id has_enter has_show has_add_to_cart has_buy1 1 1 1 12 1 1 1 13 1 0 0 14 1 0 0 1Шаг 4. Считаем воронку.
Теперь у нас есть флаги. Посчитаем, сколько пользователей дошло до каждого этапа.
text
WITH user_actions AS (SELECT DISTINCTuser_id,actionFROM users_log),user_flags AS (SELECTuser_id,MAX (CASE WHEN action = ’enter’ THEN 1 ELSE 0 END) AS has_enter,MAX (CASE WHEN action = ’show’ THEN 1 ELSE 0 END) AS has_show,MAX (CASE WHEN action = ’add_to_cart’ THEN 1 ELSE 0 END) AS has_add_to_cart,MAX (CASE WHEN action = ’buy’ THEN 1 ELSE 0 END) AS has_buyFROM user_actionsGROUP BY user_id)SELECTCOUNT (*) AS total_users,SUM (has_enter) AS enter_count,SUM (has_show) AS show_count,SUM (has_add_to_cart) AS add_to_cart_count,SUM (has_buy) AS buy_countFROM user_flags;Как это работает
– COUNT (*) – все пользователи, у которых есть хотя бы одно действие (те, кто вообще залогинился).
– SUM (has_enter) – суммируем единицы. Сколько пользователей с флагом has_enter = 1.
– И так далее по каждому этапу.
Шаг 5. Считаем конверсию в процентах.
Конверсия – это процент пользователей, которые перешли с одного этапа воронки на следующий.
text
Конверсия = (количество на следующем этапе / количество на текущем этапе) * 100
Для нашей воронки:
– Из enter в show: (show_count / enter_count) * 100
– Из show в add_to_cart: (add_to_cart_count / show_count) * 100
– Из add_to_cart в buy: (buy_count / add_to_cart_count) * 100
Почему умножаем на 100? Потому что хотим получить проценты, а не доли. 0.75 → 75%.
Добавим проценты: сколько процентов пользователей дошли до каждого этапа от предыдущего.
text
WITH user_actions AS (SELECT DISTINCTuser_id,actionFROM users_log),user_flags AS (SELECTuser_id,MAX (CASE WHEN action = ’enter’ THEN 1 ELSE 0 END) AS has_enter,MAX (CASE WHEN action = ’show’ THEN 1 ELSE 0 END) AS has_show,MAX (CASE WHEN action = ’add_to_cart’ THEN 1 ELSE 0 END) AS has_add_to_cart,MAX (CASE WHEN action = ’buy’ THEN 1 ELSE 0 END) AS has_buyFROM user_actionsGROUP BY user_id),funnel_counts AS (SELECTCOUNT (*) AS total_users,SUM (has_enter) AS enter_count,SUM (has_show) AS show_count,SUM (has_add_to_cart) AS add_to_cart_count,SUM (has_buy) AS buy_countFROM user_flags)SELECTtotal_users,enter_count,ROUND (100.0 * enter_count / total_users, 2) AS enter_to_total,show_count,ROUND (100.0 * show_count / enter_count, 2) AS show_to_enter,add_to_cart_count,ROUND (100.0 * add_to_cart_count / show_count, 2) AS add_to_cart_to_show,buy_count,ROUND (100.0 * buy_count / add_to_cart_count, 2) AS buy_to_add_to_cartFROM funnel_counts;Как это работает
– Конверсия из enter в show: show_count / enter_count * 100.
– Конверсия из show в add_to_cart: add_to_cart_count / show_count * 100.
– И так далее.
– ROUND округляет число до указанного количества знаков после запятой. ROUND (75.666666, 2) → 75.67, делаем число визуально более понятным.
Почему для первого шага мы делим на total_users, а для остальных – на предыдущий этап?
Первый шаг (enter) показывает, какая доля зарегистрированных пользователей вообще зашла на сайт. Дальше мы смотрим, сколько из тех, кто зашёл, дошли до следующего этапа. Это стандартный подход к воронкам.
Вопросы и ответы
В: Почему мы используем DISTINCT в user_actions?
О: Потому что один пользователь может совершить действие много раз. Нам важно только «было или нет». DISTINCT убирает дубли.
В: Можно ли обойтись без CTE?
О: Можно, но запрос станет громоздким и трудным для понимания. CTE позволяет разбить логику на шаги.
В: Что делать, если у пользователя нет действия enter?
О: Он не попадёт в подсчёт enter_count. Это правильно – если он не заходил, считать его в воронке бессмысленно.
В: Как добавить в воронку другие действия?
О: Добавьте новый CASE в user_flags и новую колонку в funnel_counts.
В: Почему конверсия из enter в show считается как show_count / enter_count, а не show_count / total_users?
О: Потому что нас интересует, сколько из тех, кто зашёл, посмотрели товары. Те, кто не заходил, не могли посмотреть.
Что в итоге
Мы научились строить воронку событий:
– Подготавливать уникальные действия пользователей с DISTINCT.
– Создавать флаги для каждого действия с CASE и MAX.
– Считать количество пользователей на каждом этапе.
– Вычислять конверсию в процентах.
Воронка – один из главных инструментов аналитика для поиска узких мест в продукте.
* Задание со звёздочкой
Постройте воронку для действий: enter → add_to_cart → buy (без show). Измените запрос, убрав show.
Подсказка: удалите из user_flags строки с show, а в funnel_counts уберите соответствующие колонки.
Глава 15. LAG, EXTRACT. Среднее время между enter и buy
Описание задачи
Мы знаем, какие пользователи зашли на сайт и какие купили. Но сколько времени проходит между этими событиями? Чем быстрее пользователь покупает, тем лучше для бизнеса.
Посчитаем среднее время между первым действием enter и первым действием buy для каждого пользователя, а затем среднее по всем пользователям.
Таблицы: users_log (логи действий пользователей).
Аналогия из жизни
Представьте, что вы пришли в торговый центр. Время входа – 12:00. Время покупки в магазине – 12:30. Разница – 30 минут. Если усреднить по всем посетителям, получим среднее время от входа до покупки. Чем оно меньше, тем быстрее люди принимают решение.
Основные SQL-конструкции
LAG () – оконная функция, которая возвращает значение из предыдущей строки в рамках окна.
text
LAG (dt_tm) OVER (PARTITION BY user_id ORDER BY dt_tm)В нашей задаче: для каждого пользователя берём время предыдущего действия.
EXTRACT – извлекает часть из даты/времени (часы, минуты, секунды).
text
EXTRACT (epoch FROM interval) – возвращает количество секундПочему мы не можем просто вычесть даты и получить минуты?
Можно, но EXTRACT даёт больше гибкости. Мы можем получить секунды, минуты, часы. Для среднего времени удобнее работать в секундах, а потом перевести в минуты.
Почему LAG, а не LEAD?
LAG смотрит на предыдущее действие. LEAD – на следующее. Нам нужно предыдущее (первое enter), поэтому LAG.
SQL-запросы с объяснением
Шаг 1. Что нам нужно?
Для каждого пользователя:
– Найти время первого enter.
– Найти время первого buy после enter.
– Посчитать разницу в минутах.
– Затем усреднить по всем пользователям.
Почему первого buy? Пользователь мог купить несколько раз. Нас интересует первый раз, когда он купил после входа.
Шаг 2. Отфильтруем нужные действия.
Возьмём только действия enter и buy.
text
WITH filtered_log AS (SELECTuser_id,action,dt_tmFROM users_logWHERE action IN (’enter’, ’buy’))SELECT * FROM filtered_log ORDER BY user_id, dt_tm;Как это работает
– WHERE action IN (’enter’, ’buy’) – оставляем только нужные действия.
– Упорядочиваем по пользователю и времени, чтобы видеть последовательность.
Шаг 3. Добавим предыдущее действие с LAG.
Для каждого действия посмотрим, что было до него.
text
WITH filtered_log AS (SELECTuser_id,action,dt_tmFROM users_logWHERE action IN (’enter’, ’buy’)),with_prev AS (SELECTuser_id,action,dt_tm,LAG (dt_tm) OVER (PARTITION BY user_id ORDER BY dt_tm) AS prev_dt_tm,LAG (action) OVER (PARTITION BY user_id ORDER BY dt_tm) AS prev_actionFROM filtered_log)SELECT * FROM with_prev ORDER BY user_id, dt_tm;Как это работает
– LAG (dt_tm) OVER (PARTITION BY user_id ORDER BY dt_tm) – для каждой строки берём время предыдущего действия того же пользователя.
– LAG (action) … – берём предыдущее действие.
– Для самой первой строки пользователя предыдущего действия нет → будет NULL.
Шаг 4. Оставим только пары enter → buy.
Нас интересуют строки, где текущее действие buy, а предыдущее – enter.
text
WITH filtered_log AS (SELECTuser_id,action,dt_tmFROM users_logWHERE action IN (’enter’, ’buy’)),with_prev AS (SELECTuser_id,action,dt_tm,LAG (dt_tm) OVER (PARTITION BY user_id ORDER BY dt_tm) AS prev_dt_tm,LAG (action) OVER (PARTITION BY user_id ORDER BY dt_tm) AS prev_actionFROM filtered_log)SELECTuser_id,prev_dt_tm AS enter_time,dt_tm AS buy_timeFROM with_prevWHERE action = ’buy’ AND prev_action = ’enter’;Как это работает
– WHERE action = ’buy’ AND prev_action = ’enter’ – оставляем только те строки, где текущее действие buy, а предыдущее – enter.
– Теперь у нас есть пары (время входа, время покупки).
Пример вывода (фрагмент):
text
user_id enter_time buy_time1 2024-02-01 10:15:00 2024-02-01 10:30:002 2024-02-15 12:00:00 2024-02-15 12:15:003 2024-01-25 14:10:00 2024-01-25 14:15:004 2024-03-05 10:00:00 2024-03-05 10:30:005 2024-02-20 13:00:00 2024-02-20 13:30:00Шаг 5. Посчитаем разницу в минутах.
Используем EXTRACT (epoch FROM …) для перевода разницы в секунды, затем делим на 60.
textWITH filtered_log AS (SELECTuser_id,action,dt_tmFROM users_logWHERE action IN (’enter’, ’buy’)),with_prev AS (SELECTuser_id,action,dt_tm,LAG (dt_tm) OVER (PARTITION BY user_id ORDER BY dt_tm) AS prev_dt_tm,LAG (action) OVER (PARTITION BY user_id ORDER BY dt_tm) AS prev_actionFROM filtered_log),enter_buy_pairs AS (SELECTuser_id,prev_dt_tm AS enter_time,dt_tm AS buy_timeFROM with_prevWHERE action = ’buy’ AND prev_action = ’enter’)SELECTuser_id,enter_time,buy_time,EXTRACT (epoch FROM (buy_time – enter_time)) / 60 AS minutes_diffFROM enter_buy_pairsORDER BY user_id;Как это работает
– buy_time – enter_time – получаем интервал (тип interval).
– EXTRACT (epoch FROM …) – переводит интервал в секунды.
– / 60 – делим на 60, получаем минуты.
Что такое epoch? Epoch – это количество секунд с 1970-01-01. Для интервала EXTRACT (epoch) возвращает общее количество секунд в интервале.
Пример вывода (фрагмент):
text
user_id enter_time buy_time minutes_diff1 2024-02-01 10:15:00 2024-02-01 10:30:00 15.002 2024-02-15 12:00:00 2024-02-15 12:15:00 15.003 2024-01-25 14:10:00 2024-01-25 14:15:00 5.004 2024-03-05 10:00:00 2024-03-05 10:30:00 30.005 2024-02-20 13:00:00 2024-02-20 13:30:00 30.00Шаг 6. Посчитаем среднее время.
text
WITH filtered_log AS (SELECTuser_id,action,dt_tmFROM users_logWHERE action IN (’enter’, ’buy’)),with_prev AS (SELECTuser_id,action,dt_tm,LAG (dt_tm) OVER (PARTITION BY user_id ORDER BY dt_tm) AS prev_dt_tm,LAG (action) OVER (PARTITION BY user_id ORDER BY dt_tm) AS prev_actionFROM filtered_log),enter_buy_pairs AS (SELECTuser_id,prev_dt_tm AS enter_time,dt_tm AS buy_time,EXTRACT (epoch FROM (dt_tm – prev_dt_tm)) / 60 AS minutes_diffFROM with_prevWHERE action = ’buy’ AND prev_action = ’enter’)SELECTCOUNT (*) AS total_pairs,ROUND (AVG (minutes_diff), 2) AS avg_minutesFROM enter_buy_pairs;Как это работает
– COUNT (*) – количество пар (пользователей, которые зашли и купили).
– AVG (minutes_diff) – среднее арифметическое разниц.
– ROUND (…, 2) – округляем до двух знаков.
Пример вывода:
text
total_pairs avg_minutes
11 23.18
Вопросы и ответы
В: Что будет, если пользователь сделал enter несколько раз, а потом buy?
О: LAG возьмёт предыдущее действие. Если перед buy был enter, то учтётся последний enter. Если нужен первый enter – запрос нужно усложнить (добавить MIN с оконной функцией).
В: Почему мы не используем LEAD?
О: LEAD смотрит вперёд. Можно было бы взять enter, а потом LEAD найти следующий buy. Результат будет тот же, но логика другая. LAG удобнее, когда мы идём от покупки назад к входу.
В: Что делать, если у пользователя нет buy?
О: Он не попадёт в результат. Это правильно – мы считаем время только для тех, кто купил.
В: Зачем мы используем EXTRACT (epoch FROM …)?
О: Потому что AVG не умеет работать напрямую с интервалами. Сначала переводим в секунды (число), считаем среднее, потом при желании переводим обратно в минуты.
Конец ознакомительного фрагмента.
Текст предоставлен ООО «Литрес».
Прочитайте эту книгу целиком, купив полную легальную версию на Литрес.
Безопасно оплатить книгу можно банковской картой Visa, MasterCard, Maestro, со счета мобильного телефона, с платежного терминала, в салоне МТС или Связной, через PayPal, WebMoney, Яндекс.Деньги, QIWI Кошелек, бонусными картами или другим удобным Вам способом.
Приобретайте полный текст книги у нашего партнера: