Întrebări de interviu SQL pentru profesioniști cu experiență

Introducere

 
Dacă sunteți un cercetător de date cu experiență în căutarea unui loc de muncă, nu ați fi putut alege un moment mai bun. În prezent, multe organizații consacrate caută oameni de știință ai datelor care își cunosc meseria în interior și în exterior. Cu toate acestea, cererea mare nu înseamnă că poți sau ar trebui să treci prin cercuri și să aplici pentru poziții de conducere fără un anumit set de abilități. Atunci când angajează oameni de știință în date cu experiență, companiile se așteaptă ca aceștia să lucreze la cele mai dificile sarcini la îndemână. Acești angajați ar trebui să înțeleagă bine chiar și cele mai obscure caracteristici, astfel încât să le poată folosi atunci când este necesar.

Nu ar trebui să fie surprinzător faptul că, atunci când intervievează pentru funcții de conducere, oamenii de știință de date cu experiență tind să li se pună întrebări mult mai dificile. Adesea, atunci când lucrează la un singur loc de muncă timp de câțiva ani, oamenii de știință în date devin foarte pricepuți în a îndeplini anumite sarcini repetitive. Este esențial ca profesioniștii să realizeze că SQL nu se oprește și nu se termină cu cunoștințele lor existente. Când vine vorba de concepte SQL avansate, este posibil să existe încă unele lacune în cunoștințele lor. Așa că nu strica să obții ajutor pentru a reuși la interviul cu cercetătorul de date. 

SQL este limba principală pentru gestionarea bazelor de date, astfel încât efectuarea operațiunilor SQL este în centrul locurilor de muncă ale cercetătorilor de date. Majoritatea interviurilor cu cercetătorii de date sunt organizate pentru a determina cunoștințele candidatului despre SQL. 

S-ar putea ca munca de zi cu zi să nu includă scrierea de interogări complexe, dar trebuie să arăți că, dacă sunt necesare astfel de abilități, ești persoana capabilă să o facă. Așa că nu ar trebui să fie surprinzător că intervievatorii solicită un set divers de Întrebări de interviu SQL pentru a testa fluența candidatului în SQL.

În acest articol, am dorit să rezumăm câteva dintre întrebările și conceptele complexe puse în interviurile cu profesioniști cu experiență. Chiar dacă aveți încredere în cunoștințele dvs. SQL, nu strica să scanați cuvintele cheie și să vă asigurați că aveți totul acoperit.

Concepte minime pentru profesioniști cu experiență

CAZ / CÂND

 
Înțelegerea temeinică a conceptului CASE (și a instrucțiunii When care îl însoțește) este esențială pentru a obține o stăpânire completă a SQL. O declarație case ne permite să verificăm anumite condiții și să returnăm o valoare în funcție de faptul dacă acele condiții sunt evaluate ca adevărate sau false. În combinație cu clauze, cum ar fi WHERE și ORDER BY, CASE ne permite să aducem logică, condiții și ordine în interogările noastre SQL.

Valoarea instrucțiunilor CASE nu se limitează la furnizarea unei simplă logică condiționată în interogările noastre. Oamenii de date cu experiență ar trebui să aibă mai mult decât o înțelegere la nivel de suprafață a declarației CASE și a utilizărilor sale. Este posibil ca intervievatorii să vă pună întrebări despre diferite tipuri de expresii CASE și despre cum să le scrieți. 

Candidații cu experiență ar trebui să fie pregătiți să răspundă la întrebări teoretice, cum ar fi să explice diferențele dintre declarațiile CASE evaluate și căutate, cum funcționează și cum să le scrie. Acest lucru necesită o înțelegere puternică a sintaxei lor și a practicilor comune. Inutil să spun că aceasta include și utilizarea corectă a clauzei ELSE.

Se așteaptă ca oamenii de știință de date cu experiență să știe cum să folosească CASE și cu funcții agregate. De asemenea, s-ar putea să vi se solicite să scrieți o instrucțiune CASE, care este mai puțin repetitivă și mai ușor de înțeles. Ar trebui să puteți vorbi în mod inteligent despre avertismente și posibilele riscuri ale utilizării instrucțiunilor CASE scurte.

În general, un specialist în date cu experiență trebuie să fie capabil să folosească CASE pentru a scrie interogări mai eficiente. La urma urmei, întregul scop al declarației CASE este de a evita scrierea prea multor interogări individuale pentru a consolida datele.

Iată un exemplu de întrebare care poate fi rezolvată folosind instrucțiunile CASE / WHEN: https://platform.stratascratch.com/coding/9634-host-response-rates-with-cleaning-fees?python= 

Aceasta este o întrebare dificilă pusă în interviurile Airbnb, unde candidații trebuie să găsească rata medie de răspuns a gazdei, codul poștal și taxa de curățenie corespunzătoare.

În acest caz, instrucțiunea CASE/ WHEN este folosită pentru a formata rezultatul ca număr și a-l prezenta ca valoare procentuală, în plus față de codul poștal.

SQL se alătură

 
Este ușor să te simți încrezător în cunoștințele tale despre SQL Joins, dar cu cât explorezi mai mult acest subiect, cu atât mai mult vei descoperi că nu știi. Intervievatorii întreabă adesea întrebări de interviu despre aspectele avansate ale SQL Joins care sunt adesea trecute cu vederea. Așa că este important să aprofundăm acest concept și să-l stăpânești temeinic.
 
Pe lângă conceptele de bază, intervievatorii s-ar putea întreba ce sunt auto-uniuni încrucișate și pot afla profunzimea cunoștințelor tale solicitând să rezolve întrebări practice. Ar trebui să cunoașteți toate tipurile diferite de îmbinări, inclusiv tipurile mai complexe, cum ar fi îmbinările hash sau îmbinările compozite. De asemenea, vi se poate cere să explicați ce sunt îmbinările naturale și când sunt cele mai utile. Uneori va trebui să explicați diferențele dintre îmbinările naturale și cele interioare.
 
Ar trebui, în general, să aveți o experiență aprofundată și o stăpânire în utilizarea îmbinărilor în combinație cu alte afirmații pentru a obține rezultatele dorite. De exemplu, ar trebui să știți cum să utilizați clauza WHERE pentru a utiliza Cross Join ca și cum ar fi o Inner Join. De asemenea, veți fi de așteptat să știți cum să utilizați îmbinările pentru a produce tabele noi fără a pune prea multă presiune pe server. Sau cum să utilizați îmbinările exterioare pentru a identifica și completa valorile lipsă atunci când interogați baza de date. Sau funcționarea interioară a îmbinărilor exterioare, cum ar fi faptul că rearanjarea ordinii lor poate modifica rezultatul. 

Iată un exemplu de întrebare care implică scrierea unei declarații interioare comune. 

Aceasta este o întrebare destul de dificilă, în care candidaților li se cere să afișeze dimensiunea comenzii ca procent din cheltuielile totale.
 
 

Concept avansat N1: Manipulare dată-oră

Este obișnuit ca bazele de date să includă date și ore, astfel încât orice om de date cu experiență ar trebui să aibă cunoștințe profunde despre lucrul cu acestea. Acest tip de date ne permite să urmărim ordinea în care apar evenimentele, schimbările de frecvență, să calculăm intervalele și să obținem alte informații importante. De multe ori efectuarea acestor operațiuni necesită o stăpânire completă a manipulării date-ora în SQL. Deci, profesioniștii cu un astfel de set de abilități vor avea un avantaj față de candidații concurenți. Dacă nu aveți încredere 100% în abilitățile dvs., priviți conceptele descrise mai jos și vedeți câte dintre ele vă sună familiar.

Deoarece există multe abordări diferite (dar valide) pentru formatarea datelor în SQL, cei mai buni programatori ar trebui să fie cel puțin familiarizați cu toate. În timpul interviurilor, managerii de angajare așteaptă cunoștințe despre conceptele de bază de formatare a datelor și capacitatea de a vorbi inteligent despre alegerea funcției potrivite pentru sarcină. Aceasta include cunoașterea unei funcții importante FORMAT() și a sintaxei asociate pentru a utiliza pe deplin această funcție. De asemenea, este de așteptat cunoașterea altor funcții de bază, cum ar fi NOW(). De asemenea, nu ar fi din senin ca profesioniștii cu experiență să fie întrebați despre concepte de bază, cum ar fi datele din seria temporală și scopul acestora.

De asemenea, este important să luați în considerare contextul jobului pentru care aplicați. O companie AI sau IoT ar fi mai preocupată de urmărirea datelor colectate de la senzori, în timp ce o aplicație de tranzacționare cu acțiuni ar putea solicita să urmăriți fluctuațiile prețului în timpul zilei, săptămânii sau lunii.

În unele cazuri, angajatorii ar putea întreba despre funcții de dată/ora mai avansate în SQL, cum ar fi CAST(), EXTRACT() sau DATE_TRUNC(). Aceste funcții pot fi de neprețuit atunci când lucrați cu un volum mare de date care conține date. Un cercetător de date cu experiență ar trebui să cunoască scopul fiecărei funcții și aplicațiile acesteia. Într-un scenariu ideal, el sau ea ar trebui să aibă experiență în utilizarea lor în trecut.

Cea mai complexă manipulare dată-oră din SQL va implica combinarea funcțiilor de bază și avansate. Așa că este necesar să le cunoaștem pe toate, pornind de la FORMAT(), NOW(), CURRENT_DATE și CURRENT_TIME, inclusiv funcțiile mai avansate menționate mai sus. În calitate de cercetător de date cu experiență, ar trebui să știți și ce face INTERVAL și când să îl utilizați.

Iată un exemplu de întrebare pusă în interviurile Airbnb, unde candidații trebuie să folosească datele disponibile pentru a urmări creșterea Airbnb.
 
 

Premisa:

 
În această întrebare, candidaților li se cere să urmărească creșterea Airbnb pe baza modificărilor numărului de gazde înscrise în fiecare an. Cu alte cuvinte, vom folosi numărul de gazde nou înregistrate ca indicator al creșterii pentru fiecare an. Vom găsi rata de creștere calculând diferența dintre numărul de gazde dintre ultimul și anul curent și împărțind acel număr la numărul de gazde înregistrate în anul precedent. Apoi vom găsi valoarea procentuală înmulțind rezultatul cu 100.

Tabelul de ieșire ar trebui să aibă coloane și datele corespunzătoare pentru numărul de gazde în anul curent, în anul precedent și procentul de creștere de la an la an. Procentul trebuie rotunjit la cel mai apropiat număr întreg, iar rândurile trebuie ordonate crescător în funcție de an.
 
 

Soluţie:

 
Pentru a răspunde la această întrebare, un candidat trebuie să lucreze cu tabelul numit „airbnb_search_details”, care include multe coloane. Coloana de care avem nevoie este etichetată „host_since”, care indică anul, luna și ziua în care gazda s-a înscris pentru prima dată pe site. Pentru acest exercițiu, luna și ziua sunt irelevante, așa că primul lucru pe care va trebui să-l facem este să extragem anul din valoare. Apoi, va trebui să creăm o vizualizare care să includă coloane separate pentru anul curent, anul precedent și numărul total de gazde din acel an. 

Select       extract(year FROM host_since::DATE) FROM airbnb_search_details WHERE host_since IS NOT NULL

Până acum, am făcut două lucruri:

1. Ne-am asigurat că includem numai rândurile în care coloana host_since nu este goală.
2. Am extras anul din date și l-am prezentat ca valoare DATE.

Select       extract(year FROM host_since::DATE)       count(id) as current_year_host FROM airbnb_search_details WHERE host_since IS NOT NULL GROUP BY extract(year FROM host_since::DATE) ORDER BY year asc

Apoi trecem la numărarea id-urilor și configuram clauza GROUP BY pentru fiecare an. Și faceți-l să fie afișat în ordine crescătoare. 

Acest lucru ar trebui să ne ofere un tabel cu două coloane: anul și numărul de gazde înregistrate în acel an. Încă nu avem o imagine completă necesară pentru a rezolva întrebarea, dar este un pas în direcția bună. De asemenea, avem nevoie de coloane separate pentru gazdele înscrise în anul precedent. Aici intervine funcția LAG().

SELECT       Year,       current_year_host,       LAG(current_year_host, 1) OVER (ORDER BY year) as prev_year_host Select       extract(year FROM host_since::DATE)       count(id) as current_year_host FROM airbnb_search_details WHERE host_since IS NOT NULL GROUP BY extract(year FROM host_since::DATE) ORDER BY year asc

Aici, am adăugat a treia coloană, care va fi etichetată „prev_year_host”, iar valorile sale vor veni de la „current_year_host”, cu excepția întârzierii unui rând. Iată cum ar putea arăta:

Aranjarea tabelului în acest fel face foarte convenabilă calcularea ratei finale de creștere. Avem o coloană separată pentru fiecare valoare din ecuație. În cele din urmă, codul nostru ar trebui să arate cam așa:

SELECT year,       current_year_host,       prev_year_host,       round(((current_year_host - prev_year_host)/(cast(prev_year_host AS numeric)))*100) estimated_growth FROM   (SELECT year,           current_year_host,           LAG(current_year_host, 1) OVER (ORDER BY year) AS 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)       ORDER BY year) t1) t2

Aici, adăugăm o altă interogare și o altă coloană în care calculăm rata de creștere. Trebuie să înmulțim rezultatul inițial cu 100 și să-l rotunjim pentru a satisface cerințele sarcinii. 

Aceasta este soluția pentru această sarcină. Este clar că funcțiile de manipulare dată-ora au fost esențiale pentru finalizarea sarcinii. 
 
 

Concept avansat N2: Funcții și partiții ale ferestrei

Funcțiile ferestrei SQL sunt unul dintre cele mai importante concepte pentru scrierea de interogări SQL complexe, dar eficiente. Se așteaptă ca profesioniștii cu experiență să aibă cunoștințe practice și teoretice profunde despre funcțiile ferestrelor. Aceasta include cunoașterea ce este clauza de supra și stăpânirea utilizării acesteia. Intervievatorii ar putea întreba cum clauza OVER poate transforma funcțiile agregate în funcții ferestre. Ați putea fi, de asemenea, întrebat despre cele trei funcții agregate care pot fi utilizate ca funcții de fereastră. Oamenii de știință de date cu experiență ar trebui să fie conștienți și de alte funcții ale ferestrei, non-agregate.

Pentru a utiliza cât mai bine funcțiile ferestrei, trebuie să știți și ce este clauza PARTITION BY și cum să o utilizați. Este posibil să vi se solicite să explicați și să oferiți exemple de câteva cazuri de utilizare. Uneori va trebui să organizați rândurile în cadrul partițiilor folosind clauza ORDER_BY.

Candidații care pot demonstra o cunoaștere aprofundată a fiecărei funcții individuale de fereastră, cum ar fi ROW_NUMBER() vor avea un avantaj. Inutil să spun că doar cunoștințele teoretice nu sunt suficiente – profesioniștii ar trebui să aibă și experiență în utilizarea lor în practică, cu sau fără partiții. De exemplu, un profesionist cu experiență ar trebui să fie capabil să explice diferențele dintre RANK() și DENSE_RANK(). Un candidat ideal ar trebui să cunoască unele dintre cele mai avansate concepte, cum ar fi cadrele din partiții, și să le poată explica clar.

Candidații grozavi ar trebui să explice și utilizarea funcției NTH_VALUE(). Nu ar strica să menționăm alternativele la această funcție, cum ar fi funcțiile FIRST_VALUE() și LAST_VALUE(). Companiilor le place adesea să măsoare quartile, cuantile și percentile în general. Pentru a efectua această operațiune, oamenii de știință de date trebuie să știe cum să folosească și funcția ferestrei NTILE().

În SQL, există de obicei multe moduri de a aborda o sarcină. Cu toate acestea, funcțiile ferestrei oferă cel mai simplu mod de a efectua operațiuni comune, dar complexe. Un bun exemplu de astfel de funcție de fereastră este LAG() sau LEAD(), așa că ar trebui să fii familiarizat și cu ele. De exemplu, să ne uităm la un exemplu din soluția anterioară la o întrebare dificilă de interviu Airbnb:

Pentru a afișa numărul de gazde în anul precedent, am folosit funcția LAG() cu instrucțiunea OVER. Acest lucru ar fi putut fi făcut în multe alte moduri, dar funcțiile ferestrei ne-au permis să obținem rezultatul dorit într-o singură linie de cod SQL:

LAG(current_year_host, 1) OVER (ORDER BY year) ca prev_year_host

Multe companii trebuie să calculeze creșterea într-o anumită perioadă de timp. Funcția LAG() poate fi neprețuită pentru finalizarea unor astfel de sarcini.
 
 

Concept avansat N3: Creștere lunară

Multe organizații folosesc analiza datelor pentru a-și măsura propria performanță. Acest lucru poate presupune măsurarea eficacității campaniilor de marketing sau a rentabilității investiției pe o anumită investiție. Efectuarea unei astfel de analize necesită o cunoaștere aprofundată a SQL, cum ar fi data, ora și funcțiile ferestrei.

Oamenii de știință de date vor trebui, de asemenea, să-și demonstreze abilitățile în formatarea datelor și afișarea lor sub formă de procente sau sub orice altă formă. În general, pentru a rezolva întrebările practice în care trebuie să calculați creșterea de la o lună la lună, trebuie să utilizați combinația de mai multe seturi de abilități. Unele dintre conceptele necesare vor fi avansate (funcții de fereastră, manipulare dată-oră), în timp ce altele vor fi de bază (funcții agregate și instrucțiuni SQL comune).

Să ne uităm la un exemplu de întrebare adresată de Intervievatorii Amazon.

Premisa:

 
În această întrebare, trebuie să lucrăm cu un tabel de achiziții și să calculăm creșterea lunară sau scăderea veniturilor. Rezultatul final trebuie formatat într-un mod specific (formatul AAAA-LL), iar procentele trebuie rotunjite la a doua zecimală cea mai apropiată. 

Soluţie:

 
Când lucrați la o sarcină ca aceasta, primul lucru pe care trebuie să-l faceți este să înțelegeți tabelul. De asemenea, ar trebui să identificați coloanele cu care trebuie să lucrați pentru a răspunde la întrebare. Și cum va arăta rezultatul dvs.

În exemplul nostru, valorile datelor au tipul de obiect, așa că va trebui să folosim funcția CAST() pentru a le transforma în tipuri de dată.

SELECT   to_char(cast(created_at as date), 'YYYY-MM') FROM sf_transactions

Întrebarea specifică, de asemenea, un format pentru date, astfel încât să putem folosi funcția TO_CHAR() în SQL pentru a afișa data în acest format.

Pentru a calcula creșterea, ar trebui să selectăm și funcția de agregare creat_at și SUM() pentru a obține volumul vânzărilor totale pentru acea dată. 

SELECT   to_char(cast(created_at as date), 'YYYY-MM'),   created_at,   sum(value) FROM sf_transactions

În acest moment, trebuie să folosim din nou funcțiile ferestrei. Mai exact, vom folosi funcția LAG() pentru a accesa volumul lunii trecute și pentru a-l afișa ca o coloană separată. Pentru asta, vom avea nevoie și de o clauză 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

Pe baza codului pe care l-am scris până acum, tabelul nostru va arăta cam așa:

Aici, avem datele și valorile totale corespunzătoare în coloana sumă, iar valorile ultimei date în coloana cu decalaj. Acum putem introduce valorile în formulă și putem afișa rata de creștere într-o coloană separată.

De asemenea, ar trebui să eliminăm coloana creată_at inutilă și să schimbăm clauzele GROUP BY și ORDER BY în year_month.

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

Odată ce rulăm codul, tabelul nostru ar trebui să includă numai coloanele care sunt esențiale pentru calculul nostru.

Acum putem ajunge în sfârșit la soluție. Iată cum ar arăta codul final:

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

În acest cod, luăm două valori de coloane din exemplul anterior și calculăm diferența dintre ele. Rețineți că folosim și aliasuri de fereastră pentru a reduce caracterul repetitiv al codului nostru.

Apoi, în conformitate cu algoritmul, îl împărțim la venitul lunii curente și îl înmulțim cu 100 pentru a obține valoarea procentuală. În cele din urmă, rotunjim valoarea procentuală la două zecimale. Ajungem la răspunsul care satisface toate cerințele sarcinii. 

Concept avansat N4: rate de abandon

 
Chiar dacă este opusul creșterii, abandonul este și o măsură importantă. Multe companii țin evidența ratelor de abandon, mai ales dacă modelul lor de afaceri se bazează pe abonament. În acest fel, ei pot urmări numărul de abonamente sau conturi pierdute și pot prezice motivele care l-au cauzat. Se așteaptă ca un specialist în date cu experiență să știe ce funcții, declarații și clauze să folosească pentru a calcula ratele de pierdere.

Datele de abonament sunt foarte private și conțin informații private despre utilizator. De asemenea, este important ca cercetătorii de date să știe cum să lucreze cu astfel de date fără a le expune. Adesea, calcularea ratelor de pierdere implică expresii comune de tabel, care sunt un concept relativ nou. Cei mai buni oameni de știință ar trebui să știe de ce sunt utile CTE-urile și când să le folosească. Atunci când lucrați cu baze de date mai vechi, unde CTE-urile nu sunt disponibile, un candidat ideal ar trebui să poată în continuare să-și facă treaba.

Iată un exemplu de sarcină dificilă. Candidații care intervievează la Lyft primesc această sarcină pentru a calcula rata de pierdere a șoferilor la companie.

Pentru a rezolva această problemă, oamenii de știință din date trebuie să utilizeze declarații caz/când, funcții de fereastră precum LAG(), precum și FROM / WHERE și alte clauze de bază. 

Concluzie

 
Lucrul ca data cientist timp de mulți ani arată cu siguranță impresionant pe un CV și îți va obține multe interviuri. Cu toate acestea, odată ce ați pus piciorul în ușă, mai trebuie să afișați cunoștințele pentru a completa anii de experiență. Chiar dacă aveți o experiență vastă în scrierea interogărilor în SQL, nu strica sa folosesti resurse precum StrataScratch pentru a vă reîmprospăta cunoștințele.

 
 
Nate Rosidi este un om de știință de date și în strategie de produs. El este, de asemenea, profesor adjunct care predă analiză și este fondatorul StrataScratch, o platformă care îi ajută pe oamenii de știință ai datelor să se pregătească pentru interviurile lor cu întrebări de interviu reale de la companii de top. Conectați-vă cu el Twitter: StrataScratch or LinkedIn.

Sursa: https://www.kdnuggets.com/2022/01/sql-interview-questions-experienced-professionals.html