CTE в SQL: задачи для проверки кандидатов

CTE (Common Table Expressions) — инструмент структурирования сложных запросов. Кандидат, умеющий писать CTE, думает о читаемости и поддерживаемости кода. Это маркер зрелости как разработчика, так и аналитика.

Почему CTE важны на собеседовании

CTE решают три проблемы сложных запросов:

1. Читаемость — длинный запрос разбивается на логические блоки с понятными именами
2. Переиспользование — одну и ту же выборку можно использовать несколько раз
3. Рекурсия — рекурсивные CTE позволяют обходить иерархии (деревья организаций, категорий)

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

Задача 1: Простой CTE вместо подзапроса (Middle)

Попросите кандидата переписать запрос с подзапросом через CTE:

-- Исходный запрос с подзапросом (сложно читать)
SELECT c.name, stats.total_orders, stats.total_amount
FROM customers c
JOIN (
    SELECT customer_id, COUNT(*) AS total_orders, SUM(amount) AS total_amount
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
) stats ON stats.customer_id = c.id
WHERE stats.total_amount > 50000;

-- То же через CTE (читабельно)
WITH completed_orders AS (
    SELECT
        customer_id,
        COUNT(*)    AS total_orders,
        SUM(amount) AS total_amount
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT c.name, co.total_orders, co.total_amount
FROM customers c
JOIN completed_orders co ON co.customer_id = c.id
WHERE co.total_amount > 50000;

Что проверяем: синтаксис WITH, умение структурировать запрос.

Задача 2: Несколько CTE в одном запросе (Middle+)

-- Задача: найдите менеджеров, чья выручка выше средней по компании,
-- и покажите, на сколько процентов они превышают среднее

WITH manager_revenue AS (
    SELECT
        manager_id,
        SUM(amount) AS revenue
    FROM deals
    WHERE deal_date >= '2026-01-01'
    GROUP BY manager_id
),
company_avg AS (
    SELECT AVG(revenue) AS avg_revenue
    FROM manager_revenue
)
SELECT
    m.name,
    mr.revenue,
    ca.avg_revenue,
    ROUND(100.0 * (mr.revenue - ca.avg_revenue) / ca.avg_revenue, 1) AS pct_above_avg
FROM manager_revenue mr
JOIN managers m       ON m.id = mr.manager_id
CROSS JOIN company_avg ca
WHERE mr.revenue > ca.avg_revenue
ORDER BY mr.revenue DESC;

Что проверяем: несколько CTE, CROSS JOIN для передачи одного значения, процентный расчёт.

Задача 3: Рекурсивный CTE (Senior)

-- Схема: employees(id, name, manager_id)
-- manager_id = NULL для CEO

-- Задача: для каждого сотрудника найдите полный путь от него до CEO
-- например: "Анна → Борис → Директор"

WITH RECURSIVE org_path AS (
    -- Базовый случай: CEO (нет руководителя)
    SELECT
        id,
        name,
        manager_id,
        name::TEXT AS path,
        0 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Рекурсивный шаг: добавляем подчинённых
    SELECT
        e.id,
        e.name,
        e.manager_id,
        op.path || ' → ' || e.name,
        op.level + 1
    FROM employees e
    JOIN org_path op ON op.id = e.manager_id
)
SELECT id, name, path, level
FROM org_path
ORDER BY path;

Что проверяем: понимание рекурсии, базовый и рекурсивный шаги, конкатенация строк.

Уточняющий вопрос для Senior: «Как защититься от бесконечной рекурсии при циклических зависимостях в данных?»

Задача 4: CTE для анализа воронки

-- Задача: постройте воронку конверсии по этапам продаж

WITH funnel AS (
    SELECT
        stage,
        COUNT(DISTINCT deal_id) AS deals
    FROM deal_events
    WHERE event_date >= '2026-01-01'
    GROUP BY stage
),
ordered_funnel AS (
    SELECT *,
           SUM(deals) OVER () AS total_entered,
           FIRST_VALUE(deals) OVER (ORDER BY
               CASE stage
                   WHEN 'lead'     THEN 1
                   WHEN 'contact'  THEN 2
                   WHEN 'proposal' THEN 3
                   WHEN 'won'      THEN 4
               END
           ) AS first_stage_count
    FROM funnel
)
SELECT
    stage,
    deals,
    ROUND(100.0 * deals / first_stage_count, 1) AS conversion_from_start
FROM ordered_funnel
ORDER BY
    CASE stage
        WHEN 'lead' THEN 1
        WHEN 'contact' THEN 2
        WHEN 'proposal' THEN 3
        WHEN 'won' THEN 4
    END;

Вопросы для обсуждения CTE

После выполнения задач задайте теоретические вопросы:

«Чем CTE отличается от временной таблицы?» CTE существует только в рамках одного запроса и не сохраняется. Временная таблица сохраняется в сессии и может иметь индексы.

«Всегда ли CTE быстрее подзапроса?» Нет. В PostgreSQL до версии 12 CTE были «заборами оптимизации» — оптимизатор не мог протолкнуть предикаты внутрь CTE. С PostgreSQL 12 это изменилось, но поведение зависит от версии и флага MATERIALIZED.

«Когда стоит использовать MATERIALIZED в CTE?» Когда одно и то же CTE используется несколько раз и вычисление тяжёлое — WITH cte AS MATERIALIZED (...) гарантирует, что оно выполнится один раз.

Шкала оценки

Итого: Middle ≥ 5, Senior ≥ 12.

Кандидаты могут потренироваться с CTE на реальных задачах на SQLlab.ru — раздел «CTE и подзапросы» доступен в бесплатной части курса.