Kitabı oxu: «Подзапросы SQL. 18 задач для аналитика»

Перед первой задачей

Зачем нужны подзапросы

Подзапрос помогает сначала выразить маленький вопрос, а потом использовать его как фильтр, значение или временную таблицу.

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

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

Как не сломать отчёт

Главная ловушка подзапросов — не синтаксис, а уровень детализации. Нужно понимать, возвращает подзапрос одно значение, список значений или таблицу.

Второй важный вопрос: подзапрос независимый или коррелированный. Коррелированный подзапрос выполняет проверку для строки внешнего запроса.

Все примеры рассчитаны на SQLite. Мы используем базовые конструкции SQL, поэтому смысл легко перенести в PostgreSQL, MySQL и другие СУБД.

Маршрут по задачам

1. Найти клиентов с оплаченными заказами через IN

2. Оставить заказы выше среднего чека

3. Найти товары без продаж через NOT EXISTS

4. Посчитать заказы клиента в SELECT

5. Использовать подзапрос как временную таблицу

6. Найти клиентов с крупным заказом через EXISTS

7. Взять последний заказ каждого клиента

8. Оставить категории выше среднего по выручке

9. Обойти ловушку NOT IN и NULL

10. Сравнить заказ со средним заказом клиента

11. Показать долю заказа от общей выручки

12. Найти категории без товаров

13. Сначала убрать дубли справочника

14. Выбрать товары из топ-категории

15. Найти первый заказ каждого клиента

16. Проверить заказы без успешной оплаты

17. Выбрать клиентов с двумя и более заказами

18. Собрать финальный отчёт по клиентам

Практические задачи

Задача 1. Найти клиентов с оплаченными заказами через IN

Рабочий вопрос

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

Данные

CREATE TABLE clients(id INTEGER, name TEXT);

CREATE TABLE orders(id INTEGER, client_id INTEGER, status TEXT);

INSERT INTO clients VALUES (1,'Анна'),(2,'Илья'),(3,'Олег'),(4,'Нина');

INSERT INTO orders VALUES (101,1,'paid'),(102,1,'cancelled'),(103,3,'paid');

Запрос

SELECT id, name

FROM clients

WHERE id IN (

SELECT client_id

FROM orders

WHERE status = 'paid'

)

ORDER BY id;

Ожидаемый результат

[[1, "Анна"], [3, "Олег"]]

Почему работает

Внутренний запрос возвращает client_id только из оплаченных заказов.

Внешний запрос оставляет клиентов, чей id входит в этот список.

У Анны есть оплаченный заказ, хотя рядом есть и отменённый: фильтр смотрит на наличие хотя бы одного paid.

Где легко ошибиться

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

Самостоятельная проверка

Добавьте заказ 104 для Нины со статусом paid. Какие клиенты будут в результате?

Ответ: В результате будут Анна, Олег и Нина, потому что id Нины появится во внутреннем списке.

Задача 2. Оставить заказы выше среднего чека

Рабочий вопрос

Нужно быстро увидеть заказы, сумма которых выше среднего значения по всем заказам.

Данные

CREATE TABLE orders(id INTEGER, total INTEGER);

INSERT INTO orders VALUES (1,300),(2,900),(3,600),(4,200);

Запрос

SELECT id, total

FROM orders

WHERE total > (

SELECT AVG(total)

FROM orders

)

ORDER BY total DESC;

Ожидаемый результат

[[2, 900], [3, 600]]

Почему работает

Скалярный подзапрос возвращает одно число: средний чек.

Среднее равно 500, поэтому остаются только суммы больше 500.

Внешний запрос сортирует уже отфильтрованные заказы по убыванию суммы.

Где легко ошибиться

Такой подзапрос обязан вернуть одно значение. Если случайно вернуть несколько строк, сравнение total > (...) потеряет смысл.

Самостоятельная проверка

Что изменится, если заказ 4 увеличить с 200 до 800?

Ответ: Среднее станет 650, поэтому останутся заказы 2 и 4; заказ 3 на 600 уже не выше среднего.

Задача 3. Найти товары без продаж через NOT EXISTS

Рабочий вопрос

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

Данные

CREATE TABLE products(sku TEXT, name TEXT);

CREATE TABLE sales(id INTEGER, sku TEXT, qty INTEGER);

INSERT INTO products VALUES ('A1','Планер'),('B2','Курс'),('C3','Шаблон'),('D4','Чек-лист');

INSERT INTO sales VALUES (1,'A1',2),(2,'C3',1),(3,'A1',1);

Запрос

SELECT p.sku, p.name

FROM products p

WHERE NOT EXISTS (

SELECT 1

FROM sales s

WHERE s.sku = p.sku

)

ORDER BY p.sku;

Ожидаемый результат

[["B2", "Курс"], ["D4", "Чек-лист"]]

Почему работает

Подзапрос коррелированный: он проверяет продажи для текущего товара p.

NOT EXISTS оставляет только те товары, для которых совпавших строк sales нет.

A1 продавался дважды, но это не важно: наличие хотя бы одной строки уже исключает товар.

Где легко ошибиться

Для анти-проверок часто безопаснее NOT EXISTS, а не NOT IN, потому что NULL в списке может неожиданно испортить логику.

Самостоятельная проверка

Добавьте продажу D4 в таблицу sales. Какие товары останутся без продаж?

Ответ: Останется только B2, потому что для D4 теперь найдётся совпавшая строка продажи.

Задача 4. Посчитать заказы клиента в SELECT

Рабочий вопрос

Нужно вывести всех клиентов и рядом с каждым показать количество его заказов.

Данные

CREATE TABLE clients(id INTEGER, name TEXT);

CREATE TABLE orders(id INTEGER, client_id INTEGER);

INSERT INTO clients VALUES (1,'Анна'),(2,'Илья'),(3,'Олег');

INSERT INTO orders VALUES (101,1),(102,1),(103,3);

Запрос

SELECT c.name,

(

SELECT COUNT(*)

FROM orders o

WHERE o.client_id = c.id

) AS orders_count

FROM clients c

ORDER BY c.id;

Ожидаемый результат

[["Анна", 2], ["Илья", 0], ["Олег", 1]]

Почему работает

Внешний запрос идёт по всем клиентам.

Подзапрос в SELECT считает заказы только для текущего c.id.

COUNT(*) возвращает 0, если совпавших заказов нет.

Где легко ошибиться

Коррелированный подзапрос прост для чтения, но на больших таблицах его нужно сравнивать с вариантом через JOIN и GROUP BY.

Самостоятельная проверка

Добавьте два заказа для Ильи. Как изменится его orders_count?

Ответ: У Ильи станет 2, остальные строки не изменятся.