Buch lesen: "Подзапросы 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 Нины появится во внутреннем списке.