Вопросы по теме «Подзапросы и CTE»
CTE (WITH) и подзапросы позволяют разбивать сложные запросы на логические блоки. На собеседованиях проверяют умение использовать коррелированные подзапросы, EXISTS, IN, а также строить цепочки CTE для пошагового решения задач. Хороший аналитик пишет читаемый SQL, а не вложенные подзапросы на пять уровней.
Всего в этом разделе 20 вопросов. Каждый — с правильным ответом и кратким разбором теории. Разбито на 4 части по 5 вопросов.
Вопросы 1–5 из 20
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'`
D`SELECT u.user_id FROM users u WHERE (SELECT 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` — удобный способ проверить наличие связанных строк, сохраняя по одной строке внешней таблицы.
Если вы берёте строки из `orders`, пользователь с несколькими заказами появится несколько раз. `JOIN` с `orders` ведёт к тому же эффекту, если не добавлять `DISTINCT` или агрегацию. `EXISTS` проверяет наличие хотя бы одной строки `orders` для текущего пользователя и поэтому не размножает строки `users`.
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Если у пользователя несколько заказов, подзапрос вернёт несколько строк и запрос упадёт; нужно гарантировать одно значение (например, `MAX`, `MIN` или `LIMIT 1`).
BЗапрос всегда вернёт случайный `order_id`, потому что подзапрос выполняется один раз.
CНужно заменить подзапрос на `IN`, иначе `SELECT` не сможет выполниться.
DНужно добавить `DISTINCT` во внешний `SELECT`, и тогда подзапрос станет скалярным.
Ответ: Скалярный подзапрос в `SELECT` должен возвращать ровно одну строку и одну колонку для каждой строки внешнего запроса.
Если подзапрос потенциально возвращает несколько строк (например, у пользователя несколько заказов), СУБД обычно выдаст ошибку «more than one row returned». Чтобы сделать подзапрос скалярным, нужно явно свести результат к одному значению: агрегатом (`MAX`/`MIN`) или ограничением (`LIMIT 1` вместе с явным правилом выбора).
3Нужно посчитать долю выручки каждого товара от общей выручки за один и тот же период. Какой вариант делает расчёт прозрачнее и избегает дублирования фильтра по периоду?
AВо внешнем `SELECT` посчитать выручку по товару, а общий `SUM` взять скалярным подзапросом, повторив фильтр по периоду дважды.
BСделать базовый CTE `WITH base AS (...)` с фильтром по периоду, затем посчитать `total` и `per_product` из `base`, и в финальном `SELECT` делить `per_product.product_amt` на `total.total_amt`.
CИспользовать коррелированный подзапрос в `WHERE`, чтобы сразу оставить только товары с высокой долей.
DУбрать фильтр по периоду из SQL и применить его уже после выгрузки данных.
Ответ: Базовый CTE помогает один раз зафиксировать период и правила отбора, а затем строить от него и числитель, и знаменатель.
Доля — это отношение двух агрегатов, которые должны быть рассчитаны на одном и том же наборе строк. Если фильтр копируется в два места, легко ошибиться (период или условия различатся) и получить неконсистентную метрику. При подходе с `WITH base AS (...)` вы явно задаёте общий источник данных и используете его повторно, поэтому логика становится детерминированной и проверяемой.
4В фильтре вы пишете `WHERE o.product_id = (SELECT product_id FROM featured_products)`, а в таблице `featured_products` несколько строк. Что верно?
ASQL автоматически возьмёт первый `product_id` из подзапроса, поэтому всё отработает.
BПодзапрос вернёт несколько строк, сравнение через `=` станет ошибкой; нужно использовать `IN` или сделать подзапрос скалярным (например, `MAX`/`MIN`).
CНужно заменить `=` на `LIKE`, чтобы работать с несколькими строками.
DДостаточно добавить `DISTINCT` в подзапрос, и он обязательно вернёт одну строку.
Ответ: Оператор `=` ожидает одно значение справа; если подзапрос возвращает набор, используйте `IN` или агрегируйте до одного значения.
Подзапрос в правой части `=` обязан вернуть ровно одно значение. Если он возвращает несколько строк, большинство СУБД выдаёт ошибку. Если вы хотите проверить принадлежность множеству значений, используйте `IN`. Если по смыслу нужно именно одно значение, сделайте подзапрос скалярным (например, агрегатом `MAX`/`MIN` или явным ограничением результата).
5Нужно выбрать заказы, у которых `amount` выше среднего `amount` по тому же пользователю. Какой вариант `WHERE` корректно использует коррелированный подзапрос?
A`SELECT * FROM orders o WHERE o.amount > (SELECT AVG(amount) FROM orders)`
B`SELECT * FROM orders o WHERE o.amount > (SELECT AVG(amount) FROM orders WHERE user_id = 123)`
C`SELECT * FROM orders o WHERE o.amount > (SELECT AVG(o2.amount) FROM orders o2 WHERE o2.user_id = o.user_id)`
D`SELECT * FROM orders o WHERE o.user_id IN (SELECT AVG(amount) FROM orders GROUP BY user_id)`
Ответ: Коррелированный подзапрос ссылается на колонку внешнего запроса (например, `o.user_id`), поэтому среднее считается отдельно для каждого пользователя.
В варианте с корреляцией условие внутри подзапроса связывает строки таблицы `orders` (алиас `o2`) со строкой внешней таблицы `orders` (алиас `o`). Так подзапрос вычисляет среднее только по заказам текущего пользователя, а не по всей таблице. Некоррелированный подзапрос посчитает одно глобальное значение и сравнит с ним все строки.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram