SQL для аналитика. 50 задач с решениями на PostgreSQL
– Присоединяем заказы → товары в заказе → продукты → категории
– Отбираем только завершённые заказы
– Группируем по категории
– Считаем среднюю сумму заказа в каждой группе
– Сортируем от самой дорогой категории к дешёвой
Мы используем INNER JOIN (просто JOIN), потому что категории без заказов нас не интересуют. Если нужны и они, замените JOIN на LEFT JOIN.
Что выводит запрос: две колонки – category_name (название категории) и avg_order_value (средняя сумма заказа в рублях).
Пример вывода (фрагмент):
text
«Ноутбуки» 121945.45«Смартфоны» 77651.72«Планшеты» 69900.00Задача 2. Средний чек с количеством заказов.
Добавим информацию о том, сколько заказов попало в расчёт.
text
SELECTc.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_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;Как это работает
Добавили 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 (*) – считает все строки, включая NULLCOUNT (column) – считает только непустые значения в колонкеCOUNT (DISTINCT column) – считает уникальные значенияHAVING – фильтрует группы после группировки (как WHERE, но для GROUP BY).
text
SELECT category, COUNT (*) AS cntFROM productsGROUP BY categoryHAVING COUNT (*)> 5;Типичная ошибка
Использовать WHERE вместо HAVING для фильтрации по агрегатным функциям. WHERE выполняется до группировки и не видит COUNT, SUM, AVG. Если есть группировка и нужно отфильтровать сгруппированные данные – всегда используем HAVING.
SQL-запросы с объяснением
Задача 1. Количество заказов по пользователям.
Посчитаем, сколько заказов сделал каждый пользователь.
text
SELECTu.user_id,u.user_name,COUNT (o. order_id) AS orders_countFROM users uLEFT JOIN orders o ON u.user_id = o.user_idGROUP BY u.user_id, u.user_nameORDER BY orders_count DESC;Как это работает
– LEFT JOIN – оставляем всех пользователей, даже без заказов.
– COUNT (o. order_id) – считает только непустые заказы (у пользователей без заказов будет 0).
– GROUP BY – группируем по пользователю.
– ORDER BY orders_count DESC – сортируем от частых покупателей к редким.
Пример вывода (фрагмент):
text
user_id user_name orders_count1 alex_ivanov 914 lucas_silva 85 nurlan_kz 8Задача 2. Пользователи с повторными покупками (больше одного заказа).
Используем HAVING, чтобы отфильтровать группы.
text
SELECTu.user_id,u.user_name,COUNT (o. order_id) AS orders_countFROM users uJOIN orders o ON u.user_id = o.user_idGROUP BY u.user_id, u.user_nameHAVING COUNT (o. order_id)> 1ORDER BY orders_count DESC;Как это работает
– INNER JOIN – берём только пользователей с заказами (те, у кого 0 заказов, не нужны).– HAVING COUNT (o. order_id)> 1 – оставляем только тех, у кого заказов больше одного.– Сортировка от самых активных.Пример вывода (фрагмент):
textuser_id user_name orders_count1 alex_ivanov 914 lucas_silva 85 nurlan_kz 810 armen_grigoryan 8Совет
Если нужно посчитать пользователей с повторными покупками в процентах, можно использовать CTE:
text
WITH repeat_buyers AS (SELECT user_idFROM ordersGROUP BY user_idHAVING COUNT (*)> 1)SELECTCOUNT (*) AS repeat_buyers,(SELECT COUNT (*) FROM users) AS total_users,ROUND (100.0 * COUNT (*) / (SELECT COUNT (*) FROM users), 2) AS percentFROM repeat_buyers;Задача 3. Распределение пользователей по количеству покупок.
Сколько пользователей купили 1 раз, 2 раза, 3 раза и так далее.
text
WITH order_counts AS(SELECTu.user_id,COUNT (o. order_id) AS orders_countFROM users uLEFT JOIN orders o ON u.user_id = o.user_idGROUP BY u.user_id)SELECTorders_count,COUNT (*) AS user_countFROM order_countsGROUP BY orders_countORDER BY orders_count;Как это работает
– CTE order_counts – считает количество заказов для каждого пользователя.
– Основной запрос группирует пользователей по числу заказов.
Пример вывода:
text
orders_count user_count5 26 47 58 39 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
SELECTu.user_id,u.user_name,SUM (oi. quantity * oi.price_per_unit) AS ltvFROM users uJOIN orders o ON u.user_id = o.user_idJOIN order_items oi ON o. order_id = oi. order_idWHERE o.status = ’completed’GROUP BY u.user_id, u.user_nameORDER BY ltv DESC;Как это работает
– Присоединяем заказы и товары в заказах.
– Фильтруем только завершённые заказы.
– Группируем по пользователю.
– Суммируем стоимость всех купленных товаров.
– Сортируем от самого ценного клиента к менее ценным.
Пример вывода (фрагмент):
text
user_id user_name ltv1 alex_ivanov 5430005 nurlan_kz 3240002 li_wei 1498003 aram_sargsyan 120000Задача 2. LTV с учётом только успешных платежей (через payments).
Иногда удобнее считать LTV по таблице платежей, а не по order_items (например, если есть возвраты).
text
SELECTu.user_id,u.user_name,SUM(p.amount) AS ltvFROM users uJOIN payments p ON u.user_id = p.user_idGROUP BY u.user_id, u.user_nameORDER BY ltv DESC;Как это работает
– Присоединяем платежи напрямую к пользователям.
– Суммируем суммы платежей.
– Группируем по пользователю.
Типичная ошибка
Забыть GROUP BY при использовании SUM. Без группировки SUM сложит все значения по всей таблице, а не по пользователям.
Задача 3. Средний LTV по странам.
Узнаем, в какой стране пользователи в среднем тратят больше.
text
SELECTu.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_ltvFROM users uJOIN orders o ON u.user_id = o.user_idJOIN order_items oi ON o. order_id = oi. order_idWHERE o.status = ’completed’GROUP BY u.countryORDER 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
SELECTDATE_TRUNC (’month’, order_date) AS month,SUM (oi. quantity * oi.price_per_unit) AS revenueFROM orders oJOIN 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) – превращает дату в первый день месяца.– JOIN – соединяем заказы с их товарами.– SUM – считаем общую выручку.– GROUP BY – группируем по месяцам.– ORDER BY month – сортируем по возрастанию.Пример вывода (фрагмент):
text
month revenue2024-01-01 00:00:00+03 1571090.002024-02-01 00:00:00+03 1458330.002024-03-01 00:00:00+03 1407930.002024-04-01 00:00:00+03 1193490.00Задача 2. Выручка по месяцам и категориям (обычный GROUP BY).
Подготовим данные для кросс-таблицы: выручка по месяцам и категориям.
text
SELECTDATE_TRUNC (’month’, o. order_date) AS month,c.name AS category_name,SUM (oi. quantity * oi.price_per_unit) AS revenueFROM 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 month, c.category_id, c.nameORDER BY month, category_name;Как это работает
– Группируем по месяцу и категории.
– Считаем выручку для каждой пары (месяц, категория).
Пример вывода (фрагмент):
text
month category_name revenue2024-01-01 00:00:00+03 Женская 11970.002024-01-01 00:00:00+03 Мужская 11970.002024-01-01 00:00:00+03 Наушники 79700.002024-01-01 00:00:00+03 Ноутбуки 689600.002024-01-01 00:00:00+03 Планшеты 69900.00Задача 3. Кросс-таблица: месяцы в строках, категории в колонках.
Превращаем результат задачи 2 в таблицу, где категории становятся колонками, используем crosstab из расширения tablefunc.
text
CREATE EXTENSION IF NOT EXISTS tablefunc;SELECT * FROM crosstab («SELECTDATE_TRUNC (»’month’», o. order_date)::date AS month,c.name AS category_name,SUM (oi. quantity * oi.price_per_unit) AS revenueFROM 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 month, c.category_id, c.nameORDER 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
SELECTp.product_id,p.name,SUM (oi. quantity * oi.price_per_unit) AS revenueFROM order_items oiJOIN products p ON oi.product_id = p.product_idGROUP BY p.product_id, p.nameORDER BY revenue DESCLIMIT 5;Как это работает
– Группируем по товару.
– Считаем выручку.
– Сортируем от большего к меньшему.
– LIMIT 5 оставляет только первые 5 строк.
Пример вывода:
text
product_id name revenue4 MacBook Pro 14 2398800.005 Lenovo ThinkPad 1078800.001 iPhone 15 1038700.002 Samsung Galaxy S24 973700.0011 iPad Air 699000.00Задача 2. Топ-5 товаров с нумерацией (ROW_NUMBER).
Добавим нумерацию строк.
text
SELECTROW_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 revenueFROM order_items oiJOIN products p ON oi.product_id = p.product_idGROUP BY p.product_id, p.nameORDER BY revenue DESCLIMIT 5;Как это работает
– ROW_NUMBER () OVER (ORDER BY SUM (…) DESC) – нумерует строки от 1 до N в порядке убывания выручки.
– Остальное как в задаче 1.
Задача 3. Топ-3 товара в каждой категории (ROW_NUMBER с PARTITION BY).
Более сложный пример: для каждой категории найти 3 самых продаваемых товара.
text
WITH product_revenue AS (SELECTc.name AS category_name,p.product_id,p.name AS product_name,SUM (oi. quantity * oi.price_per_unit) AS revenueFROM order_items oiJOIN products p ON oi.product_id = p.product_idJOIN categories c ON p.category_id = c.category_idGROUP BY c.category_id, c.name, p.product_id, p.name),ranked AS (SELECTcategory_name,product_name,revenue,ROW_NUMBER () OVER (PARTITION BY category_name ORDER BY revenue DESC) AS rankFROM product_revenue)SELECTcategory_name,product_name,revenue,rankFROM rankedWHERE rank <= 3ORDER 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 (день регистрации) для каждой когорты.