SQL для аналитика
Написание запросов, которое спрашивают вживую — виды JOIN, GROUP BY и HAVING, операции над множествами, анти-джойны и подзапросы, представления, оконные функции и разбор сломанного запроса.
14 вопросов
JuniorКодОчень частоКаждый клиент с суммой его заказов, включая клиентов без единого заказа
Каждый клиент с суммой его заказов, включая клиентов без единого заказа
LEFT JOIN от customers к orders сохраняет клиентов без заказов; GROUP BY по клиенту и COALESCE(SUM(o.amount), 0) превращают NULL несопоставленных строк в 0. INNER JOIN отбросил бы всех без заказов.
Типичные ошибки
- ✗Брать INNER JOIN и терять каждого клиента без заказов
- ✗Считать, что SUM по несопоставленной стороне уже даёт 0 без COALESCE
- ✗Добавлять WHERE по присоединённому столбцу и снова терять клиентов без заказов
Уточняющие вопросы
- →Почему SUM по несопоставленной стороне возвращает NULL, а не 0?
- →Как в том же запросе показать ещё и число заказов на клиента?
JuniorТеорияОчень частоКакие виды JOIN существуют, и что LEFT JOIN возвращает такого, чего не даёт INNER JOIN?
Какие виды JOIN существуют, и что LEFT JOIN возвращает такого, чего не даёт INNER JOIN?
Основные виды — INNER, LEFT, RIGHT, FULL, CROSS. INNER оставляет только строки, совпавшие в обеих таблицах. LEFT возвращает все левые строки, подставляя NULL в правые столбцы без совпадения — сохраняя левые строки, которые INNER отбрасывает.
Типичные ошибки
- ✗Считать, что LEFT и INNER различаются лишь скоростью, а не набором строк
- ✗Думать, что LEFT JOIN хранит несопоставленные строки с обеих сторон, как FULL
- ✗Забывать, что несопоставленные правые столбцы возвращаются как NULL, а не пустыми
Уточняющие вопросы
- →Когда RIGHT JOIN предпочтительнее, чем переписать его как LEFT JOIN?
- →Чем FULL OUTER JOIN отличается здесь от LEFT JOIN?
JuniorТеорияОчень частоВ чём разница между WHERE и HAVING, и почему агрегат нельзя фильтровать в WHERE?
В чём разница между WHERE и HAVING, и почему агрегат нельзя фильтровать в WHERE?
WHERE фильтрует отдельные строки до группировки; HAVING фильтрует целые группы после агрегации. Агрегат вроде SUM(x) нельзя писать в WHERE, ведь WHERE выполняется до группировки, и значения агрегата ещё нет. Условия на агрегаты идут в HAVING.
Типичные ошибки
- ✗Писать условие на агрегат в WHERE и получать ошибку синтаксиса
- ✗Считать WHERE и HAVING взаимозаменяемыми синонимами
- ✗Думать, что HAVING фильтрует строки, а не уже сформированные группы
Уточняющие вопросы
- →Может ли запрос использовать и WHERE, и HAVING, и в каком порядке они применяются?
- →Где становится доступен алиас из списка SELECT — в WHERE или в HAVING?
JuniorТеорияЧастоЧем различаются TRUNCATE, DELETE и DROP, и какие из них можно откатить?
Чем различаются TRUNCATE, DELETE и DROP, и какие из них можно откатить?
DELETE удаляет выбранные строки транзакционно, поэтому ROLLBACK внутри транзакции отменяет её. TRUNCATE быстро очищает всю таблицу; DROP удаляет саму таблицу. На большинстве СУБД TRUNCATE и DROP — это DDL с авто-коммитом, и откатить их нельзя.
Типичные ошибки
- ✗Считать, что TRUNCATE транзакционен и откатывается, как DELETE
- ✗Думать, что DROP лишь удаляет строки, оставляя пустую таблицу
- ✗Полагать, что все три эквивалентны, кроме скорости выполнения
Уточняющие вопросы
- →Почему DELETE сбрасывает меньше состояния, чем TRUNCATE, например счётчики auto-increment?
- →На каких СУБД TRUNCATE всё же может быть транзакционным?
JuniorТеорияЧастоВ чём разница между UNION и UNION ALL, и что должно совпадать у двух SELECT-ов?
В чём разница между UNION и UNION ALL, и что должно совпадать у двух SELECT-ов?
Оба ставят результаты двух SELECT вертикально. UNION убирает дубликаты ценой лишнего прохода; UNION ALL оставляет все строки и быстрее. Оба требуют одинакового числа столбцов совместимых, позиционно совпадающих типов.
Типичные ошибки
- ✗Считать, что UNION ALL убирает дубли, а UNION оставляет всё
- ✗Думать, что два SELECT могут возвращать разное число столбцов
- ✗Брать UNION, когда дублей быть не может, и платить за сортировку
Уточняющие вопросы
- →Когда UNION ALL правильный выбор, даже если дубли возможны?
- →Как выбираются имена столбцов итога — из какого SELECT?
MiddleКодЧастоСтроки таблицы A, у которых нет совпадения в таблице B — двумя способами
Строки таблицы A, у которых нет совпадения в таблице B — двумя способами
Способ один — LEFT JOIN b ON b.a_id = a.id WHERE b.a_id IS NULL, оставляя несопоставленные левые строки. Способ два — WHERE NOT EXISTS (SELECT 1 FROM b WHERE b.a_id = a.id). Предпочитайте NOT EXISTS: NOT IN ломается на NULL в подзапросе.
Типичные ошибки
- ✗Брать INNER JOIN, который возвращает совпадения, а не их отсутствие
- ✗Считать, что NOT IN и NOT EXISTS ведут себя одинаково при наличии NULL
- ✗Фильтровать по IS NOT NULL не той стороны и переворачивать результат
Уточняющие вопросы
- →Почему NULL в подзапросе ломает NOT IN, но не NOT EXISTS?
- →Как планировщик обычно выполняет форму LEFT JOIN / IS NULL?
MiddleКодЧастоПодсчёт заказов по статусам, показывая 0 для статусов без заказов
Подсчёт заказов по статусам, показывая 0 для статусов без заказов
Ведут запрос от statuses с LEFT JOIN к orders, чтобы статус без заказа давал строку. COUNT(o.id) — по присоединённому столбцу, а не COUNT(*) — потому что COUNT по NULL несопоставленной стороны даёт 0, а COUNT(*) посчитал бы строку-заполнитель как 1.
Типичные ошибки
- ✗Брать INNER JOIN, который отбрасывает статусы без заказов
- ✗Брать COUNT(*) на outer join и считать строку-заполнитель как 1
- ✗Считать, что пустой скалярный подзапрос вернёт 0, а не NULL
Уточняющие вопросы
- →Почему COUNT(o.id) даёт 0, а COUNT(*) — 1 для пустого статуса?
- →Как в том же запросе показать ещё и сумму заказов на статус?
MiddleКодЧастоНайти строки, дублирующиеся по (email, phone), и оставить только самую раннюю
Найти строки, дублирующиеся по (email, phone), и оставить только самую раннюю
Нумеруют строки внутри каждой пары (email, phone) по возрасту и берут первую — ROW_NUMBER() OVER (PARTITION BY email, phone ORDER BY created_at) AS rn в подзапросе, затем фильтр rn = 1. Обычный GROUP BY схлопнул бы группу, но не вернул бы всю раннюю строку целиком.
Типичные ошибки
- ✗Ждать, что DISTINCT выберет раннюю строку, а не точные дубли
- ✗Считать, что MIN(created_at) тянет прочие столбцы из той же строки
- ✗Полагаться на физический порядок строк вместо явного ORDER BY
Уточняющие вопросы
- →Как разрешить ничьи, когда две строки делят один created_at?
- →Как изменится запрос, если дубли нужно удалить на месте?
MiddleТеорияЧастоЧто NULL делает с проверкой на равенство, с COUNT() и с NOT IN (подзапрос)?
Что NULL делает с проверкой на равенство, с COUNT() и с NOT IN (подзапрос)?
NULL означает «неизвестно», поэтому x = NULL даёт UNKNOWN, а не true — проверяют через IS NULL. COUNT(*) считает все строки, а COUNT(col) пропускает строки, где col равен NULL. А NOT IN (подзапрос) не вернёт ничего при любом NULL в подзапросе.
Типичные ошибки
- ✗Фильтровать null через
= NULLвместоIS NULL - ✗Считать, что COUNT(col) учитывает строки, где столбец NULL
- ✗Использовать NOT IN с подзапросом, дающим NULL, и терять все строки
Уточняющие вопросы
- →Как трёхзначная логика меняет то, что возвращает WHERE?
- →Как переписать NOT IN, рискующий получить NULL, чтобы он был безопасным?
MiddleКодЧастоТри самых высокооплачиваемых сотрудника в каждом отделе
Три самых высокооплачиваемых сотрудника в каждом отделе
Ранжируют внутри отдела оконной функцией, затем фильтруют. Помещают ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) в подзапрос и оставляют строки, где номер <= 3. Берут RANK(), чтобы сохранить всех с третьей зарплатой.
Типичные ошибки
- ✗Брать глобальный LIMIT 3, дающий три строки всего, а не на отдел
- ✗Пытаться достать ранги 1-3 повторными агрегатами MAX
- ✗Путать RANK и ROW_NUMBER, когда третья зарплата делится
Уточняющие вопросы
- →Чем RANK, DENSE_RANK и ROW_NUMBER различаются на равных зарплатах?
- →Как написать это без оконных функций, коррелированным подзапросом?
JuniorТеорияИногдаЧто такое представление (view), что такое материализованное представление, и когда что использовать?
Что такое представление (view), что такое материализованное представление, и когда что использовать?
View — сохранённый запрос, виртуальная таблица, выполняющая SELECT при каждом чтении; актуальна, без хранения. Материализованное хранит результат физически — чтение быстрое, но данные устаревают до обновления. View — для живых данных, материализованное — для тяжёлых частых запросов.
Типичные ошибки
- ✗Считать, что обычное view хранит строки физически, как таблица
- ✗Думать, что материализованное представление актуально само, без обновления
- ✗Путать материализованное представление с временной таблицей сессии
Уточняющие вопросы
- →Что запускает обновление материализованного представления, и бывает ли оно инкрементальным?
- →Можно ли писать через view, обновляя нижележащую таблицу?
SeniorДебаггингИногдаНеагрегированный столбец стоит рядом с COUNT(*) при неполном GROUP BY и даёт бессмыслицу — исправьте
Неагрегированный столбец стоит рядом с COUNT(*) при неполном GROUP BY и даёт бессмыслицу — исправьте
department_name не сгруппирован и не агрегирован — это некорректный SQL. Строгие СУБД его отвергают; MySQL в нестрогом режиме берёт произвольное значение строки — отсюда бессмыслица. Исправить — добавить его в GROUP BY или обернуть в MIN/MAX.
Типичные ошибки
- ✗Считать, что функционально зависимый столбец подставляется автоматически
- ✗Винить отображение или collation вместо правила о негруппированном столбце
- ✗Думать, что именно COUNT(*) делает запрос некорректным
Уточняющие вопросы
- →Почему строгий и нестрогий режимы SQL расходятся в том, запустится ли это?
- →Когда исключение о функциональной зависимости для GROUP BY действительно допустимо?
SeniorДебаггингИногдаTRUNCATE, затем ROLLBACK, но строки всё равно исчезли — объясните почему
TRUNCATE, затем ROLLBACK, но строки всё равно исчезли — объясните почему
В MySQL и многих СУБД TRUNCATE — это DDL, а DDL вызывает неявный COMMIT открытой транзакции перед запуском. Поэтому TRUNCATE уже зафиксирован, и ROLLBACK нечего отменять. DELETE — это DML, и он бы откатился.
Типичные ошибки
- ✗Считать, что TRUNCATE участвует в окружающей транзакции, как DELETE
- ✗Винить тайминг или сброс на диск, а не неявный commit
- ✗Думать, что ROLLBACK вообще не отменяет удаление строк, даже DELETE
Уточняющие вопросы
- →Какие ещё команды вызывают тот же неявный commit перед выполнением?
- →Как переписать операцию, чтобы она была действительно откатываемой?
SeniorКодРедкоКлиенты, заказавшие каждый продукт из заданной категории
Клиенты, заказавшие каждый продукт из заданной категории
Группируют заказы клиента в категории и оставляют тех, чьё число различных продуктов равно итогу категории — HAVING COUNT(DISTINCT product_id) = (SELECT COUNT(*) FROM products WHERE category_id = :cat). DISTINCT не даёт повторам завысить счёт.
Типичные ошибки
- ✗Использовать IN, возвращающий заказавших любой продукт, а не все
- ✗Считать без DISTINCT, и повторные заказы завышают совпадение
- ✗Сравнивать через >= вместо =, пропуская лишние продукты
Уточняющие вопросы
- →Как формулировка с двойным NOT EXISTS выразит то же деление?
- →Почему DISTINCT необходим, если клиент может заказать один продукт повторно?
