Подзапросы и CTE: вопросы для собеседования (часть 2)
CTE (WITH) и подзапросы позволяют разбивать сложные запросы на логические блоки. На собеседованиях проверяют умение использовать коррелированные подзапросы, EXISTS, IN, а также строить цепочки CTE для пошагового решения задач. Хороший аналитик пишет читаемый SQL, а не вложенные подзапросы на пять уровней.
Вопросы 6–10 из 20
6Вы ищете пользователей без заказов. Почему запрос `SELECT u.user_id FROM users u WHERE u.user_id NOT IN (SELECT o.user_id FROM orders o)` может вернуть 0 строк и какой подход безопаснее?
A`NOT IN` всегда корректен, проблема только в том, что он медленнее `NOT EXISTS`.
BНужно добавить `ORDER BY` в подзапрос, иначе `NOT IN` не работает.
CЕсли подзапрос возвращает хотя бы один `NULL`, то условие `NOT IN` может стать `UNKNOWN` для всех строк; безопаснее использовать `NOT EXISTS` с корреляцией `o.user_id = u.user_id`.
DНужно заменить `NOT IN` на `!=`, чтобы исключить `NULL`.
Ответ: `NOT EXISTS` проверяет отсутствие связанных строк, а `NOT IN` чувствителен к `NULL` в списке значений.
В трёхзначной логике SQL выражение `x NOT IN (...)` может стать `UNKNOWN`, если в списке есть `NULL`, даже когда явного совпадения нет. В `WHERE` это означает, что строка не пройдёт фильтр, и итог может оказаться пустым. `NOT EXISTS` формулирует задачу иначе: «нет ни одной строки, удовлетворяющей связи и условию», и поэтому гораздо надёжнее для анти-джойна.
7Нужно выбрать пользователей, у которых число заказов выше среднего по всем пользователям. Какой запрос корректно считает «среднее по пользователям», а не «среднее по заказам»?
A`WITH cnt AS (SELECT user_id, COUNT(*) AS orders_cnt FROM orders GROUP BY user_id) SELECT user_id FROM cnt WHERE orders_cnt > (SELECT AVG(orders_cnt) FROM cnt)`
B`SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) > (SELECT AVG(COUNT(*)) FROM orders)`
C`SELECT user_id FROM orders GROUP BY user_id HAVING COUNT(*) > (SELECT AVG(amount) FROM orders)`
D`SELECT user_id FROM users WHERE user_id IN (SELECT AVG(orders_cnt) FROM cnt)`
Ответ: Нужно сначала агрегировать заказы до уровня пользователя, а затем считать среднее уже по этому уровню.
Среднее по пользователям означает: для каждого пользователя считаем `COUNT(*)`, получаем таблицу «пользователь → число заказов», и только потом берём `AVG` по этим значениям. Если пытаться посчитать среднее напрямую по таблице `orders`, легко перепутать уровень данных и получить другую метрику (например, среднее по строкам заказов). CTE помогает явно зафиксировать нужную гранулярность.
8Вы хотите вывести по каждому пользователю `orders_cnt` и `last_order_dt`. Какой вариант обычно проще читать и расширять новыми метриками?
AДва коррелированных подзапроса в `SELECT`: один с `COUNT`, другой с `MAX`.
BОдин подзапрос в `SELECT`, который возвращает сразу две колонки (`COUNT` и `MAX`).
CСделать CTE `WITH user_agg AS (SELECT user_id, COUNT(*) AS orders_cnt, MAX(created_at) AS last_order_dt FROM orders GROUP BY user_id)` и затем `LEFT JOIN` к `users`.
DИспользовать `IN` в `WHERE`, а счётчики посчитать уже после выгрузки данных.
Ответ: Предагрегация в CTE делает уровни данных явными: одна строка на `user_id`, затем присоединение к `users`.
Несколько скалярных подзапросов в `SELECT` быстро превращаются в трудночитаемый набор «встроенных» расчётов. Когда вы агрегируете факты в одном CTE, вы явно фиксируете гранулярность (1 строка на пользователя) и можете добавлять новые поля в одном месте, не размножая вложенность. Плюс промежуточный CTE удобно проверить отдельно.
9Нужно посчитать средний дневной доход: сначала `SUM(amount)` по каждому дню, затем `AVG` по дням. Какой запрос соответствует этой логике?
A`SELECT AVG(day_revenue) FROM (SELECT pay_date, SUM(amount) AS day_revenue FROM payments GROUP BY pay_date) d`
B`SELECT AVG(SUM(amount)) FROM payments GROUP BY pay_date`
C`SELECT AVG(amount) FROM payments`
D`SELECT SUM(amount) / COUNT(*) FROM payments`
Ответ: Для «агрегации над агрегацией» удобно вынести первый уровень в подзапрос в `FROM` (или CTE), а затем агрегировать второй раз.
Средний дневной доход — это среднее по дням, а не среднее по платежам. Поэтому сначала нужно получить таблицу «день → выручка дня» (через `GROUP BY pay_date`), и только потом считать `AVG` по этим дневным суммам. Подзапрос в `FROM` делает уровни агрегации явными и уменьшает вероятность перепутать метрику.
10В отчёте нужно посчитать выручку по странам пользователей только по оплаченным заказам за период. Какой подход обычно делает запрос более читаемым и позволяет переиспользовать шаг фильтрации?
AСделать один большой `SELECT` и несколько раз повторить одинаковые условия в `WHERE`.
BВыделить оплаченные заказы в CTE через `WITH paid_orders AS (...)`, а затем агрегировать по странам из этой CTE.
CВычислять выручку по стране через коррелированный подзапрос в `SELECT` для каждой строки пользователя.
DИспользовать несколько вложенных `IN`-подзапросов вместо явного шага, чтобы не писать `JOIN`.
Ответ: `WITH` (CTE) позволяет вынести общий шаг (например, фильтрацию оплаченных заказов) в именованный блок и использовать его дальше как таблицу.
Когда один и тот же набор строк нужен нескольким частям запроса, CTE помогает избежать дублирования условий: фильтры пишутся один раз, а затем используются в финальном `SELECT`. Это повышает читаемость и снижает шанс, что фильтры «разъедутся» между метриками. Плюс промежуточный шаг легко проверить отдельно, выполнив CTE как самостоятельный запрос.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram