Как написать запрос с использованием CTE в SQL

Что проверяют

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

Как отвечать

CTE объявляется через WITH имя AS (запрос) и живёт в пределах одного оператора. Можно объявить несколько через запятую, каждый следующий видит предыдущие. Пример для аналитика, когорты по месяцу первой покупки:

WITH first_order AS (
  SELECT user_id, date_trunc('month', min(created_at)) AS cohort
  FROM orders GROUP BY user_id
),
activity AS (
  SELECT o.user_id, f.cohort, date_trunc('month', o.created_at) AS month
  FROM orders o JOIN first_order f USING (user_id)
)
SELECT cohort, month, count(DISTINCT user_id) AS users
FROM activity GROUP BY cohort, month;

Что сказать кроме синтаксиса:

• рекурсия: WITH RECURSIVE состоит из якорной части и рекурсивной через UNION ALL. Нужна для иерархий и генерации рядов дат, обязательно с условием остановки;
• материализация в PostgreSQL: с версии 12 нерекурсивный CTE, на который ссылаются один раз, встраивается в запрос, и фильтры проталкиваются внутрь. Явно управлять можно через AS MATERIALIZED и AS NOT MATERIALIZED;
• ClickHouse: по умолчанию CTE не материализуется, а подставляется в каждое место использования. Сослались трижды, подзапрос выполнится трижды;
• временная таблица: лучше CTE, если промежуточный результат нужен в нескольких запросах или на нём нужен индекс.

Где спотыкаются

Считают, что CTE всегда вычисляется один раз и кэшируется. Это зависит от базы и версии.

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

Используют CTE вместо оконных функций: считают агрегат отдельным шагом и соединяют с исходной таблицей, хотя sum(...) OVER (PARTITION BY ...) дал бы то же за один проход.