Czytaj książkę: «CTE в SQL. 18 задач с WITH для аналитика»

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

Зачем выносить шаги в WITH

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

В аналитике это почти всегда снижает риск: отдельно собрать оплаченные заказы, отдельно посчитать итоги, отдельно добавить долю или фильтр.

В этой книге WITH используется только там, где он делает отчёт прозрачнее. Мы не прячем сложность, а раскладываем её на проверяемые слои.

Как проверять себя

Сначала прочитайте каждый CTE как временную таблицу: какие колонки она возвращает и сколько строк в ней должно быть.

Затем проверьте финальный SELECT. Ошибки обычно появляются на стыке: перепутали зерно, повторно размножили строки или взяли среднее не на том уровне.

Все запросы написаны для SQLite. Конструкция WITH и большинство приёмов переносимы в PostgreSQL, MySQL и другие SQL-системы.

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

1. Вынести оплаченные заказы в отдельный шаг

2. Собрать выручку по клиентам

3. Отфильтровать итоги после группировки

4. Соединить клиентов с заранее собранным итогом

5. Использовать две CTE подряд

6. Проверить дубли в справочнике

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

8. Найти товары без продаж через CTE

9. Взять последние статусы заказов

10. Сгенерировать числа через рекурсивный CTE

11. Заполнить пропущенные дни нулями

12. Сравнить товар со средней выручкой категории

13. Переиспользовать очищенный набор данных

14. Найти клиентов без повторной покупки

15. Собрать план-факт по категориям

16. Разделить валидные и ошибочные строки

17. Найти первую покупку клиента

18. Собрать финальный отчёт по магазину

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

Задача 1. Вынести оплаченные заказы в отдельный шаг

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

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

Данные

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

INSERT INTO orders VALUES (1,'paid',500),(2,'new',700),(3,'paid',300),(4,'cancelled',900);

Запрос

WITH paid_orders AS (

SELECT id, total

FROM orders

WHERE status = 'paid'

)

SELECT SUM(total) AS paid_revenue

FROM paid_orders;

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

[[800]]

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

CTE paid_orders содержит только оплаченные строки.

Финальный запрос считает сумму уже по очищенному набору.

Так проще увидеть, что new и cancelled не участвуют в выручке.

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

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

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

Добавьте paid-заказ на 200. Каким станет paid_revenue и почему?

Ответ: paid_revenue станет 1000, потому что новый заказ попадёт в paid_orders.