# SQL для аналитика. 50 задач с решениями на PostgreSQL

Максим Чалышев

50 задач на PostgreSQL для аналитика. Конверсия, LTV, retention, воронка, DAU, оконные функции, JSONB, полнотекстовый поиск и другие метрики. Готовые SQL-запросы с пояснениями. База данных в комплекте.

© Максим Чалышев, 2026


ISBN 978-5-0069-9177-4

Создано в интеллектуальной издательской системе Ridero

## SQL для аналитика. 50 задач с решениями на PostgreSQL

ПОСВЯЩАЕТСЯ

моим коллегам-аналитикам


Тем, кто каждый день борется с хаосом данных

и не сдается

### Введение

Для кого эта книга

Эта книга написана аналитиком данных с многолетним опытом работы. Она предназначена для моих коллег, но также будет полезна разработчикам, маркетологам, студентам 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. Удачи!

## Часть 1. Установка. Повторение SQL

### Глава 1. Устанавливаем PostgreSQL и pgAdmin

#### Готовим рабочее место

Чтобы выполнять примеры из книги, понадобится установить 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

Запустите скачанный файл. Обычно он лежит в папке «Загрузки» и называется вроде 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, чтобы не вводить пароль каждый раз.


Если всё прошло успешно – вы увидите структуру сервера в левой панели. Иногда требуется повторить подключение дважды: первый запуск может быть долгим.


#### Первое знакомство с pgAdmin

Для работы с запросами используйте 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

SELECT current_user;

Напишите этот запрос в редакторе, нажмите Execute (как Play) – и увидите данные из таблицы.

#### Что в итоге

Если всё сделали правильно – переходите к главе 2. Если нет – перечитайте шаг 4 про пароль, чаще всего ошибка там.

### Глава 2. Устанавливаем схему. Что есть в нашей схеме

Для дальнейшей работы нам нужно создать демо-базу данных и заполнить её информацией.

#### Что скачиваем

Вам понадобится загрузить файл market. sql. Он находится в специальном репозитории на GitHub.

Ссылка на репозиторий: https://github.com/atvcross/postgresql_analyst

Как скачать файл:

– Нажмите на зелёную кнопку Code

– Выберите Download ZIP

– Распакуйте архив в любую папку

– Внутри найдите файл market. sql

#### Устанавливаем схему через pgAdmin

– Откройте 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

SELECT COUNT (*) FROM users;

Должно вернуться 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’ и выполните скрипт заново.

### Глава 3. Повторяем базовый SQL (CREATE, INSERT, SELECT, UPDATE, DELETE)

#### Описание задачи

Повторим основные знакомые команды SQL. Для этого создадим таблицу-пример и заполним её данными. Потом попробуем обновить или удалить некоторые записи.

Так мы сможем потренировать и вспомнить основные команды SQL на готовом примере.

#### Основные SQL-конструкции

Нам понадобятся следующие команды:

– CREATE TABLE – создаёт новую таблицу

– INSERT INTO – добавляет строки


– SELECT – показывает данные

– UPDATE – изменяет данные

– DELETE – удаляет строки


– WHERE – фильтрует строки для SELECT, UPDATE, DELETE (AND и OR – логика)

– LIKE – ищет по шаблону внутри WHERE


Расскажем кратко о каждой команде:

CREATE TABLE имя_таблицы (колонка1 ТИП, колонка2 ТИП)

Создаёт пустую таблицу с заданной структурой.

INSERT INTO имя_таблицы (колонки) VALUES (значения)

Добавляет одну или несколько строк.

SELECT колонки FROM имя_таблицы WHERE условие

Показывает строки, которые подходят под условие. В условиях бывает логика – AND или OR.

UPDATE имя_таблицы SET колонка = значение WHERE условие

Изменяет заданные колонки, где строки подходят под условие.

DELETE FROM имя_таблицы WHERE условие

Удаляет строки из таблицы, подходящие под условие.


WHERE условие

Используется с SELECT, UPDATE, DELETE.

Возможные примеры:

text

SELECT * FROM test_table WHERE city = «Москва»/* выбираем все строки, где город Москва */

text

UPDATE users SET age = age +1 WHERE age> 18 /* обновляем возраст, где он больше 18 */

text

DELETE FROM temp_orders WHERE status IS NULL /* удаляем строки с пустым статусом */

Типичная ошибка

Забыть 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 «а%' найдёт и «Алексей», и «алексей».

#### Основные типы данных в PostgreSQL

– 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).

#### SQL-запросы с объяснением

Создаём таблицу.

text

CREATE TABLE IF NOT EXISTS test_users (
user_id SERIAL PRIMARY KEY,
name VARCHAR (50),
city VARCHAR (50),
status VARCHAR (20)
);

Как это работает

– SERIAL – user_id будет заполняться автоматически (1, 2, 3, 4…).

– PRIMARY KEY – уникальный идентификатор каждой строки, не должен быть пустым и не должен дублироваться.

– VARCHAR (50) – имя и город не длиннее 50 символов.

– VARCHAR (20) – статус не длиннее 20 символов.


Вставляем данные.

text

INSERT INTO test_users (name, city, status) VALUES
(«Алексей», «Москва», ’active’),
(«Мария», «Санкт-Петербург», ’active’),
(«Иван», «Казань», ’inactive’),
(«Ольга», «Москва», ’active’);

Как это работает

Каждая строка в скобках – одна запись. Значения перечислены в том же порядке, что и колонки после INTO: в name – «Алексей», в city – «Москва», в status – ’active’.


Теперь посмотрим, что получилось.

text

SELECT * FROM test_users;

Как это работает

Звёздочка означает «все колонки». Без WHERE – показываем все строки.


Отбор с WHERE и LIKE.

Найдём пользователей из Москвы или Казани:

text

SELECT * FROM test_users WHERE city = «Москва» OR city = «Казань»;

Найдём пользователей, у которых имя начинается на «А»:

text

SELECT * FROM test_users WHERE name LIKE «А%»;

Найдём пользователей, у которых имя заканчивается на «я»:

text

SELECT * FROM test_users WHERE name LIKE '%я»;

Редактируем данные.

Сделаем всех пользователей из Москвы активными.

text

UPDATE test_users
SET status = ’active’
WHERE city = «Москва»;

Как это работает

– WHERE city = «Москва» – выбираем строки, где город Москва.

– SET status = ’active’ – меняем статус на active.

Проверим

SELECT * FROM test_users;


Удаляем данные с условием.

Удалим пользователей со статусом inactive.

text

DELETE FROM test_users WHERE status = ’inactive’;

Как это работает

– 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% для всех сотрудников отдела «Аналитика».

### Глава 4. Повторяем продвинутый SQL (GROUP BY, JOIN, IN, BETWEEN)

#### Описание задачи

Продолжим повторение SQL и вспомним запросы с группировкой данных, объединением таблиц, а также IN и BETWEEN. Используется почти в каждой аналитической задаче.

Разберём, как GROUP BY собирает строки в группы, JOIN соединяет данные из разных таблиц, а IN с подзапросом проверяет вхождение в список значений. Узнаем про команду BETWEEN для проверки на диапазон значений.

#### Основные SQL-конструкции

GROUP BY – создаёт группы из строк с одинаковыми значениями.

Обычно используется вместе с агрегатными функциями:

– COUNT (*) – количество элементов в группе

– MAX (column) – максимальное значение в группе

– MIN (column) – минимальное значение в группе

– AVG (column) – среднее значение в группе

Пример: посчитать количество пользователей в каждой стране.

text

SELECT country, COUNT (*) AS user_count
FROM users
GROUP BY country;

Как это работает

– GROUP BY country – все строки с одинаковым country попадают в одну группу.

– COUNT (*) считает количество строк в каждой группе.


JOIN – объединяет две таблицы по ключу.

Основные виды JOIN:

– INNER JOIN – только строки, где есть совпадение в обеих таблицах

– LEFT JOIN – все строки из левой таблицы, даже если нет совпадения в правой

– RIGHT JOIN – все строки из правой таблицы

– FULL JOIN – все строки из обеих таблиц

Пример: вывести заказы вместе с именами пользователей.

text

SELECT o. order_id, u.user_name, o. order_date
FROM orders o
INNER JOIN users u ON o.user_id = u.user_id;

Как это работает

– o – псевдоним для таблицы orders

– u – псевдоним для таблицы users

– ON o.user_id = u.user_id – условие соединения


Типичная ошибка

Забыть условие JOIN. Если написать FROM orders o, users u без ON, получите декартово произведение (каждая строка из orders соединится с каждой строкой из users). Результат будет огромным и бессмысленным.

IN с подзапросом – проверяет, входит ли значение в результат подзапроса.

Пример: найти пользователей, которые делали заказы.

text

SELECT user_name
FROM users
WHERE user_id IN (SELECT DISTINCT user_id FROM orders);

Как это работает

– Подзапрос (SELECT DISTINCT user_id FROM orders) возвращает список user_id, которые есть в заказах. DISTINCT убирает дубли и возвращает только уникальные значения.

– WHERE user_id IN… оставляет только тех пользователей, чей user_id есть в этом списке.


IN со списком значений – проверяет, входит ли значение в заданный список.

Пример: найти пользователей из нескольких стран.

text

SELECT user_name, country
FROM users
WHERE country IN («Россия», «Беларусь», «Казахстан»);

Как это работает

– IN («Россия», «Беларусь», «Казахстан») – проверяет, совпадает ли country с одним из указанных значений.

– Это то же самое, что WHERE country = «Россия» OR country = «Беларусь» OR country = «Казахстан», но короче и понятнее.


Совет

Для коротких списков (до 5—7 значений) IN удобен и читаем. Если список большой, лучше использовать JOIN с временной таблицей.


BETWEEN – фильтр по интервалу значений, проверяет, входит ли значение в интервал чисел или дат.

Найти товары с ценой от 10000 до 50000 рублей.

text

SELECT product_id, name, price
FROM products
WHERE price BETWEEN 10000 AND 50000
ORDER BY price;

BETWEEN 10000 AND 50000 включает границы. То же самое что price> = 10000 AND price <= 50000.


Найти заказы, сделанные в феврале 2024 года.

text

SELECT order_id, user_id, order_date
FROM orders
WHERE order_date BETWEEN «2024-02-01» AND «2024-02-29» ORDER BY order_date;

Учтите, что в феврале 2024 было 29 дней (високосный год).

#### SQL-запросы с объяснением

Задача 1. Количество заказов по статусам.

Посчитаем, сколько заказов в каждом статусе.

text

SELECT status, COUNT (*) AS order_count
FROM orders
GROUP BY status
ORDER BY order_count DESC;

Как это работает

– GROUP BY status – группируем заказы по статусу.

– COUNT (*) – считаем количество в каждой группе.

– ORDER BY order_count DESC – сортируем от большего к меньшему.

Пример вывода (фрагмент):

text

’completed’ 79
’pending’ 11
’shipped’ 10
’cancelled’ 2

Задача 2. Сумма выручки по месяцам.

Посчитаем общую выручку по месяцам на основе завершённых заказов.

text

SELECT
DATE_TRUNC (’month’, order_date) AS month,
SUM (oi. quantity * oi.price_per_unit) AS revenue
FROM orders o
INNER JOIN order_items oi ON o. order_id = oi. order_id
WHERE o.status = ’completed’
GROUP BY DATE_TRUNC (’month’, order_date)
ORDER BY month;

Как это работает

– DATE_TRUNC (’month’, order_date) – обрезаем дату до первого дня месяца.

– INNER JOIN – соединяем заказы с их товарами.

– SUM (oi. quantity * oi.price_per_unit) – считаем общую сумму.

– GROUP BY month – группируем по месяцам.


Задача 3. Топ-5 пользователей по сумме покупок.

Найдём пользователей, которые потратили больше всего.

text

SELECT
u.user_id,
u.user_name,
SUM (oi. quantity * oi.price_per_unit) AS total_spent
FROM users u
INNER JOIN orders o ON u.user_id = o.user_id
INNER JOIN order_items oi ON o. order_id = oi. order_id
WHERE o.status = ’completed’
GROUP BY u.user_id, u.user_name
ORDER BY total_spent DESC
LIMIT 5;

Как это работает

– Два 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

SELECT user_id, user_name, country
FROM users
WHERE user_id NOT IN (
SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL
);

Как это работает

– Подзапрос собирает список всех user_id, которые есть в заказах.

– NOT IN оставляет пользователей, которых нет в этом списке.


Типичная ошибка

Если в подзапросе NOT IN встретится NULL, результат будет пустым. Поэтому внутри подзапроса всегда добавляйте WHERE user_id IS NOT NULL.


Задача 5. Пользователи, которые покупали хотя бы раз (через IN).

Найдём пользователей, у которых есть хотя бы один заказ.

text

SELECT user_id, user_name, country
FROM users
WHERE user_id IN (
SELECT DISTINCT user_id FROM orders
);

Как это работает

– Подзапрос собирает список всех user_id, которые есть в заказах.

– IN оставляет пользователей, которые есть в этом списке.


Задача 6. Пользователи из выбранных стран (IN со списком).

Найдём пользователей из России, Беларуси и Казахстана.

text

SELECT user_id, user_name, country
FROM users
WHERE country IN («Россия», «Беларусь», «Казахстан»)
ORDER BY country, user_name;

Как это работает

– IN («Россия», «Беларусь», «Казахстан») – оставляет только пользователей из этих трёх стран.

– ORDER BY country, user_name – сортируем сначала по стране, потом по имени.


Пример вывода (фрагмент):

text
user_id user_name country
4 ’dmitry_kozlov’ «Беларусь».
11 ’sergey_volkov’ «Беларусь».
12 ’aidar_kz’ «Казахстан».

#### Вопросы и ответы

В: Что будет, если в 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.

Результат отсортируйте по сумме от большей к меньшей.

### Глава 5. Повторяем UNION, INTERSECT, EXCEPT

#### Описание задачи

Повторим операторы, которые работают с множествами, используют результат двух и более запросов: UNION, INTERSECT, EXCEPT.

Эти команды могут объединять, пересекать и вычитать наборы строк.

Это особенно удобно, когда нужно сравнить два набора данных в одном отчёте или, наоборот, убрать лишние данные, или же найти данные, которые есть и в одном, и в другом наборе.

Такие задачи очень часто встречаются в работе аналитика.


Основные SQL-конструкции

UNION – объединяет результаты двух запросов, убирая дубликаты.

UNION ALL – объединяет результаты, оставляя дубликаты.

INTERSECT – возвращает строки, которые есть в обоих запросах.

EXCEPT – возвращает строки из первого запроса, которых нет во втором.


Важные требования:

– Количество колонок в запросах должно совпадать.

– Типы данных колонок должны быть совместимы.

– Порядок колонок имеет значение.

Примеры:

– UNION: пользователи, которые сделали заказы ИЛИ оставили отзывы.

– INTERSECT: пользователи, которые сделали заказы И оставили отзывы.

– EXCEPT: пользователи, которые сделали заказы, НО не оставили отзывы.

Совет

UNION убирает дубликаты и сортирует результат. Сначала объединяет данные, потом убирает дубли. Если это не важно, используйте UNION ALL – он будет работать быстрее.


Представьте, вам нужен отчёт: посмотреть заказы из Москвы и Казани. Используем UNION.

Отдельно выбираем заказы пользователей из Москвы, отдельно из Казани, объединяем.

text

SELECT o. order_id, o. order_date, u.user_name, u.city
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE u.city = «Москва»/*фильр Мсоква*/
UNION
SELECT o. order_id, o. order_date, u.user_name, u.city
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE u.city = «Казань»  ORDER BY order_date;

Как это работает

– Первый запрос – заказы пользователей из Москвы.

– Второй запрос – заказы пользователей из Казани.

– UNION объединяет результаты и убирает дубликаты (если пользователь из Москвы сделал заказ и как-то попал в Казань – но такого не будет).

– ORDER BY применяется к общему результату.

Пример вывода (фрагмент):

order_id 3, order_date 2024-01-10, user_name alex_ivanov, city Москва
order_id 7, order_date 2024-01-25, user_name ivan_sokolov, city Казань

#### SQL-запросы с объяснением

Задача 1. Пользователи, которые и покупали, и оставляли отзывы (INTERSECT).

text

SELECT user_id FROM orders
INTERSECT
SELECT user_id FROM user_comments;

Как это работает

– Первый запрос – все user_id из заказов.

– Второй запрос – все user_id из отзывов.

– INTERSECT оставляет только тех, кто есть в обоих списках.


Типичная ошибка

INTERSECT требует совпадения по всем колонкам. Если добавить в SELECT лишнюю колонку (например, order_date), результат может стать пустым, потому что дата заказа не совпадёт с датой отзыва.


Задача 2. Пользователи, которые покупали, но не оставляли отзывы (EXCEPT).

text

SELECT user_id FROM orders
EXCEPT
SELECT user_id FROM user_comments;

Как это работает

– Первый запрос – все user_id из заказов.

– Второй запрос – все user_id из отзывов.

– EXCEPT убирает из первого списка тех, кто есть во втором.


Задача 3. Сравнение двух способов: UNION vs UNION ALL.

text

SELECT country FROM users
WHERE country = «Россия» UNION
SELECT country FROM users WHERE country = «Россия»;

Вернёт одну строку: «Россия».

text

SELECT country FROM users WHERE country = «Россия»/*1*/
UNION ALL
SELECT country FROM users WHERE country = «Россия»;

Вернёт столько строк, сколько пользователей из России (каждый дубль сохранится).

#### Вопросы и ответы

В: Чем UNION отличается от UNION ALL?

О: UNION убирает дубликаты и сортирует результат. UNION ALL оставляет все строки как есть и работает быстрее.


В: Какой порядок выполнения у UNION, INTERSECT, EXCEPT?

О: Все операторы имеют одинаковый приоритет. Для управления порядком используйте скобки.


В: Можно ли комбинировать больше двух запросов?

О: Да. SELECT… UNION SELECT… INTERSECT SELECT … – но лучше использовать скобки, чтобы явно задать порядок.

#### Что в итоге

Мы научились:

– Объединять результаты запросов с UNION и UNION ALL.

– Находить пересечения с INTERSECT.

– Вычитать множества с EXCEPT.

То есть освоили все команды работы с множествами.

Эти операторы удобны для сравнения списков и подготовки отчётов.

#### * Задание со звёздочкой

Напишите запрос, который выводит user_id пользователей, которые оставляли отзывы, но никогда не покупали товары из категории «Смартфоны».

Используйте EXCEPT и подзапрос с JOIN.

## Часть 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.

### Глава 7. JOIN, NOT EXISTS. Пользователи без покупок

#### Описание задачи

Представьте: утро понедельника. Менеджер по маркетингу подходит с просьбой – «Дай список пользователей, которые зарегистрировались, но ничего не купили. Будем отправлять промокоды».

Задача простая. Но если сделать её правильно – маркетологи перестанут спамить всем подряд и начнут работать точечно.

Что нужно выгрузить:

– user_id

– user_name

– registered_at

– country

Только тех, у кого ноль покупок.

#### Основные SQL-конструкции

Для решения нам пригодятся JOIN и IS NULL (эти команды уже разобраны в главе 4), а также новый оператор NOT EXISTS. Сейчас с ним познакомимся.

NOT EXISTS проверяет, что подзапрос не вернул ни одной строки.

text

SELECT columns
FROM table_a a
WHERE NOT EXISTS (
SELECT 1
FROM table_b b
WHERE b.key = a.key
);

Как это работает

Берём строку из table_a. Проверяем, есть ли её ключ (WHERE b.key = a.key) в table_b. Ничего не находим – оставляем. Если находим – не выводим (так работает, потому что есть NOT).


Запомните: NOT EXISTS проверяет отсутствие. Если подзапрос вернул хотя бы одну строку – строка из внешнего запроса не попадёт в результат.

#### SQL-запросы с объяснением

Способ 1. LEFT JOIN + IS NULL

text

SELECT
u.user_id,
u.user_name,
u.registered_at,
u.country
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
WHERE o. order_id IS NULL;

Как это работает

Запрос берёт всех пользователей из users. Присоединяет к ним заказы из orders по user_id.

Если у пользователя нет заказов – все колонки из orders становятся NULL.

Условие WHERE o. order_id IS NULL оставляет только таких пользователей.

Почему проверяем именно order_id? Потому что это первичный ключ таблицы orders. Если он NULL – значит, строки из orders нет вообще.


Способ 2. NOT EXISTS (рекомендуется)

text

SELECT
u.user_id,
u.user_name,
u.registered_at,
u.country
FROM users u
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.user_id
);

Как это работает

Для каждого пользователя запрос проверяет: есть ли в orders хотя бы одна запись с таким user_id?

Если есть – пользователь исключается. Если нет – попадает в результат.

Обратите внимание на SELECT 1 внутри подзапроса. EXISTS не смотрит на содержимое строк, ему важно только их наличие. Поэтому SELECT 1 – стандартное соглашение, оно работает быстрее, чем SELECT *.

Запомните: NOT EXISTS читается как «таких, для которых не существует записей в…».


Способ 3. NOT IN (с осторожностью)

text

SELECT
user_id,
user_name,
registered_at,
country
FROM users
WHERE user_id NOT IN (
SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL);

Как это работает

Подзапрос собирает список всех user_id, которые есть в orders. Основной запрос выбирает пользователей, чей user_id отсутствует в этом списке.

Важное предупреждение: Если в подзапросе встретится хотя бы один NULL – NOT IN вернёт пустой результат. Именно поэтому мы добавили WHERE user_id IS NOT NULL.

На заметку: NOT IN работает, но требует осторожности. На собеседованиях часто спрашивают про этот нюанс с NULL.

#### Вопросы и ответы

В: А какой способ быстрее?

О: Для больших таблиц – NOT EXISTS. Он оптимизирован для проверки наличия строк и не требует сборки полного списка user_id.

В: А что, если у пользователя заказ, но он отменён?

О: В текущих запросах такой пользователь не попадёт в результат – ведь запись в orders есть.

Если нужны только успешные заказы, добавьте условие в подзапрос:

text

WHERE NOT EXISTS (
SELECT 1 FROM orders o
WHERE o.user_id = u.user_id AND o.status = ’completed’/*завершенные*/
)

В: Почему результат пустой, хотя я знаю, что есть пользователи без заказов?

О: Три самые частые причины:

– В таблице orders есть строки с user_id = NULL (для NOT IN это фатально)

– Нарушены внешние ключи – в orders есть user_id, которого нет в users

– Опечатка в названии таблиц или колонок

#### Что в итоге

Что мы узнали в этой главе:

– LEFT JOIN + IS NULL – поняли, как работает присоединение с проверкой на пустоту

– NOT EXISTS – освоили самый эффективный способ поиска отсутствующих записей

– NOT IN – узнали про подводный камень с NULL

Теперь вы умеете находить пользователей, которые зарегистрировались, но не купили. Задача, которая встречается в работе постоянно.

Если всё сделали правильно – переходите к главе 8.

#### * Задание со звёздочкой

Напишите запрос, который находит пользователей, зарегистрировавшихся в 2024 году, но не сделавших ни одного заказа со статусом ’completed’. Используйте NOT EXISTS.

### Глава 8. GROUP BY, AVG. Средний чек по категориям

#### Описание задачи

В отчёте нужны данные, какой у нас средний чек по каждой категории товаров. То есть какие категории приносят больше денег за одну покупку.

Таблицы: orders, order_items, products, categories.

Нужно вывести:

– название категории

– среднюю сумму заказа в рублях

Только по завершённым заказам (status = ’completed’).

#### Основные SQL-конструкции

GROUP BY – группирует строки с одинаковыми значениями (проходили, глава 4).


AVG – агрегатная функция, она считает среднее арифметическое в заданной колонке по группе.

text

AVG (числовое_поле)

Важно: AVG игнорирует NULL. Если в группе все значения NULL, результат будет NULL.

Совет

Если нужно среднее с учётом NULL как нулей, используйте AVG (COALESCE (price, 0)). COALESCE проверяет: если price равен NULL, возвращает 0.

#### SQL-запросы с объяснением

Задача 1. Средний чек по категориям.

text

SELECT
c.name AS category_name,
AVG(oi.price_per_unit * oi. quantity)::decimal (10,2) AS avg_order_value
FROM orders o
JOIN order_items oi ON o. order_id = oi. order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
WHERE o.status = ’completed’/*завершенные*/
GROUP BY c.category_id, c.name
ORDER BY avg_order_value DESC;

Как это работает

– Присоединяем заказы → товары в заказе → продукты → категории

– Отбираем только завершённые заказы

– Группируем по категории

– Считаем среднюю сумму заказа в каждой группе

– Сортируем от самой дорогой категории к дешёвой


Мы используем INNER JOIN (просто JOIN), потому что категории без заказов нас не интересуют. Если нужны и они, замените JOIN на LEFT JOIN.


Что выводит запрос: две колонки – category_name (название категории) и avg_order_value (средняя сумма заказа в рублях).

Пример вывода (фрагмент):

text

«Ноутбуки» 121945.45
«Смартфоны» 77651.72
«Планшеты» 69900.00

Задача 2. Средний чек с количеством заказов.

Добавим информацию о том, сколько заказов попало в расчёт.

text

SELECT
c.name AS category_name,
COUNT (DISTINCT o. order_id) AS orders_count,
AVG(oi.price_per_unit * oi. quantity)::decimal (10,2) AS avg_order_value
FROM orders o
JOIN order_items oi ON o. order_id = oi. order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
WHERE o.status = ’completed’
GROUP BY c.category_id, c.name
ORDER BY avg_order_value DESC;

Как это работает

Добавили 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 года. Используйте фильтр по дате и группировку.

Подсказка: добавьте в WHERE условие на order_date BETWEEN «2024-02-01» AND «2024-02-29» – .

### Глава 9. GROUP BY, COUNT. Повторные покупки

#### Описание задачи

Составим отчёт о пользователях, которые совершили больше одной покупки. Иными словами – найти тех, кто возвращается в интернет магазин.

Узнать, сколько пользователей купили дважды, трижды и так далее.

#### Основные SQL-конструкции

COUNT – агрегатная функция, считает количество строк в группе.

text

COUNT (*) – считает все строки, включая NULL
COUNT (column) – считает только непустые значения в колонке
COUNT (DISTINCT column) – считает уникальные значения

HAVING – фильтрует группы после группировки (как WHERE, но для GROUP BY).

text

SELECT category, COUNT (*) AS cnt
FROM products
GROUP BY category
HAVING COUNT (*)> 5;

Типичная ошибка

Использовать WHERE вместо HAVING для фильтрации по агрегатным функциям. WHERE выполняется до группировки и не видит COUNT, SUM, AVG. Если есть группировка и нужно отфильтровать сгруппированные данные – всегда используем HAVING.

#### SQL-запросы с объяснением

Задача 1. Количество заказов по пользователям.

Посчитаем, сколько заказов сделал каждый пользователь.

text

SELECT
u.user_id,
u.user_name,
COUNT (o. order_id) AS orders_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.user_name
ORDER BY orders_count DESC;

Как это работает

– LEFT JOIN – оставляем всех пользователей, даже без заказов.

– COUNT (o. order_id) – считает только непустые заказы (у пользователей без заказов будет 0).

– GROUP BY – группируем по пользователю.

– ORDER BY orders_count DESC – сортируем от частых покупателей к редким.

Пример вывода (фрагмент):

text

user_id user_name orders_count
1 alex_ivanov 9
14 lucas_silva 8
5 nurlan_kz 8

Задача 2. Пользователи с повторными покупками (больше одного заказа).

Используем HAVING, чтобы отфильтровать группы.

text

SELECT
u.user_id,
u.user_name,
COUNT (o. order_id) AS orders_count
FROM users u
JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id, u.user_name
HAVING COUNT (o. order_id)> 1
ORDER BY orders_count DESC;

Как это работает

– INNER JOIN – берём только пользователей с заказами (те, у кого 0 заказов, не нужны).
– HAVING COUNT (o. order_id)> 1 – оставляем только тех, у кого заказов больше одного.
– Сортировка от самых активных.

Пример вывода (фрагмент):

text
user_id user_name orders_count
1 alex_ivanov 9
14 lucas_silva 8
5 nurlan_kz 8
10 armen_grigoryan 8

Совет

Если нужно посчитать пользователей с повторными покупками в процентах, можно использовать CTE:

text

WITH repeat_buyers AS (
SELECT user_id
FROM orders
GROUP BY user_id
HAVING COUNT (*)> 1
)
SELECT
COUNT (*) AS repeat_buyers,
(SELECT COUNT (*) FROM users) AS total_users,
ROUND (100.0 * COUNT (*) / (SELECT COUNT (*) FROM users), 2) AS percent
FROM repeat_buyers;

Задача 3. Распределение пользователей по количеству покупок.

Сколько пользователей купили 1 раз, 2 раза, 3 раза и так далее.

text

WITH order_counts AS
(
SELECT
u.user_id,
COUNT (o. order_id) AS orders_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id
GROUP BY u.user_id
)
SELECT
orders_count,
COUNT (*) AS user_count
FROM order_counts
GROUP BY orders_count
ORDER BY orders_count;

Как это работает

– CTE order_counts – считает количество заказов для каждого пользователя.

– Основной запрос группирует пользователей по числу заказов.

Пример вывода:

text

orders_count user_count
5 2
6 4
7 5
8 3
9 1

#### Вопросы и ответы

В: Чем 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.

### Глава 10. GROUP BY, SUM. LTV пользователя

#### Описание задачи

LTV (Lifetime Value) – общая сумма денег, потраченная пользователем в магазине за всё время. Это ключевая метрика для оценки рейтинга клиента.

Всегда нужно знать: кто приносит больше всего денег, чтобы фокусироваться на удержании ценных клиентов, предложить скидки, подарить купон на день рождения.

#### Основные SQL-конструкции

SUM – агрегатная функция, суммирует значения в группе. Работает только для числовых колонок.

text

SUM (числовое_поле)

GROUP BY – группирует строки с одинаковыми значениями (разобрано в главе 4).


COALESCE – возвращает первое не-NULL значение. Полезно для замены NULL на 0.

text

COALESCE (column, 0) – если column NULL, вернёт 0

Совет

При суммировании лучше использовать SUM (COALESCE (amount, 0)), чтобы избежать NULL в результате. Но в нашей схеме суммы не NULL.

#### SQL-запросы с объяснением

Задача 1. LTV каждого пользователя (все заказы).

Посчитаем, сколько всего денег потратил каждый пользователь по завершённым заказам.

text

SELECT
u.user_id,
u.user_name,
SUM (oi. quantity * oi.price_per_unit) AS ltv
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN order_items oi ON o. order_id = oi. order_id
WHERE o.status = ’completed’
GROUP BY u.user_id, u.user_name
ORDER BY ltv DESC;

Как это работает

– Присоединяем заказы и товары в заказах.

– Фильтруем только завершённые заказы.

– Группируем по пользователю.

– Суммируем стоимость всех купленных товаров.

– Сортируем от самого ценного клиента к менее ценным.

Пример вывода (фрагмент):

text

user_id user_name ltv
1 alex_ivanov 543000
5 nurlan_kz 324000
2 li_wei 149800
3 aram_sargsyan 120000

Задача 2. LTV с учётом только успешных платежей (через payments).

Иногда удобнее считать LTV по таблице платежей, а не по order_items (например, если есть возвраты).

text

SELECT
u.user_id,
u.user_name,
SUM(p.amount) AS ltv
FROM users u
JOIN payments p ON u.user_id = p.user_id
GROUP BY u.user_id, u.user_name
ORDER BY ltv DESC;

Как это работает

– Присоединяем платежи напрямую к пользователям.

– Суммируем суммы платежей.

– Группируем по пользователю.

Типичная ошибка

Забыть GROUP BY при использовании SUM. Без группировки SUM сложит все значения по всей таблице, а не по пользователям.


Задача 3. Средний LTV по странам.

Узнаем, в какой стране пользователи в среднем тратят больше.

text

SELECT
u.country,
COUNT (DISTINCT u.user_id) AS user_count,
SUM (oi. quantity * oi.price_per_unit) AS total_revenue,
ROUND (AVG (oi. quantity * oi.price_per_unit), 2) AS avg_ltv
FROM users u
JOIN orders o ON u.user_id = o.user_id
JOIN order_items oi ON o. order_id = oi. order_id
WHERE o.status = ’completed’
GROUP BY u.country
ORDER BY avg_ltv DESC;

Как это работает

– Группируем по стране.

– Считаем количество уникальных пользователей в стране.

– Общую выручку по стране.

– AVG считает средний LTV по стране.

Пример вывода (фрагмент):

text

country user_count total_revenue avg_ltv
Армения 2 240000 120000.00
Россия 5 500000 100000.00
Казахстан 2 150000 75000.00
Египет 1 50000 50000.00

#### Вопросы и ответы

В: Чем 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 – ключевую метрику для анализа ценности клиента

### Глава 11. DATE_TRUNC, SUM, кросс-таблицы. Выручка по месяцам

#### Описание задачи

Новый отчёт: посчитаем общую выручку по месяцам на основе завершённых заказов. А затем представим эти данные в виде кросс-таблицы, где месяцы – строки, а категории товаров – колонки.

Таблицы: orders, order_items, products, categories.

#### Основные SQL-конструкции

DATE_TRUNC – обрезает дату до указанной точности (год, месяц, день, час).

text

DATE_TRUNC (’month’, order_date) /* 2024-01-15 → 2024-01-01 */

SUM – агрегатная функция, суммирует значения (см. главу 10).


crosstab – функция из расширения PostgreSQL tablefunc. Превращает строки в колонки. В функцию передаётся в кавычках запрос, который нам надо перевернуть.

Для использования crosstab нужно установить расширение:

text

CREATE EXTENSION IF NOT EXISTS tablefunc;

Совет

DATE_TRUNC удобен для группировки по периодам: неделям, месяцам, кварталам, годам.

#### SQL-запросы с объяснением

Задача 1. Выручка по месяцам.

text

SELECT
DATE_TRUNC (’month’, order_date) AS month,
SUM (oi. quantity * oi.price_per_unit) AS revenue
FROM orders o
JOIN order_items oi ON o. order_id = oi. order_id
WHERE o.status = ’completed’
GROUP BY DATE_TRUNC (’month’, order_date)
ORDER BY month;
Как это работает
– DATE_TRUNC (’month’, order_date) – превращает дату в первый день месяца.
– JOIN – соединяем заказы с их товарами.
– SUM – считаем общую выручку.
– GROUP BY – группируем по месяцам.
– ORDER BY month – сортируем по возрастанию.

Пример вывода (фрагмент):

text

month revenue
2024-01-01 00:00:00+03 1571090.00
2024-02-01 00:00:00+03 1458330.00
2024-03-01 00:00:00+03 1407930.00
2024-04-01 00:00:00+03 1193490.00

Задача 2. Выручка по месяцам и категориям (обычный GROUP BY).

Подготовим данные для кросс-таблицы: выручка по месяцам и категориям.

text

SELECT
DATE_TRUNC (’month’, o. order_date) AS month,
c.name AS category_name,
SUM (oi. quantity * oi.price_per_unit) AS revenue
FROM orders o
JOIN order_items oi ON o. order_id = oi. order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
WHERE o.status = ’completed’
GROUP BY month, c.category_id, c.name
ORDER BY month, category_name;

Как это работает

– Группируем по месяцу и категории.

– Считаем выручку для каждой пары (месяц, категория).

Пример вывода (фрагмент):

text

month category_name revenue
2024-01-01 00:00:00+03 Женская 11970.00
2024-01-01 00:00:00+03 Мужская 11970.00
2024-01-01 00:00:00+03 Наушники 79700.00
2024-01-01 00:00:00+03 Ноутбуки 689600.00
2024-01-01 00:00:00+03 Планшеты 69900.00

Задача 3. Кросс-таблица: месяцы в строках, категории в колонках.

Превращаем результат задачи 2 в таблицу, где категории становятся колонками, используем crosstab из расширения tablefunc.

text

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab (
«SELECT
DATE_TRUNC (»’month’», o. order_date)::date AS month,
c.name AS category_name,
SUM (oi. quantity * oi.price_per_unit) AS revenue
FROM orders o
JOIN order_items oi ON o. order_id = oi. order_id
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
WHERE o.status = '’completed’'/*выполнено*/
GROUP BY month, c.category_id, c.name
ORDER BY 1,2»,
«SELECT DISTINCT name FROM categories WHERE name IN (»«Ноутбуки»», ««Смартфоны»», ««Планшеты»») ORDER BY 1»)
AS ct (month date, «Ноутбуки» numeric, «Смартфоны» numeric, «Планшеты» numeric);

Как это работает

– Первый запрос – данные: месяц, категория, выручка.

– Второй запрос – список категорий, которые станут колонками.

– crosstab превращает строки в колонки AS ct (month date, «Ноутбуки» numeric, «Смартфоны» numeric, «Планшеты» numeric).

– ::date – преобразуем timestamp в дату.

– Кавычки в названиях колонок нужны из-за русских букв.

– Две одинарные кавычки подряд используются для экранирования кавычек внутри строки crosstab.

результат

«2024-01-01» 689600.00 69900.00 699000.00
«2024-02-01» 489500.00 209700.00 594200.00
«2024-03-01» 799400.00 209700.00 354500.00
«2024-04-01» 614400.00 69900.00 449400.00
«2024-05-01» 89900.00 null 154800.00

#### Вопросы и ответы

В: Зачем нужна кросс-таблица?

О: Она компактно показывает данные: строки – месяцы, колонки – категории. Удобно для отчётов и дашбордов.


В: Почему во втором запросе crosstab указаны не все категории?

О: Чтобы колонок было не слишком много. В реальных отчётах выбирают топ-5 или топ-10 категорий.


В: Что делать, если в какой-то месяц не было продаж по категории?

О: В примере выше будет 0.00 или NULL.


В: Можно ли использовать crosstab без второго запроса?

О: Да, но тогда категории нужно перечислить вручную в определении колонок, а запрос должен возвращать данные в строгом порядке.

#### Что в итоге

Мы научились:

– Группировать данные по месяцам с DATE_TRUNC

– Считать выручку по месяцам и категориям

– Строить кросс-таблицы с помощью crosstab

Кросс-таблицы удобны для отчётов, где нужно сравнить показатели по разным категориям в динамике.

#### * Задание со звёздочкой

Напишите запрос, который строит кросс-таблицу выручки по кварталам (используйте DATE_TRUNC (’quarter’, order_date)) и категориям «Ноутбуки», «Смартфоны», «Планшеты».

Подсказка: замените ’month’ на ’quarter’ в первом запросе crosstab.

### Глава 12. Аналитические функции. Топ-5 товаров

#### Описание задачи

Представьте, что нам нужны 5 товаров, которые принесли больше всего выручки. Это классическая задача для отчётов и дашбордов.

Таблицы: order_items, products.

#### Основные SQL-конструкции

Аналитические (оконные) функции – выполняют вычисления по набору строк, связанных с текущей строкой. В отличие от GROUP BY, они не сворачивают строки в одну.

ROW_NUMBER () – присваивает уникальный номер каждой строке в рамках окна (партиции).

Синтаксис:

text

ROW_NUMBER () OVER (PARTITION BY колонка ORDER BY колонка)

– PARTITION BY – делит строки на группы (если не указано, всё окно)

– ORDER BY – задаёт порядок нумерации внутри группы

Совет

ROW_NUMBER () часто используют для нумерации строк в отчётах и для отбора топ-N записей в каждой категории.

#### SQL-запросы с объяснением

Задача 1. Топ-5 товаров по выручке (простой способ).

text

SELECT
p.product_id,
p.name,
SUM (oi. quantity * oi.price_per_unit) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY revenue DESC
LIMIT 5;

Как это работает

– Группируем по товару.

– Считаем выручку.

– Сортируем от большего к меньшему.

– LIMIT 5 оставляет только первые 5 строк.

Пример вывода:

text

product_id name revenue
4 MacBook Pro 14 2398800.00
5 Lenovo ThinkPad 1078800.00
1 iPhone 15 1038700.00
2 Samsung Galaxy S24 973700.00
11 iPad Air 699000.00

Задача 2. Топ-5 товаров с нумерацией (ROW_NUMBER).

Добавим нумерацию строк.

text

SELECT
ROW_NUMBER () OVER (ORDER BY SUM (oi. quantity * oi.price_per_unit) DESC) AS rank,
p.product_id,
p.name,
SUM (oi. quantity * oi.price_per_unit) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_id, p.name
ORDER BY revenue DESC
LIMIT 5;

Как это работает

– ROW_NUMBER () OVER (ORDER BY SUM (…) DESC) – нумерует строки от 1 до N в порядке убывания выручки.

– Остальное как в задаче 1.


Задача 3. Топ-3 товара в каждой категории (ROW_NUMBER с PARTITION BY).

Более сложный пример: для каждой категории найти 3 самых продаваемых товара.

text

WITH product_revenue AS (
SELECT
c.name AS category_name,
p.product_id,
p.name AS product_name,
SUM (oi. quantity * oi.price_per_unit) AS revenue
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
GROUP BY c.category_id, c.name, p.product_id, p.name
),
ranked AS (
SELECT
category_name,
product_name,
revenue,
ROW_NUMBER () OVER (PARTITION BY category_name ORDER BY revenue DESC) AS rank
FROM product_revenue
)
SELECT
category_name,
product_name,
revenue,
rank
FROM ranked
WHERE rank <= 3
ORDER BY category_name, rank;

Как это работает

– product_revenue – считаем выручку по товарам с категориями.

– ranked – нумеруем товары внутри каждой категории (PARTITION BY category_name) по убыванию выручки.

– WHERE rank <= 3 – оставляем только топ-3 в каждой категории.

Пример вывода (фрагмент):

text

category_name product_name revenue rank
Женская Сумка женская 29940.00 1
Женская Платье летнее 20940.00 2
Мужская Джинсы мужские 27930.00 1
Мужская Футболка хлопок 5970.00 2
Наушники Наушники Sony WH-1000XM5 179400.00 1
Наушники Наушники AirPods Pro 174300.00 2

#### Вопросы и ответы

В: Чем 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 …).

Подсказка: возьмите за основу задачу 3 и измените условие фильтрации rank <= 2.

### Глава 13. Аналитические функции. Retention 7 дней

#### Описание задачи

Retention – это способность продукта возвращать пользователей. Он показывает, какая доля пользователей вернулась на следующий день, через неделю, через месяц.

Мы посчитаем retention за 7 дней: сколько пользователей, зарегистрировавшихся в определённый день, совершили хотя бы одно действие (например, покупку или вход) в каждый из следующих дней в течение 7 дней после регистрации.

Таблицы: users, users_log.

#### Основные SQL-конструкции

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 считают не только по дням, но и по неделям, месяцам. Формула одна и та же, меняется только период группировки.

#### SQL-запросы с объяснением

Задача 1. Подготовка данных: уникальные действия пользователей по дням.

Сначала найдём все дни, когда пользователь был активен.

text

SELECT DISTINCT
user_id,
DATE (dt_tm) AS activity_date
FROM users_log
ORDER BY user_id, activity_date;
Как это работает
– DATE (dt_tm) – отбрасываем время, оставляем только дату.
– DISTINCT – убираем дубли (несколько действий в один день).

Пример вывода (фрагмент):

text

user_id activity_date
1 2024-01-15
1 2024-02-01
2 2024-02-10
2 2024-02-15
3 2024-01-20

Задача 2. Добавляем дату регистрации.

Для расчёта retention нужно знать, когда пользователь зарегистрировался.

text

SELECT
u.user_id,
u.registered_at::date AS reg_date,
DATE (l. dt_tm) AS activity_date
FROM users u
JOIN users_log l ON u.user_id = l.user_id
GROUP BY u.user_id, reg_date, activity_date
ORDER BY u.user_id, activity_date;

Как это работает

– Присоединяем логи к пользователям.

– GROUP BY убирает дубли (то же, что DISTINCT, но явно).

– registered_at::date – преобразуем в дату.


Задача 3. Считаем дни после регистрации.

Для каждого действия посчитаем, на какой день после регистрации оно произошло.

text

WITH user_activity AS (
SELECT
u.user_id,
u.registered_at::date AS reg_date,
DATE (l. dt_tm) AS activity_date
FROM users u
JOIN users_log l ON u.user_id = l.user_id
GROUP BY u.user_id, reg_date, activity_date
)
SELECT
user_id,
reg_date,
activity_date,
(activity_date – reg_date) AS day_number
FROM user_activity
ORDER BY user_id, day_number;

Как это работает

– activity_date – reg_date – разница в днях (0 – день регистрации, 1 – следующий день и т.д.).

– CTE user_activity – подготовленные данные.


Задача 4. Считаем retention 7 дней.

Для каждого дня регистрации посчитаем, сколько пользователей вернулось в каждый из следующих дней.

text

WITH user_activity AS (
SELECT
u.user_id,
u.registered_at::date AS reg_date,
DATE (l. dt_tm) AS activity_date
FROM users u
JOIN users_log l ON u.user_id = l.user_id
GROUP BY u.user_id, reg_date, activity_date
),
day_numbers AS (
SELECT
reg_date,
(activity_date – reg_date) AS day_number,
COUNT (DISTINCT user_id) AS users
FROM user_activity
WHERE activity_date> = reg_date
AND activity_date <= reg_date +7
GROUP BY reg_date, day_number
)
SELECT
reg_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_rate
FROM day_numbers
ORDER 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_rate
2024-01-15 0 1 1 100.00
2024-01-18 0 1 1 100.00
2024-01-18 2 1 1 100.00
2024-01-20 0 1 1 100.00
2024-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

CASE
WHEN условие THEN значение1
ELSE значение2
END

В нашей задаче: если действие равно ’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 DISTINCT
user_id,
action
FROM users_log
)
SELECT * FROM user_actions ORDER BY user_id, action;

Как это работает

– DISTINCT убирает дубли (если пользователь сделал enter 5 раз, оставляем один).

– Теперь у нас есть список: пользователь X сделал действия A, B, C.

Пример вывода (реальные данные, фрагмент):

text
user_id action
1 add_to_cart
1 buy
1 enter
1 exit
1 register
1 show
2 add_to_cart
2 buy

Шаг 3. Добавим флаги для каждого действия с помощью CASE.

Теперь для каждого пользователя посчитаем флаги: 1 – если действие было, 0 – если нет. Маркируем действия пользователя 0 или 1 для удобного дальнейшего использования.

text

WITH user_actions AS (
SELECT DISTINCT
user_id,
action
FROM users_log
),
user_flags AS (
SELECT
user_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_buy
FROM user_actions
GROUP 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_buy
1 1 1 1 1
2 1 1 1 1
3 1 0 0 1
4 1 0 0 1

Шаг 4. Считаем воронку.

Теперь у нас есть флаги. Посчитаем, сколько пользователей дошло до каждого этапа.

text

WITH user_actions AS (
SELECT DISTINCT
user_id,
action
FROM users_log
),
user_flags AS (
SELECT
user_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_buy
FROM user_actions
GROUP BY user_id
)
SELECT
COUNT (*) 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_count
FROM 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 DISTINCT
user_id,
action
FROM users_log
),
user_flags AS (
SELECT
user_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_buy
FROM user_actions
GROUP BY user_id
),
funnel_counts AS (
SELECT
COUNT (*) 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_count
FROM user_flags
)
SELECT
total_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_cart
FROM 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 (
SELECT
user_id,
action,
dt_tm
FROM users_log
WHERE 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 (
SELECT
user_id,
action,
dt_tm
FROM users_log
WHERE action IN (’enter’, ’buy’)
),
with_prev AS (
SELECT
user_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_action
FROM 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 (
SELECT
user_id,
action,
dt_tm
FROM users_log
WHERE action IN (’enter’, ’buy’)
),
with_prev AS (
SELECT
user_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_action
FROM filtered_log
)
SELECT
user_id,
prev_dt_tm AS enter_time,
dt_tm AS buy_time
FROM with_prev
WHERE action = ’buy’ AND prev_action = ’enter’;

Как это работает

– WHERE action = ’buy’ AND prev_action = ’enter’ – оставляем только те строки, где текущее действие buy, а предыдущее – enter.

– Теперь у нас есть пары (время входа, время покупки).

Пример вывода (фрагмент):

text

user_id enter_time buy_time
1 2024-02-01 10:15:00 2024-02-01 10:30:00
2 2024-02-15 12:00:00 2024-02-15 12:15:00
3 2024-01-25 14:10:00 2024-01-25 14:15:00
4 2024-03-05 10:00:00 2024-03-05 10:30:00
5 2024-02-20 13:00:00 2024-02-20 13:30:00

Шаг 5. Посчитаем разницу в минутах.

Используем EXTRACT (epoch FROM …) для перевода разницы в секунды, затем делим на 60.

text
WITH filtered_log AS (
SELECT
user_id,
action,
dt_tm
FROM users_log
WHERE action IN (’enter’, ’buy’)
),
with_prev AS (
SELECT
user_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_action
FROM filtered_log
),
enter_buy_pairs AS (
SELECT
user_id,
prev_dt_tm AS enter_time,
dt_tm AS buy_time
FROM with_prev
WHERE action = ’buy’ AND prev_action = ’enter’
)
SELECT
user_id,
enter_time,
buy_time,
EXTRACT (epoch FROM (buy_time – enter_time)) / 60 AS minutes_diff
FROM enter_buy_pairs
ORDER 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_diff
1 2024-02-01 10:15:00 2024-02-01 10:30:00 15.00
2 2024-02-15 12:00:00 2024-02-15 12:15:00 15.00
3 2024-01-25 14:10:00 2024-01-25 14:15:00 5.00
4 2024-03-05 10:00:00 2024-03-05 10:30:00 30.00
5 2024-02-20 13:00:00 2024-02-20 13:30:00 30.00

Шаг 6. Посчитаем среднее время.

text

WITH filtered_log AS (
SELECT
user_id,
action,
dt_tm
FROM users_log
WHERE action IN (’enter’, ’buy’)
),
with_prev AS (
SELECT
user_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_action
FROM filtered_log
),
enter_buy_pairs AS (
SELECT
user_id,
prev_dt_tm AS enter_time,
dt_tm AS buy_time,
EXTRACT (epoch FROM (dt_tm – prev_dt_tm)) / 60 AS minutes_diff
FROM with_prev
WHERE action = ’buy’ AND prev_action = ’enter’
)
SELECT
COUNT (*) AS total_pairs,
ROUND (AVG (minutes_diff), 2) AS avg_minutes
FROM 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 не умеет работать напрямую с интервалами. Сначала переводим в секунды (число), считаем среднее, потом при желании переводим обратно в минуты.