Что спрашивают на собеседовании sql
Перейти к содержимому

Что спрашивают на собеседовании sql

  • автор:

22 вопроса и ответы на собеседовании по SQL: от базового до продвинутого

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

Будут объяснять сценарии, писать образцы SQL-запросов и определять команды, среди прочего.

Ну, вы пришли в нужное место!

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

Основные вопросы по SQL на собеседовании

Что такое соединения в SQL?

Соединение — это операция, которая используется для объединения данных из нескольких таблиц в новую таблицу. Различные типы объединений определяют, как данные между таблицами сопоставляются с новой таблицей. Когда вам нужно получить данные из нескольких таблиц в одном запросе, есть большая вероятность, что вы будете использовать операцию соединения.

Интервьюеры задают этот вопрос, чтобы проверить ваше фундаментальное понимание того, как запрашиваются данные и насколько это необходимо практически для любого типа приложений. Мы не можем просто свалить все данные в одну таблицу, так как она станет громоздкой и плохо организованной.

Пример: мы хотели бы хранить таблицу клиентов (с их именем, адресом, компанией, которую они представляют и т. д.) отдельно от таблицы транзакций (с купленными товарами, когда была совершена транзакция, сколько стоили товары, кто совершил покупка и др.).

Когда мы сохраняем информацию о том, кто совершил покупку, мы не хотели бы дублировать всю информацию о покупателе в каждой строке таблицы транзакций, и мы хотели бы сохранить только идентификатор клиента. Чтобы ответить на такой вопрос, как «сколько покупок было совершено клиентами, проживающими в определенном почтовом индексе?», нам нужно соединить две таблицы, чтобы получить такой ответ:

SELECT COUNT(*) FROM customers c JOIN transactions t ON t.customer_id = c.id WHERE c.zipcode = 94107; 

В чем разница между DELETE и TRUNCATE заявления?

Это один из тех вопросов на собеседовании по SQL, которые призваны оценить ваше понимание того, как строки базы данных хранятся и управляются внутри. Хотя DELETE и TRUNCATE оба могут использоваться для удаления всех данных из таблицы, база данных обрабатывает эти запросы по-разному. DELETE операции могут фильтровать целевые строки, поскольку они поддерживают предложение WHERE , тогда как TRUNCATE операция удаляет всю таблицу.

При удалении всей таблицы с помощью этих двух операций TRUNCATE выполняется быстрее за счет невозможности выполнения некоторых операций, зависящих от журнала транзакций. Например, некоторые SQL-серверы могут откатывать DELETE операции, а не TRUNCATE операции.

Как правило, вы должны использовать эту DELETE операцию из-за таких преимуществ, как история в журнале транзакций. В ситуациях, когда DELETE операция над всей таблицей занимает слишком много времени, вы можете рассмотреть возможность использования этой TRUNCATE операции. Интервьюеру было бы интересно услышать о компромиссах между двумя операциями.

В чем разница между первичным ключом и уникальным ключом?

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

Например, когда вы разрабатываете таблицу для отслеживания пользователей, у вас может быть столбец для user_id в качестве первичного ключа. user_id будет уникальным и используется для ссылки на остальные данные в строке. Чтобы поддерживать качество данных, у вас может быть столбец для в phone_number качестве уникального ключа, чтобы гарантировать, что в таблице может существовать только один номер каждого телефона.

Какова цель внешнего ключа в SQL?

Внешние ключи — это столбцы, используемые для ссылки на первичные ключи в других таблицах. Это помогает установить ограничение для столбца, чтобы обеспечить ссылочную целостность таблиц. Внешние ключи являются фундаментальной концепцией проектирования таблиц базы данных, и интервьюеры захотят убедиться, что вы их понимаете и использовали в прошлом. Понимание их показывает, что вы знаете, как проектировать схемы, охватывающие несколько таблиц базы данных.

Например, представьте, что у вас есть две таблицы: заказы и пользователи. Каждый заказ должен быть создан пользователем, чтобы таблица заказов могла иметь внешний ключ для первичного ключа пользовательской таблицы. Это ограничение гарантирует, что каждый пользователь, определенный в таблице заказов, ссылается на допустимую строку.

Какие существуют способы оптимизации запроса?

Планы запросов — это полезный способ изучить, как будет выполняться запрос, и помочь вам лучше понять, что замедляет выполнение вашего запроса.

Например, добавление к запросу в PostgreSQL символа EXPLAIN покажет план запроса для команды. Это покажет сканирование таблицы, которое будет задействовано в запросе.

Итак, если мы хотим проверить поведение запроса: SELECT * FROM table_1;, we can run the query EXPLAIN SELECT * FROM table_1;. Оттуда план запроса разбивает различные шаги, которые будут предприняты для выполнения запроса. Это распространенный способ выявления ненужных полных сканирований таблицы, который можно облегчить, настроив правильные индексы.

Эти методы важны, потому что интервьюеры хотят понять, как вы можете подходить к устранению неполадок запросов к базе данных. Если кандидат знает только, как выполнить запрос к базе данных для измерения производительности, это свидетельствует о недостатке знаний и опыта работы с реляционными базами данных.

Какими способами можно определить, как можно оптимизировать запрос?

Запросы можно оптимизировать разными способами. Вот несколько распространенных примеров:

  • Уменьшите объем данных для запроса с помощью WHERE предложений.
  • Ограничьте количество полезных строк, которые база данных должна запрашивать с помощью LIMIT предложения.
  • Добавьте индекс для часто запрашиваемых столбцов.

Цель этого вопроса часто состоит не в том, чтобы просто перечислить все приведенные выше примеры. Поскольку этот вопрос является открытым, некоторые интервьюеры могут представить этот вопрос со сценарием, в котором можно определить и применить определенные оптимизации.

Что такое нормализация и в чем ее преимущества?

Нормализация базы данных — это стратегия эффективной организации данных в базе данных. Цель состоит в том, чтобы уменьшить количество избыточных данных, чтобы одни и те же данные не хранились в нескольких таблицах. Вместо этого на данные будет ссылаться первичный ключ.

Например, таблица с именем orders может иметь столбец с именем user_id . Вместо того, чтобы хранить копию пользовательских данных в таблице заказов, мы можем просто ссылаться на эти данные из другой таблицы с помощью соединения.

Его часто просят оценить понимание кандидатом дизайна таблицы. Нормализация данных является ключевым компонентом проектирования схем таблиц в реляционных базах данных.

Что такое сущности и отношения?

Объект представляет собой абстракцию набора связанных данных и представлен в виде таблиц . Отношения определяют, как объекты связаны друг с другом.

Например, предположим, что у нас есть две таблицы с именами orders и users. Наши orders и users являются нашими сущностями. Мы можем представить, что у одного пользователя может быть много заказов. Таким образом, пользователи могут иметь отношения «один ко многим» с заказами.

Сущности и отношения часто используются в процессе разработки схемы таблицы. Понимая, как определять объекты и отображать их отношения, вы показываете, что можете продуктивно работать в коллективном сеансе проектирования таблиц базы данных.

Как можно настроить таблицу так, чтобы запросы к определенным строкам не приводили к полному сканированию таблицы?

Полного сканирования таблицы можно избежать, запрашивая индексированные столбцы и используя ограничения. Индексированные столбцы помогают базе данных оптимизировать поиск в таблицах.

Существует множество стратегий сокращения запрашиваемых строк, и их можно подтвердить с помощью планов запросов. Интервьюеры часто задают этот вопрос о SQL, чтобы оценить ваше понимание того, как правильно проектировать таблицы SQL и оптимизировать запросы.

Дополнительные вопросы и ответы на собеседовании по SQL

Объясните некоторые различные типы индексов в SQL.

В кластеризованном индексе данные физически хранятся в одной и той же области таблицы, поэтому их можно эффективно запрашивать и извлекать вместе. Некластеризованный индекс часто используется для запросов на основе ключей, тогда как кластеризованный индекс часто используется для диапазонов.

Большинство баз данных SQL выберут лучший тип индекса для вашего варианта использования. Интервьюеры обычно задают этот вопрос, чтобы оценить ваше понимание внутренней настройки индексов.

Каков сценарий, когда вы решите использовать ноль или пробел над NULL значением в строке?

Использование нуля или пробела над NULL значением является конструктивным решением. NULL может означать отсутствие данных.

Пример можно привести с отчеством: если пользователь не указал отчество, то его отчество будет NULL. Если пользователь указал, что у него нет отчества, то его отчество будет пустой строкой.

Если мы знаем, что никогда не будем заботиться о различии между значением нуля/пробела и отсутствием значения, тогда мы можем пойти дальше и по умолчанию установить для столбца нулевое значение или значение пробела. Это может быть полезно в таблице, которая отслеживает, сколько раз что-то произошло (например, количество посещений веб-сайта). Нам не нужно различать 0 и отсутствие данных, поскольку отсутствие данных подразумевает 0, поэтому мы можем упростить код нашего приложения, установив по умолчанию 0.

Технический рекрутер или менеджер по найму может задать подобный вопрос на собеседовании по SQL, чтобы оценить ваше понимание того, как различные типы хранятся в таблице базы данных. Возможность сообщить об этих решениях показывает, что вы понимаете некоторые соображения по выбору подходящих компромиссов в дизайне.

В чем разница между перекрестным соединением и естественным соединением?

Если не указано, соединение по умолчанию будет использовать внутреннее соединение. Хотя есть вероятность, что вы раньше не использовали перекрестное соединение или естественное соединение, понимание различных типов соединений может показать интервьюеру, что вы знакомы с различными способами объединения данных между таблицами.

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

Естественное соединение создаст таблицу, которая объединяет столбцы с одинаковыми именами и типами. Любой общий столбец между таблицами будет рассматриваться для объединения. Это отличается от обычно используемого внутреннего соединения, где соединения явно выполняются для указанных столбцов. Важно отметить, что если естественное соединение не находит совпадающих столбцов, оно по существу дает тот же результат, что и перекрестное соединение.

Что такое UNION, MINUSи INTERSECT команды?

Эти три команды известны как операции над множествами. Интервьюеры зададут вам этот вопрос, чтобы оценить ваше знакомство с анализом данных и обработкой данных с использованием SQL. Если вы широко использовали SQL, но не сталкивались с этими командами, интервьюер может узнать, что вы можете использовать команды SQL для выполнения операций CRUD вместо анализа данных.

  • UNION создаст таблицу, в которой данные содержатся в двух таблицах.
  • MINUS создаст таблицу, в которой есть данные из первой таблицы за вычетом данных из второй таблицы.
  • INTERSECT создаст таблицу с общими данными между двумя таблицами.

Если мы представим диаграмму Венна с двумя пересекающимися окружностями и тремя отдельными частями, UNION представляющими все три части, MINUS представляющими левую часть и INTERSECT представляющую среднюю часть.

Напишите SQL-запрос, чтобы получить третью по величине зарплату сотрудника из employee_table.

SELECT salary FROM employee_table ORDER BY salary DESC LIMIT 1 OFFSET 2;

  • SELECT salary указывает, что мы хотим вернуть только зарплату и не включать другие столбцы.
  • ORDER BY salary DESC возвращает все результаты, упорядоченные от наибольшей до наименьшей зарплаты.
  • LIMIT 1 указывает, что мы хотим получить только одну строку. Поскольку результаты уже упорядочены фильтром ORDER BY , это вернет наибольшую зарплату.
  • OFFSET 2 указывает, что мы хотим пропустить первые 2 строки.

Этот вопрос состоит из общих команд в SQL. Вы должны быть знакомы со всеми этими командами, чтобы эффективно писать SQL-запросы.

Обратите внимание, что работодатель может задавать вам похожие вопросы на собеседовании по SQL с разной формулировкой. Цель состоит не в том, чтобы запомнить это решение и повторить его интервьюеру. Когда вам задают этот тип вопроса, вы должны разбить свой запрос на части, которые приводят к окончательному решению. Это покажет вашему интервьюеру, что у вас дисциплинированный процесс написания запросов.

Зачем нужны групповые функции в SQL?

Групповые функции — один из ключевых способов выполнения анализа данных с помощью SQL. Интервьюер задаст этот вопрос, чтобы определить, используете ли вы SQL для CRUD или использовали ли вы SQL для анализа данных.

Групповые функции помогают нам объединить набор строк в одну группу данных, представленных строками. Эти функции часто используются для анализа таблиц, чтобы лучше понять данные, которые они представляют.

Например, предположим, что у нас есть таблица users , в которой есть строка с именем country , указывающая страну, из которой находится пользователь. Используя GROUP BY, мы можем определить количество пользователей для каждой страны в нашей таблице.

Отвечая на этот вопрос интервью для SQL, также важно предоставить конкретные примеры того, как вы, возможно, использовали групповые функции раньше в прошлом.

Для чего используются функции SQL?

Функции SQL предоставляют способы выполнения вычислений в базе данных. Они могут включать агрегации, которые часто используются для аналитики. Примером функции агрегирования может быть AVG функция, которая возвращает средние цены, уплаченные за покупку: SELECT AVG(price) AS average_price FROM purchases.

Когда вам зададут этот вопрос, интервьюер будет впечатлен, если вы сможете привести несколько примеров функций SQL, которые вы использовали, и как они использовались в вашем запросе.

В чем разница между HAVING оговоркой и WHERE оговоркой?

WHERE используется для фильтрации строк перед их возможной группировкой. HAVING используется для фильтрации строк после их группировки. HAVING функции очень похожи на , WHERE но используются после некоторой формы агрегации. WHERE Предложения должны быть предпочтительными, когда это возможно, так как наш запрос будет быстрее, если мы отфильтруем предварительную агрегацию данных, чем последующую, но некоторые фильтры могут выполняться только после агрегации и поэтому требуют использования предложения WHERE .

Пример . Учитывая таблицу заказов, верните число customer id и количество заказов, сделанных среди клиентов, совершивших не менее 10 покупок. Единственный способ узнать, сколько заказов сделал клиент, — это сначала подсчитать (объединить) все его заказы перед фильтрацией. Мы могли бы написать этот запрос как SELECT customer_id FROM (SELECT COUNT(*) AS count, customer_id from orders GROUP BY 2) WHERE COUNT >= 10, или мы можем упростить его с помощью HAVING предложения: SELECT COUNT(*) AS count, customer_id from orders GROUP BY 2 HAVING count >= 10

Интервьюер может не спрашивать вас явно о разнице между этими двумя пунктами, но представить вопрос в более широком контексте. Они могут попросить вас написать запрос, в котором для возврата правильного результата необходимы оба предложения WHERE и .HAVING

Как вы можете получить альтернативные записи из таблицы?

Есть несколько способов получить чередующиеся записи. Цель интервьюера — оценить ваше знакомство и удобство при написании SQL-запросов и использовании функций.

Обычно таблицы имеют автоматически увеличивающиеся первичные ключи. Мы можем использовать оператор модуля % для получения чередующихся строк. Это работает, потому что число, деленное на 2, всегда возвращает либо 0, либо 1.

SELECT * FROM WHERE % 2 = 0; SELECT * FROM WHERE % 2 = 1;

Назовите оператор, который используется в запросе на сопоставление с образцом.

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

Если у нас есть таблица products с именем столбца с именем name, мы можем искать в каждой строке продукты с именами, содержащими слово toy : SELECT * FROM products WHERE name LIKE ‘%toy%.

Сопоставление с образцом часто используется для поиска определенного текста в столбце по многим строкам. Интервьюеры будут рады узнать, что вы понимаете, как работает сопоставление с образцом и как оно часто применяется в производственных базах данных.

Когда было бы более уместно использовать материализованное представление вместо представления?

Как материализованные представления, так и представления упрощают запрос данных, преобразуя некоторые табличные данные в собственную структуру. Материализованные представления сохраняют данные, тогда как представления вычисляют свои данные при каждом запросе.

Это означает, что материализованные представления имеют лучшую производительность чтения, поскольку данные сохраняются. Когда производительности достаточно, иногда рекомендуется использовать представление, чтобы уменьшить объем данных, которые будут храниться с материализованным представлением.

Когда менеджеры по найму и технические рекрутеры задают подобные вопросы на собеседовании по SQL, они заинтересованы в том, чтобы знать, что вы понимаете, как добиться компромиссов в производительности в SQL. Даже если интервьюер не задает именно этот вопрос, вы можете сослаться на преимущества материализованного представления или представления в ситуации, когда вам нужно создавать сложные запросы.

Перечислите некоторые преимущества и недостатки хранимой процедуры.

Хранимые процедуры полезны для многократно используемых SQL-запросов. На практике мы можем создать хранимую процедуру вместо хранения списка многократно используемых SQL-запросов. Это уменьшает возможность ошибки и стандартизирует набор операций для воспроизводимости.

Однако хранимые процедуры часто очень специфичны для случая использования и не переносимы. Хранимая процедура не будет работать в другой таблице базы данных с аналогичной структурой, и ее необходимо будет создать заново, чтобы она заработала.

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

Чтобы сделать ваш ответ на этот вопрос интервью SQL более эффективным, сошлитесь на пример, когда вы, возможно, использовали или рассматривали возможность использования хранимой процедуры. Интервьюеры, как правило, больше впечатляются, когда ответ кандидата подкрепляется соответствующим опытом, а не определениями.

В чем разница между базами данных OLTP (например, MySQL) и OLAP (например, хранилище данных)?

Это еще один из тех вопросов для собеседования с разработчиками SQL, которые призваны помочь интервьюеру оценить, понимает ли кандидат различные базы данных SQL, которые могут существовать, и чем они отличаются друг от друга. Различные варианты использования часто определяют тип используемой базы данных SQL.

Базы данных OLTP предназначены для быстрых запросов с надежной целостностью данных. Как правило, они оптимизированы для выполнения повседневных бизнес-операций, связанных с чтением и записью в реальном времени.

Базы данных OLAP оптимизированы для автономной аналитики и обычно включают сложные агрегаты. По сравнению с базами данных OLTP они часто имеют меньшие объемы запросов, содержат больше исторических данных и требуют значительно меньшего количества правок.

Если мы проиллюстрируем это на веб-приложении, база данных OLTP, вероятно, обрабатывает метаданные для веб-приложения. Действия пользователя и данные сохраняются и извлекаются для базы данных OLTP. Базы данных OLAP, вероятно, будут хранить данные того же типа, но будут использоваться для понимания ключевых показателей, таких как удержание пользователей и их поведение.

Ваш ответ на этот вопрос SQL будет гораздо более эффективным, если вы предоставите примеры различных баз данных OLTP и баз данных OLAP, которые вы, возможно, использовали.

Подведение итогов

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

Техническое собеседование по SQL предназначено для оценки вашего уровня понимания и определения того, подходите ли вы для этой работы. Однако правильный ответ на каждый вопрос не гарантирует вам роль. Рекрутеры используют эти вопросы на собеседовании по SQL, чтобы глубже понять, как вы подходите к решению проблем и общению.

Таким образом, несмотря на то, что это техническое собеседование, социальные навыки будут важным фактором для прохождения этого этапа вашего процесса найма. Удачи вам на предстоящем собеседовании по SQL!

Наше агентство по подбору ИТ-персонала предлагает вам найти квалифицированных разработчиков за срок менее 2 недель. Свяжитесь с нами прямо сейчас, чтобы узнать подробнее о возможностях расширения вашего будущего проекта. Мы обеспечиваем подбор лучших кандидатов по разумной цене. За 10 лет работы в этой сфере мы успешно заполнили свыше 5500 вакансий и сформировали 25+ команд с нуля. Проверьте отзывы от наших клиентов об агентстве и убедитесь в нашей компетентности! Если требуются дополнительные рекомендации, пишите нам в Telegram.

50 лучших вопросов для собеседования по SQL-запросам (и примеры ответов)

Язык структурированных запросов, или SQL, — это язык, который используется при управлении базой данных. Если вы специалист по работе с данными, вам может понадобиться практическое понимание SQL-запросов и их использования. В этой статье мы приводим 50 лучших вопросов для собеседования по SQL-запросам и примеры успешных ответов на них.

Общие вопросы

Вот несколько общих вопросов, которые вы можете использовать для следующего собеседования на должность специалиста по SQL-запросам:

  • Чего вы хотите добиться с помощью SQL-запросов в этой компании??
  • Как вы думаете, что вы можете сделать за 90 дней??
  • Опишите жизненный цикл данных.
  • Определите SQL.
  • Почему вы покидаете свою текущую должность?
  • Каковы лучшие навыки для людей, работающих с SQL-запросами? Почему?
  • Почему мы должны выбрать именно вас из всех кандидатов?
  • Были ли у вас профессиональные достижения с использованием SQL-запросов? Можете ли вы описать их?
  • На какую зарплату вы претендуете?
  • Расскажите об одном дне из жизни на работе вашей мечты.
  • Расскажите, как вы упрощаете сложные идеи, выступая перед группой людей с разным уровнем знаний и способностей.
  • Как бы вас охарактеризовал ваш последний начальник?
  • Что вы думаете о работе в гибкой рабочей среде?
  • Как бы вы общались с членом команды, написавшим запрос с ошибками, по поводу исправления ошибок?

Вопросы об опыте и биографии

Иногда интервьюеры задают вам вопросы о вашей биографии и опыте работы. Именно таких вопросов вы можете ожидать от собеседования по SQL-запросам:

  • С какими продуктами баз данных вы имеете опыт работы?
  • Как давно вы пишете SQL-запросы?
  • Опишите свой опыт работы.
  • Объясните проекты в вашем портфолио, в которых используются SQL-запросы.
  • Есть ли у вас специальное образование в области SQL или написания запросов?
  • Каковы, по вашему опыту, наиболее важные области применения SQL-запросов на предприятиях??
  • Знаете ли вы какие-либо другие языки кодирования? Что это такое?
  • Что вам нравится в написании SQL-запросов?
  • Как долго вы работаете с SQL?
  • Каковы ваши сильные стороны в написании SQL-запросов??
  • Есть ли у вас слабые места в SQL?
  • Расскажите мне об успешном проекте, который вы завершили, используя SQL-запросы?
  • Расскажите мне о случае, когда проект по SQL был неудачным, что произошло?
  • Есть ли у вас опыт работы с DevOps?
  • Какие инструменты управления проектами вы использовали для отслеживания проектов SQL?
  • Какие запросы в SQL вы использовали чаще всего??

Углубленные вопросы

Помимо вопросов о вашем опыте и общих вопросов, вам могут быть заданы углубленные вопросы, требующие от вас решения проблем SQL или ответа в несколько шагов или действий:

  • Подготовка выборочных данных из базы данных SQL с помощью SQL-запросов.
  • Создать запрос, который выбирает сопоставленный фрагмент данных из таблицы с помощью псевдонима.
  • Создайте SQL-запрос, который выбирает сопоставленный фрагмент данных из таблицы-образца и возвращает его обратно.
  • Напишите SQL-запрос, который возвращает только уникальные значения из столбца данных в таблице.
  • Создайте запрос, который выводит данные об имени и фамилии из таблицы-образца в столбец FULL_NAME.
  • Напишите запрос, который выводит данные из таблицы-образца в порядке возрастания фамилий.
  • Используйте запрос, чтобы найти максимальную зарплату каждого отдела, перечисленного в таблице-образце.
  • Вывести текущую дату с помощью SQL-запроса.
  • Используя выборочную совокупность данных, найдите все записи о сотрудниках, которые имеют одинаковое имя и фамилию.
  • Вернуть дату рождения сотрудника между 1955-1985 гг.

Общие вопросы для собеседования по SQL-запросам и примеры ответов

Вот распространенные вопросы для собеседования по SQL-запросам и примеры ответов:

1. Что такое запрос в SQL?

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

Пример: Запрос в SQL — это запрос. Это может быть запрос на получение данных или выполнение действия, такого как вставка или обновление.

2. Определение подзапроса

Это еще один базовый вопрос. Любой специалист по работе с данными, который работает с запросами SQL, должен понимать, что такое подзапросы. Чтобы продемонстрировать понимание, ответьте лаконичным и четким определением.

Пример: Подзапросы — это компоненты, из которых может состоять запрос. Подзапрос обеспечивает внутренний запрос — термин, используемый для запроса, возвращающего компонент другого запроса.

3. Объясните транзакции и опишите их контроль

Транзакции представляют собой рутинные операции, с которыми работают администраторы баз данных. Специалистам по работе с данными может потребоваться умение объяснять транзакции и перечислять важные элементы управления.

Пример: Транзакции — это последовательные задачи, которые производят такие операции, как создание, обновление и удаление записей. Существует четыре элемента управления транзакциями: фиксация, откат, установка трака и точка сохранения.

4. Опишите, как операторы сравнения строк работают с подзапросом

Операторы сравнения строк помогают специалистам по работе с данными выполнять функции, необходимые для работы. Чтобы показать владение последовательными запросами, объясните, какие операторы строк участвуют в подзапросе.

Пример: Существует четыре оператора сравнения строк для подзапросов: IN, ANY и ALL.

5. Определите хранимую процедуру

Когда процедура SQL, в которой задействовано несколько процессов, должна быть повторяемой, хранимая процедура может быть хорошим решением. Создание этих процедурных пакетов может быть ежедневной рутиной для администраторов баз данных, поэтому важно, чтобы ответ демонстрировал понимание.

Пример: Набор операторов SQL, которые могут быть выполнены, когда вам это нужно, с включенным условным форматированием.

6. Объясните процессы: ОБЪЕДИНЕНИЕ, МИНУС, ОБЪЕДИНЕНИЕ ВСЕХ, ПЕРЕСЕЧЕНИЕ.

На собеседованиях по SQL-запросам вам, скорее всего, придется продемонстрировать знание процессов и их роли в выполнении запросов. Чтобы ответить на этот вопрос, дайте краткое определение каждому из них.

Пример: UNION: возвращает все отдельные строки, которые отвечают на запрос.

МИНУС: Возвращает любые строки данных, вызванные запросом один, но не запросом два.

UNION ALL: Показывает все строки, на которые отвечают оба запроса. Данные не обязательно должны быть различимыми, и дубликаты включаются в выборку.

ИНТЕРСЕКТ: Возвращает все строки, содержащие уникальные данные, которые вызываются обоими запросами.

7. Определите индекс.

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

Пример: Индекс — это уникальная структура, которая может быть создана в таблице для оптимизации производительности запросов.

8. Какое преимущество дает использование представлений в запросе?

Существует множество преимуществ использования представлений, некоторые из них связаны с запросами. Объясните, почему администратор данных может использовать представления в SQL для управления запросами.

Пример: Представления могут использовать простые запросы на получение данных для отображения информации из более сложных запросов по мере необходимости.

9. Продемонстрируйте, как написать запрос, чтобы показать подробную информацию о сотруднике, чья фамилия начинается на М.

Написание практических запросов может быть частью вашего процесса собеседования, или у вас может быть домашнее задание, которое вы должны выполнить до или после собеседования. В любом случае, важно освежить в памяти, как писать запросы, чтобы быть готовым к распространенным вопросам на собеседовании по SQL-запросам.

Пример: SELECT * FROM Student WHERE Employee_Name like ‘M%’.

10. Сравните и противопоставьте вложенный подзапрос и коррелированный подзапрос

Всякий раз, когда вас просят рассказать о разнице между двумя вещами, у вас есть возможность подробно остановиться на двух концепциях. Используйте это в своих интересах, составив ответ, который покажет, что вы разбираетесь в обоих вопросах.

Пример: Вложенный подзапрос — это запрос, который существует внутри другого подзапроса. Коррелированный подзапрос — это запрос, в котором результат подзапроса зависит от значений из таблицы запроса.

Ключевые слова:

  • indeed.com

Топ-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) используются для указания ограничения на тип данных таблицы. Они могут быть указаны при создании или изменении таблицы. Пример ограничений:

Вопрос 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?
  1. Различия между кластеризованным и некластеризованным индексами в SQL:
    Кластерный индекс используется для простого и быстрого извлечения данных из базы данных, тогда как чтение из некластеризованного индекса происходит относительно медленнее.
  2. Кластеризованный индекс изменяет способ хранения записей в базе данных — он сортирует строки по столбцу, который установлен как кластеризованный индекс, тогда как в некластеризованном индексе он не меняет способ хранения, но создает отдельный объект внутри таблицы, который указывает на исходные строки таблицы при поиске.
  3. Одна таблица может иметь только один кластеризованный индекс, тогда как некластеризованных у нее может быть много.
Вопрос 14. Напишите SQL-запрос для отображения текущей даты.

В SQL есть встроенная функция GetDate (), которая помогает возвращать текущий timestamp/дату.

Вопрос 15. Перечислите типы соединений

Существуют различные типы соединений, которые используются для извлечения данных между таблицами. Принципиально они делятся на четыре типа, а именно:

Inner join (Внутреннее соединение): в MySQL является наиболее распространенным типом. Оно используется для возврата всех строк из нескольких таблиц, для которых выполняется условие соединения.

Left Join (Левое соединение): в MySQL используется для возврата всех строк из левой (первой) таблицы и только совпадающих строк из правой (второй) таблицы, для которых выполняется условие соединения.

Right Join (Правое соединение): в MySQL используется для возврата всех строк из правой (второй) таблицы и только совпадающих строк из левой (первой) таблицы, для которых выполняется условие соединения.

Full Join (Полное соединение): возвращает все записи, для которых есть совпадение в любой из таблиц. Следовательно, он возвращает все строки из левой таблицы и все строки из правой таблицы.

Вопрос 16. Что вы подразумеваете под денормализацией?

Денормализация — техника, которая используется для преобразования из высших к низшим нормальным формам. Она помогает разработчикам баз данных повысить производительность всей инфраструктуры, поскольку вносит избыточность в таблицу. Она добавляет избыточные данные в таблицу, учитывая частые запросы к базе данных, которые объединяют данные из разных таблиц в одну таблицу.

Вопрос 17. Что такое сущности и отношения?

Сущности: человек, место или объект в реальном мире, данные о которых могут храниться в базе данных. В таблицах хранятся данные, которые представляют один тип сущности. Например — база данных банка имеет таблицу клиентов для хранения информации о клиентах. Таблица клиентов хранит эту информацию в виде набора атрибутов (столбцы в таблице) для каждого клиента.

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

Вопрос 18. Что такое индекс?

Индексы относятся к методу настройки производительности, позволяющему быстрее извлекать записи из таблицы. Индекс создает отдельную структуру для индексируемого поля и, следовательно, позволяет быстрее получать данные.

Вопрос 19. Опишите различные типы индексов.

Есть три типа индексов, а именно:

  1. Уникальный индекс (Unique Index): этот индекс не позволяет полю иметь повторяющиеся значения, если столбец индексируется уникально. Если первичный ключ определен, уникальный индекс может быть применен автоматически.
  2. Кластеризованный индекс (Clustered Index): этот индекс меняет физический порядок таблицы и выполняет поиск на основе значений ключа. Каждая таблица может иметь только один кластеризованный индекс.
  3. Некластеризованный индекс (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 доступно три типа оператора, а именно:

  1. Арифметические Операторы
  2. Логические Операторы
  3. Операторы сравнения
Вопрос 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 вопросов с ответами опубликуем в следующей части… Следите за новостями!

Лучшие вопросы средней сложности по SQL на собеседовании аналитика данных

С 2015 по 2019 годы я прошёл четыре цикла собеседований на должность аналитика данных и специалиста по анализу данных в более чем десятке компаний. После очередного неудачного интервью в 2017 году — когда я запутался в сложных вопросах по SQL — я начал составлять задачник с вопросами по SQL средней и высокой сложности, чтобы лучше готовиться к собеседованиям. Этот справочник очень пригодился в последнем цикле собеседований 2019 года. За последний год я поделился этим руководством с парой друзей, а благодаря дополнительному свободному времени из-за пандемии отшлифовал его — и составил этот документ.

Есть множество отличных руководств по SQL для начинающих. Мои любимые — это интерактивные курсы Codecademy по SQL и Select Star SQL от Цзы Чон Као. Но в реальности первые 70% из курса SQL довольно просты, а настоящие сложности начинаются в остальных 30%, которые не освещаются в руководствах для начинающих. Так вот, на собеседованиях для аналитиков данных и специалистов по анализу данных в технологических компаниях часто задают вопросы именно по этим 30%.

Удивительно, но я не нашёл исчерпывающего источника по таким вопросам среднего уровня сложности, поэтому составил данное руководство.

Оно полезно для собеседований, но заодно повысит вашу эффективность на текущем и будущих местах работы. Лично я считаю, что некоторые упомянутые шаблоны SQL полезны и для ETL-систем, на которых работают инструменты отчётности и функции анализа данных для выявления тенденций.

Нужно понимать, что на собеседованиях дата-аналитиков и специалистов по анализу данных задают вопросы не только по SQL. Другие общие темы включают обсуждение прошлых проектов, A/B-тестирование, разработку метрик и открытые аналитические проблемы. Примерно три года назад на Quora публиковались советы по собеседованию на должность аналитика продукта (product analyst) в Facebook. Там эта тема обсуждается более подробно. Тем не менее, если улучшение знаний по SQL поможет вам на собеседовании, то это руководство вполне стоит потраченного времени.

В будущем я могу перенести код из этого руководства на сайт вроде Select Star SQL, чтобы было проще писать инструкции SQL — и видеть результат выполнения кода в реальном времени. Как вариант — добавить вопросы как проблемы на платформу для подготовки к собеседованиям LeetCode. Пока же я просто хотел опубликовать этот документ, чтобы люди могли прямо сейчас ознакомиться с этой информацией.

Сделанные допущения и как использовать руководство

Предположения о знании языка SQL: Предполагается, что у вас есть рабочие знания SQL. Вероятно, вы часто используете его на работе, но хотите отточить навыки в таких темах, как самообъединения и оконные функции.

Как использовать данное руководство: Поскольку на собеседовании часто используется доска или виртуальный блокнот (без компиляции кода), то рекомендую взять карандаш и бумагу — и записать решения для каждой проблемы, а после завершения сравнить свои записи с ответами. Или отработайте свои ответы вместе с другом, который выступит в качестве интервьюера!

  • Небольшие синтаксические ошибки не имеют большого значения во время собеседования с доской или блокнотом. Но они могут отвлекать интервьюера, поэтому в идеале старайтесь уменьшить их количество, чтобы сконцентрировать всё внимание на логике.
  • Приведённые ответы не обязательно единственный способ решить каждую задачу. Не стесняйтесь писать комментарии с дополнительными решениями, которые можно добавить в это руководство!

Советы по решению сложных задач на собеседованиях по SQL

Сначала стандартные советы для всех собеседований по программированию…

  1. Внимательно выслушайте описание проблемы, повторите всю суть проблемы интервьюеру
  2. Сформулируйте пограничный случай, чтобы продемонстрировать, что вы действительно понимаете проблему (т. е. строку, которая не будет включена в итоговый запрос SQL, который вы собираетесь написать)
  3. (Если проблема связана с самообъединением) для своей же пользы нарисуйте, как будет выглядеть самообъединение — обычно это минимум три столбца: нужный столбец из основной таблицы, столбец для объединения из основной таблицы и столбец для объединения из вторичной таблицы
    • Или, когда вы лучше освоите задачи самообъединения, можете объяснить этот шаг устно
  4. Начните писать SQL, пусть с ошибками, вместо попыток полностью понять проблему. Формулируйте свои предположения по ходу дела, чтобы ваш интервьюер мог вас поправить.

Благодарности и дополнительные ресурсы

Некоторые из перечисленных здесь проблем адаптированы из старых записей в блоге Periscope (в основном написанных Шоном Куком около 2014 года, хотя его авторство, видимо, убрали из материалов после слияния SiSense с Periscope), а также из обсуждений на StackOverflow. В случае необходимости, источники отмечены в начале каждого вопроса.

На Select Star SQL тоже хорошая подборка задачек, дополняющих проблемы из этого документа.

Пожалуйста, обратите внимание, что эти вопросы не являются буквальными копиями вопросов с моих собственных собеседований, и они не использовались в компаниях, в которых я работал или работаю.

Задачи на самообъединение

№ 1. Процентное изменение месяц к месяцу

Контекст: часто полезно знать, как изменяется ключевая метрика, например, месячная аудитория активных пользователей, от месяца к месяцу. Допустим у нас есть таблица logins в таком виде:

| user_id | date | |---------|------------| | 1 | 2018-07-01 | | 234 | 2018-07-02 | | 3 | 2018-07-02 | | 1 | 2018-07-02 | | . | . | | 234 | 2018-10-04 |

Задача: найти ежемесячное процентное изменение месячной аудитории активных пользователей (MAU).

Решение:
(Это решение, как и другие блоки кода в этом документе, содержит комментарии об элементах синтаксисе SQL, которые могут отличаться между разными вариантами SQL, и прочие заметки)

WITH mau AS ( SELECT /* * Обычно интервьюер позволяет вам написать псевдокод для * функций даты, т. е. НЕ будет проверять, как вы их помните. * Просто объясните на доске, что делает функция * * В Postgres доступна DATE_TRUNC(), но аналогичный результат * могут дать другие функции даты SQL или их комбинации * См. https://www.postgresql.org/docs/9.0/functions-datetime.html#FUNCTIONS-DATETIME-TRUNC */ DATE_TRUNC('month', date) month_timestamp, COUNT(DISTINCT user_id) mau FROM logins GROUP BY DATE_TRUNC('month', date) ) SELECT /* * В эту инструкцию SELECT не нужно буквально включать предыдущий месяц. * * Но как упоминалось в разделе с советами выше, может быть полезно * хотя бы набросать самообъединения, чтобы не запутаться, какая * таблица представляет прошлый месяц к текущему и т.д. */ a.month_timestamp previous_month, a.mau previous_mau, b.month_timestamp current_month, b.mau current_mau, ROUND(100.0*(b.mau - a.mau)/a.mau,2) AS percent_change FROM mau a JOIN /* * Как вариант `ON b.month_timestamp = a.month_timestamp + interval '1 month'` */ mau b ON a.month_timestamp = b.month_timestamp - interval '1 month' 

№ 2. Маркировка древовидной структуры

Контекст: предположим, у вас есть таблица tree с двумя столбцами: в первом указаны узлы, а во втором — родительские узлы.

node parent 1 2 2 5 3 5 4 3 5 NULL

Задача: написать SQL таким образом, чтобы мы обозначили каждый узел как внутренний (inner), корневой (root) или конечный узел/лист (leaf), так что для вышеперечисленных значений получится следующее:

node label 1 Leaf 2 Inner 3 Inner 4 Leaf 5 Root

(Примечание: более подробно о терминологии древовидной структуры данных можно почитать здесь. Однако для решения этой проблемы она не нужна!)

Решение:
Благодарность: это более обобщённое решение предложил Фабиан Хофман 2 мая 2020 года. Спасибо, Фабиан!

WITH join_table AS ( SELECT cur.node, cur.parent, COUNT(next.node) AS num_children FROM tree cur LEFT JOIN tree next ON (next.parent = cur.node) GROUP BY cur.node, cur.parent ) SELECT node, CASE WHEN parent IS NULL THEN "Root" WHEN num_children = 0 THEN "Leaf" ELSE "Inner" END AS label FROM join_table 

Альтернативное решение, без явных соединений:

Благодарность: Уильям Чарджин 2 мая 2020 года обратил внимание на необходимость условия WHERE parent IS NOT NULL , чтобы это решение возвращало Leaf вместо NULL . Спасибо, Уильям!

SELECT node, CASE WHEN parent IS NULL THEN 'Root' WHEN node NOT IN (SELECT parent FROM tree WHERE parent IS NOT NULL) THEN 'Leaf' WHEN node IN (SELECT parent FROM tree) AND parent IS NOT NULL THEN 'Inner' END AS label from tree

№ 3. Удержание пользователей в месяц (несколько частей)

Часть 1

Контекст: допустим, у нас есть статистика по авторизации пользователей на сайте в таблице logins :

| user_id | date | |---------|------------| | 1 | 2018-07-01 | | 234 | 2018-07-02 | | 3 | 2018-07-02 | | 1 | 2018-07-02 | | . | . | | 234 | 2018-10-04 |

Задача: написать запрос, который получает количество удержанных пользователей в месяц. В нашем случае данный параметр определяется как количество пользователей, которые авторизовались в системе и в этом, и в предыдущем месяце.

Решение:

SELECT DATE_TRUNC('month', a.date) month_timestamp, COUNT(DISTINCT a.user_id) retained_users FROM logins a JOIN logins b ON a.user_id = b.user_id AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) + interval '1 month' GROUP BY date_trunc('month', a.date)

Благодарность:
Том Моэртел указал на то, что предварительная дедубликация user_id перед самообъединением делает решение более эффективным, и предложил код ниже. Спасибо, Том!

WITH DistinctMonthlyUsers AS ( /* * Для каждого месяца определяем *набор* пользователей, которые * выполнили авторизацию */ SELECT DISTINCT DATE_TRUNC('MONTH', a.date) AS month_timestamp, user_id FROM logins ) SELECT CurrentMonth.month_timestamp month_timestamp, COUNT(PriorMonth.user_id) AS retained_user_count FROM DistinctMonthlyUsers AS CurrentMonth LEFT JOIN DistinctMonthlyUsers AS PriorMonth ON CurrentMonth.month_timestamp = PriorMonth.month_timestamp + INTERVAL '1 MONTH' AND CurrentMonth.user_id = PriorMonth.user_id
Часть 2

Задача: теперь возьмём предыдущую задачу по вычислению количества удержанных пользователей в месяц — и перевернём её с ног на голову. Напишем запрос для подсчёта пользователей, которые не вернулись на сайт в этом месяце. То есть «потерянных» пользователей.

Решение:

SELECT DATE_TRUNC('month', a.date) month_timestamp, COUNT(DISTINCT b.user_id) churned_users FROM logins a FULL OUTER JOIN logins b ON a.user_id = b.user_id AND DATE_TRUNC('month', a.date) = DATE_TRUNC('month', b.date) + interval '1 month' WHERE a.user_id IS NULL GROUP BY DATE_TRUNC('month', a.date)

Обратите внимание, что эту проблему можно решить также с помощью соединений LEFT или RIGHT .

Часть 3

Примечание: вероятно, это более сложная задача, чем вам предложат на реальном собеседовании. Воспринимайте её скорее как головоломку — или можете пропустить и перейти к следующей задаче.

Контекст: итак, мы хорошо справились с двумя предыдущими проблемами. По условиям новой задачи теперь у нас появилась таблица потерянных пользователей user_churns . Если пользователь была активен в прошлом месяце, но затем не активен в этом, то он вносится в таблицу за этот месяц. Вот как выглядит user_churns :

| user_id | month_date | |---------|------------| | 1 | 2018-05-01 | | 234 | 2018-05-01 | | 3 | 2018-05-01 | | 12 | 2018-05-01 | | . | . | | 234 | 2018-10-01 |

Задача: теперь вы хотите провести когортный анализ, то есть анализ совокупности активных пользователей, которые были реактивированы в прошлом. Создайте таблицу с такими пользователями. Для создания когорты можете использовать таблицы user_churns и logins . В Postgres текущая временная метка доступна через current_timestamp .

Решение:

WITH user_login_data AS ( SELECT DATE_TRUNC('month', a.date) month_timestamp, a.user_id, /* * По крайней мере, в тех вариантах SQL, что я использовал, * не нужно включать в инструкцию SELECT колонки из HAVING. * Я здесь выписал их для большей ясности. */ MAX(b.month_date) as most_recent_churn, MAX(DATE_TRUNC('month', c.date)) as most_recent_active FROM logins a JOIN user_churns b ON a.user_id = b.user_id AND DATE_TRUNC('month', a.date) > b.month_date JOIN logins c ON a.user_id = c.user_id AND DATE_TRUNC('month', a.date) > DATE_TRUNC('month', c.date) WHERE DATE_TRUNC('month', a.date) = DATE_TRUNC('month', current_timestamp) GROUP BY DATE_TRUNC('month', a.date), a.user_id HAVING most_recent_churn > most_recent_active

№ 4. Нарастающий итог

Благодарность: эта задача адаптирована из статьи в блоге SiSense «Моделирование денежных потоков в SQL».

Контекст: допустим, у нас есть таблица transactions в таком виде:

| date | cash_flow | |------------|-----------| | 2018-01-01 | -1000 | | 2018-01-02 | -100 | | 2018-01-03 | 50 | | . | . |

Где cash_flow — это выручка минус затраты за каждый день.

Задача: написать запрос, чтобы получить нарастающий итог для денежного потока каждый день таким образом, чтобы в конечном итоге получилась таблица в такой форме:

| date | cumulative_cf | |------------|---------------| | 2018-01-01 | -1000 | | 2018-01-02 | -1100 | | 2018-01-03 | -1050 | | . | . |

Решение:

SELECT a.date date, SUM(b.cash_flow) as cumulative_cf FROM transactions a JOIN b transactions b ON a.date >= b.date GROUP BY a.date ORDER BY date ASC

Альтернативное решение с использованием оконной функции (более эффективное!):

SELECT date, SUM(cash_flow) OVER (ORDER BY date ASC) as cumulative_cf FROM transactions ORDER BY date ASC

№ 5. Скользящее среднее

Благодарность: эта задача адаптирована из статьи в блоге SiSense «Скользящие средние в MySQL и SQL Server».

Примечание: скользящее среднее можно вычислить разными способами. Здесь мы используем предыдущее среднее значение. Таким образом, метрика для седьмого дня месяца будет средним значением предыдущих шести дней и его самого.

Контекст: допустим, у нас есть таблица signups в таком виде:

| date | sign_ups | |------------|----------| | 2018-01-01 | 10 | | 2018-01-02 | 20 | | 2018-01-03 | 50 | | . | . | | 2018-10-01 | 35 |

Задача: написать запрос, чтобы получить 7-дневное скользящее среднее ежедневных регистраций.

Решение:

SELECT a.date, AVG(b.sign_ups) average_sign_ups FROM signups a JOIN signups b ON a.date = b.date GROUP BY a.date

№ 6. Несколько условий соединения

Благодарность: эта задача адаптирована из статьи в блоге SiSense «Анализ вашей электронной почты с помощью SQL».

Контекст: скажем, наша таблица emails содержит электронные письма, отправленные с адреса zach@g.com и полученные на него:

| id | subject | from | to | timestamp | |----|----------|--------------|--------------|---------------------| | 1 | Yosemite | zach@g.com | thomas@g.com | 2018-01-02 12:45:03 | | 2 | Big Sur | sarah@g.com | thomas@g.com | 2018-01-02 16:30:01 | | 3 | Yosemite | thomas@g.com | zach@g.com | 2018-01-02 16:35:04 | | 4 | Running | jill@g.com | zach@g.com | 2018-01-03 08:12:45 | | 5 | Yosemite | zach@g.com | thomas@g.com | 2018-01-03 14:02:01 | | 6 | Yosemite | thomas@g.com | zach@g.com | 2018-01-03 15:01:05 | | .. | .. | .. | .. | .. |

Задача:написать запрос, чтобы получить время отклика на каждое письмо ( id ), отправленное на zach@g.com . Не включать письма на другие адреса. Предположим, что у каждого треда уникальная тема. Имейте в виду, что в треде может быть несколько писем туда и обратно между zach@g.com и другими адресатами.

Решение:

SELECT a.id, MIN(b.timestamp) - a.timestamp as time_to_respond FROM emails a JOIN emails b ON b.subject = a.subject AND a.to = b.from AND a.from = b.to AND a.timestamp < b.timestamp WHERE a.to = 'zach@g.com' GROUP BY a.id 

Задачи на оконные функции

№ 1. Найти идентификатор с максимальным значением

Контекст: Допустим, у нас есть таблица salaries с данными об отделах и зарплате сотрудников в следующем формате:

depname | empno | salary | -----------+-------+--------+ develop | 11 | 5200 | develop | 7 | 4200 | develop | 9 | 4500 | develop | 8 | 6000 | develop | 10 | 5200 | personnel | 5 | 3500 | personnel | 2 | 3900 | sales | 3 | 4800 | sales | 1 | 5000 | sales | 4 | 4800 |

Задача: написать запрос, чтобы получить empno с самой высокой зарплатой. Убедитесь, что ваше решение обрабатывает случаи одинаковых зарплатами!

Решение:

WITH max_salary AS ( SELECT MAX(salary) max_salary FROM salaries ) SELECT s.empno FROM salaries s JOIN max_salary ms ON s.salary = ms.max_salary

Альтернативное решение с использованием RANK() :

WITH sal_rank AS (SELECT empno, RANK() OVER(ORDER BY salary DESC) rnk FROM salaries) SELECT empno FROM sal_rank WHERE rnk = 1;

№ 2. Среднее значение и ранжирование с оконной функцией (несколько частей)

Часть 1

Контекст: допустим, у нас есть таблица salaries в таком формате:

depname | empno | salary | -----------+-------+--------+ develop | 11 | 5200 | develop | 7 | 4200 | develop | 9 | 4500 | develop | 8 | 6000 | develop | 10 | 5200 | personnel | 5 | 3500 | personnel | 2 | 3900 | sales | 3 | 4800 | sales | 1 | 5000 | sales | 4 | 4800 |

Задача: написать запрос, который возвращает ту же таблицу, но с новым столбцом, в котором указана средняя зарплата по департаменту. Мы бы ожидали таблицу в таком виде:

depname | empno | salary | avg_salary | -----------+-------+--------+------------+ develop | 11 | 5200 | 5020 | develop | 7 | 4200 | 5020 | develop | 9 | 4500 | 5020 | develop | 8 | 6000 | 5020 | develop | 10 | 5200 | 5020 | personnel | 5 | 3500 | 3700 | personnel | 2 | 3900 | 3700 | sales | 3 | 4800 | 4867 | sales | 1 | 5000 | 4867 | sales | 4 | 4800 | 4867 |

Решение:

SELECT *, /* * AVG() is a Postgres command, but other SQL flavors like BigQuery use * AVERAGE() */ ROUND(AVG(salary),0) OVER (PARTITION BY depname) avg_salary FROM salaries
Часть 2

Задача: напишите запрос, который добавляет столбец с позицией каждого сотрудника в табели на основе его зарплаты в своём отделе, где сотрудник с самой высокой зарплатой получает позицию 1. Мы бы ожидали таблицу в таком виде:

depname | empno | salary | salary_rank | -----------+-------+--------+-------------+ develop | 11 | 5200 | 2 | develop | 7 | 4200 | 5 | develop | 9 | 4500 | 4 | develop | 8 | 6000 | 1 | develop | 10 | 5200 | 2 | personnel | 5 | 3500 | 2 | personnel | 2 | 3900 | 1 | sales | 3 | 4800 | 2 | sales | 1 | 5000 | 1 | sales | 4 | 4800 | 2 |

Решение:

SELECT *, RANK() OVER(PARTITION BY depname ORDER BY salary DESC) salary_rank FROM salaries 

Другие задачи средней и высокой сложности

№ 1. Гистограммы

Контекст: Допустим, у нас есть таблица sessions , где каждая строка представляет собой сеанс потоковой передачи видео с длиной в секундах:

| session_id | length_seconds | |------------|----------------| | 1 | 23 | | 2 | 453 | | 3 | 27 | | .. | .. |

Задача: написать запрос, чтобы подсчитать количество сеансов, которые попадают промежутки по пять секунд, т. е. для приведённого выше фрагмента результат будет примерно такой:

| bucket | count | |---------|-------| | 20-25 | 2 | | 450-455 | 1 |

Максимальная оценка засчитывается за надлежащие метки строк («5-10» и т. д.)

Решение:

WITH bin_label AS (SELECT session_id, FLOOR(length_seconds/5) as bin_label FROM sessions ) SELECT CONCATENTATE(STR(bin_label*5), '-', STR(bin_label*5+5)) bucket, COUNT(DISTINCT session_id) count GROUP BY bin_label ORDER BY bin_label ASC 

№ 2. Перекрёстное соединение (несколько частей)

Часть 1

Контекст: допустим, у нас есть таблица state_streams , где в каждой строке указано название штата и общее количество часов потоковой передачи с видеохостинга:

| state | total_streams | |-------|---------------| | NC | 34569 | | SC | 33999 | | CA | 98324 | | MA | 19345 | | .. | .. |

(На самом деле в агрегированных таблицах такого типа обычно есть ещё столбец даты, но для этой задачи мы его исключим)

Задача: написать запрос, чтобы получить пары штатов с общим количеством потоков в пределах тысячи друг от друга. Для приведённого выше фрагмента мы хотели бы увидеть что-то вроде:

| state_a | state_b | |---------|---------| | NC | SC | | SC | NC |

Решение:

SELECT a.state as state_a, b.state as state_b FROM state_streams a CROSS JOIN state_streams b WHERE ABS(a.total_streams - b.total_streams) < 1000 AND a.state <>b.state 

Для информации, перекрёстные соединения также можно писать без явного указания соединения:

SELECT a.state as state_a, b.state as state_b FROM state_streams a, state_streams b WHERE ABS(a.total_streams - b.total_streams) < 1000 AND a.state <>b.state 
Часть 2

Примечание: этот скорее бонусный вопрос, чем реально важный шаблон SQL. Можете его пропустить!

Задача: как можно изменить SQL из предыдущего решения, чтобы удалить дубликаты? Например, на примере той же таблицы, чтобы пара NC и SC появилась только один раз, а не два.

Решение:

SELECT a.state as state_a, b.state as state_b FROM state_streams a, state_streams b WHERE ABS(a.total_streams - b.total_streams) < 1000 AND a.state >b.state 

№ 3. Продвинутые расчёты

Благодарность: эта задача адаптирована из обсуждения по вопросу, который я задал на StackOverflow (мой ник zthomas.nc).

Примечание: вероятно, это более сложная задача, чем вам предложат на реальном собеседовании. Воспринимайте её скорее как головоломку — или можете пропустить её!

Контекст: допустим, у нас есть таблица table такого вида, где одному и тому же пользователю user могут соответствовать разные значения класса class :

| user | class | |------|-------| | 1 | a | | 1 | b | | 1 | b | | 2 | b | | 3 | a |

Задача: предположим, что существует только два возможных значения для класса. Напишите запрос для подсчёта количества пользователей в каждом классе. При этом пользователи с обеими метками a и b должны относиться к классу b .

Для нашего образца получится такой результат:

| class | count | |-------|-------| | a | 1 | | b | 2 |

Решение:

WITH usr_b_sum AS ( SELECT user, SUM(CASE WHEN THEN 1 ELSE 0 END) num_b FROM table GROUP BY user ), usr_class_label AS ( SELECT user, CASE WHEN num_b > 0 THEN 'b' ELSE 'a' END class FROM usr_b_sum ) SELECT class, COUNT(DISTINCT user) count FROM usr_class_label GROUP BY class ORDER BY class ASC

Альтернативное решение использует инструкции SELECT в операторах SELECT и UNION :

SELECT "a" class, COUNT(DISTINCT user_id) - (SELECT COUNT(DISTINCT user_id) FROM table WHERE ) count UNION SELECT "b" class, (SELECT COUNT(DISTINCT user_id) FROM table WHERE ) count 

Получите 300 руб на заказ

за подписку на нашу рассылку

промокод придет вам на email

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *