Вопросы по SQL
JOIN, индексы, транзакции и уровни изоляции, оптимизация запросов.
- 01Как посчитать DAU, WAU, MAU и скользящий MAU на SQL?
DAU — число уникальных пользователей за день: COUNT(DISTINCT user_id) GROUP BY day. MAU календарный — то же по месяцам. Скользящий MAU на каждый день — уникальные за последние 30…
Junior - 02Как удалить дубликаты и оставить последнюю версию записи?
Найти: GROUP BY ключ HAVING COUNT() > 1. Оставить последнюю версию: пронумеровать строки внутри ключа ROW_NUMBER() OVER (PARTITION BY ключ ORDER BY updated_at DESC) и взять rn = 1…
Junior - 03Как посчитать воронку конверсии на SQL?
Для каждого пользователя определить, дошёл ли он до каждого шага в правильном порядке (время шага не раньше предыдущего) и в пределах окна (например, 7 дней от первого шага)…
Middle - 04Как найти самую длинную серию дней подряд, когда пользователь был активен?
Классический приём «gaps and islands»: пронумеровать даты активности пользователя ROW_NUMBER и вычесть номер из даты — у дней одной непрерывной серии разность одинакова. Затем…
Middle - 05Почему после JOIN суммы в отчёте оказались больше, чем должны быть?
Связь один-ко-многим размножает строки: заказ с тремя позициями после JOIN с позициями превращается в три строки, и SUM(order.amount) учитывает сумму заказа трижды. Решение …
Junior - 06Как посчитать медиану и перцентили в SQL и почему среднее часто вводит в заблуждение?
В PostgreSQL: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) — медиана с интерполяцией между значениями, PERCENTILE_DISC — ближайшее реальное значение. В ClickHouse — quantile, в…
Junior - 07Как посчитать когортный retention на SQL?
Определить когорту каждого пользователя (обычно неделя или месяц первого действия), для каждого события посчитать номер периода от начала когорты, затем посчитать уникальных…
Middle - 08Как разбить поток событий на сессии на SQL?
Сессия — серия событий пользователя без пауз дольше порога (обычно 30 минут). LAG даёт время предыдущего события; где пауза больше порога — флаг начала новой сессии; нарастающая…
Middle - 09Как выбрать топ-N записей в каждой группе, например три самых дорогих заказа каждого клиента?
Пронумеровать строки внутри группы оконной функцией и отфильтровать номер во внешнем запросе: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) и WHERE rn <= 3…
Junior - 10Что такое оконные функции в SQL и чем они отличаются от GROUP BY?
GROUP BY схлопывает группу в одну строку; оконная функция считает по группе строк (окну), сохраняя все строки. Окно задаёт OVER (PARTITION BY … ORDER BY … ROWS …). Основные…
Junior - 11Что означает аббревиатура ACID?
ACID — гарантии транзакции: атомарность (всё или ничего), согласованность (инварианты не нарушаются), изоляция (параллельные транзакции не мешают друг другу), долговечность…
Junior - 12Какой уровень изоляции транзакций по стандарту SQL гарантированно предотвращает phantom reads?
По стандарту SQL фантомы гарантированно исключает только SERIALIZABLE. На практике PostgreSQL не показывает фантомов уже на REPEATABLE READ благодаря снимку.
Middle - 13Что такое проблема N+1 запросов и как её решать?
N+1 — один запрос за списком и ещё по запросу на связанные данные каждого элемента: 100 заказов → 101 запрос. Причина — ленивая загрузка связей в цикле.
Middle - 14Каков основной эффект добавления индекса по столбцу?
Индекс — отдельная упорядоченная структура (обычно B-дерево): поиск по колонке за O(log n) вместо полного просмотра таблицы. Цена — медленнее запись и больше места.
Junior - 15Чем B-tree индекс принципиально превосходит hash-индекс?
B-дерево упорядочено — поддерживает равенство, диапазоны, сортировку и префиксы строк. Hash-индекс — только равенство. Поэтому почти всегда выбирают B-дерево.
Middle - 16Что такое нормализация и когда оправдана денормализация?
Нормализация — каждый факт хранится в одном месте: нет дублей, а значит нет аномалий обновления. Денормализация — сознательное дублирование ради быстрого чтения.
Middle - 17Чем отличается оптимистичная блокировка от пессимистичной?
Пессимистическая — блокируем строку заранее (SELECT ... FOR UPDATE), остальные ждут. Оптимистическая — не блокируем, а при записи проверяем версию (WHERE version = ?); не совпала…
Middle - 18Какой JOIN вернёт все строки левой таблицы и совпавшие из правой (NULL там, где совпадений нет)?
LEFT JOIN возвращает все строки левой таблицы и совпавшие из правой; где совпадений нет — в колонках правой NULL.
Junior - 19Чем HAVING отличается от WHERE?
WHERE фильтрует строки до группировки, HAVING — группы после агрегации. Условие на count() или sum() — только в HAVING.
Junior - 20Что вернёт INNER JOIN?
INNER JOIN возвращает только пары строк, удовлетворяющие условию ON. Строки без пары в результат не попадают.
Junior - 21Чем primary key отличается от unique-ограничения?
Первичный ключ — главный идентификатор строки: уникален, NOT NULL, один на таблицу, на него ссылаются внешние ключи. UNIQUE — дополнительная гарантия уникальности, таких может…
Junior - 22Как правильно проверить значение на NULL в SQL?
NULL — «неизвестно», и любое сравнение с ним даёт UNKNOWN, а не TRUE. Поэтому проверяют только IS NULL и IS NOT NULL.
Junior - 23К какой группе SQL-команд относятся SELECT, INSERT, UPDATE, DELETE?
SELECT, INSERT, UPDATE, DELETE — DML (работа с данными). DDL — схема (CREATE, ALTER, DROP), TCL — транзакции (COMMIT, ROLLBACK), DCL — права (GRANT, REVOKE).
Junior - 24Чем TRUNCATE отличается от DELETE и DROP?
DELETE удаляет строки (можно с WHERE), построчно, с триггерами; TRUNCATE очищает таблицу целиком и быстро; DROP удаляет саму таблицу со структурой.
Junior - 25Почему в составном индексе важен порядок колонок?
Составной индекс (a, b) отсортирован сначала по a, затем по b — как телефонный справочник по фамилии и имени. Поэтому он помогает в поиске по a и по a + b, но не по одному b.
Middle - 26Что делает EXPLAIN для SQL-запроса?
EXPLAIN показывает план, который выбрал оптимизатор: способы чтения таблиц, порядок и алгоритмы соединений, оценки числа строк и стоимости. EXPLAIN ANALYZE ещё и выполняет запрос…
Middle - 27Чем PreparedStatement безопаснее обычного Statement?
PreparedStatement передаёт текст запроса и значения раздельно: значение никогда не разбирается как SQL — инъекция невозможна. Statement склеивает строку и выполняет как есть.
Junior - 28Как в JDBC выполнить несколько операций в одной транзакции вручную?
conn.setAutoCommit(false) → операции → commit(); при исключении rollback(); в finally вернуть autocommit и закрыть соединение.
Middle - 29Чем dirty read, non-repeatable read и phantom read отличаются друг от друга?
Грязное чтение — видим чужие неподтверждённые изменения. Неповторяемое — повторное чтение той же строки даёт другое значение. Фантом — повторный запрос с условием возвращает…
Middle - 30Что обеспечивает foreign key?
Внешний ключ гарантирует ссылочную целостность: нельзя сослаться на несуществующую строку и нельзя удалить строку, на которую ссылаются (если не задано иное).
Junior - 31Напиши SQL-запрос, возвращающий вторую по величине зарплату из таблицы employee(id, salary).
SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 1 — или подзапрос с MAX, или оконная функция DENSE_RANK.
Junior - 32Почему keyset-пагинация (seek) обычно лучше OFFSET-пагинации на больших таблицах?
OFFSET читает и выбрасывает все пропускаемые строки — глубокие страницы дорогие. Keyset продолжает с последнего показанного значения по индексу — стоимость почти постоянна.
Middle - 33В каких случаях созданный индекс, скорее всего, не будет использован?
Индекс не используется, когда: функция или приведение типа над колонкой, LIKE '%…' с ведущим шаблоном, низкая селективность (выбирается большая доля строк), запрос не по левому…
Middle - 34Что требуют первые три нормальные формы?
1НФ — атомарные значения, нет списков в ячейке. 2НФ — нет частичной зависимости неключевых полей от части составного ключа. 3НФ — нет транзитивных зависимостей (неключевое поле не…
Middle - 35Чем опасно объявление внешнего ключа с ON DELETE CASCADE?
Удаление одной строки может рекурсивно удалить тысячи связанных строк по цепочке внешних ключей — и это не видно из кода приложения.
Middle - 36Почему в качестве первичного ключа обычно предпочитают суррогатный ключ, а не естественный?
Бизнес-значения меняются (email, телефон, номер паспорта), а идентичность записи меняться не должна. Суррогатный ключ (bigint identity, UUIDv7) разделяет эти вещи.
Middle - 37Чем INNER JOIN отличается от LEFT JOIN?
INNER JOIN оставляет только совпавшие пары; LEFT JOIN сохраняет все строки левой таблицы и дополняет их NULL, если пары нет.
Junior - 38Запрос стал работать медленно. С чего правильно начать разбор?
Сначала измерить: EXPLAIN ANALYZE покажет, что реально происходит, вместо догадок. Затем — по находке: индекс, переписанный запрос, статистика, N+1.
Junior - 39Что обеспечивает внешний ключ (foreign key)?
Внешний ключ гарантирует, что ссылка ведёт на реально существующую строку: нельзя создать заказ для несуществующего клиента и нельзя удалить клиента с заказами (без явного…
Junior - 40Почему полнотекстовый поиск не делают запросом LIKE '%слово%'?
LIKE '%слово%' не использует обычный индекс (ведущий шаблон) — читает всю таблицу, и качество поиска примитивное: нет морфологии, опечаток, ранжирования.
Junior - 41Зачем тестировщику SQL и какие запросы нужно уметь писать?
SQL нужен, чтобы проверять данные в базе после действий, готовить тестовые данные и искать причину бага.
Junior
Разбор собеседования с sotto
После собеседования sotto разбирает разговор: оценка, каждый вопрос — хорошо, частично или слабо, чего не хватило в ответе и над чем поработать к следующему разу. Первые 30 минут бесплатно.