Подзапросы и CTE: вопросы для собеседования (часть 4)
CTE (WITH) и подзапросы позволяют разбивать сложные запросы на логические блоки. На собеседованиях проверяют умение использовать коррелированные подзапросы, EXISTS, IN, а также строить цепочки CTE для пошагового решения задач. Хороший аналитик пишет читаемый SQL, а не вложенные подзапросы на пять уровней.
Вопросы 16–20 из 20
16Дашборд содержит две метрики на одних и тех же продажах: топ товаров по выручке за период и общая выручка за тот же период. Витрина обновляется ежедневно, и важно, чтобы фильтр по периоду был один и тот же для обеих цифр. Какой подход надёжнее защищает от рассинхронизации?
AСкопировать одинаковый фильтр по периоду в два разных подзапроса в `FROM sales`
BСчитать топ через `IN (SELECT ...)`, а общую выручку через `EXISTS` на тех же данных
CБазовый CTE `WITH base_sales AS (...)` с фильтром по периоду и расчёт обоих чисел из него
DПоставить фильтр по периоду только в `SUM`, а в группировке полагаться на `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 (...)` с проверкой каждого шага
Ответ: Многошаговый `WITH` делает расчёт метрики похожим на пайплайн: каждый шаг имеет имя и понятную гранулярность.
Для метрик вроде конверсии важно контролировать уровни данных: одна строка на пользователя в регистрациях, одна строка на покупку в транзакциях, затем связка по `user_id` и окну времени. Когда каждый шаг оформлен как отдельный `CTE`, вы можете проверить объёмы и примеры данных на каждом этапе и быстрее найти, где появилась ошибка (неверная связь, лишние строки, неверный фильтр). Большой вложенный запрос трудно отлаживать; коррелированный подзапрос смешивает уровни; один подзапрос в `FROM` не даёт промежуточных проверок.
Подробный разбор → 20Нужно найти пользователей, у которых был хотя бы один заказ и при этом нет ни одного заказа со статусом `canceled`. Какой вариант корректно выражает это условие?
A`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')`
B`SELECT u.user_id FROM users u WHERE u.user_id NOT IN (SELECT o.user_id FROM orders o WHERE o.status = 'canceled') AND u.user_id IN (SELECT user_id FROM orders)`
C`SELECT u.user_id FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id AND o.status <> 'canceled') AND u.user_id IN (SELECT user_id FROM orders)`
D`SELECT u.user_id FROM users u LEFT JOIN orders o ON o.user_id = u.user_id GROUP BY u.user_id HAVING COUNT(o.order_id) > 0 AND SUM(CASE WHEN o.status = 'canceled' THEN 1 ELSE 0 END) = 0`
Ответ: Комбинация `EXISTS` + `NOT EXISTS` явно задаёт «есть хотя бы одна строка» и «нет ни одной строки с условием» для одного пользователя.
Важно разделить две части бизнес-логики: у пользователя должен быть хотя бы один заказ и среди его заказов не должно быть отменённых. `NOT IN` неожиданно ведёт себя при `NULL`, а вариант с `EXISTS (... <> 'canceled')` пропускает пользователей, у которых среди заказов есть и отменённые, и обычные. Пара `EXISTS` и `NOT EXISTS` делает оба требования явными и читабельными.
Подробный разбор →