Топ-65 вопросов по SQL с собеседований, к которым вы должны подготовиться в 2019 году. Часть I
Реляционные базы данных являются одними из наиболее часто используемых баз данных по сей день, и поэтому навыки работы с SQL для большинства должностей являются обязательными. В этой статье с вопросами по SQL с собеседований я познакомлю вас с наиболее часто задаваемыми вопросами по SQL (Structured Query Language — язык структурированных запросов). Эта статья является идеальным руководством для изучения всех концепций, связанных с SQL, Oracle, MS SQL Server и базой данных MySQL.
Наша статья с вопросами по SQL — универсальный ресурс, с помощью которого вы можете ускорить подготовку к собеседованию. Она состоит из набора из 65 самых распространенных вопросов, которые интервьюер может задать во время собеседования. Оно обычно начинается с базовых вопросов по SQL, а затем переходит к более сложным на основе обсуждения и ваших ответов. Эти вопросы по SQL с собеседований помогут вам извлечь максимальную выгоду на различных уровнях понимания.
Давайте начнем!
Вопросы по SQL с собеседований
Вопрос 1. В чем разница между операторами DELETE и TRUNCATE?
| DELETE | TRUNCATE |
|---|---|
| Используется для удаления строки в таблице | Используется для удаления всех строк из таблицы |
| Вы можете восстановить данные после удаления | Вы не можете восстановить данные (прим. перевод.: операции логируются по разному, но в SQL Server есть возможность сделать откат) транзакции) |
| DML-команда | DDL-команда |
| Медленнее, чем оператор TRUNCATE | Быстрее |
№ Вопрос 2. Из каких подмножеств состоит SQL?
- DDL (Data Definition Language, язык описания данных) — позволяет выполнять различные операции с базой данных, такие как CREATE (создание), ALTER (изменение) и DROP (удаление объектов).
- DML (Data Manipulation Language, язык управления данными) — позволяет получать доступ к данным и манипулировать ими, например, вставлять, обновлять, удалять и извлекать данные из базы данных.
- DCL (Data Control Language, язык контролирования данных) — позволяет контролировать доступ к базе данных. Пример — GRANT (предоставить права), REVOKE (отозвать права).
Вопрос 3. Что подразумевается под СУБД? Какие существуют типы СУБД?
База данных — структурированная коллекция данных. Система управления базами данных (СУБД) — программное обеспечение, которое взаимодействует с пользователем, приложениями и самой базой данных для сбора и анализа данных. СУБД позволяет пользователю взаимодействовать с базой данных. Данные, хранящиеся в базе данных, могут быть изменены, извлечены и удалены. Они могут быть любых типов, таких как строки, числа, изображения и т. д.
Существует два типа СУБД:
- Реляционная система управления базами данных: данные хранятся в отношениях (таблицах). Пример — MySQL.
- Нереляционная система управления базами данных: не существует понятия отношений, кортежей и атрибутов. Пример — Mongo.
Вопрос 4. Что подразумевается под таблицей и полем в SQL?
Таблица — организованный набор данных в виде строк и столбцов. Поле — это столбцы в таблице. Например:
Таблица: Student_Information
Поле: Stu_Id, Stu_Name, Stu_Marks
Вопрос 5. Что такое соединения в SQL?
Для соединения строк из двух или более таблиц на основе связанного между ними столбца используется оператор JOIN. Он используется для объединения двух таблиц или получения данных оттуда. В SQL есть 4 типа соединения, а именно:
- Inner Join (Внутреннее соединение)
- Right Join (Правое соединение)
- Left Join (Левое соединение)
- Full Join (Полное соединение)
Вопрос 6. В чем разница между типом данных CHAR и VARCHAR в SQL?
И Char, и Varchar служат символьными типами данных, но varchar используется для строк символов переменной длины, тогда как Char используется для строк фиксированной длины. Например, char(10) может хранить только 10 символов и не сможет хранить строку любой другой длины, тогда как varchar(10) может хранить строку любой длины до 10, т.е. например 6, 8 или 2.
Вопрос 7. Что такое первичный ключ (Primary key)?
- Первичный ключ — столбец или набор столбцов, которые однозначно идентифицируют каждую строку в таблице.
- Однозначно идентифицирует одну строку в таблице
- Нулевые (Null) значения не допускаются
_Пример: в таблице Student StuID является первичным ключом.
Вопрос 8. Что такое ограничения (Constraints)?
Ограничения (constraints) используются для указания ограничения на тип данных таблицы. Они могут быть указаны при создании или изменении таблицы. Пример ограничений:
- NOT NULL
- CHECK
- DEFAULT
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
Вопрос 9. В чем разница между SQL и MySQL?
SQL — стандартный язык структурированных запросов (Structured Query Language) на основе английского языка, тогда как MySQL — система управления базами данных. SQL — язык реляционной базы данных, который используется для доступа и управления данными, MySQL — реляционная СУБД (система управления базами данных), также как и SQL Server, Informix и т. д.
Вопрос 10. Что такое уникальный ключ (Unique key)?
- Однозначно идентифицирует одну строку в таблице.
- Допустимо множество уникальных ключей в одной таблице.
- Допустимы NULL-значения (прим. перевод.: зависит от СУБД, в SQL Server значение NULL может быть добавлено только один раз в поле с UNIQUE KEY).
Вопрос 11. Что такое внешний ключ (Foreign key)?
- Внешний ключ поддерживает ссылочную целостность, обеспечивая связь между данными в двух таблицах.
- Внешний ключ в дочерней таблице ссылается на первичный ключ в родительской таблице.
- Ограничение внешнего ключа предотвращает действия, которые разрушают связи между дочерней и родительской таблицами.
Вопрос 12. Что подразумевается под целостностью данных?
Целостность данных определяет точность, а также согласованность данных, хранящихся в базе данных. Она также определяет ограничения целостности для обеспечения соблюдения бизнес-правил для данных, когда они вводятся в приложение или базу данных.
Вопрос 13. В чем разница между кластеризованным и некластеризованным индексами в SQL?
- Различия между кластеризованным и некластеризованным индексами в SQL:
Кластерный индекс используется для простого и быстрого извлечения данных из базы данных, тогда как чтение из некластеризованного индекса происходит относительно медленнее. - Кластеризованный индекс изменяет способ хранения записей в базе данных — он сортирует строки по столбцу, который установлен как кластеризованный индекс, тогда как в некластеризованном индексе он не меняет способ хранения, но создает отдельный объект внутри таблицы, который указывает на исходные строки таблицы при поиске.
- Одна таблица может иметь только один кластеризованный индекс, тогда как некластеризованных у нее может быть много.
Вопрос 14. Напишите SQL-запрос для отображения текущей даты.
В SQL есть встроенная функция GetDate (), которая помогает возвращать текущий timestamp/дату.
Вопрос 15. Перечислите типы соединений
Существуют различные типы соединений, которые используются для извлечения данных между таблицами. Принципиально они делятся на четыре типа, а именно:
Inner join (Внутреннее соединение): в MySQL является наиболее распространенным типом. Оно используется для возврата всех строк из нескольких таблиц, для которых выполняется условие соединения.
Left Join (Левое соединение): в MySQL используется для возврата всех строк из левой (первой) таблицы и только совпадающих строк из правой (второй) таблицы, для которых выполняется условие соединения.
Right Join (Правое соединение): в MySQL используется для возврата всех строк из правой (второй) таблицы и только совпадающих строк из левой (первой) таблицы, для которых выполняется условие соединения.
Full Join (Полное соединение): возвращает все записи, для которых есть совпадение в любой из таблиц. Следовательно, он возвращает все строки из левой таблицы и все строки из правой таблицы.
Вопрос 16. Что вы подразумеваете под денормализацией?
Денормализация — техника, которая используется для преобразования из высших к низшим нормальным формам. Она помогает разработчикам баз данных повысить производительность всей инфраструктуры, поскольку вносит избыточность в таблицу. Она добавляет избыточные данные в таблицу, учитывая частые запросы к базе данных, которые объединяют данные из разных таблиц в одну таблицу.
Вопрос 17. Что такое сущности и отношения?
Сущности: человек, место или объект в реальном мире, данные о которых могут храниться в базе данных. В таблицах хранятся данные, которые представляют один тип сущности. Например — база данных банка имеет таблицу клиентов для хранения информации о клиентах. Таблица клиентов хранит эту информацию в виде набора атрибутов (столбцы в таблице) для каждого клиента.
Отношения: отношения или связи между сущностями, которые имеют какое-то отношение друг к другу. Например — имя клиента связано с номером учетной записи клиента и контактной информацией, которая может быть в той же таблице. Также могут быть отношения между отдельными таблицами (например, клиент к счетам).
Вопрос 18. Что такое индекс?
Индексы относятся к методу настройки производительности, позволяющему быстрее извлекать записи из таблицы. Индекс создает отдельную структуру для индексируемого поля и, следовательно, позволяет быстрее получать данные.
Вопрос 19. Опишите различные типы индексов.
Есть три типа индексов, а именно:
- Уникальный индекс (Unique Index): этот индекс не позволяет полю иметь повторяющиеся значения, если столбец индексируется уникально. Если первичный ключ определен, уникальный индекс может быть применен автоматически.
- Кластеризованный индекс (Clustered Index): этот индекс меняет физический порядок таблицы и выполняет поиск на основе значений ключа. Каждая таблица может иметь только один кластеризованный индекс.
- Некластеризованный индекс (Non-Clustered Index): не изменяет физический порядок таблицы и поддерживает логический порядок данных. Каждая таблица может иметь много некластеризованных индексов.
Вопрос 20. Что такое нормализация и каковы ее преимущества?
Нормализация — процесс организации данных, цель которого избежать дублирования и избыточности. Некоторые из преимуществ:
- Лучшая организация базы данных
- Больше таблиц с небольшими строками
- Эффективный доступ к данным
- Большая гибкость для запросов
- Быстрый поиск информации
- Проще реализовать безопасность данных
- Позволяет легко модифицировать
- Сокращение избыточных и дублирующихся данных
- Более компактная база данных
- Обеспечивает согласованность данных после внесения изменений
Вопрос 21. В чем разница между командами DROP и TRUNCATE?
Команда DROP удаляет саму таблицу, и нельзя сделать Rollback команды, тогда как команда TRUNCATE удаляет все строки из таблицы (прим. перевод.: в SQL Server Rollback нормально отработает и откатит DROP).
Вопрос 22. Объясните различные типы нормализации.
Существует много последовательных уровней нормализации. Это так называемые нормальные формы. Каждая последующая нормальная форма включает предыдущую. Первых трех нормальных форм обычно достаточно.
- Первая нормальная форма (1NF) — нет повторяющихся групп в строках
- Вторая нормальная форма (2NF) — каждое неключевое (поддерживающее) значение столбца зависит от всего первичного ключа
- Третья нормальная форма (3NF) — каждое неключевое значение зависит только от первичного ключа и не имеет зависимости от другого неключевого значения столбца
Вопрос 23. Что такое свойство ACID в базе данных?
ACID означает атомарность (Atomicity), согласованность (Consistency), изолированность (Isolation), долговечность (Durability). Он используется для обеспечения надежной обработки транзакций данных в системе базы данных.
Атомарность. Гарантирует, что транзакция будет полностью выполнена или потерпит неудачу, где транзакция представляет одну логическую операцию данных. Это означает, что при сбое одной части любой транзакции происходит сбой всей транзакции и состояние базы данных остается неизменным.
Согласованность. Гарантирует, что данные должны соответствовать всем правилам валидации. Проще говоря, вы можете сказать, что ваша транзакция никогда не оставит вашу базу данных в недопустимом состоянии.
Изолированность. Основной целью изолированности является контроль механизма параллельного изменения данных.
Долговечность. Долговечность подразумевает, что если транзакция была подтверждена (COMMIT), произошедшие в рамках транзакции изменения сохранятся независимо от того, что может встать у них на пути (например, потеря питания, сбой или ошибки любого рода).
Вопрос 24. Что вы подразумеваете под «триггером» в SQL?
Триггер в SQL — особый тип хранимых процедур, которые предназначены для автоматического выполнения в момент или после изменения данных. Это позволяет вам выполнить пакет кода, когда вставка, обновление или любой другой запрос выполняется к определенной таблице.
Вопрос 25. Какие операторы доступны в SQL?
В SQL доступно три типа оператора, а именно:
- Арифметические Операторы
- Логические Операторы
- Операторы сравнения
Вопрос 26. Совпадают ли значения NULL со значениями нуля или пробела?
Значение NULL вовсе не равно нулю или пробелу. Значение NULL представляет значение, которое недоступно, неизвестно, присвоено или неприменимо, тогда как ноль — это число, а пробел — символ.
Вопрос 27. В чем разница между перекрестным (cross join) и естественным (natural join) соединением?
Перекрестное соединение создает перекрестное или декартово произведение двух таблиц, тогда как естественное соединение основано на всех столбцах, имеющих одинаковое имя и типы данных в обеих таблицах.
Вопрос 28. Что такое подзапрос в SQL?
Подзапрос — это запрос внутри другого запроса, в котором определен запрос для извлечения данных или информации из базы данных. В подзапросе внешний запрос называется основным запросом, тогда как внутренний запрос называется подзапросом. Подзапросы всегда выполняются первыми, а результат подзапроса передается в основной запрос. Он может быть вложен в SELECT, UPDATE или любой другой запрос. Подзапрос также может использовать любые операторы сравнения, такие как >, < или =.
Вопрос 29. Какие бывают типы подзапросов?
Существует два типа подзапросов, а именно: коррелированные и некоррелированные.
- Коррелированный подзапрос: это запрос, который выбирает данные из таблицы со ссылкой на внешний запрос. Он не считается независимым запросом, поскольку ссылается на другую таблицу или столбец в таблице.
- Некоррелированный подзапрос: этот запрос является независимым запросом, в котором выходные данные подзапроса подставляются в основной запрос.
Вопрос 30. Перечислите способы получить количество записей в таблице?
Для подсчета количества записей в таблице вы можете использовать следующие команды:
SELECT * FROM table1
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE AND indid < 2
Ещё 35 вопросов с ответами опубликуем в следующей части… Следите за новостями!
Собеседование разработчика: разбор вопросов по базам данных
Всем привет! Все мы здесь бьемся над одной целью — стать Java Developer -ами. Пожалуй, самый важный из этапов на пути становления — техническое собеседование. Как правило собеседующий проходится по основным темам, задавая по паре вопросов. В этой статье мы поговорим об одной из таких ключевых тем — базы данных . Рассмотрим наиболее частые вопросы и попытаемся ответить на них без глубокого погружения в материал, ведь в таком случае нам и объема книги не хватит! Итак, поехали.
1. Что такое базы данных? На какие типы они делятся?
Что подразумевается под СУБД?
База данных (БД) — это организованная структура, предназначенная для хранения, изменения и обработки взаимосвязанной информации преимущественно больших объемов. Иными словами, БД — это структурированное хранилище данных. Например, телефонная книга.
Типы БД
- Реляционная база данных — это набор данных с предопределенными связями между ними. Данные хранятся в виде набора таблиц, состоящих из столбцов и строк. В таблицах хранится информация об объектах, представленных в базе данных. В каждом столбце таблицы хранится определенный тип данных, в каждой ячейке — значение атрибута.
- Нереляционные системы (NoSQL) — системы, созданные для определенных моделей данных, обладающие гибкими схемами. Иначе говоря, это БД, которые хранят данные не в виде табличных схем, строк и столбцов, а в иных форматах.
2. Что такое нормализация? Нормализованная форма? Сколько форм нормализации существует? Назовите первые три.
- Первая нормальная форма:
- Все атрибуты простые (то есть атомарные и неделимые);
- Все данные скалярные (то есть положительные);
- Нет повторяющихся строк (для этого для каждой строки создается первичный ключ).
- Вторая нормальная форма:
- Соблюдены условия первой нормальной формы;
- Каждый неключевой атрибут ссылается на первичный ключ.
- Третья нормальная форма:
- Соблюдены условия второй нормальной группы;
- Неключевые поля не зависят от других неключевых полей: они могут быть связаны лишь с первичным ключом.
3. Денормализация
4. Индексы
В чем разница между кластеризованным и некластеризованным индексами в SQL?
Кластеризованный:
- Обеспечивает физический порядок по выбранному полю;
- Если у таблицы есть кластеризованный индекс, она называется кластеризованной;
- Нужно не более одного индекса на таблицу;
- В MySQL кластеризованный индекс не задается явно пользователем, так как если вы не определяете PRIMARY KEY для своей таблицы, MySQL находит первый индекс UNIQUE , где все ключевые столбцы — NOT NULL , и InnoDB использует его в качестве кластеризованного индекса.
Некластеризованный:
- В одной таблице возможно до 999 некластеризованных индексов;
- Содержит указатель на строки с реальными данными в таблице;
- Не обеспечивает физический порядок;
- Для некластеризованных индексов присутствуют отдельные таблицы с отсортированными данными, а именно — одна таблица для одного столбца, на котором индекс, поэтому при запросе данных, не входящих в состав данного поля, будет сначала выполняться запрос к полю в данной таблице, а только затем — дополнительный запрос к строке в изначальной таблице.
6. Что такое составной индекс?
7. Что такое покрывающий индекс? Уникальный индекс?
8. Что такое первичный ключ
Первичный ключ (Primary key) — поле в таблице для идентификации каждой строки в таблице базы данных. В таблице может быть только одно такое поле, и все значения должны быть уникальными. Ничего не напомнило?Ведь первичный ключ — ничто иное, как уникальный, кластеризованный индекс. Как правило, первичные ключи создаются, при заведении таблицы: К данной колонке автоматически добавится ограничение — NOT NULL . Также можно задать ключ и для уже созданной таблицы: Если первичный ключ добавляется способом, описанным выше, то значения полей, задаваемых как первичный ключ ( column_name ), проверяются на отсутствие нулевых (также будет добавлено ограничение — NOT NULL ).
Что такое внешний ключ?
- CASCADE — с этим свойством будут автоматически удалены или изменены строки из зависимой таблицы при удалении или изменении связанных строк в главной таблице;
- SET NULL — при данном свойстве, когда происходит удаление или обновление связанной строки из главной таблицы, будет устанавливаться значение NULL для столбца внешнего ключа;
- NO ACTION — отклоняет попытки удалить или изменить строки в главной таблице, если присутствуют связанные строки в зависимой таблице;
- RESTRICT — эквивалентно NO ACTION ; SET DEFAULT — при данном свойстве, когда происходит удаление или обновление связанной строки из главной таблицы, будет устанавливаться значение по умолчанию (если оно есть) для столбца внешнего ключа.
10. Типы соединений между таблицами (Join)
- INNER JOIN — соединение, которое показывает только те данные из первой таблицы, которым соответствуют некоторые данные из второй таблицы. Остальные — опускаются.
- LEFT JOIN — соединение, которое показывает все данные из первой таблицы и соответствующие данные из второй, если они есть. Если же соответствующих данных нет, поля для данных из второй таблицы будут пустыми.
- RIGHT JOIN — соединение, которое показывает все данные из второй таблицы и соответствующие данные из первой, если они есть. Если же соответствующих данных нет, поля для данных из первой таблицы будут пустыми.
- FULL JOIN — соединение, которое показывает все данные из первой и второй таблицы. Если связанных данных в другой таблице нет, поля для этих данных будут пустыми.
- CROSS JOIN — перекрестное соединение, при котором каждая строка первой таблицы соединяется с каждой строкой второй таблицы (каждая с каждой). То есть, если в двух таблицах по 3 строки, после данного соединения мы получим результат из 9 строк.
11. Что такое свойство ACID в базе данных?
- Перевести деньги на счет банка.
- Перевести деньги со счета банка на конкретный счет.
12. Уровни изолированности транзакций
- фантомное чтение — в рамках одной транзакции один и тот же запрос данных дает разные результаты, что происходит из-за добавления данных другой (параллельной) транзакцией.
- неповторяющееся чтение — в рамках одной транзакции один и тот же запрос данных дает разные результаты, что происходит из-за изменения или удаления данных другой (параллельной) транзакцией.
- «грязное» чтение — чтение данных, добавленных или измененных транзакцией, которая впоследствии не откатится;
- потерянное обновление — при одновременном изменении одного блока данных разными транзакциями теряются все изменения, кроме последнего (похоже на “состояние гонки” в многопоточности).
13. Что такое SQL-инъекция?
SQL-инъекция — один из способов взлома сайта, суть которого — внедрение в данные через GET , POST запросы или Cookie некоторого SQL кода. Если веб-сайт выполняет такие инъекции, можно получить доступ к БД и взломать приложение. Например, мы знаем имя некоторой переменной. Допустим, column_name с типом boolean . Если система восприимчива к инъекциям, можно добавить OR column_name=true и после этого писать всё, что нам нужно от БД. OR создаст условие ИЛИ, и наше выражение после него будет всегда true , что и пропустит нас дальше. Атака на сайт вроде инъекции SQL возможна из-за неправильной обработки входящих данных, используемых в SQL-запросах. При соединении с базой данных при помощи JDBC вы используете различные Statements . Для повышения безопасности необходимо использовать PreparedStatement вместо обычного Statement , так как при использовании Statement строки запроса и значений просто складываются, делая возможными инъекции. В свою очередь, в PreparedStatement есть конкретный шаблон запроса, и данные в него вставляются с отражением кавычек. В итоге SQL-инъекции будут восприниматься только как строковое представление какого-то поля. Для защиты от SQL-инъекций можно использовать проверку на основе регулярных выражений (подробнее о регулярных выражениях можно почитать в этой статье).
Ещё вариант — установить ограничение на количество символов входящих параметров: например, если вам должно приходить число не более 9999, подойдет ограничение в четыре входящих символа. Оно снизит риск взлома с помощью SQL-инъекций.
Подробнее о безопасности в Java можно узнать из статьи “Безопасность в Java: best practices”.
14. Что такое хранимые процедуры? Хранимые функции? Триггер?
15. Практика
Как бы то ни было, самым частым SQL-вопросом на собеседовании будет практика — решение задач. Пытаться угадать, какие именно вам попадутся задачи, бессмысленно, ведь все зависит от изощренности фантазии человека напротив. Поэтому единственным рабочим вариантом будет набивание руки на SQL-запросах разной сложности. В качестве ресурса для практики на различных задачах может послужить sql-ex.ru. Уже после первых двадцати выполненных тасков вашему собеседнику будет довольно сложно напугать вас какой либо SQL-задачей.На этом сегодня все: надеюсь, после прочтения данной статьи вопросы по базам данных не вызовут никаких сложностей и проблем. Спасибо за внимание и до новых встреч!
Лучшие вопросы средней сложности по SQL на собеседовании аналитика данных
С 2015 по 2019 годы я прошёл четыре цикла собеседований на должность аналитика данных и специалиста по анализу данных в более чем десятке компаний. После очередного неудачного интервью в 2017 году — когда я запутался в сложных вопросах по SQL — я начал составлять задачник с вопросами по SQL средней и высокой сложности, чтобы лучше готовиться к собеседованиям. Этот справочник очень пригодился в последнем цикле собеседований 2019 года. За последний год я поделился этим руководством с парой друзей, а благодаря дополнительному свободному времени из-за пандемии отшлифовал его — и составил этот документ.
Есть множество отличных руководств по SQL для начинающих. Мои любимые — это интерактивные курсы Codecademy по SQL и Select Star SQL от Цзы Чон Као. Но в реальности первые 70% из курса SQL довольно просты, а настоящие сложности начинаются в остальных 30%, которые не освещаются в руководствах для начинающих. Так вот, на собеседованиях для аналитиков данных и специалистов по анализу данных в технологических компаниях часто задают вопросы именно по этим 30%.
Удивительно, но я не нашёл исчерпывающего источника по таким вопросам среднего уровня сложности, поэтому составил данное руководство.
Оно полезно для собеседований, но заодно повысит вашу эффективность на текущем и будущих местах работы. Лично я считаю, что некоторые упомянутые шаблоны SQL полезны и для ETL-систем, на которых работают инструменты отчётности и функции анализа данных для выявления тенденций.
Содержание
- Сделанные допущения и как использовать руководство
- Советы по решению сложных задач на собеседованиях по SQL
- Задачи на самообъединения
- № 1. Процентное изменение месяц к месяцу
- № 2. Маркировка древовидной структуры
- № 3. Удержание пользователей в месяц (несколько частей)
- Часть 1
- Часть 2
- Часть 3
- № 1. Найти идентификатор с максимальным значением
- № 2. Среднее значение и ранжирование с оконной функцией (несколько частей)
- Часть 1
- Часть 2
- № 1. Гистограммы
- № 2. Перекрёстное соединение (несколько частей)
- Часть 1
- Часть 2
Нужно понимать, что на собеседованиях дата-аналитиков и специалистов по анализу данных задают вопросы не только по SQL. Другие общие темы включают обсуждение прошлых проектов, A/B-тестирование, разработку метрик и открытые аналитические проблемы. Примерно три года назад на Quora публиковались советы по собеседованию на должность аналитика продукта (product analyst) в Facebook. Там эта тема обсуждается более подробно. Тем не менее, если улучшение знаний по SQL поможет вам на собеседовании, то это руководство вполне стоит потраченного времени.
В будущем я могу перенести код из этого руководства на сайт вроде Select Star SQL, чтобы было проще писать инструкции SQL — и видеть результат выполнения кода в реальном времени. Как вариант — добавить вопросы как проблемы на платформу для подготовки к собеседованиям LeetCode. Пока же я просто хотел опубликовать этот документ, чтобы люди могли прямо сейчас ознакомиться с этой информацией.
Сделанные допущения и как использовать руководство
Предположения о знании языка SQL: Предполагается, что у вас есть рабочие знания SQL. Вероятно, вы часто используете его на работе, но хотите отточить навыки в таких темах, как самообъединения и оконные функции.
Как использовать данное руководство: Поскольку на собеседовании часто используется доска или виртуальный блокнот (без компиляции кода), то рекомендую взять карандаш и бумагу — и записать решения для каждой проблемы, а после завершения сравнить свои записи с ответами. Или отработайте свои ответы вместе с другом, который выступит в качестве интервьюера!
- Небольшие синтаксические ошибки не имеют большого значения во время собеседования с доской или блокнотом. Но они могут отвлекать интервьюера, поэтому в идеале старайтесь уменьшить их количество, чтобы сконцентрировать всё внимание на логике.
- Приведённые ответы не обязательно единственный способ решить каждую задачу. Не стесняйтесь писать комментарии с дополнительными решениями, которые можно добавить в это руководство!
Советы по решению сложных задач на собеседованиях по SQL
Сначала стандартные советы для всех собеседований по программированию…
- Внимательно выслушайте описание проблемы, повторите всю суть проблемы интервьюеру
- Сформулируйте пограничный случай, чтобы продемонстрировать, что вы действительно понимаете проблему (т. е. строку, которая не будет включена в итоговый запрос SQL, который вы собираетесь написать)
- (Если проблема связана с самообъединением) для своей же пользы нарисуйте, как будет выглядеть самообъединение — обычно это минимум три столбца: нужный столбец из основной таблицы, столбец для объединения из основной таблицы и столбец для объединения из вторичной таблицы
- Или, когда вы лучше освоите задачи самообъединения, можете объяснить этот шаг устно
- Начните писать SQL, пусть с ошибками, вместо попыток полностью понять проблему. Формулируйте свои предположения по ходу дела, чтобы ваш интервьюер мог вас поправить.
Благодарности и дополнительные ресурсы
Некоторые из перечисленных здесь проблем адаптированы из старых записей в блоге Periscope (в основном написанных Шоном Куком около 2014 года, хотя его авторство, видимо, убрали из материалов после слияния SiSense с Periscope), а также из обсуждений на StackOverflow. В случае необходимости, источники отмечены в начале каждого вопроса.
На Select Star SQL тоже хорошая подборка задачек, дополняющих проблемы из этого документа.
Пожалуйста, обратите внимание, что эти вопросы не являются буквальными копиями вопросов с моих собственных собеседований, и они не использовались в компаниях, в которых я работал или работаю.
Задачи на самообъединение
№ 1. Процентное изменение месяц к месяцу
Контекст: часто полезно знать, как изменяется ключевая метрика, например, месячная аудитория активных пользователей, от месяца к месяцу. Допустим у нас есть таблица logins в таком виде:
Задача: найти ежемесячное процентное изменение месячной аудитории активных пользователей (MAU).
Решение:
(Это решение, как и другие блоки кода в этом документе, содержит комментарии об элементах синтаксисе SQL, которые могут отличаться между разными вариантами SQL, и прочие заметки)№ 2. Маркировка древовидной структуры
Контекст: предположим, у вас есть таблица tree с двумя столбцами: в первом указаны узлы, а во втором — родительские узлы.
Задача: написать SQL таким образом, чтобы мы обозначили каждый узел как внутренний (inner), корневой (root) или конечный узел/лист (leaf), так что для вышеперечисленных значений получится следующее:
(Примечание: более подробно о терминологии древовидной структуры данных можно почитать здесь. Однако для решения этой проблемы она не нужна!)
Решение:
Благодарность: это более обобщённое решение предложил Фабиан Хофман 2 мая 2020 года. Спасибо, Фабиан!Альтернативное решение, без явных соединений:
Благодарность: Уильям Чарджин 2 мая 2020 года обратил внимание на необходимость условия WHERE parent IS NOT NULL , чтобы это решение возвращало Leaf вместо NULL . Спасибо, Уильям!
№ 3. Удержание пользователей в месяц (несколько частей)
Благодарность: эта задача адаптирована из статьи в блоге SiSense «Использование самообъединений для расчёта показателей удержания, оттока и реактивации».
Часть 1
Контекст: допустим, у нас есть статистика по авторизации пользователей на сайте в таблице logins :
Задача: написать запрос, который получает количество удержанных пользователей в месяц. В нашем случае данный параметр определяется как количество пользователей, которые авторизовались в системе и в этом, и в предыдущем месяце.
Благодарность:
Том Моэртел указал на то, что предварительная дедубликация user_id перед самообъединением делает решение более эффективным, и предложил код ниже. Спасибо, Том!Часть 2
Задача: теперь возьмём предыдущую задачу по вычислению количества удержанных пользователей в месяц — и перевернём её с ног на голову. Напишем запрос для подсчёта пользователей, которые не вернулись на сайт в этом месяце. То есть «потерянных» пользователей.
Обратите внимание, что эту проблему можно решить также с помощью соединений LEFT или RIGHT .
Часть 3
Примечание: вероятно, это более сложная задача, чем вам предложат на реальном собеседовании. Воспринимайте её скорее как головоломку — или можете пропустить и перейти к следующей задаче.
Контекст: итак, мы хорошо справились с двумя предыдущими проблемами. По условиям новой задачи теперь у нас появилась таблица потерянных пользователей user_churns . Если пользователь была активен в прошлом месяце, но затем не активен в этом, то он вносится в таблицу за этот месяц. Вот как выглядит user_churns :
Задача: теперь вы хотите провести когортный анализ, то есть анализ совокупности активных пользователей, которые были реактивированы в прошлом. Создайте таблицу с такими пользователями. Для создания когорты можете использовать таблицы user_churns и logins . В Postgres текущая временная метка доступна через current_timestamp .
№ 4. Нарастающий итог
Благодарность: эта задача адаптирована из статьи в блоге SiSense «Моделирование денежных потоков в SQL».
Контекст: допустим, у нас есть таблица transactions в таком виде:
Где cash_flow — это выручка минус затраты за каждый день.
Задача: написать запрос, чтобы получить нарастающий итог для денежного потока каждый день таким образом, чтобы в конечном итоге получилась таблица в такой форме:
Альтернативное решение с использованием оконной функции (более эффективное!):
№ 5. Скользящее среднее
Благодарность: эта задача адаптирована из статьи в блоге SiSense «Скользящие средние в MySQL и SQL Server».
Примечание: скользящее среднее можно вычислить разными способами. Здесь мы используем предыдущее среднее значение. Таким образом, метрика для седьмого дня месяца будет средним значением предыдущих шести дней и его самого.
Контекст: допустим, у нас есть таблица signups в таком виде:
Задача: написать запрос, чтобы получить 7-дневное скользящее среднее ежедневных регистраций.
№ 6. Несколько условий соединения
Благодарность: эта задача адаптирована из статьи в блоге SiSense «Анализ вашей электронной почты с помощью SQL».
Контекст: скажем, наша таблица emails содержит электронные письма, отправленные с адреса zach@g.com и полученные на него:
Задача:написать запрос, чтобы получить время отклика на каждое письмо ( id ), отправленное на zach@g.com . Не включать письма на другие адреса. Предположим, что у каждого треда уникальная тема. Имейте в виду, что в треде может быть несколько писем туда и обратно между zach@g.com и другими адресатами.
Задачи на оконные функции
№ 1. Найти идентификатор с максимальным значением
Контекст: Допустим, у нас есть таблица salaries с данными об отделах и зарплате сотрудников в следующем формате:
Задача: написать запрос, чтобы получить empno с самой высокой зарплатой. Убедитесь, что ваше решение обрабатывает случаи одинаковых зарплатами!
Альтернативное решение с использованием RANK() :
№ 2. Среднее значение и ранжирование с оконной функцией (несколько частей)
Часть 1
Контекст: допустим, у нас есть таблица salaries в таком формате:
Задача: написать запрос, который возвращает ту же таблицу, но с новым столбцом, в котором указана средняя зарплата по департаменту. Мы бы ожидали таблицу в таком виде:
Часть 2
Задача: напишите запрос, который добавляет столбец с позицией каждого сотрудника в табели на основе его зарплаты в своём отделе, где сотрудник с самой высокой зарплатой получает позицию 1. Мы бы ожидали таблицу в таком виде:
Другие задачи средней и высокой сложности
№ 1. Гистограммы
Контекст: Допустим, у нас есть таблица sessions , где каждая строка представляет собой сеанс потоковой передачи видео с длиной в секундах:
Задача: написать запрос, чтобы подсчитать количество сеансов, которые попадают промежутки по пять секунд, т. е. для приведённого выше фрагмента результат будет примерно такой:
Максимальная оценка засчитывается за надлежащие метки строк ("5-10" и т. д.)
№ 2. Перекрёстное соединение (несколько частей)
Часть 1
Контекст: допустим, у нас есть таблица state_streams , где в каждой строке указано название штата и общее количество часов потоковой передачи с видеохостинга:
(На самом деле в агрегированных таблицах такого типа обычно есть ещё столбец даты, но для этой задачи мы его исключим)
Задача: написать запрос, чтобы получить пары штатов с общим количеством потоков в пределах тысячи друг от друга. Для приведённого выше фрагмента мы хотели бы увидеть что-то вроде:
Для информации, перекрёстные соединения также можно писать без явного указания соединения:
Часть 2
Примечание: этот скорее бонусный вопрос, чем реально важный шаблон SQL. Можете его пропустить!
Задача: как можно изменить SQL из предыдущего решения, чтобы удалить дубликаты? Например, на примере той же таблицы, чтобы пара NC и SC появилась только один раз, а не два.
№ 3. Продвинутые расчёты
Благодарность: эта задача адаптирована из обсуждения по вопросу, который я задал на StackOverflow (мой ник zthomas.nc).
Примечание: вероятно, это более сложная задача, чем вам предложат на реальном собеседовании. Воспринимайте её скорее как головоломку — или можете пропустить её!
Контекст: допустим, у нас есть таблица table такого вида, где одному и тому же пользователю user могут соответствовать разные значения класса class :
Задача: предположим, что существует только два возможных значения для класса. Напишите запрос для подсчёта количества пользователей в каждом классе. При этом пользователи с обеими метками a и b должны относиться к классу b .
Для нашего образца получится такой результат:
Альтернативное решение использует инструкции SELECT в операторах SELECT и UNION :