Подзапросы и 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`NOT IN` требует `ORDER BY` в подзапросе: без сортировки фильтр работает нестабильно и строки могут пропадать
C`NULL` в подзапросе делает `NOT IN` равным `UNKNOWN` для всех строк: безопаснее использовать `NOT EXISTS` с корреляцией
DЗамена `NOT IN` на `<>` по списку значений исключает `NULL` из сравнения и даёт корректный набор строк
Ответ: `NOT EXISTS` проверяет отсутствие связанных строк, а `NOT IN` чувствителен к `NULL` в списке значений и может обнулить выборку.
В трёхзначной логике SQL выражение `x NOT IN (...)` становится `UNKNOWN`, если в списке есть `NULL`, даже когда явного совпадения нет. В `WHERE` это означает, что строка не пройдёт фильтр, и итог может оказаться пустым. `NOT EXISTS` формулирует задачу иначе: «нет ни одной строки, удовлетворяющей связи и условию», и поэтому надёжнее для анти-джойна. Замены через `<>` или `ORDER BY` проблему `NULL` не решают.
Подробный разбор → 7Нужно выбрать пользователей, у которых число заказов выше среднего по всем пользователям. Какой запрос корректно считает «среднее по пользователям», а не «среднее по заказам»?
A`SELECT o.user_id FROM orders o GROUP BY o.user_id HAVING COUNT(*) > (SELECT AVG(COUNT(*)) FROM orders GROUP BY user_id)`
B`SELECT o.user_id FROM orders o GROUP BY o.user_id HAVING COUNT(*) > (SELECT AVG(amount) AS avg_amt FROM orders)`
C`WITH cnt AS (SELECT user_id, COUNT(*) AS o FROM orders GROUP BY user_id) SELECT user_id FROM cnt WHERE o > (SELECT AVG(o) FROM cnt)`
D`SELECT u.user_id FROM users u WHERE u.user_id IN (SELECT AVG(orders_cnt) FROM cnt GROUP BY user_id)`
Ответ: Нужно сначала агрегировать заказы до уровня пользователя, а затем считать среднее уже по этому уровню.
Среднее по пользователям означает: для каждого пользователя считаем `COUNT(*)`, получаем таблицу «пользователь → число заказов», и только потом берём `AVG` по этим значениям. Если пытаться посчитать среднее напрямую по таблице `orders`, легко перепутать уровень данных и получить другую метрику (например, среднее по строкам заказов). CTE помогает явно зафиксировать нужную гранулярность.
Подробный разбор → 8Вы хотите вывести по каждому пользователю `orders_cnt` и `last_order_dt`. Какой вариант обычно проще читать и расширять новыми метриками?
AДва коррелированных скалярных подзапроса в `SELECT`: один считает `COUNT(*)`, другой `MAX(created_at)` по той же таблице.
BОдин подзапрос в `SELECT`, который пытается вернуть сразу две колонки `COUNT(*)` и `MAX(created_at)` через одну запись.
CСделать `WITH user_agg AS (SELECT user_id, COUNT(*), MAX(created_at) FROM orders GROUP BY user_id)` и `LEFT JOIN` к users.
DСделать выгрузку через `IN` в `WHERE`, а счётчики и максимальную дату собрать уже после выгрузки в Python или Excel.
Ответ: Предагрегация в CTE делает уровни данных явными: одна строка на `user_id`, дальше присоединение к users.
Несколько скалярных подзапросов в `SELECT` быстро превращаются в трудночитаемый набор «встроенных» расчётов и плохо расширяются. Если агрегировать факты в одном CTE, гранулярность фиксируется явно: одна строка на пользователя, и новые поля добавляются в одном месте без вложенности. Промежуточный CTE удобно проверить отдельно, что снижает риск ошибок. Постобработка в Python/Excel или вторая выгрузка с `IN` усложняют поддержку и неэффективны.
Подробный разбор → 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` делает уровни агрегации явными и уменьшает вероятность перепутать метрику. Вариант с `AVG(SUM(...))` без подзапроса не работает; `AVG(amount)` и `SUM/COUNT` дают среднее по платежам, а не по дням.
Подробный разбор → 10В отчёте нужно посчитать выручку по странам пользователей только по оплаченным заказам за период, причём шаг «оплаченные за период» используется ещё в трёх соседних метриках. Какой подход обычно делает запрос проверяемее и позволяет переиспользовать фильтрацию?
AОдин большой `SELECT`, в котором одинаковые условия по статусу и периоду повторяются в каждом подзапросе
BCTE через `WITH paid_orders AS (...)`, а затем агрегация по странам и другим метрикам из этой CTE
CКоррелированный подзапрос в `SELECT` для каждой строки пользователя, считающий выручку отдельно
DНесколько вложенных `IN`-подзапросов вместо явного шага фильтрации, чтобы не писать `JOIN`
Ответ: `WITH` (CTE) позволяет вынести общий шаг (например, фильтрацию оплаченных заказов) в именованный блок и использовать его дальше как таблицу.
Когда один и тот же набор строк нужен нескольким частям запроса, CTE помогает избежать дублирования условий: фильтры пишутся один раз, а затем используются в финальном `SELECT`. Это повышает читаемость и снижает шанс, что фильтры «разъедутся» между метриками. Плюс промежуточный шаг легко проверить отдельно, выполнив CTE как самостоятельный запрос.
Подробный разбор →