SQL для аналитика. 50 задач с решениями на PostgreSQL
text
SELECT o. order_id, u.user_name, o. order_dateFROM orders oINNER 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_nameFROM usersWHERE 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, countryFROM usersWHERE country IN («Россия», «Беларусь», «Казахстан»);Как это работает
– IN («Россия», «Беларусь», «Казахстан») – проверяет, совпадает ли country с одним из указанных значений.
– Это то же самое, что WHERE country = «Россия» OR country = «Беларусь» OR country = «Казахстан», но короче и понятнее.
Совет
Для коротких списков (до 5—7 значений) IN удобен и читаем. Если список большой, лучше использовать JOIN с временной таблицей.
BETWEEN – фильтр по интервалу значений, проверяет, входит ли значение в интервал чисел или дат.
Найти товары с ценой от 10000 до 50000 рублей.
text
SELECT product_id, name, priceFROM productsWHERE price BETWEEN 10000 AND 50000ORDER BY price;BETWEEN 10000 AND 50000 включает границы. То же самое что price> = 10000 AND price <= 50000.
Найти заказы, сделанные в феврале 2024 года.
text
SELECT order_id, user_id, order_dateFROM ordersWHERE order_date BETWEEN «2024-02-01» AND «2024-02-29» ORDER BY order_date;Учтите, что в феврале 2024 было 29 дней (високосный год).
SQL-запросы с объяснением
Задача 1. Количество заказов по статусам.
Посчитаем, сколько заказов в каждом статусе.
text
SELECT status, COUNT (*) AS order_countFROM ordersGROUP BY statusORDER BY order_count DESC;Как это работает
– GROUP BY status – группируем заказы по статусу.
– COUNT (*) – считаем количество в каждой группе.
– ORDER BY order_count DESC – сортируем от большего к меньшему.
Пример вывода (фрагмент):
text
’completed’ 79’pending’ 11’shipped’ 10’cancelled’ 2Задача 2. Сумма выручки по месяцам.
Посчитаем общую выручку по месяцам на основе завершённых заказов.
text
SELECTDATE_TRUNC (’month’, order_date) AS month,SUM (oi. quantity * oi.price_per_unit) AS revenueFROM orders oINNER JOIN order_items oi ON o. order_id = oi. order_idWHERE 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
SELECTu.user_id,u.user_name,SUM (oi. quantity * oi.price_per_unit) AS total_spentFROM users uINNER JOIN orders o ON u.user_id = o.user_idINNER JOIN order_items oi ON o. order_id = oi. order_idWHERE o.status = ’completed’GROUP BY u.user_id, u.user_nameORDER BY total_spent DESCLIMIT 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, countryFROM usersWHERE 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, countryFROM usersWHERE user_id IN (SELECT DISTINCT user_id FROM orders);Как это работает
– Подзапрос собирает список всех user_id, которые есть в заказах.
– IN оставляет пользователей, которые есть в этом списке.
Задача 6. Пользователи из выбранных стран (IN со списком).
Найдём пользователей из России, Беларуси и Казахстана.
text
SELECT user_id, user_name, countryFROM usersWHERE country IN («Россия», «Беларусь», «Казахстан»)ORDER BY country, user_name;Как это работает
– IN («Россия», «Беларусь», «Казахстан») – оставляет только пользователей из этих трёх стран.
– ORDER BY country, user_name – сортируем сначала по стране, потом по имени.
Пример вывода (фрагмент):
textuser_id user_name country4 ’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.cityFROM orders oJOIN users u ON o.user_id = u.user_idWHERE u.city = «Москва»/*фильр Мсоква*/UNIONSELECT o. order_id, o. order_date, u.user_name, u.cityFROM orders oJOIN users u ON o.user_id = u.user_idWHERE 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 ordersINTERSECTSELECT user_id FROM user_comments;Как это работает
– Первый запрос – все user_id из заказов.
– Второй запрос – все user_id из отзывов.
– INTERSECT оставляет только тех, кто есть в обоих списках.
Типичная ошибка
INTERSECT требует совпадения по всем колонкам. Если добавить в SELECT лишнюю колонку (например, order_date), результат может стать пустым, потому что дата заказа не совпадёт с датой отзыва.
Задача 2. Пользователи, которые покупали, но не оставляли отзывы (EXCEPT).
text
SELECT user_id FROM ordersEXCEPTSELECT user_id FROM user_comments;Как это работает
– Первый запрос – все user_id из заказов.
– Второй запрос – все user_id из отзывов.
– EXCEPT убирает из первого списка тех, кто есть во втором.
Задача 3. Сравнение двух способов: UNION vs UNION ALL.
text
SELECT country FROM usersWHERE country = «Россия» UNIONSELECT country FROM users WHERE country = «Россия»;Вернёт одну строку: «Россия».
text
SELECT country FROM users WHERE country = «Россия»/*1*/UNION ALLSELECT 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)SELECTbuyers,total,ROUND (100.0 * buyers / total, 2) AS conversion_rateFROM all_users, buying_users;Как это работает
– all_users – временная таблица с количеством всех пользователей
– buying_users – временная таблица с количеством покупателей
– Основной запрос берёт данные из обоих CTE и считает процент
– 100.0 (с точкой) нужен, чтобы PostgreSQL понял, что нужно деление с дробной частью
– ROUND (…, 2) округляет до двух знаков после запятой
Пример вывода:
text
buyers total conversion_rate15 15 100Шаг 4. Конверсия с группировкой по странам.
text
WITH buying_users AS (SELECT DISTINCT user_id FROM orders)SELECTu.country,COUNT (*) AS total_users,COUNT(bu.user_id) AS buyers,ROUND (100.0 * COUNT(bu.user_id) / COUNT (*), 2) AS conversion_rateFROM users uLEFT JOIN buying_users bu ON u.user_id = bu.user_idGROUP BY u.countryORDER 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 columnsFROM table_a aWHERE NOT EXISTS (SELECT 1FROM table_b bWHERE b.key = a.key);Как это работает
Берём строку из table_a. Проверяем, есть ли её ключ (WHERE b.key = a.key) в table_b. Ничего не находим – оставляем. Если находим – не выводим (так работает, потому что есть NOT).
Запомните: NOT EXISTS проверяет отсутствие. Если подзапрос вернул хотя бы одну строку – строка из внешнего запроса не попадёт в результат.
SQL-запросы с объяснением
Способ 1. LEFT JOIN + IS NULL
text
SELECTu.user_id,u.user_name,u.registered_at,u.countryFROM users uLEFT JOIN orders o ON u.user_id = o.user_idWHERE 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
SELECTu.user_id,u.user_name,u.registered_at,u.countryFROM users uWHERE NOT EXISTS (SELECT 1FROM orders oWHERE o.user_id = u.user_id);Как это работает
Для каждого пользователя запрос проверяет: есть ли в orders хотя бы одна запись с таким user_id?
Если есть – пользователь исключается. Если нет – попадает в результат.
Обратите внимание на SELECT 1 внутри подзапроса. EXISTS не смотрит на содержимое строк, ему важно только их наличие. Поэтому SELECT 1 – стандартное соглашение, оно работает быстрее, чем SELECT *.
Запомните: NOT EXISTS читается как «таких, для которых не существует записей в…».
Способ 3. NOT IN (с осторожностью)
text
SELECTuser_id,user_name,registered_at,countryFROM usersWHERE 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 oWHERE 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
SELECTc.name AS category_name,AVG(oi.price_per_unit * oi. quantity)::decimal (10,2) AS avg_order_valueFROM orders oJOIN order_items oi ON o. order_id = oi. order_idJOIN products p ON oi.product_id = p.product_idJOIN categories c ON p.category_id = c.category_idWHERE o.status = ’completed’/*завершенные*/GROUP BY c.category_id, c.nameORDER BY avg_order_value DESC;Как это работает