Вопросы по SQL

JOIN, индексы, транзакции и уровни изоляции, оптимизация запросов.

1. Как посчитать DAU, WAU, MAU и скользящий MAU на SQL? Junior

   DAU — число уникальных пользователей за день: COUNT(DISTINCT user_id) GROUP BY day. MAU календарный — то же по месяцам. Скользящий MAU на каждый день — уникальные за последние 30…

2. Как удалить дубликаты и оставить последнюю версию записи? Junior

   Найти: GROUP BY ключ HAVING COUNT() > 1. Оставить последнюю версию: пронумеровать строки внутри ключа ROW_NUMBER() OVER (PARTITION BY ключ ORDER BY updated_at DESC) и взять rn = 1…

3. Как посчитать воронку конверсии на SQL? Middle

   Для каждого пользователя определить, дошёл ли он до каждого шага в правильном порядке (время шага не раньше предыдущего) и в пределах окна (например, 7 дней от первого шага)…

4. Как найти самую длинную серию дней подряд, когда пользователь был активен? Middle

   Классический приём «gaps and islands»: пронумеровать даты активности пользователя ROW_NUMBER и вычесть номер из даты — у дней одной непрерывной серии разность одинакова. Затем…

5. Почему после JOIN суммы в отчёте оказались больше, чем должны быть? Junior

   Связь один-ко-многим размножает строки: заказ с тремя позициями после JOIN с позициями превращается в три строки, и SUM(order.amount) учитывает сумму заказа трижды. Решение …

6. Как посчитать медиану и перцентили в SQL и почему среднее часто вводит в заблуждение? Junior

   В PostgreSQL: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) — медиана с интерполяцией между значениями, PERCENTILE_DISC — ближайшее реальное значение. В ClickHouse — quantile, в…

7. Как посчитать когортный retention на SQL? Middle

   Определить когорту каждого пользователя (обычно неделя или месяц первого действия), для каждого события посчитать номер периода от начала когорты, затем посчитать уникальных…

8. Как разбить поток событий на сессии на SQL? Middle

   Сессия — серия событий пользователя без пауз дольше порога (обычно 30 минут). LAG даёт время предыдущего события; где пауза больше порога — флаг начала новой сессии; нарастающая…

9. Как выбрать топ-N записей в каждой группе, например три самых дорогих заказа каждого клиента? Junior

   Пронумеровать строки внутри группы оконной функцией и отфильтровать номер во внешнем запросе: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY amount DESC) и WHERE rn <= 3…

10. Что такое оконные функции в SQL и чем они отличаются от GROUP BY? Junior

    GROUP BY схлопывает группу в одну строку; оконная функция считает по группе строк (окну), сохраняя все строки. Окно задаёт OVER (PARTITION BY … ORDER BY … ROWS …). Основные…

11. Что означает аббревиатура ACID? Junior

    ACID — гарантии транзакции: атомарность (всё или ничего), согласованность (инварианты не нарушаются), изоляция (параллельные транзакции не мешают друг другу), долговечность…

12. Какой уровень изоляции транзакций по стандарту SQL гарантированно предотвращает phantom reads? Middle

    По стандарту SQL фантомы гарантированно исключает только SERIALIZABLE. На практике PostgreSQL не показывает фантомов уже на REPEATABLE READ благодаря снимку.

13. Что такое проблема N+1 запросов и как её решать? Middle

    N+1 — один запрос за списком и ещё по запросу на связанные данные каждого элемента: 100 заказов → 101 запрос. Причина — ленивая загрузка связей в цикле.

14. Каков основной эффект добавления индекса по столбцу? Junior

    Индекс — отдельная упорядоченная структура (обычно B-дерево): поиск по колонке за O(log n) вместо полного просмотра таблицы. Цена — медленнее запись и больше места.

15. Чем B-tree индекс принципиально превосходит hash-индекс? Middle

    B-дерево упорядочено — поддерживает равенство, диапазоны, сортировку и префиксы строк. Hash-индекс — только равенство. Поэтому почти всегда выбирают B-дерево.

16. Что такое нормализация и когда оправдана денормализация? Middle

    Нормализация — каждый факт хранится в одном месте: нет дублей, а значит нет аномалий обновления. Денормализация — сознательное дублирование ради быстрого чтения.

17. Чем отличается оптимистичная блокировка от пессимистичной? Middle

    Пессимистическая — блокируем строку заранее (SELECT ... FOR UPDATE), остальные ждут. Оптимистическая — не блокируем, а при записи проверяем версию (WHERE version = ?); не совпала…

18. Какой JOIN вернёт все строки левой таблицы и совпавшие из правой (NULL там, где совпадений нет)? Junior

    LEFT JOIN возвращает все строки левой таблицы и совпавшие из правой; где совпадений нет — в колонках правой NULL.

19. Чем HAVING отличается от WHERE? Junior

    WHERE фильтрует строки до группировки, HAVING — группы после агрегации. Условие на count() или sum() — только в HAVING.

20. Что вернёт INNER JOIN? Junior

    INNER JOIN возвращает только пары строк, удовлетворяющие условию ON. Строки без пары в результат не попадают.

21. Чем primary key отличается от unique-ограничения? Junior

    Первичный ключ — главный идентификатор строки: уникален, NOT NULL, один на таблицу, на него ссылаются внешние ключи. UNIQUE — дополнительная гарантия уникальности, таких может…

22. Как правильно проверить значение на NULL в SQL? Junior

    NULL — «неизвестно», и любое сравнение с ним даёт UNKNOWN, а не TRUE. Поэтому проверяют только IS NULL и IS NOT NULL.

23. К какой группе SQL-команд относятся SELECT, INSERT, UPDATE, DELETE? Junior

    SELECT, INSERT, UPDATE, DELETE — DML (работа с данными). DDL — схема (CREATE, ALTER, DROP), TCL — транзакции (COMMIT, ROLLBACK), DCL — права (GRANT, REVOKE).

24. Чем TRUNCATE отличается от DELETE и DROP? Junior

    DELETE удаляет строки (можно с WHERE), построчно, с триггерами; TRUNCATE очищает таблицу целиком и быстро; DROP удаляет саму таблицу со структурой.

25. Почему в составном индексе важен порядок колонок? Middle

    Составной индекс (a, b) отсортирован сначала по a, затем по b — как телефонный справочник по фамилии и имени. Поэтому он помогает в поиске по a и по a + b, но не по одному b.

26. Что делает EXPLAIN для SQL-запроса? Middle

    EXPLAIN показывает план, который выбрал оптимизатор: способы чтения таблиц, порядок и алгоритмы соединений, оценки числа строк и стоимости. EXPLAIN ANALYZE ещё и выполняет запрос…

27. Чем PreparedStatement безопаснее обычного Statement? Junior

    PreparedStatement передаёт текст запроса и значения раздельно: значение никогда не разбирается как SQL — инъекция невозможна. Statement склеивает строку и выполняет как есть.

28. Как в JDBC выполнить несколько операций в одной транзакции вручную? Middle

    conn.setAutoCommit(false) → операции → commit(); при исключении rollback(); в finally вернуть autocommit и закрыть соединение.

29. Чем dirty read, non-repeatable read и phantom read отличаются друг от друга? Middle

    Грязное чтение — видим чужие неподтверждённые изменения. Неповторяемое — повторное чтение той же строки даёт другое значение. Фантом — повторный запрос с условием возвращает…

30. Что обеспечивает foreign key? Junior

    Внешний ключ гарантирует ссылочную целостность: нельзя сослаться на несуществующую строку и нельзя удалить строку, на которую ссылаются (если не задано иное).

31. Напиши SQL-запрос, возвращающий вторую по величине зарплату из таблицы employee(id, salary). Junior

    SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 1 — или подзапрос с MAX, или оконная функция DENSE_RANK.

32. Почему keyset-пагинация (seek) обычно лучше OFFSET-пагинации на больших таблицах? Middle

    OFFSET читает и выбрасывает все пропускаемые строки — глубокие страницы дорогие. Keyset продолжает с последнего показанного значения по индексу — стоимость почти постоянна.

33. В каких случаях созданный индекс, скорее всего, не будет использован? Middle

    Индекс не используется, когда: функция или приведение типа над колонкой, LIKE '%…' с ведущим шаблоном, низкая селективность (выбирается большая доля строк), запрос не по левому…

34. Что требуют первые три нормальные формы? Middle

    1НФ — атомарные значения, нет списков в ячейке. 2НФ — нет частичной зависимости неключевых полей от части составного ключа. 3НФ — нет транзитивных зависимостей (неключевое поле не…

35. Чем опасно объявление внешнего ключа с ON DELETE CASCADE? Middle

    Удаление одной строки может рекурсивно удалить тысячи связанных строк по цепочке внешних ключей — и это не видно из кода приложения.

36. Почему в качестве первичного ключа обычно предпочитают суррогатный ключ, а не естественный? Middle

    Бизнес-значения меняются (email, телефон, номер паспорта), а идентичность записи меняться не должна. Суррогатный ключ (bigint identity, UUIDv7) разделяет эти вещи.

37. Чем INNER JOIN отличается от LEFT JOIN? Junior

    INNER JOIN оставляет только совпавшие пары; LEFT JOIN сохраняет все строки левой таблицы и дополняет их NULL, если пары нет.

38. Запрос стал работать медленно. С чего правильно начать разбор? Junior

    Сначала измерить: EXPLAIN ANALYZE покажет, что реально происходит, вместо догадок. Затем — по находке: индекс, переписанный запрос, статистика, N+1.

39. Что обеспечивает внешний ключ (foreign key)? Junior

    Внешний ключ гарантирует, что ссылка ведёт на реально существующую строку: нельзя создать заказ для несуществующего клиента и нельзя удалить клиента с заказами (без явного…

40. Почему полнотекстовый поиск не делают запросом LIKE '%слово%'? Junior

    LIKE '%слово%' не использует обычный индекс (ведущий шаблон) — читает всю таблицу, и качество поиска примитивное: нет морфологии, опечаток, ранжирования.

41. Зачем тестировщику SQL и какие запросы нужно уметь писать? Junior

    SQL нужен, чтобы проверять данные в базе после действий, готовить тестовые данные и искать причину бага.