Подзапросы и CTE на собеседовании аналитика
Зачем на собеседовании спрашивают подзапросы и CTE
Реальные аналитические задачи редко решаются одним простым запросом. Нужно сначала посчитать метрику, потом отфильтровать по ней, потом объединить с другими данными. Подзапросы и CTE (Common Table Expressions, конструкция WITH) — два способа разбить сложный запрос на шаги.
Интервьюер проверяет сразу несколько вещей: умеете ли вы декомпозировать задачу, знаете ли разницу между подзапросом и CTE, можете ли написать читаемый запрос, понимаете ли нюансы производительности.
Подзапросы и CTE — инструменты декомпозиции. На собеседовании они показывают, как вы думаете: сваливаете всё в один запрос или разбиваете на понятные шаги.
Подзапросы: виды и применение
Скалярный подзапрос — возвращает одно значение. Используется в SELECT, WHERE, HAVING. Пример: сравнить выручку каждого магазина со средней выручкой по сети. Подзапрос в WHERE вычисляет среднее, основной запрос фильтрует по нему.
Табличный подзапрос (подзапрос в FROM) — возвращает таблицу, к которой можно обращаться как к обычному источнику данных. Используется, когда нужно сначала агрегировать данные, а потом работать с результатом. Важно: табличному подзапросу нужен алиас.
Подзапрос в WHERE с IN, EXISTS, ANY — фильтрация по набору значений из другой таблицы. «Пользователи, которые совершили хотя бы один заказ» — WHERE user_id IN (SELECT user_id FROM orders) или WHERE EXISTS (SELECT 1 FROM orders WHERE ...).
Коррелированный подзапрос — подзапрос, который ссылается на внешний запрос. Выполняется для каждой строки внешнего запроса. Пример: для каждого сотрудника найти коллег с зарплатой выше. Мощный, но медленный инструмент — интервьюер может спросить о производительности.
CTE: синтаксис и преимущества
CTE (WITH) позволяет вынести подзапрос в именованный блок перед основным запросом. Результат — тот же, но код читается значительно лучше: каждый шаг вычисления назван и изолирован.
Преимущества CTE перед подзапросами:
- Читаемость — каждый шаг назван и легко понимается отдельно. Вместо вложенных скобок — последовательные блоки.
- Переиспользование — к одному CTE можно обратиться несколько раз в основном запросе. Подзапрос пришлось бы дублировать.
- Отладка — можно выполнить каждый CTE отдельно и проверить промежуточный результат.
- Рекурсия — только CTE поддерживает рекурсивные запросы.
Когда подзапрос лучше CTE
Не всегда CTE — правильный выбор. Скалярный подзапрос в WHERE для простого фильтра — компактнее и понятнее, чем отдельный CTE. Подзапрос с EXISTS для проверки существования — стандартный паттерн, который не нуждается в CTE.
Общее правило: если подзапрос короткий, используется один раз и не усложняет чтение — оставьте подзапрос. Если запрос сложный, содержит несколько шагов или один промежуточный результат используется дважды — переходите на CTE.
На собеседовании интервьюер оценит, если вы объясните свой выбор. Просто сказать «я всегда использую CTE» — слабый ответ. Сказать «здесь CTE лучше, потому что промежуточный результат используется дважды» — сильный.
Рекурсивные CTE
Рекурсивный CTE состоит из якорного запроса (начальное условие) и рекурсивного запроса (ссылается на сам себя), объединённых через UNION ALL. Применение: обход иерархий (найти всех подчинённых руководителя), генерация последовательностей дат, работа с графами.
Рекурсивные CTE — продвинутая тема. Для junior и middle достаточно знать, что они существуют и для чего применяются. Уметь написать — бонус, который выделит вас среди кандидатов.
Типичные задачи с разбором
Задача 1: Пользователи с выручкой выше средней. Подход — скалярный подзапрос для вычисления средней выручки, основной запрос фильтрует по нему. Альтернатива — CTE с предварительным расчётом. Интервьюер может попросить переписать один вариант в другой.
Задача 2: Для каждого пользователя — последний заказ с деталями. Подход — CTE с ROW_NUMBER для нумерации заказов по дате, основной запрос фильтрует rn = 1. Типичная комбинация CTE и оконной функции.
Задача 3: Товары, которые покупали вместе. Подход — CTE для промежуточной таблицы пар товаров внутри одного заказа, затем агрегация и сортировка. Задача проверяет умение декомпозировать.
Вложенность и производительность
Три уровня вложенности подзапросов — предел читаемости. Глубже — сигнал переписать на CTE. На собеседовании глубоко вложенный запрос показывает неумение планировать решение.
В современных СУБД (PostgreSQL 12+, Snowflake, BigQuery) CTE и подзапрос выполняются одинаково — оптимизатор разворачивает CTE. На собеседовании достаточно сказать: «разница в производительности минимальна, выбор — вопрос читаемости».
FAQ
Когда лучше использовать CTE, а когда подзапрос?
CTE лучше, когда промежуточный результат используется более одного раза или задача требует рекурсии. Подзапрос лучше для простых случаев: скалярное значение в WHERE, проверка EXISTS. На собеседовании ценится умение обосновать выбор.
Влияет ли CTE на производительность запроса?
В современных СУБД (PostgreSQL 12+, Snowflake, BigQuery) — практически нет. Оптимизатор разворачивает CTE и оптимизирует запрос целиком. Выбирайте между CTE и подзапросом по читаемости, а не по скорости.
Нужно ли знать рекурсивные CTE для junior-позиции?
Нет, для junior достаточно обычных CTE и подзапросов. Рекурсия появляется на middle и senior собеседованиях в задачах с иерархиями. Если знаете — бонус, но не обязательно.