Как строить сложные аналитические запросы с несколькими CTE

Зачем нужны несколько CTE

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

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

Типичный подход

Сначала отфильтровать исходные данные — убрать лишние строки по дате, статусу, сегменту или другому признаку.
Потом агрегировать — посчитать суммы, средние, ранги, когорты или другие метрики.
Затем соединить промежуточные результаты — например, сравнить текущий период с прошлым.
Финальный CTE — собрать всё в один отчёт.

Пример

WITH filtered_events AS (
  SELECT user_id, event_date, amount
  FROM events
  WHERE event_date >= CURRENT_DATE - INTERVAL '30 days'
),
user_totals AS (
  SELECT user_id, SUM(amount) AS total_amount
  FROM filtered_events
  GROUP BY user_id
),
ranked_users AS (
  SELECT user_id, total_amount,
         ROW_NUMBER() OVER (ORDER BY total_amount DESC) AS rn
  FROM user_totals
)
SELECT *
FROM ranked_users
WHERE rn <= 100;

На что обращать внимание

не делать CTE слишком много без причины — это может ухудшить читаемость;
следить, материализует ли СУБД CTE или встраивает его в основной запрос;
если нужна максимальная производительность, проверять план EXPLAIN ANALYZE;
иногда подзапрос или временная таблица могут быть лучше, если CTE мешает оптимизатору.

Как ответить на собеседовании

Я строю сложный аналитический запрос как цепочку CTE, где каждый шаг решает отдельную задачу: фильтрация, агрегация, ранжирование, финальная выборка. Это помогает читать и поддерживать SQL, но для больших данных я обязательно проверяю план, потому что в некоторых СУБД CTE может влиять на оптимизацию.

Как строить сложные аналитические запросы с несколькими CTE?