50 задач на PostgreSQL для аналитика. Конверсия, LTV, retention, воронка, DAU, оконные функции, JSONB, полнотекстовый поиск и другие метрики. Готовые SQL-запросы с пояснениями. База данных в комплекте.
© Максим Чалышев, 2026
ISBN 978-5-0069-9177-4
Создано в интеллектуальной издательской системе Ridero
Для кого эта книга
Эта книга написана аналитиком данных с многолетним опытом работы. Она предназначена для моих коллег, но также будет полезна разработчикам, маркетологам, студентам IT-специальностей и студентам финансовых факультетов.
Если вы уже знаете SQL, но не знаете, как реализовать воронку, посчитать retention, построить событийную аналитику, то в этой книге найдёте ответы.
Если вы почти не знаете SQL, то в начале книги несколько глав познакомят читателя с базовыми операторами языка SQL. Основные операторы языка кратко описаны в приложении в конце книги.
Чем поможет эта книга
Здесь почти нет теории, зато есть 50 реальных задач с решениями. И каждая задача – это настоящий отчёт, который аналитик применяет в работе.
Например:
– конверсия и LTV, воронка событий, отзывы с полнотекстовым поиском, аномалии в доставке и платежах
После прочтения вы сможете уверенно писать запросы любой сложности и использовать знания на практике.
Что нужно, чтобы работать с книгой
– Установите PostgreSQL и pgAdmin (глава 1). Базу данных для примеров вы найдёте в репозитории: https://github.com/atvcross/postgresql_analyst
– Пишите запросы и проверяйте себя. Ответы к заданиям (для первых 15 глав) вы найдёте в книге. Остальные ответы – в репозитории на GitHub.
– Используйте главы книги как справочник. Если что-то забыли, например, как работает LAG или jsonb_set, открывайте соответствующую главу и применяйте
.
Данные для обучения
Все примеры основаны на единой схеме интернет-магазина. Скрипт для создания базы данных и заполнения тестовыми данными лежит в репозитории:
https://github.com/atvcross/postgresql_analyst
Скачайте файл market. sql и выполните его в pgAdmin (инструкция – в главе 2).
Вперёд, к задачам!
Если что пишите в VK https://vk.com/maxandmouse или Issues на GitHub. Удачи!
Чтобы выполнять примеры из книги, понадобится установить PostgreSQL на ваш компьютер.
Разберём установку PostgreSQL и pgAdmin – графического инструмента для работы с базами данных. Это несложно и недолго, если действовать по инструкции.
Для установки потребуется:
– 64-разрядный компьютер или ноутбук
– оперативная память от 2 ГБ
– свободное место на диске от 2 ГБ
– операционная система Windows
Если у вас macOS или Linux – процесс установки будет отличаться. На официальном сайте есть инструкции для всех основных платформ.
Скачивание дистрибутива
Перейдите на официальный сайт PostgreSQL:
https://www.postgresql.org/download/
На странице выберите вашу операционную систему. Для Windows нажмите на значок Windows в верхней части страницы.
Вы попадёте на страницу загрузки для Windows. Нажмите Download the installer.
В списке версий выберите последнюю (на момент написания – 18.3). В колонке Windows x86—64 нажмите на значок скачивания.
Через несколько секунд начнётся загрузка. Если этого не случилось – нажмите Click me.
Запустите скачанный файл. Обычно он лежит в папке «Загрузки» и называется вроде postgresql-18.3-1-windows-x64.exe.
Программа проверит наличие нужных пакетов и, если потребуется, загрузит их. Возможно, понадобится повторный запуск или перезагрузка.
После этого запустится мастер установки.
Шаг 1. Выбор папки установки. Оставьте настройки по умолчанию и нажмите Next.
Шаг 2. Выбор компонентов. Оставьте настройки по умолчанию и нажмите Next.
Шаг 3. Выбор папки для данных. Оставьте настройки по умолчанию и нажмите Next.
Шаг 4. Установка пароля администратора.
Важно! Задайте пароль для доступа к PostgreSQL.
Рекомендуем простой пароль вроде manager или admin. Можете придумать свой, но обязательно запишите его.
Введите пароль дважды и нажмите Next.
Шаг 5. Выбор порта. Оставьте порт по умолчанию (5432) и нажмите Next.
Шаг 6. Выбор локали. Оставьте значение по умолчанию (обычно DEFAULT) и нажмите Next.
Шаг 7. Просмотр настроек. Проверьте параметры и нажмите Next.
Шаг 8. Установка. Нажмите Next. Процесс займёт от 5 до 15 минут.
Шаг 9. Завершение. Снимите галочку Launch Stack Builder and Exit и нажмите Finish.
Если вы забыли пароль – процедура сброса описана в шаге 88 (раздел «Сброс забытого пароля пользователя PostgreSQL»).
Проверка установки
В меню Пуск найдите и запустите pgAdmin 4.
В левой части окна раскройте Servers → PostgreSQL 18 (версия может отличаться).
Введите пароль, который задали при установке. Рекомендуем снять галочку Store password, чтобы не вводить пароль каждый раз.
Если всё прошло успешно – вы увидите структуру сервера в левой панели. Иногда требуется повторить подключение дважды: первый запуск может быть долгим.
Для работы с запросами используйте Query Tool.
Выберите в меню Tools → Query Tool.
Откроется окно, где можно писать и выполнять SQL-запросы.
pgAdmin – это графический интерфейс для PostgreSQL. Он позволяет:
– просматривать структуру баз данных
– выполнять SQL-запросы
– редактировать данные в таблицах
– управлять серверами и пользователями
Простыми словами: pgAdmin – это окно в ваш PostgreSQL.
В левой панели pgAdmin находится дерево объектов. Разберитесь с иерархией:
– Servers – подключения к серверам PostgreSQL
– PostgreSQL 18 (или ваша версия) – ваш локальный сервер
– Databases – список баз данных
– Schemas → public – здесь находятся таблицы, представления, функции
– Tables – список таблиц в схеме public
Совет: чтобы быстро найти нужную таблицу, используйте поиск (Ctrl + G).
Подключение к серверу
В левой панели pgAdmin найдите Servers и раскройте его. Вы увидите сервер PostgreSQL 18 (или вашу версию).
Нажмите на сервер левой кнопкой мыши. Откроется окно ввода пароля.
Введите пароль, который задали при установке. Рекомендуем снять галочку Store password.
После правильного ввода сервер станет активным – зелёная иконка загорится, и вы увидите структуру баз данных.
Инструменты pgAdmin
В верхнем меню есть раздел Tools. Здесь находятся основные инструменты:
– Query Tool – главное место для написания и выполнения SQL-запросов. Именно его мы будем использовать на протяжении всей книги.
– Search Objects (Ctrl + G) – поиск по объектам базы данных.
– Import/Export – импорт и экспорт данных (например, из CSV).
Query Tool
Это главный элемент pgAdmin. Здесь пишутся запросы.
Как открыть:
– Нажмите правой кнопкой на базе данных → Query Tool
– Или выберите базу → Tools → Query Tool
Интерфейс:
– Верхняя часть – редактор кода (пишете запрос)
– Нижняя часть – результат выполнения (таблицы, сообщения, время выполнения)
– Кнопка Execute (Play) или клавиша F5 – выполнить запрос
Пример кода:
text
Напишите этот запрос в редакторе, нажмите Execute (как Play) – и увидите данные из таблицы.
Если всё сделали правильно – переходите к главе 2. Если нет – перечитайте шаг 4 про пароль, чаще всего ошибка там.
Для дальнейшей работы нам нужно создать демо-базу данных и заполнить её информацией.
Вам понадобится загрузить файл market. sql. Он находится в специальном репозитории на GitHub.
Как скачать файл:
– Нажмите на зелёную кнопку Code
– Выберите Download ZIP
– Распакуйте архив в любую папку
– Внутри найдите файл market. sql
– Откройте pgAdmin.
– Создайте новую базу данных.
В дереве слева нажмите правой кнопкой на Databases → Create → Database.
В поле Database введите book_analytics. Остальные поля менять не нужно. Нажмите Save.
– Откройте Query Tool. (Главное меню -> Tools -> Query Tool)
Нажмите правой кнопкой на book_analytics → Query Tool.
– Загрузите файл market. sql.
В редакторе Query Tool нажмите на значок папки (Open File). Выберите скачанный файл market. sql.
Также можно открыть файл в текстовом редакторе (например, Блокнот) и перенести текст в Query Tool через буфер обмена.
– Выполните скрипт.
Нажмите кнопку Execute (или клавишу F5).
Будут созданы нужные таблицы и заполнены данными.
В левой панели (дерево объектов) нажмите правой кнопкой на book_analytics → Refresh.
Раскройте Schemas → public → Tables. Здесь должны отобразиться все восемь таблиц:
– users
– categories
– products
– orders
– order_items
– payments
– users_log
– user_comments
Если таблиц нет или их меньше – схема установилась не полностью, или произошла ошибка. Попробуйте выполнить скрипт заново.
Если всё хорошо, выполните проверочный запрос в Query Tool:
text
Должно вернуться 15.
Это схема интернет-магазина, который торгует разными товарами. Здесь есть товары, заказы, пользователи, платежи. Давайте подробнее о каждой таблице.
users – пользователи.
Колонки: user_id, user_name, registered_at, country, is_active.
Хранит идентификатор, имя, дату регистрации, страну, активен ли пользователь.
categories – категории товаров.
Колонки: category_id, name, parent_id.
Построена как дерево: у каждой категории может быть родитель (parent_id). Это позволяет делать иерархические запросы.
products – товары.
Колонки: product_id, name, category_id, price.
Название, цена, привязка к категории.
orders – заказы.
Колонки: order_id, user_id, order_date, shipped_date, delivered_date, status.
Кто заказал, когда, статус заказа, даты отгрузки и доставки.
order_items – состав заказа.
Колонки: order_id, product_id, quantity, price_per_unit.
Какие товары и в каком количестве купили. Цена за единицу фиксируется на момент покупки.
payments – платежи.
Колонки: payment_id, order_id, amount, payment_date, payment_method.
Сумма, дата, способ оплаты. Привязан к заказу.
users_log – лог событий.
Колонки: log_id, user_id, action, dt_tm, log_data.
Действия пользователя: enter, show, add_to_cart, buy, exit, error. Плюс время события и дополнительные данные в формате JSONB.
user_comments – отзывы.
Колонки: comment_id, user_id, product_id, rating, comment_text, comment_date.
Текст отзыва, оценка от 1 до 5, дата. Привязан к пользователю и товару.
В: Ошибка: база book_analytics уже существует
О: Удалите старую: нажмите правой кнопкой на book_analytics → Delete/Drop. Затем создайте заново.
В: Ошибка: duplicate key violates unique constraint
О: Та же проблема. Удалите старую базу и повторите установку.
В: Ошибка: permission denied
О: Запустите pgAdmin от имени администратора.
В: Пустой результат при проверке
О: Выполнили SELECT COUNT (*) FROM users, а вернулся 0? Значит, данные не загрузились. Запустите market. sql ещё раз.
Если всё сделали правильно – переходите к главе 3. Если таблиц нет или запрос вернул не 15 – перечитайте раздел «Устанавливаем схему через pgAdmin’ и выполните скрипт заново.
Повторим основные знакомые команды SQL. Для этого создадим таблицу-пример и заполним её данными. Потом попробуем обновить или удалить некоторые записи.
Так мы сможем потренировать и вспомнить основные команды SQL на готовом примере.
Нам понадобятся следующие команды:
– CREATE TABLE – создаёт новую таблицу
– INSERT INTO – добавляет строки
– SELECT – показывает данные
– UPDATE – изменяет данные
– DELETE – удаляет строки
– WHERE – фильтрует строки для SELECT, UPDATE, DELETE (AND и OR – логика)
– LIKE – ищет по шаблону внутри WHERE
Расскажем кратко о каждой команде:
Создаёт пустую таблицу с заданной структурой.
Добавляет одну или несколько строк.
Показывает строки, которые подходят под условие. В условиях бывает логика – AND или OR.
Изменяет заданные колонки, где строки подходят под условие.
Удаляет строки из таблицы, подходящие под условие.
WHERE условие
Используется с SELECT, UPDATE, DELETE.
Возможные примеры:
text
text
text
Типичная ошибка
Забыть WHERE в DELETE или UPDATE. Без WHERE удаляются или изменяются ВСЕ строки таблицы. Всегда сначала напишите SELECT с тем же WHERE, чтобы проверить, какие строки попадут под действие.
LIKE «шаблон»/*like*/
Используется внутри WHERE для поиска по тексту.
Символ % заменяет любую последовательность символов (даже пустую).
Примеры:
– WHERE name LIKE «А%' – имена, начинающиеся на А
– WHERE name LIKE '%ов» – имена, заканчивающиеся на «ов».
– WHERE name LIKE '%а%' – имена, содержащие букву «а» в любом месте
– WHERE email LIKE '%@gmail.com' – адреса gmail
– WHERE name LIKE «А__' – имена из трёх букв, начинающиеся на А
Символ _ заменяет ровно один любой символ.
Примеры:
– WHERE name LIKE «А___' – имена из четырёх букв, начинающиеся на А
– WHERE name LIKE '_а%' – имена, где вторая буква «а».
Совет
LIKE чувствителен к регистру. «А%' не найдёт «алексей». Если регистр не важен, используйте ILIKE. Например: WHERE name ILIKE «а%' найдёт и «Алексей», и «алексей».
– SERIAL – последовательность, заполняется автоматически (1, 2, 3…). Для первичных ключей.
– VARCHAR (n) – строка длиной до n символов.
– TEXT – большой текст, строка без ограничения длины.
– DATE – хранит даты (год-месяц-день, например 2026-01-01).
– TIMESTAMP – хранит и дату, и время.
– BOOLEAN – истина или ложь (true/false).
– DECIMAL (10,2) – число с двумя знаками после запятой. Подходит для денег (например, 11.22, 10.50, 110.00).
Создаём таблицу.
text
– SERIAL – user_id будет заполняться автоматически (1, 2, 3, 4…).
– PRIMARY KEY – уникальный идентификатор каждой строки, не должен быть пустым и не должен дублироваться.
– VARCHAR (50) – имя и город не длиннее 50 символов.
– VARCHAR (20) – статус не длиннее 20 символов.
Вставляем данные.
text
Каждая строка в скобках – одна запись. Значения перечислены в том же порядке, что и колонки после INTO: в name – «Алексей», в city – «Москва», в status – ’active’.
Теперь посмотрим, что получилось.
text
Звёздочка означает «все колонки». Без WHERE – показываем все строки.
Отбор с WHERE и LIKE.
Найдём пользователей из Москвы или Казани:
text
Найдём пользователей, у которых имя начинается на «А»:
text
Найдём пользователей, у которых имя заканчивается на «я»:
text
Редактируем данные.
Сделаем всех пользователей из Москвы активными.
text
– WHERE city = «Москва» – выбираем строки, где город Москва.
– SET status = ’active’ – меняем статус на active.
Проверим
SELECT * FROM test_users;
Удаляем данные с условием.
Удалим пользователей со статусом inactive.
text
– WHERE status = ’inactive’ – выбираем строки с неактивным статусом.
– DELETE удаляет их.
Снова смотрим результат.
text
SELECT * FROM test_users;
Остались только активные пользователи.
В: Зачем нам временная таблица test_users?
О: Чтобы не трогать основные таблицы (users, orders) во время тренировки.
В: Что будет, если в DELETE убрать WHERE?
О: Удалятся все строки таблицы. Будьте осторожны.
В: Как удалить саму таблицу?
О: Команда DROP TABLE test_users;
В: Можно ли вставить сразу несколько строк?
О: Да, как в примере выше – несколько строк через запятую.
В: Что делает SERIAL?
О: Автоматически увеличивает значение при каждой вставке. Последовательность. Не нужно указывать user_id руками.
В: Чем VARCHAR отличается от TEXT?
О: VARCHAR (n) ограничивает длину. TEXT может быть любой длины. Для коротких полей (имя, город) лучше VARCHAR.
В: Что значит % и _ в LIKE?
О: % – любая последовательность символов (может быть и пустой). _ – ровно один любой символ.
Теперь мы создали таблицу, вставили данные, отредактировали, удалили что не нужно и проверили результат. Все эти команды пригодятся в следующих главах.
Создайте таблицу test_employees с колонками: employee_id SERIAL PRIMARY KEY, name VARCHAR (100), department VARCHAR (50), salary NUMERIC.
Вставьте одного сотрудника: «Иван Иванов», отдел «Аналитика», зарплата 100000.
Напишите запрос, который увеличивает зарплату на 10% для всех сотрудников отдела «Аналитика».
Продолжим повторение SQL и вспомним запросы с группировкой данных, объединением таблиц, а также IN и BETWEEN. Используется почти в каждой аналитической задаче.
Разберём, как GROUP BY собирает строки в группы, JOIN соединяет данные из разных таблиц, а IN с подзапросом проверяет вхождение в список значений. Узнаем про команду BETWEEN для проверки на диапазон значений.
GROUP BY – создаёт группы из строк с одинаковыми значениями.
Обычно используется вместе с агрегатными функциями:
– COUNT (*) – количество элементов в группе
– MAX (column) – максимальное значение в группе
– MIN (column) – минимальное значение в группе
– AVG (column) – среднее значение в группе
Пример: посчитать количество пользователей в каждой стране.
text
– GROUP BY country – все строки с одинаковым country попадают в одну группу.
– COUNT (*) считает количество строк в каждой группе.
JOIN – объединяет две таблицы по ключу.
Основные виды JOIN:
– INNER JOIN – только строки, где есть совпадение в обеих таблицах
– LEFT JOIN – все строки из левой таблицы, даже если нет совпадения в правой
– RIGHT JOIN – все строки из правой таблицы
– FULL JOIN – все строки из обеих таблиц
Пример: вывести заказы вместе с именами пользователей.
text
– o – псевдоним для таблицы orders
– u – псевдоним для таблицы users
– ON o.user_id = u.user_id – условие соединения
Типичная ошибка
Забыть условие JOIN. Если написать FROM orders o, users u без ON, получите декартово произведение (каждая строка из orders соединится с каждой строкой из users). Результат будет огромным и бессмысленным.
IN с подзапросом – проверяет, входит ли значение в результат подзапроса.
Пример: найти пользователей, которые делали заказы.
text
– Подзапрос (SELECT DISTINCT user_id FROM orders) возвращает список user_id, которые есть в заказах. DISTINCT убирает дубли и возвращает только уникальные значения.
– WHERE user_id IN… оставляет только тех пользователей, чей user_id есть в этом списке.
IN со списком значений – проверяет, входит ли значение в заданный список.
Пример: найти пользователей из нескольких стран.
text
– IN («Россия», «Беларусь», «Казахстан») – проверяет, совпадает ли country с одним из указанных значений.
– Это то же самое, что WHERE country = «Россия» OR country = «Беларусь» OR country = «Казахстан», но короче и понятнее.
Совет
Для коротких списков (до 5—7 значений) IN удобен и читаем. Если список большой, лучше использовать JOIN с временной таблицей.
BETWEEN – фильтр по интервалу значений, проверяет, входит ли значение в интервал чисел или дат.
Найти товары с ценой от 10000 до 50000 рублей.
text
BETWEEN 10000 AND 50000 включает границы. То же самое что price> = 10000 AND price <= 50000.
Найти заказы, сделанные в феврале 2024 года.
text
Учтите, что в феврале 2024 было 29 дней (високосный год).
Задача 1. Количество заказов по статусам.
Посчитаем, сколько заказов в каждом статусе.
text
– GROUP BY status – группируем заказы по статусу.
– COUNT (*) – считаем количество в каждой группе.
– ORDER BY order_count DESC – сортируем от большего к меньшему.
Пример вывода (фрагмент):
text
Задача 2. Сумма выручки по месяцам.
Посчитаем общую выручку по месяцам на основе завершённых заказов.
text
– DATE_TRUNC (’month’, order_date) – обрезаем дату до первого дня месяца.
– INNER JOIN – соединяем заказы с их товарами.
– SUM (oi. quantity * oi.price_per_unit) – считаем общую сумму.
– GROUP BY month – группируем по месяцам.
Задача 3. Топ-5 пользователей по сумме покупок.
Найдём пользователей, которые потратили больше всего.
text
– Два INNER JOIN – соединяем users → orders → order_items.
– GROUP BY u.user_id, u.user_name – группируем по пользователю.
– SUM – считаем общую сумму покупок.
– ORDER BY total_spent DESC – сортируем от большего к меньшему.
– LIMIT 5 – оставляем только пять строк.
Задача 4. Пользователи, которые ничего не покупали (через NOT IN).
Найдём пользователей, у которых нет ни одного заказа.
text
– Подзапрос собирает список всех user_id, которые есть в заказах.
– NOT IN оставляет пользователей, которых нет в этом списке.
Типичная ошибка
Если в подзапросе NOT IN встретится NULL, результат будет пустым. Поэтому внутри подзапроса всегда добавляйте WHERE user_id IS NOT NULL.
Задача 5. Пользователи, которые покупали хотя бы раз (через IN).
Найдём пользователей, у которых есть хотя бы один заказ.
text
– Подзапрос собирает список всех user_id, которые есть в заказах.
– IN оставляет пользователей, которые есть в этом списке.
Задача 6. Пользователи из выбранных стран (IN со списком).
Найдём пользователей из России, Беларуси и Казахстана.
text
– IN («Россия», «Беларусь», «Казахстан») – оставляет только пользователей из этих трёх стран.
– ORDER BY country, user_name – сортируем сначала по стране, потом по имени.
Пример вывода (фрагмент):
В: Что будет, если в GROUP BY указать не все колонки из SELECT?
О: PostgreSQL выдаст ошибку. Все колонки, которые не обёрнуты в агрегатную функцию (SUM, COUNT, AVG), должны быть перечислены в GROUP BY.
В: Чем INNER JOIN отличается от LEFT JOIN?
О: INNER JOIN оставляет только строки с совпадением в обеих таблицах. LEFT JOIN оставляет все строки из левой таблицы, даже если в правой нет совпадения (тогда поля из правой таблицы будут NULL).
В: Чем IN с подзапросом отличается от JOIN?
О: Оба могут дать одинаковый результат, но работают по-разному. IN удобен, когда нужно просто проверить вхождение. JOIN даёт больше гибкости (можно вывести поля из обеих таблиц).
В: Почему в подзапросе для NOT IN нужно убирать NULL?
О: Потому что NOT IN с NULL в списке всегда возвращает пустой результат. Это особенность SQL.
Мы научились:
– Группировать строки с GROUP BY и считать агрегаты (COUNT, SUM)
– Соединять таблицы с помощью INNER JOIN и LEFT JOIN
– Использовать IN и NOT IN с подзапросами и со списками значений
– Использовать BETWEEN для фильтрации по интервалу
– Комбинировать WHERE, GROUP BY и ORDER BY в одном запросе
Эти навыки – основа для следующих глав, где мы будем решать реальные аналитические задачи.
Напишите запрос, который выводит общую сумму выручки по каждому пользователю (user_id, user_name) и название страны, но только для пользователей из России и Казахстана. Используйте JOIN и WHERE с IN.
Результат отсортируйте по сумме от большей к меньшей.
Повторим операторы, которые работают с множествами, используют результат двух и более запросов: UNION, INTERSECT, EXCEPT.
Эти команды могут объединять, пересекать и вычитать наборы строк.
Это особенно удобно, когда нужно сравнить два набора данных в одном отчёте или, наоборот, убрать лишние данные, или же найти данные, которые есть и в одном, и в другом наборе.
Такие задачи очень часто встречаются в работе аналитика.
Основные SQL-конструкции
UNION – объединяет результаты двух запросов, убирая дубликаты.
UNION ALL – объединяет результаты, оставляя дубликаты.
INTERSECT – возвращает строки, которые есть в обоих запросах.
EXCEPT – возвращает строки из первого запроса, которых нет во втором.
Важные требования:
– Количество колонок в запросах должно совпадать.
– Типы данных колонок должны быть совместимы.
– Порядок колонок имеет значение.
Примеры:
– UNION: пользователи, которые сделали заказы ИЛИ оставили отзывы.
– INTERSECT: пользователи, которые сделали заказы И оставили отзывы.
– EXCEPT: пользователи, которые сделали заказы, НО не оставили отзывы.
Совет
UNION убирает дубликаты и сортирует результат. Сначала объединяет данные, потом убирает дубли. Если это не важно, используйте UNION ALL – он будет работать быстрее.
Представьте, вам нужен отчёт: посмотреть заказы из Москвы и Казани. Используем UNION.
Отдельно выбираем заказы пользователей из Москвы, отдельно из Казани, объединяем.
text
– Первый запрос – заказы пользователей из Москвы.
– Второй запрос – заказы пользователей из Казани.
– UNION объединяет результаты и убирает дубликаты (если пользователь из Москвы сделал заказ и как-то попал в Казань – но такого не будет).
– ORDER BY применяется к общему результату.
Пример вывода (фрагмент):
Задача 1. Пользователи, которые и покупали, и оставляли отзывы (INTERSECT).
text
– Первый запрос – все user_id из заказов.
– Второй запрос – все user_id из отзывов.
– INTERSECT оставляет только тех, кто есть в обоих списках.
Типичная ошибка
INTERSECT требует совпадения по всем колонкам. Если добавить в SELECT лишнюю колонку (например, order_date), результат может стать пустым, потому что дата заказа не совпадёт с датой отзыва.
Задача 2. Пользователи, которые покупали, но не оставляли отзывы (EXCEPT).
text
– Первый запрос – все user_id из заказов.
– Второй запрос – все user_id из отзывов.
– EXCEPT убирает из первого списка тех, кто есть во втором.
Задача 3. Сравнение двух способов: UNION vs UNION ALL.
text
Вернёт одну строку: «Россия».
text
Вернёт столько строк, сколько пользователей из России (каждый дубль сохранится).
В: Чем UNION отличается от UNION ALL?
О: UNION убирает дубликаты и сортирует результат. UNION ALL оставляет все строки как есть и работает быстрее.
В: Какой порядок выполнения у UNION, INTERSECT, EXCEPT?
О: Все операторы имеют одинаковый приоритет. Для управления порядком используйте скобки.
В: Можно ли комбинировать больше двух запросов?
О: Да. SELECT… UNION SELECT… INTERSECT SELECT … – но лучше использовать скобки, чтобы явно задать порядок.
Мы научились:
– Объединять результаты запросов с UNION и UNION ALL.
– Находить пересечения с INTERSECT.
– Вычитать множества с EXCEPT.
То есть освоили все команды работы с множествами.
Эти операторы удобны для сравнения списков и подготовки отчётов.
Напишите запрос, который выводит user_id пользователей, которые оставляли отзывы, но никогда не покупали товары из категории «Смартфоны».
Используйте EXCEPT и подзапрос с JOIN.
Аналитики в работе часто сталкиваются с задачами по конверсии, например считаем конверсию пользователей из регистрации в покупку. Надо знать, сколько пользователей или процент пользователей, зарегистрировавшихся в магазине, что-то купили, то есть сделали хотя бы одну покупку.
Это одна из самых частых метрик в аналитике. Метрика показывает, насколько эффективно продукт превращает новых пользователей в покупателей. И позволяет выяснить процент таких пользователей падает или наоборот растёт
CTE (Common Table Expression) – временная таблица в памяти сервера, она существует только во время выполнения основного запроса.
Очень удобно, так как позволяет разбить большой сложный запрос на части, а потом использовать их в основном запросе, в отчете.
Синтаксис:
text
Совет
CTE удобны, когда один и тот же подзапрос нужен несколько раз, или когда запрос состоит из нескольких логических шагов.
Наша задача сосчитать конверсию – это отношение количества пользователей, которые купили, к общему количеству пользователей.
Формула: (пользователи с покупкой / все пользователи) * 100
Эту задачу как раз удобно решать подзапросами с CTE
Сначала считаем зарегистрированных пользователей (первый CTE), далее пользователей с покупками (второй CTE), а затем найдем соотношение
Шаг 1. Посчитаем всех зарегистрированных пользователей.
text
Шаг 2. Посчитаем пользователей с покупками.
text
Шаг 3. Считаем конверсию через CTE.
text
– all_users – временная таблица с количеством всех пользователей
– buying_users – временная таблица с количеством покупателей
– Основной запрос берёт данные из обоих CTE и считает процент
– 100.0 (с точкой) нужен, чтобы PostgreSQL понял, что нужно деление с дробной частью
– ROUND (…, 2) округляет до двух знаков после запятой
Пример вывода:
text
Шаг 4. Конверсия с группировкой по странам.
text
– 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.
Представьте: утро понедельника. Менеджер по маркетингу подходит с просьбой – «Дай список пользователей, которые зарегистрировались, но ничего не купили. Будем отправлять промокоды».
Задача простая. Но если сделать её правильно – маркетологи перестанут спамить всем подряд и начнут работать точечно.
Что нужно выгрузить:
– user_id
– user_name
– registered_at
– country
Только тех, у кого ноль покупок.
Для решения нам пригодятся JOIN и IS NULL (эти команды уже разобраны в главе 4), а также новый оператор NOT EXISTS. Сейчас с ним познакомимся.
NOT EXISTS проверяет, что подзапрос не вернул ни одной строки.
text
Берём строку из table_a. Проверяем, есть ли её ключ (WHERE b.key = a.key) в table_b. Ничего не находим – оставляем. Если находим – не выводим (так работает, потому что есть NOT).
Запомните: NOT EXISTS проверяет отсутствие. Если подзапрос вернул хотя бы одну строку – строка из внешнего запроса не попадёт в результат.
Способ 1. LEFT JOIN + IS NULL
text
Запрос берёт всех пользователей из users. Присоединяет к ним заказы из orders по user_id.
Если у пользователя нет заказов – все колонки из orders становятся NULL.
Условие WHERE o. order_id IS NULL оставляет только таких пользователей.
Почему проверяем именно order_id? Потому что это первичный ключ таблицы orders. Если он NULL – значит, строки из orders нет вообще.
Способ 2. NOT EXISTS (рекомендуется)
text
Для каждого пользователя запрос проверяет: есть ли в orders хотя бы одна запись с таким user_id?
Если есть – пользователь исключается. Если нет – попадает в результат.
Обратите внимание на SELECT 1 внутри подзапроса. EXISTS не смотрит на содержимое строк, ему важно только их наличие. Поэтому SELECT 1 – стандартное соглашение, оно работает быстрее, чем SELECT *.
Запомните: NOT EXISTS читается как «таких, для которых не существует записей в…».
Способ 3. NOT IN (с осторожностью)
text
Подзапрос собирает список всех user_id, которые есть в orders. Основной запрос выбирает пользователей, чей user_id отсутствует в этом списке.
Важное предупреждение: Если в подзапросе встретится хотя бы один NULL – NOT IN вернёт пустой результат. Именно поэтому мы добавили WHERE user_id IS NOT NULL.
На заметку: NOT IN работает, но требует осторожности. На собеседованиях часто спрашивают про этот нюанс с NULL.
В: А какой способ быстрее?
О: Для больших таблиц – NOT EXISTS. Он оптимизирован для проверки наличия строк и не требует сборки полного списка user_id.
В: А что, если у пользователя заказ, но он отменён?
О: В текущих запросах такой пользователь не попадёт в результат – ведь запись в orders есть.
Если нужны только успешные заказы, добавьте условие в подзапрос:
text
В: Почему результат пустой, хотя я знаю, что есть пользователи без заказов?
О: Три самые частые причины:
– В таблице orders есть строки с user_id = NULL (для NOT IN это фатально)
– Нарушены внешние ключи – в orders есть user_id, которого нет в users
– Опечатка в названии таблиц или колонок
Что мы узнали в этой главе:
– LEFT JOIN + IS NULL – поняли, как работает присоединение с проверкой на пустоту
– NOT EXISTS – освоили самый эффективный способ поиска отсутствующих записей
– NOT IN – узнали про подводный камень с NULL
Теперь вы умеете находить пользователей, которые зарегистрировались, но не купили. Задача, которая встречается в работе постоянно.
Если всё сделали правильно – переходите к главе 8.
Напишите запрос, который находит пользователей, зарегистрировавшихся в 2024 году, но не сделавших ни одного заказа со статусом ’completed’. Используйте NOT EXISTS.
В отчёте нужны данные, какой у нас средний чек по каждой категории товаров. То есть какие категории приносят больше денег за одну покупку.
Таблицы: orders, order_items, products, categories.
Нужно вывести:
– название категории
– среднюю сумму заказа в рублях
Только по завершённым заказам (status = ’completed’).
GROUP BY – группирует строки с одинаковыми значениями (проходили, глава 4).
AVG – агрегатная функция, она считает среднее арифметическое в заданной колонке по группе.
text
Важно: AVG игнорирует NULL. Если в группе все значения NULL, результат будет NULL.
Совет
Если нужно среднее с учётом NULL как нулей, используйте AVG (COALESCE (price, 0)). COALESCE проверяет: если price равен NULL, возвращает 0.
Задача 1. Средний чек по категориям.
text
– Присоединяем заказы → товары в заказе → продукты → категории
– Отбираем только завершённые заказы
– Группируем по категории
– Считаем среднюю сумму заказа в каждой группе
– Сортируем от самой дорогой категории к дешёвой
Мы используем INNER JOIN (просто JOIN), потому что категории без заказов нас не интересуют. Если нужны и они, замените JOIN на LEFT JOIN.
Что выводит запрос: две колонки – category_name (название категории) и avg_order_value (средняя сумма заказа в рублях).
Пример вывода (фрагмент):
text
Задача 2. Средний чек с количеством заказов.
Добавим информацию о том, сколько заказов попало в расчёт.
text
Добавили COUNT (DISTINCT o. order_id), чтобы не считать один заказ несколько раз, если в нём несколько товаров из одной категории.
Типичная ошибка
Добавить в GROUP BY не все колонки из SELECT. PostgreSQL требует, чтобы все колонки, не обёрнутые в агрегатную функцию, были перечислены в GROUP BY. Поэтому мы добавили c.category_id и c.name.
В: А если в категории нет заказов?
О: Такая категория не попадёт в результат, потому что INNER JOIN её отсекает. Чтобы показать категории с нулевыми продажами, замените JOIN на LEFT JOIN. Тогда в avg_order_value будет NULL.
В: Что будет, если в заказе несколько товаров из одной категории?
О: Сумма по каждому товару считается отдельно, потом все складываются в группе. Получается средняя сумма заказа по категории. Всё верно.
В: Почему мы используем DISTINCT в COUNT?
О: Если в заказе несколько товаров из одной категории, без DISTINCT один заказ посчитается несколько раз. Нам важно количество уникальных заказов.
В: Как понять, что средний чек получился достоверным?
О: Посмотрите на количество заказов в категории. Если заказов мало, средний чек может быть случайным.
Мы научились считать средний чек:
• Группировать данные по категориям с GROUP BY
• Считать среднее арифметическое с AVG
• Добавлять в отчёт количество заказов для оценки достоверности
Эти навыки нужны для любой аналитики продаж.
Напишите запрос, который выводит средний чек по категориям для заказов, сделанных в феврале 2024 года. Используйте фильтр по дате и группировку.
Составим отчёт о пользователях, которые совершили больше одной покупки. Иными словами – найти тех, кто возвращается в интернет магазин.
Узнать, сколько пользователей купили дважды, трижды и так далее.
COUNT – агрегатная функция, считает количество строк в группе.
text
HAVING – фильтрует группы после группировки (как WHERE, но для GROUP BY).
text
Типичная ошибка
Использовать WHERE вместо HAVING для фильтрации по агрегатным функциям. WHERE выполняется до группировки и не видит COUNT, SUM, AVG. Если есть группировка и нужно отфильтровать сгруппированные данные – всегда используем HAVING.
Задача 1. Количество заказов по пользователям.
Посчитаем, сколько заказов сделал каждый пользователь.
text
– LEFT JOIN – оставляем всех пользователей, даже без заказов.
– COUNT (o. order_id) – считает только непустые заказы (у пользователей без заказов будет 0).
– GROUP BY – группируем по пользователю.
– ORDER BY orders_count DESC – сортируем от частых покупателей к редким.
Пример вывода (фрагмент):
text
Задача 2. Пользователи с повторными покупками (больше одного заказа).
Используем HAVING, чтобы отфильтровать группы.
text
Пример вывода (фрагмент):
Совет
Если нужно посчитать пользователей с повторными покупками в процентах, можно использовать CTE:
text
Задача 3. Распределение пользователей по количеству покупок.
Сколько пользователей купили 1 раз, 2 раза, 3 раза и так далее.
text
– CTE order_counts – считает количество заказов для каждого пользователя.
– Основной запрос группирует пользователей по числу заказов.
Пример вывода:
text
В: Чем HAVING отличается от WHERE?
О: WHERE фильтрует строки до группировки, HAVING – после. WHERE не может использовать агрегатные функции (COUNT, SUM, AVG).
В: Почему в первом запросе LEFT JOIN, а во втором INNER JOIN?
О: В первом запросе мы хотим видеть всех пользователей (даже с 0 заказов). LEFT JOIN это позволяет. COUNT (o. order_id) считает только непустые значения, так что 0 заказов = 0.
В: Как посчитать только уникальные заказы (без отменённых)?
О: Добавьте условие в JOIN или в WHERE: AND o.status = ’completed’.
Что в итоге
Мы научились:
– Считать количество заказов на пользователя с GROUP BY и COUNT
– Отбирать группы с помощью HAVING
– Строить распределение пользователей по частоте покупок
Эти навыки нужны для анализа лояльности и удержания пользователей.
Напишите запрос, который выводит пользователей, сделавших хотя бы 3 заказа со статусом ’completed’. Используйте HAVING.
LTV (Lifetime Value) – общая сумма денег, потраченная пользователем в магазине за всё время. Это ключевая метрика для оценки рейтинга клиента.
Всегда нужно знать: кто приносит больше всего денег, чтобы фокусироваться на удержании ценных клиентов, предложить скидки, подарить купон на день рождения.
SUM – агрегатная функция, суммирует значения в группе. Работает только для числовых колонок.
text
GROUP BY – группирует строки с одинаковыми значениями (разобрано в главе 4).
COALESCE – возвращает первое не-NULL значение. Полезно для замены NULL на 0.
text
Совет
При суммировании лучше использовать SUM (COALESCE (amount, 0)), чтобы избежать NULL в результате. Но в нашей схеме суммы не NULL.
Задача 1. LTV каждого пользователя (все заказы).
Посчитаем, сколько всего денег потратил каждый пользователь по завершённым заказам.
text
– Присоединяем заказы и товары в заказах.
– Фильтруем только завершённые заказы.
– Группируем по пользователю.
– Суммируем стоимость всех купленных товаров.
– Сортируем от самого ценного клиента к менее ценным.
Пример вывода (фрагмент):
text
Задача 2. LTV с учётом только успешных платежей (через payments).
Иногда удобнее считать LTV по таблице платежей, а не по order_items (например, если есть возвраты).
text
– Присоединяем платежи напрямую к пользователям.
– Суммируем суммы платежей.
– Группируем по пользователю.
Типичная ошибка
Забыть GROUP BY при использовании SUM. Без группировки SUM сложит все значения по всей таблице, а не по пользователям.
Задача 3. Средний LTV по странам.
Узнаем, в какой стране пользователи в среднем тратят больше.
text
– Группируем по стране.
– Считаем количество уникальных пользователей в стране.
– Общую выручку по стране.
– AVG считает средний LTV по стране.
Пример вывода (фрагмент):
text
В: Чем LTV отличается от общей выручки?
О: LTV – сумма на одного пользователя. Общая выручка – сумма по всем пользователям.
В: Как считать LTV для пользователей без заказов?
О: Они не попадут в результат при INNER JOIN. Если нужно показать и их (с LTV = 0), используйте LEFT JOIN и COALESCE.
В: Можно ли считать LTV за определённый период?
О: Да, добавьте фильтр по дате в WHERE, например o. order_date> = «2024-01-01».
В: Почему в задаче 3 используется AVG, а не SUM?
О: AVG даёт средний LTV по стране, SUM – общую выручку. В задаче нужен именно средний.
Мы научились считать LTV:
– Суммировать стоимость покупок с SUM
– Группировать данные по пользователям и странам
– Считать LTV – ключевую метрику для анализа ценности клиента
Напишите запрос, который выводит топ-3 пользователей по LTV за 2024 год (используйте фильтр по order_date). Выведите user_id, user_name и ltv.
Мы научились считать LTV:
– Суммировать стоимость покупок с SUM
– Группировать данные по пользователям и странам
– Считать LTV – ключевую метрику для анализа ценности клиента
Новый отчёт: посчитаем общую выручку по месяцам на основе завершённых заказов. А затем представим эти данные в виде кросс-таблицы, где месяцы – строки, а категории товаров – колонки.
Таблицы: orders, order_items, products, categories.
DATE_TRUNC – обрезает дату до указанной точности (год, месяц, день, час).
text
SUM – агрегатная функция, суммирует значения (см. главу 10).
crosstab – функция из расширения PostgreSQL tablefunc. Превращает строки в колонки. В функцию передаётся в кавычках запрос, который нам надо перевернуть.
Для использования crosstab нужно установить расширение:
text
Совет
DATE_TRUNC удобен для группировки по периодам: неделям, месяцам, кварталам, годам.
Задача 1. Выручка по месяцам.
text
Пример вывода (фрагмент):
text
Задача 2. Выручка по месяцам и категориям (обычный GROUP BY).
Подготовим данные для кросс-таблицы: выручка по месяцам и категориям.
text
– Группируем по месяцу и категории.
– Считаем выручку для каждой пары (месяц, категория).
Пример вывода (фрагмент):
text
Задача 3. Кросс-таблица: месяцы в строках, категории в колонках.
Превращаем результат задачи 2 в таблицу, где категории становятся колонками, используем crosstab из расширения tablefunc.
text
– Первый запрос – данные: месяц, категория, выручка.
– Второй запрос – список категорий, которые станут колонками.
– crosstab превращает строки в колонки AS ct (month date, «Ноутбуки» numeric, «Смартфоны» numeric, «Планшеты» numeric).
– ::date – преобразуем timestamp в дату.
– Кавычки в названиях колонок нужны из-за русских букв.
– Две одинарные кавычки подряд используются для экранирования кавычек внутри строки crosstab.
результат
В: Зачем нужна кросс-таблица?
О: Она компактно показывает данные: строки – месяцы, колонки – категории. Удобно для отчётов и дашбордов.
В: Почему во втором запросе crosstab указаны не все категории?
О: Чтобы колонок было не слишком много. В реальных отчётах выбирают топ-5 или топ-10 категорий.
В: Что делать, если в какой-то месяц не было продаж по категории?
О: В примере выше будет 0.00 или NULL.
В: Можно ли использовать crosstab без второго запроса?
О: Да, но тогда категории нужно перечислить вручную в определении колонок, а запрос должен возвращать данные в строгом порядке.
Мы научились:
– Группировать данные по месяцам с DATE_TRUNC
– Считать выручку по месяцам и категориям
– Строить кросс-таблицы с помощью crosstab
Кросс-таблицы удобны для отчётов, где нужно сравнить показатели по разным категориям в динамике.
Напишите запрос, который строит кросс-таблицу выручки по кварталам (используйте DATE_TRUNC (’quarter’, order_date)) и категориям «Ноутбуки», «Смартфоны», «Планшеты».
Представьте, что нам нужны 5 товаров, которые принесли больше всего выручки. Это классическая задача для отчётов и дашбордов.
Таблицы: order_items, products.
Аналитические (оконные) функции – выполняют вычисления по набору строк, связанных с текущей строкой. В отличие от GROUP BY, они не сворачивают строки в одну.
ROW_NUMBER () – присваивает уникальный номер каждой строке в рамках окна (партиции).
Синтаксис:
text
ROW_NUMBER () OVER (PARTITION BY колонка ORDER BY колонка)
– PARTITION BY – делит строки на группы (если не указано, всё окно)
– ORDER BY – задаёт порядок нумерации внутри группы
Совет
ROW_NUMBER () часто используют для нумерации строк в отчётах и для отбора топ-N записей в каждой категории.
Задача 1. Топ-5 товаров по выручке (простой способ).
text
– Группируем по товару.
– Считаем выручку.
– Сортируем от большего к меньшему.
– LIMIT 5 оставляет только первые 5 строк.
Пример вывода:
text
Задача 2. Топ-5 товаров с нумерацией (ROW_NUMBER).
Добавим нумерацию строк.
text
– ROW_NUMBER () OVER (ORDER BY SUM (…) DESC) – нумерует строки от 1 до N в порядке убывания выручки.
– Остальное как в задаче 1.
Задача 3. Топ-3 товара в каждой категории (ROW_NUMBER с PARTITION BY).
Более сложный пример: для каждой категории найти 3 самых продаваемых товара.
text
– product_revenue – считаем выручку по товарам с категориями.
– ranked – нумеруем товары внутри каждой категории (PARTITION BY category_name) по убыванию выручки.
– WHERE rank <= 3 – оставляем только топ-3 в каждой категории.
Пример вывода (фрагмент):
text
В: Чем ROW_NUMBER () отличается от RANK () и DENSE_RANK ()?
О: При одинаковых значениях ROW_NUMBER () даёт разные номера (1,2,3,4…), RANK () пропускает номера при совпадении (1,1,3,4…), DENSE_RANK () не пропускает (1,1,2,3…).
В: Зачем использовать ROW_NUMBER (), если есть LIMIT?
О: LIMIT работает только на всём результате. ROW_NUMBER () с PARTITION BY позволяет отобрать топ-N в каждой группе (категории, стране и т.д.).
В: Можно ли использовать ROW_NUMBER () без PARTITION BY?
О: Да. Тогда нумерация будет по всему результату.
В: Что будет, если несколько товаров имеют одинаковую выручку?
О: ROW_NUMBER () назначит разные номера (порядок не определён). Если нужна одинаковая позиция, используйте RANK () или DENSE_RANK ().
Мы научились:
– Находить топ-5 товаров с помощью GROUP BY и LIMIT
– Добавлять нумерацию с ROW_NUMBER ()
– Отбирать топ-N товаров в каждой категории с PARTITION BY
Оконные функции открывают возможности для более сложной аналитики.
Напишите запрос, который выводит топ-2 товара по выручке в каждой категории. Используйте ROW_NUMBER () OVER (PARTITION BY …).
Retention – это способность продукта возвращать пользователей. Он показывает, какая доля пользователей вернулась на следующий день, через неделю, через месяц.
Мы посчитаем retention за 7 дней: сколько пользователей, зарегистрировавшихся в определённый день, совершили хотя бы одно действие (например, покупку или вход) в каждый из следующих дней в течение 7 дней после регистрации.
Таблицы: users, users_log.
FIRST_VALUE () – оконная функция, которая возвращает значение из первой строки в рамках окна.
text
FIRST_VALUE (users) OVER (PARTITION BY reg_date ORDER BY day_number)
В нашей задаче мы используем FIRST_VALUE (users) OVER (PARTITION BY reg_date ORDER BY day_number) – берём количество пользователей в день 0 (день регистрации) для каждой когорты.
Оконные функции – выполняют вычисления по набору строк, связанных с текущей строкой (см. главу 12). Ключевое слово OVER.
DATE_TRUNC () – обрезает дату до указанной точности (см. главу 11).
Совет
Retention считают не только по дням, но и по неделям, месяцам. Формула одна и та же, меняется только период группировки.
Задача 1. Подготовка данных: уникальные действия пользователей по дням.
Сначала найдём все дни, когда пользователь был активен.
text
Пример вывода (фрагмент):
text
Задача 2. Добавляем дату регистрации.
Для расчёта retention нужно знать, когда пользователь зарегистрировался.
text
– Присоединяем логи к пользователям.
– GROUP BY убирает дубли (то же, что DISTINCT, но явно).
– registered_at::date – преобразуем в дату.
Задача 3. Считаем дни после регистрации.
Для каждого действия посчитаем, на какой день после регистрации оно произошло.
text
– activity_date – reg_date – разница в днях (0 – день регистрации, 1 – следующий день и т.д.).
– CTE user_activity – подготовленные данные.
Задача 4. Считаем retention 7 дней.
Для каждого дня регистрации посчитаем, сколько пользователей вернулось в каждый из следующих дней.
text
– user_activity – подготовленные данные (день регистрации и дни активностей).
– day_numbers – считаем количество уникальных пользователей для каждой пары (дата регистрации, день после регистрации).
– FIRST_VALUE (users) OVER (PARTITION BY reg_date ORDER BY day_number) – берём количество пользователей в день 0 (день регистрации).
– retention_rate – процент от дня 0.
Пример вывода (фрагмент):
text
Типичная ошибка
Учитывать пользователей, у которых нет активности в конкретный день, как вернувшихся. 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.
Представьте, действия пользователя в интернет-магазине. Пользователь сначала заходит на сайт, смотрит товары, добавляет в корзину, покупает. Но на каждом шаге кто-то отсеивается, кто-то смотрит и не покупает, кто-то не доходит даже до просмотра товаров.
Воронка (funnel) – это последовательность шагов, которая показывает, сколько пользователей доходит до каждого этапа.
Почему воронка? Пришли много пользователей, посмотрели товары уже меньше, купило еще меньше, визуально можно представить, что это воронка.
Такая воронка – это один из самых важных отчётов в аналитике событий.
Мы построим воронку: enter → show → add_to_cart → buy.
Таблицы: users_log (логи действий пользователей).
Аналогия из жизни
Представьте торговый центр. На вход зашли 1000 человек. До витрины с телефонами дошли 600. В магазин зашли 300. Купили 100. Это и есть воронка. Наша задача – посчитать такие же цифры по логам.
COUNT (DISTINCT …) – считает уникальные значения. В воронке это важно, потому что один пользователь может совершить действие несколько раз.
CASE – условный оператор, работает как if-then-else (если правда, то одно, иначе другое). Позволяет вернуть одно значение, если условие истинно, и другое – если ложно.
text
В нашей задаче: если действие равно ’enter’, то 1, иначе 0.
CTE (WITH) – временная таблица (см. главу 6). Разбивает сложный запрос на части.
Почему мы не можем просто использовать GROUP BY?
GROUP BY сгруппирует всех пользователей, но не позволит легко посчитать, сколько пользователей выполнили несколько действий подряд. CTE с CASE даёт гибкость.
Шаг 1. Что нам нужно посчитать?
Нам нужно для каждого пользователя определить по таблице логов:
– Был ли у него enter? Вошёл ли он на сайт?
– Был ли у него show? Посмотрел ли он товары?
– Был ли у него add_to_cart? Добавил ли товары в корзину?
– Был ли у него buy? Совершил покупку?
Если был – ставим 1, если нет – 0.
Аналогия: Как контрольный список. Был пользователь на этапе? Да/нет.
Шаг 2. Подготовим данные: уникальные действия пользователей.
Сначала найдём все уникальные действия каждого пользователя.
text
– DISTINCT убирает дубли (если пользователь сделал enter 5 раз, оставляем один).
– Теперь у нас есть список: пользователь X сделал действия A, B, C.
Пример вывода (реальные данные, фрагмент):
Шаг 3. Добавим флаги для каждого действия с помощью CASE.
Теперь для каждого пользователя посчитаем флаги: 1 – если действие было, 0 – если нет. Маркируем действия пользователя 0 или 1 для удобного дальнейшего использования.
text
– 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
Шаг 4. Считаем воронку.
Теперь у нас есть флаги. Посчитаем, сколько пользователей дошло до каждого этапа.
text
– 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
– Конверсия из 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.
Мы знаем, какие пользователи зашли на сайт и какие купили. Но сколько времени проходит между этими событиями? Чем быстрее пользователь покупает, тем лучше для бизнеса.
Посчитаем среднее время между первым действием enter и первым действием buy для каждого пользователя, а затем среднее по всем пользователям.
Таблицы: users_log (логи действий пользователей).
Аналогия из жизни
Представьте, что вы пришли в торговый центр. Время входа – 12:00. Время покупки в магазине – 12:30. Разница – 30 минут. Если усреднить по всем посетителям, получим среднее время от входа до покупки. Чем оно меньше, тем быстрее люди принимают решение.
LAG () – оконная функция, которая возвращает значение из предыдущей строки в рамках окна.
text
В нашей задаче: для каждого пользователя берём время предыдущего действия.
EXTRACT – извлекает часть из даты/времени (часы, минуты, секунды).
text
Почему мы не можем просто вычесть даты и получить минуты?
Можно, но EXTRACT даёт больше гибкости. Мы можем получить секунды, минуты, часы. Для среднего времени удобнее работать в секундах, а потом перевести в минуты.
Почему LAG, а не LEAD?
LAG смотрит на предыдущее действие. LEAD – на следующее. Нам нужно предыдущее (первое enter), поэтому LAG.
Шаг 1. Что нам нужно?
Для каждого пользователя:
– Найти время первого enter.
– Найти время первого buy после enter.
– Посчитать разницу в минутах.
– Затем усреднить по всем пользователям.
Почему первого buy? Пользователь мог купить несколько раз. Нас интересует первый раз, когда он купил после входа.
Шаг 2. Отфильтруем нужные действия.
Возьмём только действия enter и buy.
text
– WHERE action IN (’enter’, ’buy’) – оставляем только нужные действия.
– Упорядочиваем по пользователю и времени, чтобы видеть последовательность.
Шаг 3. Добавим предыдущее действие с LAG.
Для каждого действия посмотрим, что было до него.
text
– LAG (dt_tm) OVER (PARTITION BY user_id ORDER BY dt_tm) – для каждой строки берём время предыдущего действия того же пользователя.
– LAG (action) … – берём предыдущее действие.
– Для самой первой строки пользователя предыдущего действия нет → будет NULL.
Шаг 4. Оставим только пары enter → buy.
Нас интересуют строки, где текущее действие buy, а предыдущее – enter.
text
– WHERE action = ’buy’ AND prev_action = ’enter’ – оставляем только те строки, где текущее действие buy, а предыдущее – enter.
– Теперь у нас есть пары (время входа, время покупки).
Пример вывода (фрагмент):
text
Шаг 5. Посчитаем разницу в минутах.
Используем EXTRACT (epoch FROM …) для перевода разницы в секунды, затем делим на 60.
– buy_time – enter_time – получаем интервал (тип interval).
– EXTRACT (epoch FROM …) – переводит интервал в секунды.
– / 60 – делим на 60, получаем минуты.
Что такое epoch? Epoch – это количество секунд с 1970-01-01. Для интервала EXTRACT (epoch) возвращает общее количество секунд в интервале.
Пример вывода (фрагмент):
text
Шаг 6. Посчитаем среднее время.
text
– 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 Кошелек, бонусными картами или другим удобным Вам способом.