Jak przygotować się do rozmowy kwalifikacyjnej z SQL – przewodnik

Rozmowa kwalifikacyjna dotycząca SQL to jeden z najważniejszych elementów jakiejkolwiek rozmowy kwalifikacyjnej związanej z analityką (danych, produktu, czy biznesową). Główne firmy technologiczne, takie jak Amazon, Uber, czy Facebook biorą pod uwagę właśnie ten element.

Jeśli przygotowujesz się do takiej rozmowy kwalifikacyjnej, przeglądanie i powtarzanie wszystkich możliwych wariantów pytań o SQL może wydawać się niezbyt zachęcające. Aby pomóc Ci w tym procesie, poniżej proponuję przewodnik zawierający przykładowe pytania i kwestie, które powinieneś przeanalizować przed kolejną rozmową kwalifikacyjną dotyczącą SQL. Przygotowałam to na podstawie własnych doświadczeń, znajdując się po dwóch stronach barykady.

Problemy dotyczące SQL można podzielić na 4 poziomy. W ramach tego przewodnika przejdziemy przez każdy z tych poziomów, podając kilka standardowych przykładów, które mogą służyć za ćwiczenia (sugeruję, aby nie sprawdzać wcześniej rozwiązania).

Poziom I:

Problemy oparte na funkcjach agregujących

SQL jest świetny, jeśli chodzi o agregacje. Mamy tutaj wiele funkcji, takich jak SUM(), AVG(), MAX(), MIN(), COUNT() itp. Znajomość funkcji agregujących to podstawowy poziom kompetencji, jakiego oczekuje się od uczestnika rozmowy kwalifikacyjnej.

Weźmy przykładową tabelę employees (pracownicy). Każdy wiersz tej tabeli zawiera szczegółowe informacje o pracowniku, takie jak dział pracy, wynagrodzenie, kierownik itp.

-- 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     |

Na podstawie tej tabeli napisz zapytanie SQL, aby znaleźć identyfikatory pracowników, którzy zarabiają najwięcej w każdym dziale.

Najlepszym sposobem na rozwiązanie każdego problemu jest odwzorowanie go w sposób logiczny krok po kroku. Na przykład zrozumienie, czego dotyczy rozwiązanie – w tym konkretnym przypadku jest to najwyższa kwota dla każdego działu. Następnie należy określić format wyjściowy, w tym przypadku wystarczy nam employee_id.

-- Part 1: Get the highest salary in each department
 SELECT max(amount) AS salary 
 From employees 
 GROUP BY dept_id
 
 -- Part 2: Get the desired output format employee_id
 -- Since employee_id cannot be directly used in the group by aggregation, we might have resort to subqueries.
 
 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 )
 

Poziom II:

Problemy oparte na operacjach JOIN i SET

SQL umożliwia użytkownikom łączenie wyników z dwóch lub więcej tabel za pomocą operacji join i set. Niektóre z tych popularnych złączeń to złączenia wewnętrzne, złączenia zewnętrzne lewostronne, zewnętrzne prawostronne i iloczyn kartezjański. Natomiast najpopularniejsze z operatorów set to UNION, UNION ALL, EXCEPT, INTERCEPT itp.

Weźmy np. wcześniej wspomnianą tabelę employees. Napisz zapytanie SQL, aby znaleźć pracowników, którzy zarabiają więcej niż ich kierownik.

-- part1 : Bring manager salary along side employee salary using self join

SELECT e1.employee_id
FROM employees as e1
LEFT JOIN employees as e2 ON e1.manager_id = e2.employee_id

-- part2 : Filter employees who earn more than manager salary

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

Poziom III:

Problemy oparte na funkcjach systemu Windows

Funkcje Windows, znane również jako funkcje analityczne, to najbardziej niesamowita rzecz, jaką oferuje SQL. Niektóre z popularnych funkcji analitycznych to RANK(), DENSE_RANK(), LEAD(), LAG() itd.

Wróćmy do naszego pierwszego problemu. Użyliśmy podzapytania, aby znaleźć pracownika, który otrzymuje najwyższe wynagrodzenie. Można to również zrobić za pomocą funkcji Windows. Spróbuj to wykonać, nie patrząc na rozwiązanie.

-- Part 1: Rank the employee_ids by highest salary for each department using DENSE_RANK()

SELECT employee_id, 
       DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY amount desc) rnk
from employees

-- Part 2: Filter the rows where 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

Poziom IV

Problemy wynikające z połączenia wyżej wymienionych poziomów

Może się zdarzyć, że napotkamy problemy, które w pierwszym rzucie mogą wydawać się nieco trudne. Najlepszą strategią rozwiązywania takich problemów jest podejście logiczne, czyli analiza krok po kroku. Rozbij problem na mniejsze problemy. Ktoś kiedyś słusznie powiedział, że praktyka czyni mistrza. Im więcej takich problemów rozwiążesz, tym lepiej poradzisz sobie z ich logicznym analizowaniem i rozwiązywaniem. Wypróbuj kilka z nich na leetcode.

Spójrzmy na następującą tabelę attendance (frekwencja). Każdy wiersz w tabeli zawiera employee_id (identyfikator pracownika) oraz datę jego wizyty w biurze.

Napisz zapytanie SQL, aby znaleźć pracowników z najdłuższym czasem przebywania w biurze. Output powinien zawierać imię i nazwisko pracownika oraz dane o najdłuższym czasie przebywania w biurze.

-- 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      |

-- part 1: Give id to each row in the table 
select * , row_number() over (partition by employee_id order by attend_dt asc) rn
from attendance

-- part 2: Find the day from the date field and find the difference between rn and day
-- This will help us in create groups of continous streaks

select *, day(attend_at) - rn
from
(select *, row_number() over (partition by employee_id order by attend_at asc) rn
from attendance)

-- Our table will look something like this now
-- 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

-- part 3 : Find the count for each group_name and each employee

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

-- part 4 : Finding the longest streak

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

-- part 5 : Arranging the data in the desired output format

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

To już jest koniec!

W tym artykule omówiliśmy kilka standardowych problemów dotyczących SQL, z którymi możesz się zetknąć podczas następnej technicznej rozmowy kwalifikacyjnej. Wspomniane tutaj problemy oparte są na moich osobistych doświadczeniach. Daj znać w komentarzach, z jakimi jeszcze problemami się wcześniej spotkałeś. Jestem pewna, że przyda się to innym osobom.

Oryginał tekstu w języku angielskim przeczytasz tutaj.