Читать книгу SQL для аналитика. 50 задач с решениями на PostgreSQL - Ар'лан ис'Дрекхэм - Страница 8
Часть 2. Продуктовая аналитика
Глава 6. CTE. Конверсия из регистрации в покупку
ОглавлениеОписание задачи
Аналитики в работе часто сталкиваются с задачами по конверсии, например считаем конверсию пользователей из регистрации в покупку. Надо знать, сколько пользователей или процент пользователей, зарегистрировавшихся в магазине, что-то купили, то есть сделали хотя бы одну покупку.
Это одна из самых частых метрик в аналитике. Метрика показывает, насколько эффективно продукт превращает новых пользователей в покупателей. И позволяет выяснить процент таких пользователей падает или наоборот растёт
Основные SQL-конструкции
CTE (Common Table Expression) – временная таблица в памяти сервера, она существует только во время выполнения основного запроса.
Очень удобно, так как позволяет разбить большой сложный запрос на части, а потом использовать их в основном запросе, в отчете.
Синтаксис:
text
WITH имя_cte AS (
SELECT…
)
SELECT… FROM имя_cte;
Преимущества CTE:
– Разбивает сложный запрос на простые шаги
– Можно использовать один и тот же CTE несколько раз
– Код становится читаемым и понятным
Совет
CTE удобны, когда один и тот же подзапрос нужен несколько раз, или когда запрос состоит из нескольких логических шагов.
Наша задача сосчитать конверсию – это отношение количества пользователей, которые купили, к общему количеству пользователей.
Формула: (пользователи с покупкой / все пользователи) * 100
Эту задачу как раз удобно решать подзапросами с CTE
Сначала считаем зарегистрированных пользователей (первый CTE), далее пользователей с покупками (второй CTE), а затем найдем соотношение
SQL-запросы с объяснением
Шаг 1. Посчитаем всех зарегистрированных пользователей.
text
SELECT COUNT (*) AS total_users FROM users;
Шаг 2. Посчитаем пользователей с покупками.
text
SELECT COUNT (DISTINCT user_id) AS users_with_orders FROM orders;
Шаг 3. Считаем конверсию через CTE.
text
WITH all_users AS (
SELECT COUNT (*) AS total FROM users
),
buying_users AS (
SELECT COUNT (DISTINCT user_id) AS buyers FROM orders
)
SELECT
buyers,
total,
ROUND (100.0 * buyers / total, 2) AS conversion_rate
FROM all_users, buying_users;
Как это работает
– all_users – временная таблица с количеством всех пользователей
– buying_users – временная таблица с количеством покупателей
– Основной запрос берёт данные из обоих CTE и считает процент
– 100.0 (с точкой) нужен, чтобы PostgreSQL понял, что нужно деление с дробной частью
– ROUND (…, 2) округляет до двух знаков после запятой
Пример вывода:
text
buyers total conversion_rate
15 15 100
Шаг 4. Конверсия с группировкой по странам.
text
WITH buying_users AS (
SELECT DISTINCT user_id FROM orders
)
SELECT
u.country,
COUNT (*) AS total_users,
COUNT(bu.user_id) AS buyers,
ROUND (100.0 * COUNT(bu.user_id) / COUNT (*), 2) AS conversion_rate
FROM users u
LEFT JOIN buying_users bu ON u.user_id = bu.user_id
GROUP BY u.country
ORDER BY conversion_rate DESC;
Как это работает
– CTE buying_users – список уникальных покупателей
– LEFT JOIN – оставляем всех пользователей, даже если они не покупали
– Если пользователь не покупал, bu.user_id будет NULL, и COUNT(bu.user_id) его не учтёт
– Группируем по странам
– Сортируем от самой высокой конверсии к низкой
Типичная ошибка
Использовать COUNT(bu.user_id) без LEFT JOIN. Если сделать INNER JOIN, потеряются страны, где нет ни одного покупателя.
Вопросы и ответы
В: Чем CTE отличается от подзапроса?
О: CTE удобнее, код удобнее читать, удобно когда подзапрос используется несколько раз, или когда запрос состоит из нескольких логических шагов. CTE можно многократно переиспользовать.
В: Можно ли использовать несколько CTE в одном запросе?
О: Да, сколько угодно раз, через запятую, как в примере выше.
В: Почему в формуле деления мы пишем 100.0, а не 100?
О: В PostgreSQL деление целых чисел даёт целый результат (5 / 2 = 2). Чтобы получить дробь, нужно хотя бы одно число сделать дробным: 100.0.
В: Как посчитать конверсию по дням после регистрации?
О: Нужно привязать дату регистрации и дату первого заказа. Это тема отдельной главы.
Что в итоге
Мы научились работать с CTE и считать конверсию:
– Создавать временные запросы с помощью CTE
– Считать конверсию пользователей в покупку
– Группировать конверсию по странам, более сложный вариант
CTE и конверсия – основа для более сложных аналитических задач. Если всё получилось, можно переходить к следующей главе.
* Задание со звёздочкой
Напишите запрос, который считает конверсию из регистрации в покупку для пользователей, зарегистрировавшихся в 2024 году. Используйте CTE.
