CTE (WITH) SQL: шпаргалка для собеседования

Зачем аналитику CTE

CTE (Common Table Expression, они же WITH-выражения) — способ именовать промежуточный результат и использовать его дальше в запросе. Фактически это временный виртуальный датасет, который живёт только внутри одного запроса. Для аналитика CTE — главный инструмент наведения порядка в длинных запросах.

Если готовитесь к собеседованию по SQL, знать CTE обязательно — почти любая задача про воронки, когорты и ранжирование решается именно через WITH.

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

WITH имя_cte AS (
    SELECT ...
    FROM ...
    WHERE ...
)
SELECT *
FROM имя_cte
WHERE ...

Всё, что между скобками после AS — обычный SELECT. Дальше вы обращаетесь к результату по имени, как к таблице.

Цепочка CTE

Можно определить несколько CTE подряд через запятую — каждый следующий видит предыдущие:

WITH
    active_users AS (
        SELECT user_id
        FROM users
        WHERE last_active >= CURRENT_DATE - INTERVAL '7 day'
    ),
    user_orders AS (
        SELECT user_id, COUNT(*) AS orders_cnt, SUM(amount) AS revenue
        FROM orders
        WHERE user_id IN (SELECT user_id FROM active_users)
        GROUP BY user_id
    )
SELECT
    a.user_id,
    COALESCE(o.orders_cnt, 0) AS orders_cnt,
    COALESCE(o.revenue, 0) AS revenue
FROM active_users a
LEFT JOIN user_orders o USING (user_id);

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

CTE vs подзапрос vs временная таблица

Частый вопрос на собеседовании. Краткое сравнение:

Критерий	CTE	Подзапрос	Temp table
Синтаксис	WITH … AS (…)	SELECT … FROM (SELECT …)	CREATE TEMP TABLE
Область видимости	один запрос	один запрос	сессия
Переиспользование	да (в этом запросе)	нет	да
Материализация	зависит от СУБД	обычно inline	физическая
Индексы	нет	нет	можно
Читаемость	✅ высокая	❌ страдает при вложенности	✅

Что отвечать: CTE и подзапросы — одноразовые конструкции. Временные таблицы живут дольше, их можно индексировать и переиспользовать между запросами. По производительности CTE ≈ подзапрос, но CTE чище для глаз.

Больше таких примеров с разборами — в Telegram-тренажёре. Короткие сессии, прогресс по темам, объяснения после каждого ответа.

Материализация CTE

Важный нюанс, о котором часто спрашивают:

• PostgreSQL ≤12 — CTE всегда материализуются (результат хранится). Можно принудительно выключить через WITH … AS NOT MATERIALIZED.
• PostgreSQL 13+ — оптимизатор решает сам, материализовать или нет. По умолчанию не материализует простые CTE.
• ClickHouse, Snowflake, BigQuery — обычно inline, работают как подзапросы.

Это влияет на производительность в крайних случаях. На собеседовании достаточно знать, что в современных СУБД CTE — это прозрачная обёртка, не тормоза.

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

Самая мощная (и часто проваливаемая на собесе) часть CTE.

WITH RECURSIVE numbers AS (
    SELECT 1 AS n           -- базовый случай
    UNION ALL
    SELECT n + 1             -- рекурсивный шаг
    FROM numbers
    WHERE n < 10             -- условие остановки
)
SELECT n FROM numbers;

Вернёт числа от 1 до 10.

Структура всегда такая же: базовый SELECT + UNION ALL + рекурсивный SELECT со ссылкой на сам CTE.

Практический пример: разворачивание иерархии сотрудников

WITH RECURSIVE tree AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL   -- корень: CEO

    UNION ALL

    SELECT e.id, e.name, e.manager_id, t.level + 1
    FROM employees e
    JOIN tree t ON e.manager_id = t.id
)
SELECT * FROM tree ORDER BY level;

Возвращает всё дерево подчинённых с уровнем иерархии. На собеседовании просят задачи ровно такого типа.

Заполнение пропущенных дат — ещё одна типовая задача:

WITH RECURSIVE calendar AS (
    SELECT DATE '2026-01-01' AS day
    UNION ALL
    SELECT day + INTERVAL '1 day'
    FROM calendar
    WHERE day < DATE '2026-01-31'
)
SELECT c.day, COALESCE(SUM(o.amount), 0) AS revenue
FROM calendar c
LEFT JOIN orders o ON o.created_at::DATE = c.day
GROUP BY c.day
ORDER BY c.day;

Генерируем календарь и LEFT JOIN-им заказы — получаем строки даже для дней без заказов.

CTE с оконными функциями

Стандартный паттерн: сначала CTE считает агрегаты и ранжирование, потом основной SELECT фильтрует.

WITH ranked AS (
    SELECT
        user_id,
        order_id,
        amount,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_num
    FROM orders
)
SELECT *
FROM ranked
WHERE order_num = 1;

«Найдите первый заказ каждого пользователя» — классическая задача. Без CTE придётся дублировать ROW_NUMBER в WHERE, что невозможно (агрегаты и оконные функции в WHERE запрещены).

Модифицирующие CTE

В PostgreSQL (и ряде других СУБД) CTE могут содержать не только SELECT, но и INSERT/UPDATE/DELETE:

WITH deleted AS (
    DELETE FROM logs
    WHERE created_at < CURRENT_DATE - INTERVAL '90 day'
    RETURNING *
)
INSERT INTO logs_archive
SELECT * FROM deleted;

Одним запросом переносим старые логи в архив. Спрашивают реже, но на senior-позициях иногда всплывает.

Частые ошибки в CTE

1. Ссылка на CTE из другого CTE, объявленного позже.

-- ❌ Не сработает: cte_b объявлен после cte_a, но используется в cte_a
WITH cte_a AS (SELECT * FROM cte_b),
     cte_b AS (SELECT 1)
SELECT * FROM cte_a;

Порядок имеет значение — CTE видят только те, что определены раньше. Исключение — рекурсивные CTE, которые ссылаются сами на себя.

2. Забытый RECURSIVE. Рекурсивный CTE без ключевого слова RECURSIVE выдаст ошибку.

3. Бесконечная рекурсия. Забыли условие остановки — запрос будет работать пока не упрётся в лимит (в PostgreSQL по умолчанию он высокий).

4. Переиспользование в разных запросах. CTE живёт один запрос. Если нужно дальше — сохраняйте во временную таблицу.

Если готовишься к собесу — бот @kariernik_bot закрывает 80% технических вопросов. SQL, Python, A/B, продуктовые метрики — всё в одном месте.

10 задач на CTE с собеседований

Задача 1. Первый заказ каждого пользователя

WITH first_orders AS (
    SELECT user_id, order_id, created_at,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn
    FROM orders
)
SELECT user_id, order_id, created_at
FROM first_orders
WHERE rn = 1;

Задача 2. Пользователи, сделавшие ≥3 заказа за месяц

WITH monthly_orders AS (
    SELECT user_id, DATE_TRUNC('month', created_at) AS month, COUNT(*) AS cnt
    FROM orders
    GROUP BY user_id, DATE_TRUNC('month', created_at)
)
SELECT DISTINCT user_id
FROM monthly_orders
WHERE cnt >= 3;

Задача 3. Доля каждого города в общей выручке

WITH city_revenue AS (
    SELECT city, SUM(amount) AS rev FROM orders GROUP BY city
),
total AS (
    SELECT SUM(rev) AS all_rev FROM city_revenue
)
SELECT city, rev, ROUND(100.0 * rev / all_rev, 2) AS pct
FROM city_revenue, total
ORDER BY rev DESC;

Задача 4. Воронка: view → cart → purchase

WITH events_pivot AS (
    SELECT user_id,
        MAX(CASE WHEN event = 'view' THEN 1 ELSE 0 END) AS viewed,
        MAX(CASE WHEN event = 'cart' THEN 1 ELSE 0 END) AS carted,
        MAX(CASE WHEN event = 'purchase' THEN 1 ELSE 0 END) AS purchased
    FROM events
    GROUP BY user_id
)
SELECT
    SUM(viewed) AS step1,
    SUM(carted) AS step2,
    SUM(purchased) AS step3
FROM events_pivot;

Задача 5. Retention по неделям

WITH cohort AS (
    SELECT user_id, DATE_TRUNC('week', MIN(created_at)) AS cohort_week
    FROM users GROUP BY user_id
),
activity AS (
    SELECT user_id, DATE_TRUNC('week', event_time) AS active_week
    FROM events
)
SELECT
    c.cohort_week,
    a.active_week,
    COUNT(DISTINCT a.user_id) AS active_users
FROM cohort c
JOIN activity a USING (user_id)
GROUP BY c.cohort_week, a.active_week
ORDER BY 1, 2;

Задача 6. Найти сотрудников, зарплата которых выше средней по отделу

WITH dept_avg AS (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
)
SELECT e.name, e.salary, e.department_id, d.avg_sal
FROM employees e
JOIN dept_avg d USING (department_id)
WHERE e.salary > d.avg_sal;

Задача 7. Иерархия менеджеров (рекурсивный CTE)

WITH RECURSIVE chain AS (
    SELECT id, name, manager_id, 0 AS level FROM employees WHERE name = 'Иван'
    UNION ALL
    SELECT e.id, e.name, e.manager_id, c.level + 1
    FROM employees e JOIN chain c ON e.id = c.manager_id
)
SELECT * FROM chain;

От сотрудника вверх по цепочке начальников.

Задача 8. Календарь всех дней месяца с выручкой (включая нули)

WITH RECURSIVE cal AS (
    SELECT DATE '2026-04-01' AS day
    UNION ALL
    SELECT day + INTERVAL '1 day' FROM cal WHERE day < DATE '2026-04-30'
)
SELECT cal.day, COALESCE(SUM(o.amount), 0) AS revenue
FROM cal LEFT JOIN orders o ON o.created_at::DATE = cal.day
GROUP BY cal.day ORDER BY cal.day;

Задача 9. Когорты: первая и последняя активность

WITH user_activity AS (
    SELECT user_id,
        MIN(event_time) AS first_seen,
        MAX(event_time) AS last_seen
    FROM events
    GROUP BY user_id
)
SELECT
    DATE_TRUNC('month', first_seen) AS cohort,
    COUNT(*) AS users,
    AVG(EXTRACT(EPOCH FROM (last_seen - first_seen)) / 86400) AS avg_lifetime_days
FROM user_activity
GROUP BY cohort
ORDER BY cohort;

Задача 10. Категории товаров с ростом продаж MoM >20%

WITH monthly AS (
    SELECT category, DATE_TRUNC('month', created_at) AS month, SUM(amount) AS rev
    FROM orders JOIN products USING (product_id)
    GROUP BY category, DATE_TRUNC('month', created_at)
),
with_lag AS (
    SELECT category, month, rev,
        LAG(rev) OVER (PARTITION BY category ORDER BY month) AS prev_rev
    FROM monthly
)
SELECT category, month, rev, prev_rev,
    ROUND(100.0 * (rev - prev_rev) / prev_rev, 1) AS growth_pct
FROM with_lag
WHERE prev_rev IS NOT NULL AND rev > prev_rev * 1.2;

---

Как тренироваться

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

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

Читайте также

• CTE vs временная таблица в SQL
• Оконные функции SQL: шпаргалка
• GROUP BY SQL: шпаргалка
• Рекурсивные CTE в SQL

FAQ

В чём разница между CTE и подзапросом?

По функциональности — они эквивалентны. CTE именуется и может использоваться несколько раз в основном запросе, подзапрос — одноразовая безымянная обёртка. В современных СУБД (PostgreSQL 13+, ClickHouse, Snowflake) оптимизатор обычно превращает CTE в подзапрос под капотом, так что по скорости разницы нет. Главное преимущество CTE — читаемость.

Когда использовать CTE, а когда временную таблицу?

CTE — если логика нужна только в этом запросе. Временная таблица — если результат переиспользуется в нескольких запросах, нужны индексы или данные тяжёлые (млн+ строк). Также временная таблица даёт оптимизатору больше свободы, когда CTE материализован принудительно и тормозит.

Может ли CTE ссылаться сам на себя?

Да — если он рекурсивный (ключевое слово RECURSIVE). Обычные CTE сами на себя ссылаться не могут. Рекурсивный CTE состоит из базового SELECT, UNION ALL и рекурсивного SELECT со ссылкой на CTE. Используется для иерархий, графов, генерации календарей.

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

Технически — десятки, если СУБД позволит. Практически 3–7 CTE — это чистый код. Если их больше 10, обычно часть стоит вынести во view или промежуточную таблицу — запрос становится слишком тяжёлым для поддержки.