Вопросы для собеседования по SQL для опытных специалистов

Введение

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

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

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

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

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

Минимум концепций для опытных профессионалов

СЛУЧАЙ / КОГДА

 
Тщательное понимание концепции CASE (и сопровождающего ее оператора When) необходимо для достижения полного мастерства в SQL. Оператор case позволяет нам проверять определенные условия и возвращать значение в зависимости от того, оцениваются ли эти условия как истинные или ложные. В сочетании с предложениями, такими как WHERE и ORDER BY, CASE позволяет нам привнести логику, условия и порядок в наши SQL-запросы.

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

Опытные кандидаты должны быть готовы ответить на теоретические вопросы, например объяснить разницу между операторами Valued и Searched CASE, как они работают и как их писать. Это требует глубокого понимания их синтаксиса и общих практик. Излишне говорить, что это также включает в себя правильное использование предложения ELSE.

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

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

Вот пример вопроса, который можно решить с помощью операторов CASE/WHEN: https://platform.stratascratch.com/coding/9634-host-response-rates-with-cleaning-fees?python= 

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

В этом случае оператор CASE/WHEN используется для форматирования результата в виде числа и представления его в виде процентного значения в дополнение к почтовому индексу.

SQL присоединяется

 
Легко быть уверенным в своих знаниях SQL Joins, но чем больше вы исследуете эту тему, тем больше вы обнаружите, что не знаете. Интервьюеры часто спрашивают интервью с вопросами о расширенных аспектах SQL Joins которые часто упускают из виду. Поэтому важно вникнуть в эту концепцию и досконально ее освоить.
 
Помимо основных понятий, интервьюеры могут поинтересоваться, что такое самостоятельные перекрестные соединения, и выяснить глубину ваших знаний, попросив решить практические вопросы. Вы должны знать все различные типы соединений, включая более сложные типы, такие как хеш-соединения или составные соединения. Вас также могут попросить объяснить, что такое естественные соединения и когда они наиболее полезны. Иногда вам придется объяснять различия между естественными и внутренними соединениями.
 
Как правило, вы должны иметь большой опыт и мастерство использования объединений в сочетании с другими операторами для достижения желаемых результатов. Например, вы должны знать, как использовать предложение WHERE для использования перекрестного соединения, как если бы это было внутреннее соединение. Вы также должны знать, как использовать объединения для создания новых таблиц, не оказывая слишком большой нагрузки на сервер. Или как использовать внешние соединения для идентификации и заполнения отсутствующих значений при запросе к базе данных. Или внутреннюю работу внешних соединений, например тот факт, что изменение их порядка может изменить вывод. 

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

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

Усовершенствованная концепция N1: манипулирование датой и временем

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

Поскольку существует множество различных (но допустимых) подходов к форматированию данных в SQL, великие программисты должны быть по крайней мере знакомы со всеми ними. Во время интервью менеджеры по найму ожидают знания основных концепций форматирования данных и умения грамотно рассуждать о выборе правильной функции для задачи. Это включает в себя знание важной функции FORMAT() и связанного с ней синтаксиса для полного использования функции. Также ожидается знание других базовых функций, таких как NOW(). Кроме того, для опытных профессионалов не будет чем-то неожиданным спросить об основных понятиях, таких как данные временных рядов и их назначение.

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

В некоторых случаях работодатели могут спросить о более сложных функциях даты/времени в SQL, таких как CAST(), EXTRACT() или DATE_TRUNC(). Эти функции могут оказаться незаменимыми при работе с большим объемом данных, содержащих даты. Опытный специалист по данным должен знать назначение каждой функции и ее приложений. В идеальном сценарии он или она должны иметь опыт их использования в прошлом.

Наиболее сложные манипуляции с датой и временем в SQL будут включать комбинацию базовых и расширенных функций. Поэтому необходимо знать их все, начиная с более простых FORMAT(), NOW(), CURRENT_DATE и CURRENT_TIME, и включая более сложные функции, упомянутые выше. Как опытный специалист по данным, вы также должны знать, что делает INTERVAL и когда его использовать.

Вот пример вопроса, заданного на собеседовании Airbnb., где кандидаты должны использовать имеющиеся данные для отслеживания роста Airbnb.
 
 

Помещение:

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

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

Решение:

 
Чтобы ответить на этот вопрос, кандидат должен работать с таблицей под названием «airbnb_search_details», которая включает множество столбцов. Столбец, который нам нужен, помечен как host_since, что означает год, месяц и день, когда хост впервые зарегистрировался на веб-сайте. В этом упражнении месяц и день не имеют значения, поэтому первое, что нам нужно сделать, это извлечь год из значения. Затем нам нужно создать представление, включающее отдельные столбцы для текущего года, предыдущего года и общего количества хостов в этом году. 

Выберите извлечение (год ОТ host_since::DATE) ИЗ airbnb_search_details, ГДЕ host_since НЕ НУЛЕВОЕ

На данный момент мы сделали две вещи:

1. Мы позаботились о том, чтобы включить только те строки, в которых столбец host_since не пуст.
2. Мы извлекли год из данных и использовали его как значение DATE.

Выберите извлечение(год ОТ host_since::DATE) count(id) as current_year_host ИЗ airbnb_search_details ГДЕ host_since НЕ НУЛЕВОЕ ГРУППИРОВАТЬ BY экстракт(год ИЗ host_since::DATE) ORDER BY year asc

Затем мы приступаем к подсчету идентификаторов и настраиваем предложение GROUP BY для каждого года. И сделать так, чтобы они отображались в порядке возрастания. 

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

SELECT Year, current_year_host, LAG(current_year_host, 1) OVER (ORDER BY year) as prev_year_host Выберите extract(year FROM host_since::DATE) count(id) as current_year_host FROM airbnb_search_details WHERE host_since НЕ NULL GROUP BY Extract(year FROM host_since: :ДАТА) В УСТРОЙСТВЕ ПО годам по возрастанию

Здесь мы добавили третий столбец, который будет помечен как «prev_year_host», и его значения будут поступать из «current_year_host», за исключением задержки на одну строку. Вот как это может выглядеть:

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

SELECT year, current_year_host, prev_year_host, round(((current_year_host - prev_year_host)/(cast(prev_year_host AS numeric)))*100) предполагаемый_рост ОТ (SELECT year, current_year_host, LAG(current_year_host, 1) OVER (ORDER BY year) AS prev_year_host ОТ (ВЫБЕРИТЕ экстракт(год ОТ host_since::дата) КАК год, количество(идентификатор) текущий_год_хост ОТ airbnb_search_details ГДЕ host_since НЕ НУЛЬ ГРУППА ПО экстракт(год ИЗ host_since::дата) ORDER BY year) t1) t2

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

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

Расширенная концепция N2: оконные функции и разделы

Функции окна SQL — одна из самых важных концепций написания сложных, но эффективных SQL-запросов. Ожидается, что опытные специалисты будут иметь глубокие практические и теоретические знания оконных функций. Это включает в себя знание того, что такое предложение over, и умение его использовать. Интервьюеры могут спросить, как предложение OVER может превратить агрегатные функции в оконные. Вас также могут спросить о трех агрегатных функциях, которые можно использовать в качестве оконных функций. Опытные специалисты по данным должны знать и о других неагрегированных оконных функциях.

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

Кандидаты, которые могут продемонстрировать глубокие знания каждой отдельной оконной функции, такой как ROW_NUMBER(), будут иметь преимущество. Излишне говорить, что одних теоретических знаний недостаточно – профессионалы также должны иметь опыт их использования на практике, с перегородками или без них. Например, опытный профессионал должен уметь объяснить разницу между RANK() и DENSE_RANK(). Идеальный кандидат должен знать некоторые из самых продвинутых понятий, таких как фреймы внутри разделов, и уметь их четко объяснить.

Отличные кандидаты также должны объяснить использование функции NTH_VALUE(). Не мешало бы упомянуть альтернативы этой функции, такие как функции FIRST_VALUE() и LAST_VALUE(). Компании часто любят измерять квартили, квантили и процентили в целом. Чтобы выполнить эту операцию, специалисты по данным должны также знать, как использовать оконную функцию NTILE().

В SQL обычно существует множество способов решения задачи. Тем не менее, оконные функции обеспечивают самый простой способ выполнения обычных, но сложных операций. Хорошим примером такой оконной функции является LAG() или LEAD(), так что вы также должны быть знакомы с ними. Например, давайте рассмотрим пример из предыдущего решения сложного вопроса на собеседовании в Airbnb:

Чтобы отобразить количество хостов за предыдущий год, мы использовали функцию LAG() с оператором OVER. Это можно было сделать многими другими способами, но оконные функции позволили нам получить желаемый результат всего одной строкой SQL-кода:

LAG(current_year_host, 1) OVER (ORDER BY year) как prev_year_host

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

Усовершенствованная концепция N3: рост по месяцам

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

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

Давайте рассмотрим один пример вопроса, который задают интервьюеры на Amazon.

Помещение:

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

Решение:

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

В нашем примере значения данных имеют тип объекта, поэтому нам придется использовать функцию CAST(), чтобы преобразовать их в типы даты.

ВЫБЕРИТЕ to_char (приведение (созданный_в качестве даты), 'ГГГГ-ММ') ИЗ sf_transactions

В вопросе также указывается формат дат, поэтому мы можем использовать функцию TO_CHAR() в SQL для вывода даты в этом формате.

Чтобы рассчитать рост, мы также должны выбрать агрегатную функцию created_at и SUM(), чтобы получить объем общих продаж на эту дату. 

SELECT to_char(cast(created_at as date), 'ГГГГ-ММ'), created_at, sum(value) FROM sf_transactions

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

ВЫБЕРИТЕ to_char(cast(created_at as date), 'ГГГГ-ММ') AS year_month, created_at, sum(value) lag(sum(value), 1) OVER (ORDER BY created_at::date) FROM sf_transactions GROUP BY created_at

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

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

Мы также должны удалить ненужный столбец created_at и изменить предложения GROUP BY и ORDER BY на year_month.

ВЫБЕРИТЕ to_char (приведение (созданный_в качестве даты), 'ГГГГ-ММ') КАК year_month, сумма (значение), отставание (сумма (значение), 1) НАД (ЗАКАЗАТЬ по to_char (приведение (созданный_в качестве даты)) ИЗ sf_transactions СГРУППИРОВАТЬ ПО год месяц

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

Теперь мы наконец можем прийти к решению. Вот как будет выглядеть окончательный код:

SELECT to_char(created_at::date, 'ГГГГ-ММ') AS year_month, round(((sum(value) - lag(sum(value), 1) OVER w) / (lag(sum(value), 1) OVER w)) * 100, 2) AS profit_diff_pct FROM sf_transactions GROUP BY year_month WINDOW w AS ( ORDER BY to_char(created_at::date, 'YYYY-MM')) ORDER BY year_month ASC

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

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

Усовершенствованная концепция N4: показатели оттока клиентов

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

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

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

Чтобы решить эту проблему, специалисты по данным должны использовать операторы case/when, оконные функции, такие как LAG(), а также FROM/WHERE и другие базовые предложения. 

Заключение

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

 
 
Нейт Росиди специалист по данным и продуктовой стратегии. Он также является адъюнкт-профессором, преподающим аналитику, и является основателем СтратаСкретч, платформа, помогающая специалистам по обработке данных подготовиться к интервью с реальными вопросами интервью от ведущих компаний. Свяжись с ним в Твиттер: StrataScratch or LinkedIn.

Источник: https://www.kdnuggets.com/2022/01/sql-interview-questions-experienced-professionals.html