Запитання на інтерв’ю SQL для досвідчених професіоналів

Вступ

 
Якщо ви досвідчений науковець з даних, який шукає роботу, ви не могли вибрати кращого часу. На даний момент багато відомих організацій шукають науковців з даних, які знають свою справу всередині та зовні. Однак високий попит не означає, що ви можете або повинні стрибати через обруч і претендувати на керівні посади без певного набору навичок. Наймаючи досвідчених спеціалістів з обробки даних, компанії очікують, що вони працюватимуть над найскладнішими завданнями. Ці співробітники повинні добре розуміти навіть найнезрозуміліші функції, щоб вони могли використовувати їх у разі потреби.

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

SQL є основною мовою для керування базами даних, тому виконання операцій SQL є основою роботи дослідників даних. Більшість інтерв’ю вчених з даних організовуються, щоб визначити знання кандидата про SQL. 

Повсякденна робота може не включати написання складних запитів, але ви повинні показати, що якщо такі навички потрібні, ви та людина, яка здатна це зробити. Тому не дивно, що інтерв’юери запитують різноманітний набір Запитання на інтерв'ю SQL щоб перевірити вільне володіння кандидатом SQL.

У цій статті ми хотіли узагальнити деякі складні питання та концепції, які задавалися під час інтерв’ю з досвідченими професіоналами. Навіть якщо ви впевнені у своїх знаннях SQL, не завадить відсканувати ключові слова та переконатися, що ви все охопили.

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

ВИПАДОК / КОЛИ

 
Досконале розуміння концепції CASE (і супровідного оператора When) є важливим для досягнення повного володіння SQL. Оператор case дозволяє нам перевірити певні умови та повернути значення на основі того, чи оцінюються ці умови як істинні чи хибні. У поєднанні з реченнями, такими як WHERE і ORDER BY, CASE дозволяє нам вносити логіку, умови та порядок у наші запити SQL.

Значення операторів CASE не обмежується наданням простої умовної логіки в наших запитах. Досвідчені науковці з даних повинні мати більше, ніж поверхневе розуміння оператора CASE та його використання. Інтерв’юери, ймовірно, зададуть вам запитання про різні типи виразів CASE та про те, як їх писати. 

Досвідчені кандидати повинні бути готові відповідати на теоретичні запитання, наприклад, пояснювати відмінності між операторами 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», що позначає рік, місяць і день, коли хост вперше зареєструвався на веб-сайті. Для цієї вправи місяць і день не мають значення, тому перше, що нам потрібно зробити, це витягти рік із значення. Потім нам доведеться створити представлення даних, яке включає окремі стовпці для поточного року, попереднього року та загальної кількості хостів у цьому році. 

Виберіть       витяг (рік FROM host_since::DATE) FROM airbnb_search_details WHERE host_since IS NOT NULL

Наразі ми зробили дві речі:

1. Ми переконалися, що включили лише ті рядки, у яких стовпець host_since не порожній.
2. Ми витягли рік із даних і перетворили його як значення DATE.

Виберіть       екстракт (рік FROM host_since::DATE)       count(id) як поточний_рік_хост FROM airbnb_search_details WHERE host_since НЕ NULL BY extract(year FROM host_since::DATE) ORDER BY year asc

Потім ми приступаємо до підрахунку ідентифікаторів і встановлюємо речення GROUP BY для кожного року. І змусьте його відображатися в порядку зростання. 

Це має дати нам таблицю з двома стовпцями: рік і кількість хостів, зареєстрованих у цьому році. Ми все ще не маємо повної картини, необхідної для вирішення питання, але це крок у правильному напрямку. Нам також потрібні окремі стовпці для хостів, зареєстрованих протягом попереднього року. Тут на допомогу приходить функція LAG().

ВИБРАТИ       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 IS NOT NU LL GROUP BY витяг (рік FROM host_since: :ДАТА) ПОРЯДОК ЗА роком зростання

Тут ми додали третій стовпець, який буде позначено як "prev_year_host", і його значення будуть надходити з "current_year_host", за винятком затримки в одному рядку. Ось як це може виглядати:

Розташування таблиці таким чином робить дуже зручним підрахунок кінцевої швидкості зростання. У нас є окремий стовпець для кожного значення рівняння. Зрештою, наш код має виглядати приблизно так:

ВИБРАТИ рік,       поточний_рік_господар,       попередній_рік_господар,       round(((current_year_host - prev_year_host)/(cast(prev_year_host AS numeric)))*100) розрахункове_зростання FROM   (ВИБРАТИ рік,           поточний_рік_хост,           LAG(поточний _year_host, 1) ПОВІД (ПОРЯДОК ЗА роком) ЯК prev_year_host FROM     (SELECT extract(year                     FROM host_since::date) AS year,             count(id) current_year_host       FROM airbnb_search_details       WHERE host_since IS NOT NULL       GROUP BY extract(year                      FROM host_since::date)       ПОРЯДОК ЗА роком) 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) як попередній_year_host

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

Розширена концепція N3: зростання з місяця в місяць

Багато організацій використовують аналіз даних для вимірювання власної ефективності. Це може означати вимірювання ефективності маркетингових кампаній або рентабельності інвестицій на конкретні інвестиції. Виконання такого аналізу вимагає глибоких знань SQL, наприклад дати, часу та віконних функцій.

Дослідникам даних також доведеться довести свої навички форматування даних і відображення їх у відсотках або в будь-якій іншій формі. Загалом, щоб розв’язати практичні питання, де потрібно обчислити зростання за місяць, потрібно використовувати комбінацію кількох наборів навичок. Деякі з необхідних концепцій будуть розширеними (віконні функції, маніпуляції з датою і часом), а інші будуть базовими (агрегатні функції та загальні оператори SQL).

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

Передумова:

 
У цьому питанні ми повинні працювати з таблицею покупок і розраховувати щомісячне зростання або зниження виручки. Кінцевий результат має бути відформатований у певний спосіб (формат РРРР-ММ), а відсотки мають бути округлені до другої найближчої десяткової коми. 

Рішення:

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

У нашому прикладі значення даних мають тип об’єкта, тому нам доведеться використовувати функцію CAST(), щоб перетворити їх у типи дат.

ВИБРАТИ   to_char(cast(created_at as date), 'YYYY-MM') FROM sf_transactions

Питання також визначає формат для дат, тому ми можемо використовувати функцію TO_CHAR() у SQL для виведення дати в цьому форматі.

Щоб розрахувати зростання, ми також повинні вибрати агрегатні функції created_at і SUM(), щоб отримати обсяг загальних продажів на цю дату. 

ВИБРАТИ   to_char(cast(created_at as date), 'YYYY-MM'),   created_at,   sum(value) FROM sf_transactions

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

SELECT to_char(cast(created_at as date), 'YYYY-MM') 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(cast(created_at as date), 'YYYY-MM') AS year_month,   sum(value),   lag(sum(value), 1) OVER (ORDER BY to_char(cast(created_at as date)) FROM sf_transactions GROUP BY рік_місяць

Після того, як ми запустимо код, наша таблиця повинна включати лише стовпці, які є важливими для нашого обчислення.

Тепер ми нарешті можемо прийти до рішення. Ось як буде виглядати остаточний код:

SELECT to_char(created_at::date, 'YYYY-MM') AS year_month,       round(((sum(value) - lag(sum(value), 1) OVER w) / (lag(sum(value), 1) OVER w)) * 100, 2) AS earned_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 щоб оновити свої знання.

 
 
Нейт Розіді є фахівцем із даних та стратегією продукту. Він також є ад’юнкт-професором, який викладає аналітику, і є засновником StrataScratch, платформа, яка допомагає науковцям з даних готуватися до інтерв’ю з реальними запитаннями для інтерв’ю від провідних компаній. Зв'яжіться з ним Twitter: StrataScratch or LinkedIn.

Джерело: https://www.kdnuggets.com/2022/01/sql-interview-questions-experienced-professionals.html