Глубина в реферальном дереве

Условие задачи

В таблице referrals каждый пользователь мог быть приглашён другим (referred_by). Пользователи без пригласителя — корни своих деревьев. Для каждого пользователя определи корень его дерева (root_id) и глубину (depth): у корня depth = 0, у непосредственно приглашённого им — 1 и т.д. Columns: root_id, user_id, user_name, depth. Сортируй по root_id, depth, user_id.

Схема данных

CREATE TABLE referrals (
  user_id      INTEGER PRIMARY KEY,
  user_name    TEXT NOT NULL,
  referred_by  INTEGER
);

Темы

cte recursive hierarchy tree null

Подсказки

• Это обход дерева — нужен рекурсивный CTE (WITH RECURSIVE).
• Якорь (anchor) — корни: WHERE referred_by IS NULL, у них depth = 0 и root_id = собственный user_id.
• Рекурсивная часть джойнит referrals к уже найденным узлам по r.referred_by = chain.user_id, протаскивает root_id неизменным и делает depth + 1.

Что проверяет эта задача

Задача «Глубина в реферальном дереве» учит разбивать сложный запрос на читаемые шаги через CTE (WITH), а где нужно — использовать рекурсию. Такие задачи встречаются на собеседованиях в Kaspi и других IT-компаниях. Уровень сложный — задача проверяет глубину и умение собрать запрос из нескольких приёмов.

Как подступиться к решению

Сначала разберись со схемой данных и пойми, какой результат нужен на выходе. Определи, какие таблицы и поля задействованы, и собирай запрос пошагово: фильтрация → соединение или группировка → итоговый отбор и сортировка. Проверь результат на крайних случаях — NULL, дубликаты, пустые группы. Свой запрос сразу прогоняешь автопроверкой в интерактивной SQL-песочнице на реальной базе (SQLite и PostgreSQL 16 прямо в браузере).

Разбор темы с примерами — в гайде «CTE (Common Table Expressions) в SQL». Хочешь системно с нуля — пройди бесплатный курс «SQL с нуля».

Решить в интерактивной SQL-песочнице → ← Все SQL-задачи