JOIN и операции множеств: вопросы для собеседования (часть 7)
JOIN-ы — фундамент аналитического SQL. INNER, LEFT, RIGHT, FULL OUTER, CROSS JOIN, а также UNION, INTERSECT, EXCEPT — на собеседовании нужно не просто знать синтаксис, но и понимать, что происходит при дубликатах ключей и NULL-значениях. Задачи на JOIN встречаются на каждом собеседовании без исключения.
Вопросы 31–35 из 40
31В таблице `events(user_id, event_name)` один пользователь может иметь много событий каждого типа. Нужно получить список пользователей, которые сделали и `install`, и `purchase`, при этом вернуть каждого пользователя один раз. Какой запрос лучше подходит?
A`SELECT e1.user_id FROM events e1 INNER JOIN events e2 ON e1.user_id = e2.user_id WHERE e1.event_name = 'install' AND e2.event_name = 'purchase';`
B`SELECT user_id FROM events WHERE event_name IN ('install','purchase');`
C`SELECT user_id FROM events WHERE event_name = 'install' INTERSECT SELECT user_id FROM events WHERE event_name = 'purchase';`
D`SELECT user_id FROM events WHERE event_name = 'install' UNION ALL SELECT user_id FROM events WHERE event_name = 'purchase';`
Ответ: `INTERSECT` вернёт пересечение пользователей и не создаст мультипликацию строк, как self-`JOIN`.
`self-join` по `events` может сильно размножить строки: если у пользователя 3 `install` и 5 `purchase`, вы получите 15 строк. Это можно исправить через `DISTINCT`, но проще выразить задачу на уровне множеств: взять пользователей с `install` и пересечь с пользователями с `purchase` через `INTERSECT`. Так вы сразу получите уникальный список пользователей, прошедших оба шага воронки.
32Нужно сгенерировать матрицу A/B-тестов: 4 варианта заголовка × 3 варианта кнопки × 2 цвета фона. Каждый параметр хранится в отдельной таблице. Как получить все 24 комбинации?
AИспользовать `INNER JOIN` — он создаст все комбинации, если не указать условие соединения
BИспользовать `UNION ALL` для каждой пары и объединить результаты в один набор
CИспользовать `FULL OUTER JOIN` без условия — он создаст комбинации с обработкой `NULL`
DИспользовать `CROSS JOIN` между таблицами параметров — он создаст все возможные комбинации
Ответ: `CROSS JOIN` двух и более таблиц генерирует полное декартово произведение — все возможные комбинации строк: 4 × 3 × 2 = 24 строки.
Цепочка `CROSS JOIN`: `SELECT * FROM titles CROSS JOIN buttons CROSS JOIN colors` создаст 24 строки — все комбинации. Это законное применение декартова произведения: количество результатов контролируемо и нужно именно все сочетания. `INNER JOIN` без `ON` тоже даст декартово произведение, но `CROSS JOIN` явно показывает намерение. `FULL OUTER JOIN` без условия не создаёт комбинации — он просто объединяет строки.
33Вы соединяете агрегаты `daily_orders(day, platform, orders)` и `daily_sessions(day, platform, sessions)` для расчёта конверсии. Сделали `JOIN` только по `day` и заметили странные значения. В чём типичная ошибка?
AВ `JOIN` нельзя использовать колонку `day`, нужно использовать только `id`.
BИз-за `JOIN` пропали строки, потому что нужен `FULL JOIN`.
CПроизошло умножение строк: на каждый `day` соединились все `platform` между собой, потому что в условии не хватает `platform`.
DНичего страшного: `JOIN` сам сопоставит правильные `platform`.
Ответ: Если не включить все ключи (например, `day` и `platform`) в условие `ON`, агрегаты могут соединиться «крест-накрест» и раздуть метрики.
Обе таблицы агрегированы по двум измерениям: `day` и `platform`. Если соединить только по `day`, то строки разных платформ внутри одного дня начнут сочетаться друг с другом, создавая лишние комбинации и искажая `orders`/`sessions`. Правильное условие должно включать оба поля ключа, например `ON o.day = s.day AND o.platform = s.platform`.
34Нужно получить одну строку на пользователя: `users(user_id)` + время последнего события из `events(user_id, created_at)`. Что лучше всего сделать, чтобы `JOIN` не вернул несколько строк на одного пользователя?
AСделать `LEFT JOIN` `users` на `events` по `user_id` и ничего больше — дубликатов не будет.
BСначала агрегировать `events` до одной строки на `user_id` (например, `MAX(created_at)`), затем сделать `LEFT JOIN` этого результата к `users`.
CЗаменить соединение на `UNION`, чтобы строки «склеились».
DИспользовать `FULL JOIN`, он автоматически убирает дубликаты.
Ответ: Если справа несколько событий на пользователя, то обычный `JOIN` даст несколько строк; типичный выход — предварительно агрегировать правую таблицу до нужного уровня.
`events` обычно содержит много строк на одного пользователя. При `JOIN` каждая из них соединится с одной строкой из `users`, и вы получите дубликаты пользователей. Чтобы сохранить гранулярность «одна строка на пользователя», сначала посчитайте последнюю дату события на уровне `user_id` (например, через `MAX(created_at)` с `GROUP BY user_id`), а затем соединяйте с `users`.
35Нужно посчитать количество уникальных пользователей, которые совершили событие либо в web (`events_web(user_id)`), либо в app (`events_app(user_id)`). Какой запрос посчитает корректно, если один и тот же пользователь мог быть в обоих источниках?
A`SELECT COUNT(*) FROM (SELECT user_id FROM events_web UNION ALL SELECT user_id FROM events_app) t;`
B`SELECT COUNT(*) FROM (SELECT user_id FROM events_web UNION SELECT user_id FROM events_app) t;`
C`SELECT COUNT(*) FROM events_web w INNER JOIN events_app a ON w.user_id = a.user_id;`
D`SELECT COUNT(*) FROM (SELECT user_id FROM events_web INTERSECT SELECT user_id FROM events_app) t;`
Ответ: Для уникальных пользователей по двум источникам используйте `UNION` (он убирает повторы) перед `COUNT(*)`.
Если использовать `UNION ALL`, пользователь, встретившийся в обоих источниках, попадёт дважды и будет посчитан два раза. `UNION` возвращает объединение множеств без дублей, поэтому `COUNT(*)` по результату даст количество уникальных пользователей, которые были хотя бы в одном источнике.
Хотите тренировать интерактивно?
В приложении — таймер, прогресс, стрики и 1700+ вопросов по всем темам.
Тренировать в Telegram