Подзапросы и CTE: вопросы для собеседования (часть 3)
CTE (WITH) и подзапросы позволяют разбивать сложные запросы на логические блоки. На собеседованиях проверяют умение использовать коррелированные подзапросы, EXISTS, IN, а также строить цепочки CTE для пошагового решения задач. Хороший аналитик пишет читаемый SQL, а не вложенные подзапросы на пять уровней.
Вопросы 11–15 из 20
11Какое утверждение лучше всего описывает коррелированный подзапрос?
AОн обязательно должен находиться в `FROM`.
BОн не может содержать агрегаты вроде `COUNT` или `AVG`.
CОн всегда возвращает одну строку, иначе SQL не работает.
DОн ссылается на колонки внешнего запроса (например, `o.user_id`) и логически оценивается отдельно для каждой строки внешнего запроса.
Ответ: Корреляция появляется, когда подзапрос использует алиасы/колонки из внешнего запроса.
Некоррелированный подзапрос можно выполнить «сам по себе» и получить один результат/набор. Коррелированный зависит от текущей строки внешнего запроса: в нём есть ссылка на внешний алиас (например, `o.user_id`), поэтому логически он пересчитывается для каждой внешней строки. Это важно понимать, чтобы не забыть условие связи и не получить неверный результат.
12Вы выбираете пользователей, у которых есть хотя бы один платеж. В таблице `payments` поле `user_id` иногда бывает `NULL` (например, анонимные платежи). Почему в такой ситуации часто предпочитают `EXISTS`, а не `IN`?
A`IN` и `EXISTS` всегда эквивалентны, поэтому выбор не важен.
BЛучше `IN`, потому что он автоматически отбрасывает `NULL` в подзапросе.
CЛучше `IN`, потому что он возвращает `TRUE`, если в подзапросе есть хотя бы один `NULL`.
DЧасто выбирают `EXISTS`, потому что он проверяет факт существования строк и не зависит от `NULL` в значениях подзапроса.
Ответ: `EXISTS` отвечает на вопрос «есть ли подходящая строка», а `IN` сравнивает значения и может дать `UNKNOWN`, если список содержит `NULL`.
Предикат `x IN (subquery)` использует трёхзначную логику: если прямого совпадения нет, но в наборе есть `NULL`, результат может стать `UNKNOWN`, и строка не пройдёт фильтр. `EXISTS` не сравнивает значения и потому не «ломается» из-за `NULL`: он просто проверяет, есть ли хотя бы одна строка, удовлетворяющая условиям корреляции.
13Нужно найти пользователей, чей самый ранний заказ (то есть `MIN(created_at)`) попадает в 2024 год. Какой вариант корректен?
A`SELECT user_id FROM orders WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01' GROUP BY user_id`
B`SELECT user_id FROM orders o WHERE o.created_at = (SELECT MIN(created_at) FROM orders) AND o.created_at >= '2024-01-01' AND o.created_at < '2025-01-01'`
C`WITH first_order AS (SELECT user_id, MIN(created_at) AS first_dt FROM orders GROUP BY user_id) SELECT user_id FROM first_order WHERE first_dt >= '2024-01-01' AND first_dt < '2025-01-01'`
D`SELECT user_id FROM users u WHERE u.user_id IN (SELECT MIN(created_at) FROM orders GROUP BY user_id)`
Ответ: Сначала нужно вычислить `MIN(created_at)` на уровне пользователя, а потом фильтровать пользователей по году — это удобно делать через CTE.
Если просто отфильтровать `orders` по 2024 году, вы найдёте пользователей с любым заказом в 2024, но их первый заказ мог быть раньше. Правильная логика: (1) получить по каждому `user_id` дату первого заказа, (2) оставить тех, у кого эта дата лежит в нужном диапазоне. CTE делает этот двухшаговый расчёт понятным и проверяемым.
14Что является практическим преимуществом разбиения сложного запроса на шаги через `WITH` по сравнению с несколькими вложенными подзапросами?
A`WITH` автоматически делает результаты «точнее», чем вложенные подзапросы.
B`WITH` запрещает использовать `NULL`, поэтому меньше ошибок.
C`WITH` гарантирует, что запрос будет выполняться быстрее.
D`WITH` позволяет дать шагам имена, переиспользовать их и проверять промежуточные результаты отдельно.
Ответ: `WITH` улучшает читаемость: вы именуете шаги и можете запускать их по отдельности для проверки.
CTE превращает сложную логику в последовательность небольших шагов: фильтрация, обогащение, агрегация. Это снижает когнитивную нагрузку и помогает обнаруживать ошибки раньше (например, неправильную гранулярность или лишние строки). Кроме того, если один и тот же шаг нужен дважды, его можно переиспользовать без копирования условий.
15Нужно вывести по каждому заказу признак `has_refund`: есть ли хотя бы одна запись в `refunds` по этому заказу. Таблица `refunds` может иметь несколько строк на один заказ. Какой вариант корректен и не размножает заказы?
A`SELECT o.order_id, r.refund_id IS NOT NULL AS has_refund FROM orders o LEFT JOIN refunds r ON r.order_id = o.order_id`
B`SELECT o.order_id, (SELECT r.refund_id FROM refunds r WHERE r.order_id = o.order_id) AS has_refund FROM orders o`
C`SELECT o.order_id, CASE WHEN EXISTS (SELECT 1 FROM refunds r WHERE r.order_id = o.order_id) THEN 1 ELSE 0 END AS has_refund FROM orders o`
D`SELECT o.order_id FROM orders o WHERE o.order_id IN (SELECT r.order_id FROM refunds r) AND 1 AS has_refund`
Ответ: `EXISTS` внутри `CASE` даёт один логический ответ на строку `orders` и не создаёт дубликаты строк.
`LEFT JOIN` к таблице `refunds` может размножить строки заказов, если по заказу несколько возвратов. Скалярный подзапрос, который возвращает `refund_id`, тоже рискован: при нескольких строках он упадёт. Конструкция `CASE WHEN EXISTS (...) THEN 1 ELSE 0 END` явно выражает проверку наличия связанных строк и всегда возвращает одно значение на заказ.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram