Перейти к основному содержимому

Практика: CTE и рекурсивные запросы


Онлайн редактор кода для PostgreSQL

Здесь вы можете попробовать свои силы в программировании на языке SQL для работы с PostgreSQL. Для этого мы предоставляем вам онлайн редактор кода, в котором вы можете написать свой код и запустить его.


❗️ При обновлении страницы код пропадёт, по этому, сохраните свой код куда-нибудь, если он важный.

Задание 1 — Простые (не-рекурсивные) CTE​

Задание 1: Простые CTE + читаемость и переиспользование

⏱️ Примерное время: 10-20 минут

Разбей запрос на 2–4 CTE, улучши читаемость, используй переиспользование.


Требования:

  • минимум 2–3 CTE
  • хотя бы одно переиспользование CTE (ссылка на предыдущий CTE)
  • понятные имена CTE
  • итоговый SELECT с ORDER BY и LIMIT 10–20
  • используй ROUND(…, 2) для денег

Пример:

WITH recent_sales AS (
    SELECT * FROM sales
    WHERE sale_date >= CURRENT_DATE - INTERVAL '90 days'
),
employee_revenue AS (
    SELECT
        employee_id,
        SUM(quantity * unit_price) AS revenue
    FROM recent_sales
    GROUP BY employee_id
)
SELECT
    e.name,
    e.department,
    ROUND(er.revenue, 2) AS total_revenue
FROM employee_revenue er
JOIN employees e ON er.employee_id = e.employee_id
ORDER BY er.revenue DESC
LIMIT 5;

Задание 2 — Рекурсия: иерархия сотрудников​

Задание 2: Дерево сотрудников (рекурсивный CTE)

⏱️ Примерное время: 20-35 минут

Построй иерархию сотрудников с использованием рекурсии.


Требования:

  • WITH RECURSIVE
  • базовый случай + рекурсивный (UNION ALL)
  • защита от циклов (массив path / visited) — обязательно!
  • отступы через REPEAT(' ', level)
  • ORDER BY path или path_ids

Пример:

WITH RECURSIVE hierarchy AS (
    SELECT
        employee_id, name, manager_id, 1 AS level,
        ARRAY[employee_id] AS path
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT
        e.employee_id, e.name, e.manager_id, h.level + 1,
        h.path || e.employee_id
    FROM employees e
    JOIN hierarchy h ON e.manager_id = h.employee_id
    WHERE e.employee_id != ALL(h.path)
)
SELECT
    REPEAT('  ', level-1) || name AS structure,
    level,
    array_to_string(path, ' → ') AS reporting_line
FROM hierarchy
ORDER BY path;

Задание 3 — Рекурсия: дерево категорий и BOM​

Задание 3: Дерево категорий / Bill of Materials

⏱️ Примерное время: 25-40 минут

Работа с иерархией категорий или компонентов продукта.


Требования:

  • рекурсия по parent_category_id
  • путь в виде строки ('Электроника > Смартфоны > Аксессуары')
  • защита от циклов
  • JOIN с products / sales где нужно

Пример:

WITH RECURSIVE cat_tree AS (
    SELECT category_id, category_name, parent_category_id,
           category_name AS full_path, 1 AS level
    FROM categories
    WHERE parent_category_id IS NULL
    UNION ALL
    SELECT c.category_id, c.category_name, c.parent_category_id,
           ct.full_path || ' > ' || c.category_name, ct.level + 1
    FROM categories c
    JOIN cat_tree ct ON c.parent_category_id = ct.category_id
)
SELECT full_path, p.product_name, p.price
FROM cat_tree ct
JOIN products p ON p.category_id = ct.category_id
ORDER BY ct.full_path, p.price DESC;

Задание 4 — Генерация серий (даты, числа)​

Задание 4: Генерация последовательностей через рекурсию

⏱️ Примерное время: 20-35 минут

Создай календарь / последовательность и соедини с данными.


Требования:

  • рекурсивная генерация дат / чисел
  • LEFT JOIN к реальным данным
  • COALESCE(…, 0) для пропусков

Пример:

WITH RECURSIVE dates AS (
    SELECT MIN(sale_date) AS dt FROM sales
    UNION ALL
    SELECT dt + 1 FROM dates WHERE dt < (SELECT MAX(sale_date) FROM sales)
)
SELECT d.dt, COALESCE(SUM(s.quantity * s.unit_price), 0) AS revenue
FROM dates d
LEFT JOIN sales s ON s.sale_date = d.dt
GROUP BY d.dt
ORDER BY d.dt;

Задание 5 — Итоговое: Аналитический отчёт с рекурсией​

Задание 5: Комплексный отчёт с CTE и рекурсией

⏱️ Примерное время: 50-100 минут

Создай аналитический отчёт, объединяющий несколько CTE, включая хотя бы одну рекурсию.


Требования:

  • минимум 4–6 CTE
  • хотя бы 1 рекурсивный CTE
  • комбинация агрегатов, оконных функций, JOIN-ов
  • красивое форматирование (ROUND, TO_CHAR, отступы)
  • защита от циклов в рекурсии
  • итоговый вывод — готовый к презентации / дашборду

Это задание отлично подходит для портфолио или демонстрации на собеседовании.

подсказка

Рекурсия — это когда ты начинаешь думать как дерево, а не как таблица.
Главные правила:

  1. Всегда защита от циклов (path array + проверка)
  2. Ограничение глубины (level < 20)
  3. Понятные имена CTE — это 50% успеха