Рекурсивный обход дерева категорий

Условие задания

**Контекст:** На маркетплейсе каталог товаров организован в дерево категорий произвольной глубины. Нужно агрегировать статистику по всему поддереву каждой корневой категории.

**Данные:**

[см. код в задании]

Пример дерева:
- Электроника (id=1)
- Смартфоны (id=10, parent=1)
- Apple (id=100, parent=10)
- Samsung (id=101, parent=10)
- Ноутбуки (id=11, parent=1)

**Задание:**

Для **каждой корневой категории** (`parent_id IS NULL`) выведите:
1. Название корневой категории
2. Общее количество **всех** подкатегорий на всех уровнях вложенности
3. Количество активных товаров во **всех** подкатегориях (включая саму корневую)
4. Среднюю цену активных товаров

Пример данных

Структура для ориентира — реальные значения из эталонного решения.

CREATE TABLE categories (
    id INT PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    parent_id INT REFERENCES categories(id)  -- NULL для корневых
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(300) NOT NULL,
    category_id INT NOT NULL REFERENCES categories(id),
    price NUMERIC(10,2) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE
);

Темы

рекурсивный CTE иерархия дерево WITH RECURSIVE

Подсказки

• WITH RECURSIVE tree AS (base_case UNION ALL recursive_case) — PostgreSQL-синтаксис рекурсивного CTE
• base_case: SELECT id, name, id AS root_id FROM categories WHERE parent_id IS NULL
• recursive_case: SELECT c.id, c.name, t.root_id FROM categories c JOIN tree t ON c.parent_id = t.id
• После раскрытия дерева JOIN к products и агрегация по root_id

Все тестовые задания →

Частые вопросы

Какой уровень знаний нужен для задачи "Рекурсивный обход дерева категорий"?

Это задание для уровня Senior. Senior-уровень — глубокое понимание темы, опыт решения нестандартных задач, обсуждение trade-off на собеседовании.

На каких собеседованиях встречается такая задача?

Подобные задания в категории «SQL» регулярно дают на собеседованиях аналитика данных в Яндекс, Сбер, Ozon, Авито, Тинькофф, Wildberries, T-Bank, X5, ВТБ и других крупных IT-компаниях. Тематика: рекурсивный CTE, иерархия, дерево, WITH RECURSIVE.

Сколько времени даётся на решение?

На реальном собеседовании на подобную задачу отводится 30-60 минут с обсуждением подходов, оптимизаций и trade-off. Для тренировки рекомендуем сначала решить самостоятельно, потом сверить с эталонным решением и подсказками.

Где ещё потренироваться по теме «SQL»?

На zasqlpython.ru есть 425 SQL задач в песочнице с автопроверкой кода, конспекты SQL для аналитика, AI мок-собеседование с разбором ваших ответов.

← Все задания