Несколько CTE в одном SQL-запросе

CTE — это временный именованный результат внутри одного SQL-запроса. Его создают через WITH ... AS, а потом используют как обычную таблицу.

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

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

1. Взяли исходные данные.
2. Почистили и отфильтровали.
3. Посчитали нужные показатели.
4. Отобрали важное.
5. Вывели финальный результат.

В SQL это можно красиво записать через несколько CTE.

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

Реальные аналитические запросы редко помещаются в один простой SELECT.

Обычно задача выглядит так:

• сначала соединить несколько таблиц;
• потом оставить только нужные строки;
• потом посчитать метрики;
• потом сравнить их между собой;
• потом выбрать топ, анти-топ или строки с проблемами;
• потом красиво вывести результат.

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

Несколько CTE позволяют разложить логику на понятные этапы.

Например, не так:

SELECT ...
FROM (
  SELECT ...
  FROM (
    SELECT ...
    FROM ...
  ) AS t1
) AS t2;

А так:

WITH
  step_1 AS (
    SELECT ...
  ),
  step_2 AS (
    SELECT ...
    FROM step_1
  ),
  step_3 AS (
    SELECT ...
    FROM step_2
  )
SELECT ...
FROM step_3;

Второй вариант читается как инструкция. У каждого шага есть имя, а имя объясняет смысл.

Базовый синтаксис нескольких CTE

Если CTE несколько, WITH пишется один раз в самом начале запроса.

Дальше CTE перечисляются через запятую.

WITH
  first_step AS (
    SELECT ...
    FROM source_table
  ),
  second_step AS (
    SELECT ...
    FROM first_step
  ),
  third_step AS (
    SELECT ...
    FROM second_step
  )
SELECT ...
FROM third_step;

Важные правила:

• WITH пишется один раз;
• каждый CTE имеет имя;
• после имени идёт AS (...);
• CTE отделяются друг от друга запятыми;
• финальный SELECT идёт после всех CTE;
• следующий CTE может ссылаться на предыдущие;
• предыдущий CTE не может ссылаться на следующий.

То есть порядок важен.

Так можно:

WITH
  paid_orders AS (
    SELECT *
    FROM orders
    WHERE status = 'paid'
  ),
  revenue_by_customer AS (
    SELECT
      customer_id,
      SUM(amount) AS total_revenue
    FROM paid_orders
    GROUP BY customer_id
  )
SELECT *
FROM revenue_by_customer;

А так нельзя:

WITH
  revenue_by_customer AS (
    SELECT
      customer_id,
      SUM(amount) AS total_revenue
    FROM paid_orders
    GROUP BY customer_id
  ),
  paid_orders AS (
    SELECT *
    FROM orders
    WHERE status = 'paid'
  )
SELECT *
FROM revenue_by_customer;

Первый CTE пытается использовать paid_orders, но тот объявлен ниже. Для SQL это всё равно что обратиться к переменной до её создания.

Как читать цепочку CTE

Хорошая цепочка CTE читается сверху вниз.

Например:

WITH
  paid_orders AS (
    SELECT *
    FROM orders
    WHERE status = 'paid'
  ),
  customer_totals AS (
    SELECT
      customer_id,
      COUNT(*) AS orders_count,
      SUM(amount) AS total_amount
    FROM paid_orders
    GROUP BY customer_id
  )
SELECT
  customer_id,
  orders_count,
  total_amount
FROM customer_totals
ORDER BY total_amount DESC;

Читать можно так:

1. paid_orders — берём только оплаченные заказы.
2. customer_totals — считаем по каждому клиенту количество заказов и сумму.
3. Финальный SELECT — выводим результат и сортируем по сумме.

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

Пример: топ-3 заказа каждого клиента

Допустим, есть таблица orders.

id	customer_id	amount	created_at
1	10	500	2026-01-01
2	10	1200	2026-01-03
3	10	700	2026-01-05
4	10	2000	2026-01-10
5	20	300	2026-01-02
6	20	900	2026-01-08
7	20	100	2026-01-12

И есть таблица customers.

id	name
10	Alice
20	Bob

Задача: для каждого клиента взять его 3 самых дорогих заказа, а потом посчитать средний чек среди этих топ-3 заказов.

Запрос через несколько CTE:

WITH
  ranked_orders AS (
    SELECT
      customer_id,
      amount,
      ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY amount DESC, id ASC
      ) AS rn
    FROM orders
  ),
  top_orders AS (
    SELECT
      customer_id,
      amount
    FROM ranked_orders
    WHERE rn <= 3
  ),
  customer_avg AS (
    SELECT
      customer_id,
      AVG(amount) AS avg_top_amount
    FROM top_orders
    GROUP BY customer_id
  )
SELECT
  c.id,
  c.name,
  ca.avg_top_amount
FROM customers AS c
JOIN customer_avg AS ca
  ON ca.customer_id = c.id
ORDER BY ca.avg_top_amount DESC;

Разберём по шагам.

Шаг 1: пронумеровать заказы клиента

Первый CTE:

WITH
  ranked_orders AS (
    SELECT
      customer_id,
      amount,
      ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY amount DESC, id ASC
      ) AS rn
    FROM orders
  )
SELECT *
FROM ranked_orders;

Он берёт заказы и нумерует их внутри каждого клиента.

PARTITION BY customer_id означает: для каждого клиента отдельная нумерация.

ORDER BY amount DESC, id ASC означает: сначала самые дорогие заказы. Если суммы одинаковые, порядок стабилизируется по id.

Результат может быть таким:

customer_id	amount	rn
10	2000	1
10	1200	2
10	700	3
10	500	4
20	900	1
20	300	2
20	100	3

Пока мы ничего не агрегируем. Только добавляем номер заказа в рейтинге клиента.

Шаг 2: оставить только топ-3

Второй CTE использует результат первого.

WITH
  ranked_orders AS (
    SELECT
      customer_id,
      amount,
      ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY amount DESC, id ASC
      ) AS rn
    FROM orders
  ),
  top_orders AS (
    SELECT
      customer_id,
      amount
    FROM ranked_orders
    WHERE rn <= 3
  )
SELECT *
FROM top_orders;

Теперь остаются только заказы с rn <= 3.

Для клиента 10 заказ на 500 исчезнет, потому что он четвёртый по сумме.

customer_id	amount
10	2000
10	1200
10	700
20	900
20	300
20	100

Этот шаг делает ровно одну вещь: оставляет топовые заказы.

Шаг 3: посчитать среднее по топ-3

Третий CTE берёт top_orders и считает среднюю сумму.

WITH
  ranked_orders AS (
    SELECT
      customer_id,
      amount,
      ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY amount DESC, id ASC
      ) AS rn
    FROM orders
  ),
  top_orders AS (
    SELECT
      customer_id,
      amount
    FROM ranked_orders
    WHERE rn <= 3
  ),
  customer_avg AS (
    SELECT
      customer_id,
      AVG(amount) AS avg_top_amount
    FROM top_orders
    GROUP BY customer_id
  )
SELECT *
FROM customer_avg;

Результат:

customer_id	avg_top_amount
10	1300
20	433.3333333333333333

Для клиента 10 среднее считается по суммам 2000, 1200, 700.

Для клиента 20 — по суммам 900, 300, 100.

Финальный SELECT: добавить имена клиентов

Последний запрос уже не делает сложных расчётов. Он просто соединяет готовый результат с таблицей клиентов.

WITH
  ranked_orders AS (
    SELECT
      customer_id,
      amount,
      ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY amount DESC, id ASC
      ) AS rn
    FROM orders
  ),
  top_orders AS (
    SELECT
      customer_id,
      amount
    FROM ranked_orders
    WHERE rn <= 3
  ),
  customer_avg AS (
    SELECT
      customer_id,
      AVG(amount) AS avg_top_amount
    FROM top_orders
    GROUP BY customer_id
  )
SELECT
  c.id,
  c.name,
  ca.avg_top_amount
FROM customers AS c
JOIN customer_avg AS ca
  ON ca.customer_id = c.id
ORDER BY ca.avg_top_amount DESC;

Результат:

id	name	avg_top_amount
10	Alice	1300
20	Bob	433.3333333333333333

Запрос получился длинный, но не страшный. Потому что он разбит на понятные части.

Каждый CTE отвечает за один этап:

CTE	Что делает
ranked_orders	Нумерует заказы клиента по сумме
top_orders	Оставляет только первые 3 заказа
customer_avg	Считает среднее по этим заказам
финальный SELECT	Добавляет имя клиента и сортирует результат

Вот это и есть хороший стиль работы с CTE.

Почему не написать всё одним подзапросом

Технически тот же запрос можно написать без CTE.

SELECT
  c.id,
  c.name,
  ca.avg_top_amount
FROM customers AS c
JOIN (
  SELECT
    customer_id,
    AVG(amount) AS avg_top_amount
  FROM (
    SELECT
      customer_id,
      amount
    FROM (
      SELECT
        customer_id,
        amount,
        ROW_NUMBER() OVER (
          PARTITION BY customer_id
          ORDER BY amount DESC, id ASC
        ) AS rn
      FROM orders
    ) AS ranked_orders
    WHERE rn <= 3
  ) AS top_orders
  GROUP BY customer_id
) AS ca
  ON ca.customer_id = c.id
ORDER BY ca.avg_top_amount DESC;

Он делает то же самое. Но читать его намного тяжелее.

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

CTE делает то же самое более спокойно:

WITH
  ranked_orders AS (...),
  top_orders AS (...),
  customer_avg AS (...)
SELECT ...
FROM customer_avg;

Для учебных задач, аналитики, отчётов и ревью кода CTE обычно понятнее.

Один CTE можно использовать несколько раз

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

Например, хотим посчитать выручку по месяцам и сравнить каждый месяц с предыдущим.

Есть таблица orders.

id	amount	status	created_at
1	1000	paid	2026-01-05
2	1500	paid	2026-01-20
3	800	canceled	2026-01-21
4	2000	paid	2026-02-10
5	2500	paid	2026-03-12

Сначала удобно собрать месячную выручку в CTE.

WITH
  monthly_revenue AS (
    SELECT
      DATE_TRUNC('month', created_at) AS month_start,
      SUM(amount) AS total_amount
    FROM orders
    WHERE status = 'paid'
    GROUP BY DATE_TRUNC('month', created_at)
  )
SELECT
  current_month.month_start,
  current_month.total_amount,
  previous_month.total_amount AS previous_total_amount,
  current_month.total_amount - previous_month.total_amount AS amount_diff
FROM monthly_revenue AS current_month
LEFT JOIN monthly_revenue AS previous_month
  ON previous_month.month_start = current_month.month_start - INTERVAL '1 month'
ORDER BY current_month.month_start;

Здесь monthly_revenue используется дважды:

• как current_month;
• как previous_month.

Это обычный self join: мы соединяем результат сам с собой, чтобы рядом с текущим месяцем увидеть предыдущий.

Без CTE пришлось бы писать одну и ту же агрегацию два раза. А так мы один раз дали ей имя monthly_revenue и спокойно переиспользовали.

CTE как способ отладки запроса

Большой плюс CTE — их удобно проверять по одному.

Допустим, финальный запрос не даёт ожидаемый результат. Не нужно сразу разбирать всю конструкцию.

Можно временно заменить финальный SELECT и посмотреть промежуточный шаг.

Например, проверить первый CTE:

WITH
  ranked_orders AS (
    SELECT
      customer_id,
      amount,
      ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY amount DESC, id ASC
      ) AS rn
    FROM orders
  ),
  top_orders AS (
    SELECT
      customer_id,
      amount
    FROM ranked_orders
    WHERE rn <= 3
  ),
  customer_avg AS (
    SELECT
      customer_id,
      AVG(amount) AS avg_top_amount
    FROM top_orders
    GROUP BY customer_id
  )
SELECT *
FROM ranked_orders
ORDER BY customer_id, rn;

Потом проверить второй:

WITH
  ranked_orders AS (
    SELECT
      customer_id,
      amount,
      ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY amount DESC, id ASC
      ) AS rn
    FROM orders
  ),
  top_orders AS (
    SELECT
      customer_id,
      amount
    FROM ranked_orders
    WHERE rn <= 3
  ),
  customer_avg AS (
    SELECT
      customer_id,
      AVG(amount) AS avg_top_amount
    FROM top_orders
    GROUP BY customer_id
  )
SELECT *
FROM top_orders
ORDER BY customer_id, amount DESC;

Так проще найти ошибку. Если ranked_orders уже неправильный, нет смысла смотреть финальный отчёт. Нужно чинить первый шаг.

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

Хорошие имена для CTE

Имя CTE должно отвечать на вопрос: «что лежит внутри?»

Плохие имена:

WITH
  t1 AS (...),
  t2 AS (...),
  t3 AS (...)
SELECT *
FROM t3;

Такие имена ничего не объясняют. Через неделю ты сам забудешь, что такое t2.

Лучше:

WITH
  paid_orders AS (...),
  orders_by_customer AS (...),
  top_customers AS (...)
SELECT *
FROM top_customers;

Хорошие имена не обязаны быть короткими. Они должны быть понятными.

Например:

• paid_orders;
• active_users;
• monthly_revenue;
• ranked_orders;
• top_products;
• customers_without_orders;
• orders_with_items;
• revenue_by_category.

Когда CTE названы хорошо, запрос можно понять почти по одним названиям.

Когда несколько CTE особенно полезны

Несколько CTE хорошо подходят для задач, где есть последовательная обработка.

Например: «активные пользователи без заказов за последний месяц».

WITH
  active_users AS (
    SELECT
      id,
      email
    FROM users
    WHERE is_active = true
  ),
  recent_orders AS (
    SELECT
      id,
      user_id
    FROM orders
    WHERE created_at >= DATE '2026-01-01'
      AND created_at < DATE '2026-02-01'
  ),
  users_without_orders AS (
    SELECT
      au.id,
      au.email
    FROM active_users AS au
    LEFT JOIN recent_orders AS ro
      ON ro.user_id = au.id
    WHERE ro.id IS NULL
  )
SELECT
  id,
  email
FROM users_without_orders
ORDER BY id;

Что здесь происходит:

1. active_users — берём активных пользователей.
2. recent_orders — берём заказы за нужный период.
3. users_without_orders — оставляем активных пользователей, у которых нет заказов.
4. Финальный SELECT — выводим список.

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

CTE и порядок выполнения: важное уточнение

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

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

В PostgreSQL начиная с версии 12 простые CTE часто могут быть встроены в общий план запроса. То есть база не всегда обязана материализовать CTE как отдельную временную таблицу.

Для новичка это значит простую вещь: CTE в первую очередь нужен для читаемости и структуры запроса. А производительность нужно проверять на реальном плане выполнения, если запрос тяжёлый.

MATERIALIZED и NOT MATERIALIZED в PostgreSQL

В PostgreSQL можно подсказать базе, что делать с CTE: материализовать его или попробовать встроить в основной запрос.

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

WITH
  paid_orders AS MATERIALIZED (
    SELECT *
    FROM orders
    WHERE status = 'paid'
  )
SELECT *
FROM paid_orders;

Можно и наоборот попросить не материализовать CTE, если это возможно:

WITH
  paid_orders AS NOT MATERIALIZED (
    SELECT *
    FROM orders
    WHERE status = 'paid'
  )
SELECT *
FROM paid_orders;

Для начинающего это не самая первая тема. В обычных запросах чаще достаточно писать простой CTE без этих слов.

Но важно знать: CTE — это не всегда «железная временная таблица». Современная база может оптимизировать запрос умнее.

Рекурсивные CTE

Обычные CTE идут по цепочке: один использует предыдущий.

Но есть особый вид CTE — рекурсивный. Он может ссылаться сам на себя.

Рекурсивные CTE используют для иерархий:

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

Допустим, есть таблица сотрудников.

id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Carol	1
4	Dave	2
5	Eve	2

manager_id указывает на руководителя. У Alice руководителя нет, она на верхнем уровне.

Хотим получить оргструктуру с уровнями.

WITH RECURSIVE org_chart AS (
  SELECT
    id,
    name,
    manager_id,
    1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT
    e.id,
    e.name,
    e.manager_id,
    oc.level + 1 AS level
  FROM employees AS e
  JOIN org_chart AS oc
    ON e.manager_id = oc.id
)
SELECT
  id,
  name,
  manager_id,
  level
FROM org_chart
ORDER BY level, id;

Результат:

id	name	manager_id	level
1	Alice	NULL	1
2	Bob	1	2
3	Carol	1	2
4	Dave	2	3
5	Eve	2	3

Как работает рекурсивный CTE

Рекурсивный CTE состоит из двух частей.

Первая часть — базовый случай.

SELECT
  id,
  name,
  manager_id,
  1 AS level
FROM employees
WHERE manager_id IS NULL

Она выбирает верхний уровень: сотрудников без руководителя.

Вторая часть — рекурсивный шаг.

SELECT
  e.id,
  e.name,
  e.manager_id,
  oc.level + 1 AS level
FROM employees AS e
JOIN org_chart AS oc
  ON e.manager_id = oc.id

Она берёт сотрудников, чей руководитель уже найден на предыдущем шаге.

UNION ALL соединяет базовый случай и все следующие шаги.

База повторяет рекурсивный шаг до тех пор, пока появляются новые строки.

Ключевое слово RECURSIVE обязательно. Без него CTE не сможет ссылаться сам на себя.

Защита от бесконечной рекурсии

С рекурсивными CTE нужно быть осторожным. Если в данных есть цикл или условие написано неправильно, запрос может уйти слишком глубоко.

Например, сотрудник 1 руководит сотрудником 2, а сотрудник 2 почему-то руководит сотрудником 1. Получается круг.

Чтобы защититься, часто добавляют ограничение глубины.

WITH RECURSIVE org_chart AS (
  SELECT
    id,
    name,
    manager_id,
    1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT
    e.id,
    e.name,
    e.manager_id,
    oc.level + 1 AS level
  FROM employees AS e
  JOIN org_chart AS oc
    ON e.manager_id = oc.id
  WHERE oc.level < 10
)
SELECT
  id,
  name,
  manager_id,
  level
FROM org_chart
ORDER BY level, id;

Условие:

WHERE oc.level < 10

не даёт рекурсии уйти глубже 10 уровней.

Это не заменяет правильную модель данных, но помогает защититься от неприятных сюрпризов.

Несколько CTE и финальный INSERT

CTE можно использовать не только перед SELECT.

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

WITH
  paid_orders AS (
    SELECT
      id,
      customer_id,
      amount,
      created_at
    FROM orders
    WHERE status = 'paid'
  ),
  monthly_revenue AS (
    SELECT
      DATE_TRUNC('month', created_at) AS month_start,
      SUM(amount) AS total_amount
    FROM paid_orders
    GROUP BY DATE_TRUNC('month', created_at)
  )
INSERT INTO revenue_report (month_start, total_amount)
SELECT
  month_start,
  total_amount
FROM monthly_revenue;

Здесь CTE готовят данные для вставки в таблицу revenue_report.

Это удобно для отчётных таблиц, миграций и backfill-задач.

Несколько CTE и финальный UPDATE

CTE можно использовать и перед UPDATE.

Например, хотим найти клиентов с большой суммой заказов и обновить им сегмент.

WITH
  customer_totals AS (
    SELECT
      customer_id,
      SUM(amount) AS total_amount
    FROM orders
    WHERE status = 'paid'
    GROUP BY customer_id
  ),
  vip_customers AS (
    SELECT
      customer_id
    FROM customer_totals
    WHERE total_amount >= 100000
  )
UPDATE customers AS c
SET segment = 'vip'
FROM vip_customers AS vc
WHERE vc.customer_id = c.id;

Смысл такой:

1. customer_totals считает сумму заказов.
2. vip_customers выбирает клиентов с большой суммой.
3. UPDATE обновляет этих клиентов.

Для сложных обновлений CTE часто делает запрос намного понятнее и безопаснее.

Несколько CTE и финальный DELETE

CTE может помочь аккуратно удалить ограниченное количество строк.

Например, в PostgreSQL нет простого синтаксиса DELETE ... LIMIT, но можно выбрать нужные id через CTE.

WITH
  old_logs AS (
    SELECT
      id
    FROM logs
    WHERE created_at < DATE '2024-01-01'
    ORDER BY id
    LIMIT 1000
  )
DELETE FROM logs
WHERE id IN (
  SELECT id
  FROM old_logs
);

Такой подход полезен, когда нужно удалять данные небольшими порциями, а не сносить огромный объём за один раз.

Когда CTE лучше не использовать

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

Если запрос простой, CTE может только добавить лишний шум.

Например, так можно, но смысла мало:

WITH
  active_users AS (
    SELECT
      id,
      email
    FROM users
    WHERE is_active = true
  )
SELECT
  id,
  email
FROM active_users;

Проще написать:

SELECT
  id,
  email
FROM users
WHERE is_active = true;

CTE особенно полезен, когда:

• запрос длинный;
• есть несколько логических шагов;
• промежуточный результат нужен несколько раз;
• нужно использовать оконную функцию, а потом отфильтровать её результат;
• нужно улучшить читаемость;
• нужно безопасно подготовить данные для INSERT, UPDATE или DELETE.

Если запрос и так читается легко, CTE не обязателен.

Частые ошибки с несколькими CTE

Писать WITH перед каждым CTE

Неправильно:

WITH a AS (
  SELECT *
  FROM orders
)
WITH b AS (
  SELECT *
  FROM a
)
SELECT *
FROM b;

Правильно:

WITH
  a AS (
    SELECT *
    FROM orders
  ),
  b AS (
    SELECT *
    FROM a
  )
SELECT *
FROM b;

WITH один, CTE несколько, между ними запятые.

Забыть запятую между CTE

Неправильно:

WITH
  paid_orders AS (
    SELECT *
    FROM orders
    WHERE status = 'paid'
  )
  customer_totals AS (
    SELECT
      customer_id,
      SUM(amount) AS total_amount
    FROM paid_orders
    GROUP BY customer_id
  )
SELECT *
FROM customer_totals;

Между paid_orders и customer_totals нужна запятая.

Правильно:

WITH
  paid_orders AS (
    SELECT *
    FROM orders
    WHERE status = 'paid'
  ),
  customer_totals AS (
    SELECT
      customer_id,
      SUM(amount) AS total_amount
    FROM paid_orders
    GROUP BY customer_id
  )
SELECT *
FROM customer_totals;

Сослаться на CTE, который объявлен ниже

Неправильно:

WITH
  customer_totals AS (
    SELECT
      customer_id,
      SUM(amount) AS total_amount
    FROM paid_orders
    GROUP BY customer_id
  ),
  paid_orders AS (
    SELECT *
    FROM orders
    WHERE status = 'paid'
  )
SELECT *
FROM customer_totals;

customer_totals не видит paid_orders, потому что paid_orders объявлен позже.

Правильно:

WITH
  paid_orders AS (
    SELECT *
    FROM orders
    WHERE status = 'paid'
  ),
  customer_totals AS (
    SELECT
      customer_id,
      SUM(amount) AS total_amount
    FROM paid_orders
    GROUP BY customer_id
  )
SELECT *
FROM customer_totals;

Сделать циклическую ссылку

Так нельзя:

WITH
  a AS (
    SELECT *
    FROM b
  ),
  b AS (
    SELECT *
    FROM a
  )
SELECT *
FROM a;

Обычные CTE не могут ссылаться друг на друга по кругу.

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

Забыть RECURSIVE

Неправильно:

WITH org_chart AS (
  SELECT
    id,
    name,
    manager_id
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT
    e.id,
    e.name,
    e.manager_id
  FROM employees AS e
  JOIN org_chart AS oc
    ON e.manager_id = oc.id
)
SELECT *
FROM org_chart;

CTE org_chart ссылается сам на себя, значит нужно WITH RECURSIVE.

Правильно:

WITH RECURSIVE org_chart AS (
  SELECT
    id,
    name,
    manager_id
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  SELECT
    e.id,
    e.name,
    e.manager_id
  FROM employees AS e
  JOIN org_chart AS oc
    ON e.manager_id = oc.id
)
SELECT *
FROM org_chart;

Писать слишком много CTE без смысла

CTE должны помогать читать запрос, а не превращать его в длинную лестницу из очевидных шагов.

Плохо, когда каждый CTE просто переименовывает предыдущий:

WITH
  step_1 AS (
    SELECT *
    FROM orders
  ),
  step_2 AS (
    SELECT *
    FROM step_1
  ),
  step_3 AS (
    SELECT *
    FROM step_2
  )
SELECT *
FROM step_3;

Такой запрос длиннее, но не понятнее.

Хороший CTE должен добавлять смысл: фильтр, расчёт, агрегацию, ранжирование, подготовку данных.

Давать CTE слишком общие имена

Плохо:

WITH
  data AS (...),
  result AS (...),
  final AS (...)
SELECT *
FROM final;

Лучше:

WITH
  paid_orders AS (...),
  revenue_by_customer AS (...),
  top_customers AS (...)
SELECT *
FROM top_customers;

Имя CTE — это маленькая документация внутри запроса.

Как писать хорошие цепочки CTE

Есть простая формула.

Каждый CTE должен отвечать на один понятный вопрос.

Например:

WITH
  paid_orders AS (...),
  orders_with_items AS (...),
  revenue_by_product AS (...),
  ranked_products AS (...),
  top_products AS (...)
SELECT ...
FROM top_products;

По именам уже видно, что происходит:

1. Берём оплаченные заказы.
2. Соединяем их с товарами.
3. Считаем выручку по товарам.
4. Ранжируем товары.
5. Оставляем топ.

Такой запрос легко читать, обсуждать с коллегой, проверять и дорабатывать.

Главное про несколько CTE

Несколько CTE позволяют разбить сложный SQL-запрос на цепочку понятных шагов.

Базовый синтаксис:

WITH
  first_cte AS (
    SELECT ...
  ),
  second_cte AS (
    SELECT ...
    FROM first_cte
  ),
  third_cte AS (
    SELECT ...
    FROM second_cte
  )
SELECT ...
FROM third_cte;

WITH пишется один раз. CTE перечисляются через запятую.

Каждый следующий CTE может использовать предыдущие. Поэтому порядок объявления важен.

Один и тот же CTE можно использовать несколько раз в финальном запросе, например для сравнения текущего месяца с предыдущим.

Рекурсивные CTE пишутся через WITH RECURSIVE и нужны для деревьев, иерархий и графов. В них есть базовый случай и рекурсивный шаг.

CTE полезны не только с SELECT, но и с INSERT, UPDATE, DELETE, когда нужно сначала аккуратно подготовить набор строк.

Главная идея простая: несколько CTE превращают большой SQL-запрос из запутанной формулы в понятный маршрут. Сначала делаем один шаг, потом второй, потом третий — и в конце получаем результат, который можно читать, проверять и поддерживать без боли.