SQL для аналитика. 50 задач с решениями на PostgreSQL
- -
- 100%
- +
© Максим Чалышев, 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_usersSET 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_countFROM usersGROUP BY country;Как это работает
– GROUP BY country – все строки с одинаковым country попадают в одну группу.
– COUNT (*) считает количество строк в каждой группе.
JOIN – объединяет две таблицы по ключу.
Основные виды JOIN:
– INNER JOIN – только строки, где есть совпадение в обеих таблицах
– LEFT JOIN – все строки из левой таблицы, даже если нет совпадения в правой
– RIGHT JOIN – все строки из правой таблицы
– FULL JOIN – все строки из обеих таблиц
Пример: вывести заказы вместе с именами пользователей.