Подзапросы и CTE: вопросы для собеседования (часть 4)
CTE (WITH) и подзапросы позволяют разбивать сложные запросы на логические блоки. На собеседованиях проверяют умение использовать коррелированные подзапросы, EXISTS, IN, а также строить цепочки CTE для пошагового решения задач. Хороший аналитик пишет читаемый SQL, а не вложенные подзапросы на пять уровней.
Вопросы 16–20 из 20
16Вы готовите дашборд: нужно (1) топ товаров по выручке за период и (2) общая выручка за тот же период. Какой вариант снижает риск, что фильтр по периоду рассинхронизируется между расчётами?
AСкопировать одинаковый фильтр в два разных подзапроса внутри `SELECT`.
BСделать CTE `WITH base_sales AS (...)` с фильтром по периоду и использовать его и для расчёта топа (через `GROUP BY`), и для общего `SUM`.
CСчитать топ товаров через `IN`, а общую выручку — через `EXISTS`.
DСделать фильтр только в одном месте, а в другом надеяться на `JOIN` без условий.
Ответ: CTE через `WITH` позволяет определить «базовый слой» данных один раз и использовать его дальше в нескольких расчётах.
Когда период/сегмент/исключения повторяются, дублирование условий повышает риск ошибки: одну часть обновили, другую забыли. Базовый CTE фиксирует набор строк, на котором строятся все метрики, и делает запрос более детерминированным и проверяемым: можно сначала посмотреть, что попало в базу, и только потом агрегировать.
17В одном отчёте вы считаете несколько метрик по событиям: `dau`, `wau` и число покупок. У всех метрик один и тот же фильтр: только продакшн-трафик и только выбранный период. Какой подход лучше защищает от ситуации, когда в одной метрике забыли часть фильтра?
AСделать базовый CTE `WITH base_events AS (...)` с общим фильтром и строить все метрики от него.
BКопировать один и тот же фильтр в каждый подзапрос/CTE отдельно.
CСделать отдельный подзапрос в `SELECT` для каждой метрики, повторяя `WHERE`.
DСначала посчитать метрики на сырых данных, а потом попытаться отфильтровать уже агрегированный результат.
Ответ: Общий базовый CTE через `WITH` снижает риск расхождения бизнес-логики между метриками.
Когда общий фильтр (период, среда, исключения) повторяется, дублирование условий делает запрос хрупким: достаточно один раз забыть условие — и метрика станет несравнимой с остальными. Базовый CTE фиксирует единый набор событий, после чего каждую метрику можно считать отдельным шагом. Это повышает детерминизм и упрощает проверку: сначала валидируете `base_events`, затем сравниваете агрегаты.
18Нужно выбрать заказы только тех пользователей, у которых был логин после `:cutoff_date`. Какой вариант корректен?
A`SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM events e WHERE e.event_name = 'login' AND e.event_time >= :cutoff_date)`
B`SELECT * FROM orders o WHERE EXISTS (SELECT 1 FROM events e WHERE e.user_id = o.user_id AND e.event_name = 'login' AND e.event_time >= :cutoff_date)`
C`SELECT * FROM orders o WHERE o.user_id = (SELECT e.user_id FROM events e WHERE e.event_name = 'login' AND e.event_time >= :cutoff_date)`
D`SELECT * FROM orders o WHERE o.user_id IN (SELECT COUNT(*) FROM events e WHERE e.event_name = 'login' AND e.event_time >= :cutoff_date)`
Ответ: Чтобы проверить активность именно текущего пользователя, подзапрос должен быть коррелирован по `user_id`.
В некоррелированном варианте `EXISTS` отвечает на вопрос «был ли вообще хоть один логин после даты» и, если да, пропустит все заказы. Корреляция `e.user_id = o.user_id` делает проверку персональной для каждой строки заказа. Ошибка с `=` вместо `IN`/`EXISTS` часто приводит к ситуации, когда подзапрос возвращает много строк и запрос падает.
19Вы считаете метрику: доля пользователей, которые совершили покупку в течение 7 дней после регистрации. Какой вариант построения запроса делает логику наиболее проверяемой и удобной для отладки?
AНаписать один огромный `SELECT` с несколькими уровнями вложенных подзапросов без имён.
BСделать коррелированный подзапрос в `SELECT`, который сразу возвращает 0/1, и не выделять выборку покупок отдельно.
CИспользовать `WITH` только для финального результата, а фильтры по регистрациям и покупкам держать внутри одного подзапроса в `FROM`.
DРазбить логику на несколько CTE, например `WITH signups AS (...), purchases AS (...), matched AS (...) SELECT ... FROM matched`: так каждый шаг можно запускать и проверять отдельно.
Ответ: Многошаговый `WITH` делает расчёт метрики похожим на пайплайн: каждый шаг имеет имя и понятную гранулярность.
Для метрик вроде конверсии важно контролировать уровни данных: 1 строка на пользователя в регистрациях, 1 строка на покупку в транзакциях, затем связка по `user_id` и окну времени. Когда каждый шаг оформлен как отдельный CTE, вы можете проверить объёмы и примеры данных на каждом этапе и быстрее найти, где появилась ошибка (неверная связь, лишние строки, неверный фильтр).
20Нужно найти пользователей, у которых был хотя бы один заказ и при этом нет ни одного заказа со статусом `'canceled'`. Какой вариант корректно выражает это условие?
A`SELECT u.user_id FROM users u WHERE u.user_id NOT IN (SELECT o.user_id FROM orders o WHERE o.status = 'canceled')`
B`SELECT u.user_id FROM users u LEFT JOIN orders o ON o.user_id = u.user_id GROUP BY u.user_id HAVING SUM(CASE WHEN o.status = 'canceled' THEN 1 ELSE 0 END) = 0`
C`SELECT u.user_id FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.status <> 'canceled')`
D`SELECT u.user_id FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id) AND NOT EXISTS (SELECT 1 FROM orders o2 WHERE o2.user_id = u.user_id AND o2.status = 'canceled')`
Ответ: Комбинация `EXISTS` + `NOT EXISTS` позволяет явно задать «есть хотя бы одна строка» и «нет ни одной строки с условием» для одного пользователя.
Важно разделить две части бизнес-логики: (1) у пользователя должен быть хотя бы один заказ и (2) среди его заказов не должно быть отменённых. `NOT IN` может неожиданно вести себя при `NULL`, а вариант с `LEFT JOIN` часто включает пользователей без заказов (они проходят условие суммы). Пара `EXISTS` и `NOT EXISTS` делает оба требования явными и читабельными.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram