Crack Data Science Интервью
4 сложных вопроса по SQL для специалистов по данным в 2021 году
Простые запросы SQL, которые могут вас сбить с толку
Это родственная глава книги Основные навыки работы с SQL для специалистов по данным в 2021 году (ссылка). Пожалуйста, посмотрите, если у вас нет такой возможности.
Написание эффективных запросов пугает и обманчиво. Даже опытные пользователи SQL могут либо чрезмерно задумываться о решении с вложенными подзапросами внутри вложенных подзапросов, выполнение которых занимает вечность; или они забывают учитывать аномалии / выбросы, что приводит к неверным результатам запроса.
Меня пугает:
Даже с неправильными SQL-запросами мы получим что-то на выходе незаметно!
В корпорациях, управляемых данными, руководители предприятий принимают обоснованные решения на основе картины данных, составленной специалистами по данным / аналитиками. Экономические издержки могут быть катастрофическими, если специалисты по обработке данных / аналитики не смогут правильно выполнять запросы.
С этой целью я представляю 4 задачи SQL, которые раньше меня сбивали с толку.
TL;DR
- Как бороться с дубликатами? Заранее обсудите дубликаты
- Условный выбор на основе нескольких переменных: включите их все в предложения WHERE.
- Два шага к достижению «Нет продаж в 2020 году»: 1. Включите кейсы с продажами в 2020 году; 2. Исключить эти случаи
- Как обрабатывать нулевые значения? IFNULL (). Вы знаете OFFSET?
№1. Занятия более 5 студентов @ LeetCode
Есть таблица courses со столбцами: студент и класс. Перечислите все классы, в которых обучается не менее 5 человек. Например, таблица:
+---------+------------+ | student | class | +---------+------------+ | A | Math | | B | English | | C | Math | | D | Biology | | E | Math | | F | Computer | | G | Math | | H | Math | | I | Math | +---------+------------+
Должен выводить:
+---------+ | class | +---------+ | Math | +---------+
Примечание.
Ученики не должны считаться дублирующимися в каждом курсе.
Прогулка по моим мыслям
Даже для абсолютных новичков это простой вопрос: основная идея состоит в том, чтобы отфильтровать классы с 5 или более учениками и сделать что-то вроде этого:
Неверный код
SELECT class FROM courses GROUP BY class HAVING COUNT(student)>=5
Вы столкнетесь с сообщением об ошибке, если отправите вышеуказанный код в LeetCode.
Сложность заключается в том, что могут быть дубликаты, а последняя строка кода, оператор HAVING COUNT (), не учитывает такой сценарий. Вместо этого мы должны использовать метод DISTINCT внутри блока для указания вывода.
Фактически, в вопросе есть дополнительное примечание, в котором указано, что делать с дубликатами.
Совет: задавайте уточняющие вопросы, если вы проводите собеседование по SQL для сотрудников DS. Беспрепятственное общение на любом собеседовании имеет большое значение.
Решение
# Write your MySQL query statement below SELECT class FROM courses GROUP BY class HAVING COUNT(DISTINCT student)>=5
№2. Анализ игрового процесса II @ LeetCode
Таблица 2_
+--------------+---------+ | Column Name | Type | +--------------+---------+ | player_id | int | | device_id | int | | event_date | date | | games_played | int | +--------------+---------+ (player_id, event_date) is the primary key of this table. This table shows the activity of players of some game. Each row is a record of a player who logged in and played a number of games (possibly 0) before logging out on some day using some device.
Напишите SQL-запрос, который сообщает об устройстве, которое первым вошел в систему для каждого игрока.
Формат результата запроса представлен в следующем примере:
Activity table: +-----------+-----------+------------+--------------+ | player_id | device_id | event_date | games_played | +-----------+-----------+------------+--------------+ | 1 | 2 | 2016-03-01 | 5 | | 1 | 2 | 2016-05-02 | 6 | | 2 | 3 | 2017-06-25 | 1 | | 3 | 1 | 2016-03-02 | 0 | | 3 | 4 | 2018-07-03 | 5 | +-----------+-----------+------------+--------------+ Result table: +-----------+-----------+ | player_id | device_id | +-----------+-----------+ | 1 | 2 | | 2 | 3 | | 3 | 1 | +-----------+-----------+
Прогулка по моим мыслям
Боже мой, этот вопрос кажется таким простым, но обманчиво сложным.
Вот почему.
Таблица результатов содержит два столбца: player_id и device_id. Мы можем использовать предложение WITH (), чтобы отфильтровать дату первого входа в систему, которую можно получить с помощью команды MIN () для каждого игрока, а затем написать внешний запрос, чтобы найти эти два столбца.
В коде мы можем сделать следующее:
Неверный код:
WITH first_log AS ( SELECT player_id, device_id, MIN(event_date) FROM Activity GROUP BY player_id ) SELECT player_id, device_id FROM first_log
Опять же, правильный результат при запуске, но сообщение об ошибке при отправке. Попробуйте здесь: LeetCode.
Но почему?
Прежде чем читать дальше, я предлагаю сделать небольшой перерыв и взглянуть на код:
почему не работает?
Ответ таков: у каждого игрока своя собственная дата первого входа в систему, и мы должны фильтровать случаи, используя эти два столбца вместе: player_id и event_date. Другими словами, у каждого игрока своя уникальная минимальная дата.
Решение
# Write your MySQL query statement below
SELECT player_id, device_id
FROM Activity
WHERE (player_id, event_date) IN (
select player_id, MIN(event_date)
from Activity
group by player_id
)
Примечание: не забудьте поставить скобки.
№3. Продавцы без продаж @ LeetCode
Таблица: Customer
+---------------+---------+ | Column Name | Type | +---------------+---------+ | customer_id | int | | customer_name | varchar | +---------------+---------+ customer_id is the primary key for this table. Each row of this table contains the information of each customer in the WebStore.
Таблица: Orders
+---------------+---------+ | Column Name | Type | +---------------+---------+ | order_id | int | | sale_date | date | | order_cost | int | | customer_id | int | | seller_id | int | +---------------+---------+ order_id is the primary key for this table. Each row of this table contains all orders made in the webstore. sale_date is the date when the transaction was made between the customer (customer_id) and the seller (seller_id).
Таблица: Seller
+---------------+---------+ | Column Name | Type | +---------------+---------+ | seller_id | int | | seller_name | varchar | +---------------+---------+ seller_id is the primary key for this table. Each row of this table contains the information of each seller.
Напишите запрос SQL, чтобы сообщить имена всех продавцов, которые не реализовали никаких продаж в 2020 году. Верните таблицу результатов, отсортированную по seller_name в порядке возрастания. Формат результата запроса показан в следующем примере.
Customertable: +--------------+---------------+ | customer_id | customer_name | +--------------+---------------+ | 101 | Alice | | 102 | Bob | | 103 | Charlie | +--------------+---------------+Orderstable: +-------------+------------+--------------+-------------+-------------+ | order_id | sale_date | order_cost | customer_id | seller_id | +-------------+------------+--------------+-------------+-------------+ | 1 | 2020-03-01 | 1500 | 101 | 1 | | 2 | 2020-05-25 | 2400 | 102 | 2 | | 3 | 2019-05-25 | 800 | 101 | 3 | | 4 | 2020-09-13 | 1000 | 103 | 2 | | 5 | 2019-02-11 | 700 | 101 | 2 | +-------------+------------+--------------+-------------+-------------+Sellertable: +-------------+-------------+ | seller_id | seller_name | +-------------+-------------+ | 1 | Daniel | | 2 | Elizabeth | | 3 | Frank | +-------------+-------------+ Result table: +-------------+ |seller_name| +-------------+ | Frank | +-------------+ Daniel made 1 sale in March 2020. Elizabeth made 2 sales in 2020 and 1 sale in 2019. Frank made 1 sale in 2019 but no sales in 2020.
Прогулка по моим мыслям
Во-первых, это довольно простой вопрос. На этот раз я серьезно. Что меня отвергает и оправдывает его включение здесь, так это его длинное описание.
Что делать с этими тремя таблицами?
С чего мне начать?
Слишком много информации, и слишком много информации не лучше, чем ее отсутствие.
Моя стратегия выживания - разбить вопрос на части и задать два больших вопроса:
О чем спрашивается?
Какая информация доступна на данный момент?
Первым делом нужно взглянуть на приведенную выше таблицу результатов, если она есть, а если нет, спросить интервьюера, что будет, если все пойдет так, как ожидалось. Опять же, хорошее общение - король!
Затем давайте проверим, какая информация доступна. Что касается этого запроса, таблица результатов содержит только один столбец (seller_name) и отображается только в таблице заказов, которая также содержит информацию sale_date и order_id. Любой из этих двух дополнительных переменных будет достаточно для определения года каждого заказа.
Теперь мы хорошо разобрались в вопросе и собрали всю необходимую информацию. Нет необходимости проверять первую таблицу (Клиент).
Последний шаг - написать последовательность запросов.
Вопрос касается продавцов, у которых нет продаж в 2020 году. Если мы будем использовать предложение WHERE для фильтрации строк для продаж не в 2020 году, то мы исключим случаи, когда продажи будут как в 2020 году, так и в последующий период, как в случае с Элизабет.
Следующий код не работает:
Неверный код
SELECT seller_name FROM Seller JOIN Orders USING(seller_id) WHERE LEFT(sale_date,4) != 2020
Он выводит два случая: Фрэнк (нет продаж в 2020 году) и Элизабет (продажи в 2020 и 2019 годах).
Для вопроса «без продаж в 2020 году» мы можем узнать имена продавцов, у которых есть продажи в 2020 году, в качестве первого шага, а затем использовать внешний запрос, чтобы отфильтровать их на следующем шаге.
Решение
SELECT seller_name FROM Seller WHERE seller_id NOT IN ( SELECT DISTINCT seller_id FROM Orders WHERE LEFT(sale_date, 4) = ‘2020’ ) ORDER BY seller_name
№4. Вторая по величине зарплата @ LeetCode
Напишите SQL-запрос, чтобы получить вторую по величине зарплату из таблицы Employee.
+----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+
Например, учитывая приведенную выше таблицу сотрудников, запрос должен вернуть 200 в качестве второй по величине заработной платы. Если второй по величине заработной платы нет, запрос должен вернуть null.
+---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+
Прогулка по моим мыслям
Собственно, это простой вопрос. Причина его включения в том, что я хочу представить два метода: IFNULL () и OFFSET.
Вопрос запрашивает вторую по величине зарплату, что напоминает нам об использовании метода OFFSET для пропуска первой строки. Кроме того, он возвращает null, если такого значения нет. При этом мы можем использовать функцию IFNULL ().
В качестве примечания, функция имеет следующую форму: IFNULL (оператор, NULL). Если нулевого значения нет, запустите инструкцию как обычно; если есть значение NULL, вернуть NULL или любое другое указанное значение.
Решение
SELECT IFNULL( (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1 OFFSET 1), null) AS SecondHighestSalary
Это вторая часть двойного сообщения по SQL, обратите внимание на первое сообщение:
Последние мысли
Существует неправильное представление о запросах SQL: писать запутанные, отмеченные наградами запросы предпочтительнее простых решений.
Это совсем не так!
Чаще всего специалистов по данным / аналитиков просят написать разумные SQL-запросы, но с хитрыми компонентами, которые могут вас сбить с толку. В этом посте я перечислил 4 типа сценариев, которые могут сбить вас с толку при выполнении ваших запросов.
Спасибо, что дочитали до этого места! Хотите поделиться своим опытом работы с SQL? Какие типы запросов самые сложные и сложные для вас?
Удачного обучения! Удачного запроса!
Medium недавно разработал свою Партнерскую программу писателей, которая поддерживает обычных писателей, таких как я. Если вы еще не являетесь подписчиком и зарегистрируетесь по следующей ссылке, я получу часть членских взносов.
Нравится читать это?
Также ознакомьтесь с другими моими сообщениями об искусственном интеллекте и машинном обучении.
