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

Бесплатный фрагмент закончился.

Купите книгу, чтобы продолжить чтение.