Руководство по подготовке к собеседованию по SQL

Вопросы по 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, которые может потребоваться решить на собеседовании.

источник