21 апреля 2026 г.

Задачи на CTE SQL на собеседовании

Q: Что такое CTE?

Common Table Expression — именованный подзапрос, который можно использовать в следующем SELECT. Начинается с `WITH`.

Q: Когда CTE лучше подзапроса?

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

Q: Рекурсивный CTE — часто спрашивают?

Middle-позиция: могут спросить структуру. Senior: скорее всего дадут задачу на иерархию.

Q: Как CTE влияет на производительность?

В Postgres 12+ и в большинстве СУБД — оптимизируется как подзапрос. В старых системах — как materialized. --- Тренируйте SQL — [откройте тренажёр](https://t.me/kariernik_bot/app?startapp=web_blog_zadachi-na-cte-sobesedovanie) с 200+ задачами SQL и 1500+ вопросами для собесов.

Карьерник — квиз-тренажёр в Telegram с 1500+ задач и вопросов для собесов аналитика. SQL, Python, A/B, метрики. Бесплатно.

Зачем спрашивают CTE

На собесах middle-аналитика CTE — обязательный навык. Проверяют:

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

Синтаксис (напоминание)

WITH name AS (
    SELECT ...
)
SELECT * FROM name;

Цепочка:

WITH step1 AS (...),
     step2 AS (SELECT ... FROM step1),
     step3 AS (SELECT ... FROM step2)
SELECT * FROM step3;

Задачи с разборами

Задача 1. Пользователи с 3+ заказами и их средний чек

WITH active_users AS (
    SELECT user_id, COUNT(*) AS orders_cnt
    FROM orders
    GROUP BY user_id
    HAVING COUNT(*) >= 3
)
SELECT
    au.user_id,
    au.orders_cnt,
    AVG(o.total) AS avg_check
FROM active_users au
JOIN orders o ON o.user_id = au.user_id
GROUP BY au.user_id, au.orders_cnt;

Идея: сначала фильтр на активных, потом JOIN для среднего чека.

Задача 2. Когорта первой покупки

WITH first_orders AS (
    SELECT
        user_id,
        MIN(created_at) AS first_order_at
    FROM orders
    GROUP BY user_id
)
SELECT
    DATE_TRUNC('month', first_order_at) AS cohort,
    COUNT(*) AS new_buyers
FROM first_orders
GROUP BY 1
ORDER BY 1;

Задача 3. Retention Day-7

WITH first_day AS (
    SELECT user_id, MIN(DATE(event_at)) AS d0
    FROM events
    GROUP BY user_id
),
day_7_active AS (
    SELECT DISTINCT f.user_id
    FROM first_day f
    JOIN events e ON e.user_id = f.user_id
        AND DATE(e.event_at) = f.d0 + INTERVAL '7 days'
)
SELECT
    (SELECT COUNT(*) FROM day_7_active)::FLOAT
    / (SELECT COUNT(*) FROM first_day) AS retention_d7;

Задача 4. Топ-3 товара в каждой категории

WITH ranked AS (
    SELECT
        product_id,
        category,
        sales,
        DENSE_RANK() OVER (PARTITION BY category ORDER BY sales DESC) AS rnk
    FROM products
)
SELECT product_id, category, sales
FROM ranked
WHERE rnk <= 3;

Задача 5. Рекурсивный CTE — иерархия сотрудников

WITH RECURSIVE emp_tree 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, et.level + 1
    FROM employees e
    JOIN emp_tree et ON e.manager_id = et.id
)
SELECT * FROM emp_tree ORDER BY level, name;

Ключевое на собесе: знать структуру рекурсивного CTE — anchor + UNION ALL + recursive part.

Задача 6. Серия активных дней

Найти пользователей, которые заходили 7 дней подряд.

WITH user_days AS (
    SELECT
        user_id,
        DATE(event_at) AS day,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY DATE(event_at)) AS rn
    FROM events
    GROUP BY user_id, DATE(event_at)
),
streaks AS (
    SELECT
        user_id,
        day - rn * INTERVAL '1 day' AS streak_group,
        COUNT(*) AS streak_length
    FROM user_days
    GROUP BY user_id, streak_group
)
SELECT DISTINCT user_id
FROM streaks
WHERE streak_length >= 7;

Задача 7. Когортный retention

WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', MIN(created_at)) AS cohort_month
    FROM users
    GROUP BY user_id
),
activity AS (
    SELECT
        user_id,
        DATE_TRUNC('month', event_at) AS activity_month
    FROM events
    GROUP BY user_id, DATE_TRUNC('month', event_at)
)
SELECT
    c.cohort_month,
    a.activity_month,
    COUNT(DISTINCT a.user_id) AS active_users
FROM cohorts c
JOIN activity a ON a.user_id = c.user_id
GROUP BY 1, 2
ORDER BY 1, 2;

Задача 8. Разные срезы в одном запросе

WITH by_day AS (
    SELECT DATE_TRUNC('day', created_at) AS day, SUM(total) AS rev
    FROM orders GROUP BY 1
),
by_category AS (
    SELECT category, SUM(total) AS rev
    FROM orders GROUP BY 1
),
totals AS (
    SELECT SUM(total) AS total_rev FROM orders
)
SELECT 'daily' AS type, by_day.day::TEXT AS key, by_day.rev FROM by_day
UNION ALL
SELECT 'category', by_category.category, by_category.rev FROM by_category;

Задача 9. Последние 3 заказа каждого пользователя

WITH ranked_orders AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
)
SELECT * FROM ranked_orders WHERE rn <= 3;

Задача 10. Пересечение двух таблиц через CTE

WITH emails_a AS (SELECT DISTINCT email FROM users_a),
     emails_b AS (SELECT DISTINCT email FROM users_b)
SELECT email FROM emails_a
INTERSECT
SELECT email FROM emails_b;

Задача 11. Сгенерировать календарь дат (рекурсивный CTE)

WITH RECURSIVE dates AS (
    SELECT DATE '2026-01-01' AS d
    UNION ALL
    SELECT d + INTERVAL '1 day'
    FROM dates
    WHERE d < '2026-12-31'
)
SELECT * FROM dates;

В Postgres чаще используют generate_series, но задача на собесе — написать через рекурсию.

Задача 12. Running total

WITH daily_sales AS (
    SELECT
        DATE_TRUNC('day', created_at) AS day,
        SUM(total) AS daily_rev
    FROM orders
    GROUP BY 1
)
SELECT
    day,
    daily_rev,
    SUM(daily_rev) OVER (ORDER BY day) AS running_total
FROM daily_sales;

CTE vs подзапросы

Критерий	CTE	Подзапрос
Читаемость	высокая	средняя при вложенности
Переиспользование	да (в одном запросе)	нет (копипаста)
Рекурсия	да	нет
Производительность	иногда хуже (materialized)	inline

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

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

Ошибка 1. RECURSIVE без UNION ALL

-- ошибка: в рекурсивной части должен быть UNION ALL
WITH RECURSIVE t AS (
    SELECT 1 AS n
    SELECT n + 1 FROM t WHERE n < 10  -- пропущен UNION ALL
)

Ошибка 2. Бесконечная рекурсия

-- забыли условие выхода → бесконечный цикл
WITH RECURSIVE t AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM t  -- нет WHERE → бесконечно
)

Ошибка 3. Обращение к последующему CTE

-- нельзя: step1 ссылается на step2, который определяется позже
WITH step1 AS (SELECT * FROM step2),
     step2 AS (...)

Порядок определения важен.

Ошибка 4. Большие данные в materialized CTE

В старых Postgres (< 12) каждый CTE был materialized (физически отдельная таблица в памяти). На больших данных — медленно. В 12+ работает как inline, если не указать MATERIALIZED.

Связанные темы

FAQ

Что такое CTE?

Common Table Expression — именованный подзапрос, который можно использовать в следующем SELECT. Начинается с WITH.

Когда CTE лучше подзапроса?

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

Рекурсивный CTE — часто спрашивают?

Middle-позиция: могут спросить структуру. Senior: скорее всего дадут задачу на иерархию.

Как CTE влияет на производительность?

В Postgres 12+ и в большинстве СУБД — оптимизируется как подзапрос. В старых системах — как materialized.

Тренируйте SQL — откройте тренажёр с 200+ задачами SQL и 1500+ вопросами для собесов.

Тренироваться в Telegram