В отчёте нужно посчитать выручку по странам пользователей только по оплаченным заказам за период, причём шаг «оплаченные за период» используется ещё в трёх соседних метриках. Какой подход обычно делает запрос проверяемее и позволяет переиспользовать фильтрацию?
CTE (WITH) и подзапросы позволяют разбивать сложные запросы на логические блоки. На собеседованиях проверяют умение использовать коррелированные подзапросы, EXISTS, IN, а также строить цепочки CTE для пошагового решения задач. Хороший аналитик пишет читаемый SQL, а не вложенные подзапросы на пять уровней.
Всего в этом разделе 20 вопросов. Каждый — с правильным ответом и кратким разбором теории. Разбито на 4 части по 5 вопросов.
1Нужно вывести список пользователей, которые сделали покупку в 2025 году, без дубликатов. Какой вариант делает это корректно без `DISTINCT`?
A`SELECT u.user_id FROM users u WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.user_id AND o.created_at >= '2025-01-01' AND o.created_at < '2026-01-01')`
B`SELECT u.user_id FROM users u JOIN orders o ON o.user_id = u.user_id WHERE o.created_at >= '2025-01-01' AND o.created_at < '2026-01-01'`
C`SELECT o.user_id FROM orders o WHERE o.created_at >= '2025-01-01' AND o.created_at < '2026-01-01' GROUP BY o.user_id HAVING COUNT(*) >= 1`
D`SELECT u.user_id FROM users u WHERE (SELECT MAX(o.order_id) FROM orders o WHERE o.user_id = u.user_id AND o.created_at >= '2025-01-01' AND o.created_at < '2026-01-01') IS NOT NULL`
Ответ: `EXISTS` проверяет наличие связанных строк и сохраняет по одной строке внешней таблицы — без `DISTINCT`.
Если брать строки напрямую из таблицы заказов или соединять её с пользователями через `JOIN`, пользователь с несколькими заказами появится несколько раз — без `DISTINCT` или агрегации это даст дубликаты. Вариант с `GROUP BY` тоже опирается на агрегацию по заказам и работает не на уровне пользователей. Конструкция `EXISTS` проверяет наличие хотя бы одной связанной строки и не размножает строки внешней таблицы, поэтому возвращает каждого пользователя ровно один раз.
2Вы пишете `SELECT u.user_id, (SELECT order_id FROM orders o WHERE o.user_id = u.user_id) AS last_order_id FROM users u`. Что может пойти не так и как исправить, чтобы подзапрос стал скалярным?
AЗапрос всегда вернёт случайный `order_id`, потому что подзапрос выполняется один раз на весь внешний запрос и кэшируется по `user_id`.
BНужно заменить подзапрос на `IN (SELECT order_id ...)`, иначе внешний `SELECT` не сможет выполниться и вернёт ошибку синтаксиса.
CНужно добавить `DISTINCT` во внешний `SELECT u.user_id`, и тогда подзапрос автоматически станет скалярным и вернёт ровно одно значение.
DЕсли у пользователя несколько заказов, подзапрос вернёт несколько строк и упадёт; нужно гарантировать одно значение через `MAX`/`MIN` или `LIMIT 1`.
Ответ: Скалярный подзапрос в `SELECT` должен возвращать ровно одну строку и одну колонку для каждой строки внешнего запроса.
Если подзапрос потенциально возвращает несколько строк (например, у пользователя несколько заказов), СУБД обычно выдаст ошибку «more than one row returned». Чтобы сделать подзапрос скалярным, нужно явно свести результат к одному значению: агрегатом (`MAX`/`MIN`) или ограничением (`LIMIT 1` вместе с явным правилом выбора).
3Нужно посчитать долю выручки каждого товара от общей выручки за один и тот же период. Какой вариант делает расчёт прозрачнее и избегает дублирования фильтра по периоду?
AВо внешнем `SELECT` с `GROUP BY product_id` посчитать `SUM(amount)` по товару, а общий `SUM(amount)` взять скалярным подзапросом, повторив `WHERE date BETWEEN ...` дважды
BСделать базовый `WITH base AS (...)` с фильтром по периоду, посчитать `total` и `per_product` из `base`, и в финальном `SELECT` делить `per_product.product_amt` на `total.total_amt`
CВ `WHERE` использовать коррелированный подзапрос со ссылкой на `outer.product_id`, чтобы оставить только товары с высокой долей и не считать `SUM(amount)` отдельно
DУбрать `WHERE date BETWEEN ...` из основного `SELECT` и применить фильтр по периоду уже после выгрузки в `pandas`, переложив согласованность на пользователя
Ответ: Базовый `CTE` помогает один раз зафиксировать период и правила отбора, а затем строить от него и числитель, и знаменатель.
Доля — это отношение двух агрегатов, которые должны быть рассчитаны на одном и том же наборе строк. Если фильтр копируется в два места, легко ошибиться (период или условия различатся) и получить неконсистентную метрику. При подходе с `WITH base AS (...)` вы явно задаёте общий источник данных и используете его повторно, поэтому логика становится детерминированной и проверяемой. Скалярный подзапрос с дублированием фильтра хрупок, коррелированный подзапрос меняет смысл (фильтрует строки, а не считает долю), а перенос фильтра наружу разрывает контракт расчёта.
4В фильтре вы пишете `WHERE o.product_id = (SELECT product_id FROM featured_products)`, а в таблице `featured_products` несколько строк. Что верно?
AПодзапрос вернёт несколько строк, сравнение через `=` станет ошибкой; нужно использовать `IN` или сделать подзапрос скалярным через `MAX` / `MIN`
BСравнение через `=` автоматически возьмёт первый `product_id` из подзапроса по порядку строк, поэтому фильтр всё равно отработает без ошибок
CНужно заменить `=` на `LIKE`: оператор `LIKE` умеет работать с несколькими строками подзапроса и без явного `IN` подберёт совпадения
DДостаточно добавить `DISTINCT` в подзапрос: после `DISTINCT` он гарантированно вернёт ровно одну строку и сравнение через `=` станет корректным
Ответ: Оператор `=` ожидает одно значение справа; если подзапрос возвращает набор, используйте `IN` или агрегируйте до одного значения.
Подзапрос в правой части `=` обязан вернуть ровно одно значение. Если он возвращает несколько строк, большинство СУБД выдаёт ошибку. Если вы хотите проверить принадлежность множеству значений, используйте `IN`. Если по смыслу нужно именно одно значение, сделайте подзапрос скалярным — например, агрегатом `MAX` или `MIN` или явным ограничением результата.
5Нужно выбрать заказы, у которых `amount` выше среднего `amount` по тому же пользователю. Какой вариант `WHERE` корректно использует коррелированный подзапрос?
A`WHERE o.amount > (SELECT AVG(amount) FROM orders)`: подзапрос даёт одно глобальное среднее по всем заказам
B`WHERE o.amount > (SELECT AVG(amount) FROM orders WHERE user_id = 123)`: среднее берётся по конкретному `user_id`
C`WHERE o.amount > (SELECT AVG(o2.amount) FROM orders o2 WHERE o2.user_id = o.user_id)`: подзапрос связан через `o.user_id` внешнего
D`WHERE o.user_id IN (SELECT AVG(amount) FROM orders GROUP BY user_id)`: список средних сравнивается с `user_id` через `IN`
Ответ: Коррелированный подзапрос ссылается на колонку внешнего запроса (например, `o.user_id`), поэтому среднее считается отдельно для каждого пользователя.
В коррелированном варианте подзапрос ссылается на колонку внешней таблицы (`o.user_id`), поэтому среднее считается отдельно для каждой строки внешнего запроса — то есть для каждого пользователя. Некоррелированный подзапрос вычисляет одно глобальное значение и сравнивает его со всеми строками. Вариант с захардкоженным `user_id = 123` ограничивает сравнение одним пользователем, а вариант с `IN` ломается логически: `AVG(amount)` сравнивается с `user_id`, что бессмысленно по типу.