Вопросы по SQL — одна из ключевых тем собеседования на должность аналитика данных или продукции, а также бизнес-аналитика. Крупные технологические компании, в том числе иностранные, такие как Amazon, Uber и Facebook, особенно тщательно проверяют знания претендента в этой области.
При подготовке к такому собеседованию могут возникнуть трудности с детальной проработкой всех возможных вариантов вопросов по SQL. Чтобы помочь вам справиться с этим, я подготовил краткий гайд на основе собственного опыта — мне довелось побывать как кандидатом на должность, так и работодателем.
Задачи по SQL можно разделить на 4 уровня. В рамках гайда мы рассмотрим каждый из них вместе со стандартными примерами для практики. Если хотите лучше усвоить материал, не переходите сразу же к решению.
Уровень 1. Задачи, основанные на агрегатных функциях
Язык SQL отлично подходит для агрегации. Для этого есть множество функций, например SUM(), AVG(), MAX(), MIN(), COUNT() и т. д. Знание таких функций — это базовый уровень знаний, который ожидается от кандидата.
Рассмотрим следующую таблицу сотрудников. В каждой строке указаны данные о сотруднике — отдел, зарплата, руководитель и т. д.
-- Table: employees
-- | dept_id | employee_id | amount | manager_id |
-- |---------|-------------|--------|------------|
-- | 1 | 1 | 8000 | 3 |
-- | 1 | 2 | 5000 | 3 |
-- | 1 | 3 | 10000 | null |
-- | 2 | 4 | 15000 | null |
-- | 2 | 5 | 16000 | 4 |
-- | 3 | 6 | 8000 | null |На основе этой таблицы напишите SQL-запрос, чтобы найти идентификаторы сотрудников, которые зарабатывают больше других в каждом из отделов.
Лучший способ решить любую задачу — это представить ее в виде пошаговой логики. В данном случае мы определяем наибольшую сумму по каждому отделу. Затем определяем формат вывода, для чего нужен только показатель employee_id.
-- Часть 1: Получаем самую высокую зарплату в каждом отделе
SELECT max(amount) AS salary
From employees
GROUP BY dept_id
-- Часть 2: Получаем желаемый формат вывода employee_id
-- Поскольку employee_id нельзя напрямую использовать в группе путем агрегации, мы можем прибегнуть к подзапросам.
SELECT e1.employee_id
FROM employees e1
WHERE e1.amount IN (
SELECT max(e2.amount) AS amount
From employees as e2
GROUP BY e2.dept_id
HAVING e1.dept_id = e2.dept_id )Уровень 2. Задачи, основанные на операциях JOIN и SET
SQL предоставляет возможность объединять результаты из двух или более таблиц с помощью операций JOIN и SET. К популярным JOIN-операциям относятся inner join, left join, right join и cross join. Наиболее известные SET-операторы — UNION, UNION ALL, EXCEPT, INTERCEPT и др.
Вернемся к таблице сотрудников, представленной выше. Напишите SQL-запрос, чтобы определить сотрудников, которые зарабатывают больше, чем их руководитель.
-- Часть 1: Поставьте рядом зарплату менеджера и зарплату сотрудника с помощью self join
SELECT e1.employee_id
FROM employees as e1
LEFT JOIN employees as e2 ON e1.manager_id = e2.employee_id
-- Часть 2: Отфильтруйте сотрудников, которые зарабатывают больше, чем менеджер
SELECT e1.employee_id
FROM employees as e1
LEFT JOIN employees as e2 ON e1.manager_id = e2.employee_id
AND e1.amount > e2.amountУровень 3. Задачи на основе функции Windows
Функции Windows, также известные как аналитические функции, — это самая удивительная особенность языка SQL. Вот несколько популярных аналитических функций: RANK(), DENSE_RANK(), LEAD(), LAG() и т. д.
Вернемся к первой задаче. Мы использовали подзапрос, чтобы определить сотрудника, получающего самую высокую зарплату. Сделать то же самое можно и с помощью функции Windows. Попробуйте решить эту задачу, не глядя на решение.
-- Часть 1: Проранжируйте идентификаторы сотрудников по наибольшей зарплате для каждого отдела с помощью функции DENSE_RANK()
SELECT employee_id,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY amount desc) rnk
from employees
-- Часть 2: Отфильтруйте строки, в которых rnk = 1
SELECT employee_id
FROM
(SELECT employee_id,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY amount desc) rnk
from employees) a
WHERE rnk = 1Уровень 4. Задачи, основанные на комбинации вышеупомянутых уровней
С первого взгляда некоторые задачи могут показаться сложными. Лучшей стратегией их решения признан пошаговый логический подход. Разбейте задачу на мелкие составляющие.
Практика — путь к совершенству. Чем больше вы решаете подобных задач, тем легче будет разбивать их логически и находить решение.
Рассмотрим следующую таблицу посещаемости. Каждая строка содержит идентификатор сотрудника и дату посещения офиса.
Напишите SQL-запрос, чтобы найти самый длинный ряд событий по каждому из сотрудников. Вывод должен содержать имя сотрудника и его самый длинный ряд событий.
-- Table: attendance
-- | employee_id | attend_dt |
-- |-------------|-------------|
-- | 1 | 2022-01-01 |
-- | 1 | 2022-01-02 |
-- | 1 | 2022-01-05 |
-- | 2 | 2022-01-01 |
-- | 2 | 2022-01-02 |
-- | 2 | 2022-01-04 |
--`| 2 | 2022-01-05 |
-- | 2 | 2022-01-06 |
-- | 3 | 2022-01-02 |
-- | 3 | 2022-01-04 |
-- Table: employees
-- | employee_id | name |
-- |-------------|-------------|
-- | 1 | samuel |
-- | 2 | karthik |
-- | 3 | casey |-- Часть 1: Присвойте идентификатор каждой строке в таблице
select * , row_number() over (partition by employee_id order by attend_dt asc) rn
from attendance
-- часть 2: Найдите показатель day из поля даты и разницу между rn и day
-- Это поможет нам в создании групп непрерывных рядов
select *, day(attend_at) - rn
from
(select *, row_number() over (partition by employee_id order by attend_at asc) rn
from attendance)
-- Теперь наша таблица будет выглядеть следующим образом
-- rn| employee_id | attend_dt |day|group_name(day-rn)|
-- --|-------------|-------------|---|------------------
-- 1 | 1 | 2022-01-01 |1 |0
-- 2 | 1 | 2022-01-02 |2 |0
-- 3 | 1 | 2022-01-05 |5 |2
-- 1 | 2 | 2022-01-01 |1 |0
-- 2 | 2 | 2022-01-02 |2 |0
-- 3 | 2 | 2022-01-04 |4 |1
-- 4 | 2 | 2022-01-05 |5 |1
-- 5 | 2 | 2022-01-06 |6 |1
-- 1 | 3 | 2022-01-02 |2 |1
-- 2 | 3 | 2022-01-04 |4 |2
-- Часть 3 : Находим количество для каждого показателя group_name и каждого сотрудника
select employee_id, group_name, count(*) streak
from
(select *, (day(attend_at) - rn ) group_name
from
(select *, row_number() over (partition by employee_id order by attend_at asc) rn
from attendance) a ) b
group by employee_id, group_name
-- Часть 4 : Находим самый длинный ряд событий
select employee_id, max(streak) longest_streak
from
(select employee_id, group_name, count(*) streak
from
(select *, (day(attend_at) - rn ) group_name
from
(select *, row_number() over (partition by employee_id order by attend_at asc) rn
from attendance) a ) b
group by employee_id, group_name ) c
-- Часть 5 : Упорядочиваем данные в желаемом выходном формате
select e.name, d.longest_streak
from
(select employee_id, max(streak) longest_streak
from
(select employee_id, group_name, count(*) streak
from
(select *, (day(attend_at) - rn ) group_name
from
(select *, row_number() over (partition by employee_id order by attend_at asc) rn
from attendance) a ) b
group by employee_id, group_name ) c ) d
join
(select *
from employees) e on d.employee_id = e.employee_idВот и все! Мы рассмотрели несколько стандартных задач по SQL, которые может потребоваться решить на собеседовании.
Читайте также:
- MongoDB: введение, преимущества и настройка среды
- Как запросить датафрейм Pandas с помощью SQL
- Что мы узнали о трендах найма разработчиков в 2019 году, изучив 112,654 тестов для программистов
Читайте нас в Telegram, VK и Яндекс.Дзен
Перевод статьи Priyanka Meena: SQL Interview Preparation Guide.
