Vprašanja za razgovor s SQL za izkušene strokovnjake

Predstavitev

 
Če ste izkušen podatkovni znanstvenik, ki išče službo, ne bi mogli izbrati boljšega časa. Trenutno številne uveljavljene organizacije iščejo znanstvenike s podatki, ki poznajo svojo obrt znotraj in zunaj. Vendar pa veliko povpraševanje ne pomeni, da lahko ali bi morali skočiti skozi obroče in se prijaviti za vodilne položaje brez določenega nabora spretnosti. Pri najemu izkušenih podatkovnih znanstvenikov podjetja pričakujejo, da bodo delali na najtežjih nalogah. Ti zaposleni bi morali dobro razumeti tudi najbolj nejasne funkcije, da jih lahko uporabljajo, kadar je to potrebno.

Ne bi smelo biti presenetljivo, da se pri razgovorih za vodilne položaje izkušenim podatkovnim znanstvenikom ponavadi zastavljajo veliko težja vprašanja. Ko nekaj let delajo na enem samem delovnem mestu, podatkovni znanstveniki postanejo zelo spretni pri opravljanju določenih ponavljajočih se nalog. Za strokovnjake je bistveno, da se zavedajo, da se SQL ne ustavi in ​​ne konča z njihovim obstoječim znanjem. Ko gre za napredne koncepte SQL, je morda še vedno nekaj vrzeli v njihovem znanju. Zato ne škodi, če si na razgovoru s podatkovnim znanstvenikom poiščete pomoč za uspeh. 

SQL je primarni jezik za upravljanje baz podatkov, zato je izvajanje operacij SQL v središču nalog podatkovnih znanstvenikov. Večina intervjujev podatkovnih znanstvenikov je urejenih tako, da se ugotovi kandidatovo znanje SQL. 

Vsakodnevno delo morda ne vključuje pisanja zapletenih poizvedb, vendar morate pokazati, da če so takšne veščine potrebne, ste vi oseba, ki je sposobna tega narediti. Zato ne bi smelo biti presenetljivo, da anketarji sprašujejo raznovrsten nabor Vprašanja za SQL intervju preizkusiti kandidatovo tekoče znanje SQL.

V tem članku smo želeli povzeti nekatera zapletena vprašanja in koncepte, zastavljene v intervjujih z izkušenimi strokovnjaki. Tudi če ste prepričani v svoje znanje SQL, ne škodi, če pregledate ključne besede in se prepričate, da imate vse zajeto.

Minimalni koncepti za izkušene strokovnjake

PRIMER / KDAJ

 
Temeljito razumevanje koncepta CASE (in njegovega spremljevalnega stavka When) je bistveno za doseganje popolnega obvladovanja SQL. Stavek case nam omogoča, da preverimo določene pogoje in vrnemo vrednost glede na to, ali so ti pogoji ocenjeni kot resnični ali napačni. V kombinaciji s členi, kot sta WHERE in ORDER BY, nam CASE omogoča vnašanje logike, pogojev in vrstnega reda v naše poizvedbe SQL.

Vrednost stavkov CASE ni omejena na zagotavljanje preproste pogojne logike v naših poizvedbah. Izkušeni podatkovni znanstveniki bi morali imeti več kot le površinsko razumevanje izjave CASE in njene uporabe. Anketarji vam bodo verjetno zastavili vprašanja o različnih vrstah izrazov CASE in o tem, kako jih napisati. 

Izkušeni kandidati bi morali biti pripravljeni odgovoriti na teoretična vprašanja, na primer razložiti razlike med vrednotenimi in iskanimi izjavami CASE, kako delujejo in kako jih napisati. To zahteva dobro razumevanje njihove sintakse in običajnih praks. Ni treba posebej poudarjati, da to vključuje tudi pravilno uporabo klavzule ELSE.

Od izkušenih podatkovnih znanstvenikov se pričakuje, da bodo znali uporabljati tudi CASE z agregatnimi funkcijami. Morda boste tudi pozvani, da napišete sten stavek CASE, ki se manj ponavlja in ga je lažje razumeti. Morali bi biti sposobni inteligentno govoriti o opozorilih in možnih tveganjih uporabe kratkih stavkov CASE.

Na splošno mora izkušen podatkovni znanstvenik znati uporabljati CASE za pisanje učinkovitejših poizvedb. Navsezadnje je celoten namen stavka CASE preprečiti pisanje preveč posameznih poizvedb za konsolidacijo podatkov.

Tukaj je primer vprašanja, ki ga je mogoče rešiti s stavki CASE / WHEN: https://platform.stratascratch.com/coding/9634-host-response-rates-with-cleaning-fees?python= 

To je težko vprašanje, zastavljeno na razgovorih z Airbnb, kjer morajo kandidati najti povprečno stopnjo odziva gostitelja, poštno številko in ustrezno pristojbino za čiščenje.

V tem primeru se stavek CASE/WHEN uporablja za oblikovanje rezultata kot števila in ga predstavi kot vrednost v odstotkih, poleg poštne številke.

SQL pridruži

 
Preprosto se je počutiti samozavestno v svojem poznavanju združevanja SQL, vendar bolj ko raziskujete to temo, bolj boste odkrili, da ne veste. Anketarji pogosto sprašujejo vprašanja za intervjuje o naprednih vidikih združevanja SQL ki so pogosto spregledane. Zato je pomembno, da se poglobite v ta koncept in ga temeljito obvladate.
 
Poleg osnovnih konceptov se lahko anketarji pozanimajo, kaj so samonavzkrižni spoji, in ugotovijo globino vašega znanja, tako da zahtevajo rešitev praktičnih vprašanj. Poznati morate vse različne vrste združevanja, vključno z bolj zapletenimi vrstami, kot so zgoščeni spoji ali sestavljeni spoji. Lahko bi vas tudi prosili, da razložite, kaj so naravni spoji in kdaj so najbolj uporabni. Včasih boste morali razložiti razlike med naravnimi in notranjimi spoji.
 
Na splošno bi morali imeti poglobljene izkušnje in obvladati uporabo spojin v kombinaciji z drugimi izjavami, da dosežete želene rezultate. Na primer, morali bi vedeti, kako uporabiti klavzulo WHERE za uporabo navzkrižnega združevanja, kot da bi bil notranji spoj. Od vas se pričakuje tudi, da boste znali uporabiti spojine za izdelavo novih tabel brez prevelikega pritiska na strežnik. Ali kako uporabiti zunanje spoje za identifikacijo in izpolnjevanje manjkajočih vrednosti pri poizvedovanju v bazi podatkov. Ali pa notranje delovanje zunanjih spojev, kot je dejstvo, da lahko preureditev njihovega vrstnega reda spremeni izhod. 

Tukaj je primer vprašanja, ki vključuje pisanje notranje skupne izjave. 

To je dokaj težko vprašanje, kjer se od kandidatov zahteva, da prikažejo velikost naročila kot odstotek celotne porabe.
 
 

Napredni koncept N1: Manipulacija datum-čas

Običajno je, da baze podatkov vključujejo datume in čase, zato bi moral vsak izkušen podatkovni znanstvenik imeti poglobljeno znanje o delu z njimi. Ta vrsta podatkov nam omogoča, da spremljamo vrstni red dogodkov, spremembe pogostosti, izračunamo intervale in pridobimo druge pomembne vpoglede. Velikokrat je za izvajanje teh operacij potrebno popolno obvladovanje manipulacije datum-čas v SQL. Tako bodo strokovnjaki s takšnim naborom veščin imeli prednost pred konkurenčnimi kandidati. Če niste 100-odstotno prepričani v svoje sposobnosti, si oglejte spodaj opisane koncepte in poglejte, koliko od njih zveni znano.

Ker obstaja veliko različnih (vendar veljavnih) pristopov k oblikovanju podatkov v SQL, bi morali odlični koderji biti vsaj seznanjeni z vsemi. Vodje zaposlovanja med razgovori pričakujejo poznavanje osnovnih konceptov oblikovanja podatkov in sposobnost inteligentnega pogovora o izbiri prave funkcije za nalogo. To vključuje poznavanje pomembne funkcije FORMAT() in povezane sintakse za popolno uporabo funkcije. Pričakuje se tudi poznavanje drugih osnovnih funkcij, kot je NOW(). Prav tako ne bi bilo nenadoma, če bi izkušene strokovnjake povprašali o osnovnih konceptih, kot so podatki časovnih vrst in njihov namen.

Prav tako je pomembno upoštevati kontekst delovnega mesta, za katerega se prijavljate. Podjetje z umetno inteligenco ali internetom stvari bi se bolj ukvarjalo s sledenjem podatkov, zbranih s senzorji, medtem ko aplikacija za trgovanje z delnicami morda zahteva, da spremljate nihanja cen čez dan, teden ali mesec.

V nekaterih primerih lahko delodajalci vprašajo o naprednejših funkcijah datuma/časa v SQL, kot so CAST(), EXTRACT() ali DATE_TRUNC(). Te funkcije so lahko neprecenljive, ko delate z veliko količino podatkov, ki vsebujejo datume. Izkušen podatkovni znanstvenik bi moral poznati namen vsake funkcije in njene uporabe. V idealnem scenariju bi moral imeti izkušnje z njihovo uporabo v preteklosti.

Najbolj zapletena manipulacija datum-čas v SQL bo vključevala kombinacijo osnovnih in naprednih funkcij. Zato jih je treba poznati vse, začenši z bolj osnovnimi FORMAT(), NOW(), CURRENT_DATE in CURRENT_TIME ter vključno z naprednejšimi funkcijami, omenjenimi zgoraj. Kot izkušen podatkovni znanstvenik bi morali vedeti tudi, kaj počne INTERVAL in kdaj ga uporabiti.

Tukaj je primer vprašanja, zastavljenega v intervjujih z Airbnb, kjer morajo kandidati uporabiti razpoložljive podatke za spremljanje rasti Airbnb.
 
 

Predpostavka:

 
V tem vprašanju so kandidati pozvani, da spremljajo rast Airbnb na podlagi sprememb v številu prijavljenih gostiteljev vsako leto. Z drugimi besedami, kot kazalnik rasti za vsako leto bomo uporabili število na novo registriranih gostiteljev. Stopnjo rasti bomo našli tako, da izračunamo razliko v številu gostiteljev med preteklim in tekočim letom in to število delimo s številom gostiteljev, registriranih v preteklem letu. Nato bomo našli odstotno vrednost tako, da rezultat pomnožimo s 100.

Izhodna tabela naj vsebuje stolpce in ustrezne podatke za število gostiteljev v tekočem letu, v preteklem letu in odstotek rasti iz leta v leto. Odstotek je treba zaokrožiti na najbližje celo število, vrstice pa morajo biti razvrščene v naraščajočem vrstnem redu, odvisno od leta.
 
 

rešitev:

 
Za odgovor na to vprašanje mora kandidat delati s tabelo, imenovano 'airbnb_search_details', ki vključuje veliko stolpcev. Stolpec, ki ga potrebujemo, je označen z 'host_since', kar označuje leto, mesec in dan, ko se je gostitelj prvič prijavil na spletno mesto. Za to vajo mesec in dan nista pomembna, zato je prva stvar, ki jo moramo narediti, iz vrednosti izluščiti leto. Nato bomo morali ustvariti pogled, ki vključuje ločene stolpce za tekoče leto, prejšnje leto in skupno število gostiteljev v tem letu. 

Izberite izvleček (leto FROM host_since::DATE) FROM airbnb_search_details WHERE host_since NI NULL

Do sedaj smo naredili dve stvari:

1. Poskrbeli smo, da smo vključili samo vrstice, kjer stolpec host_since ni prazen.
2. Iz podatkov smo izluščili leto in ga uvedli kot vrednost DATE.

Izberite izvleček (leto FROM host_since::DATE) count(id) kot current_year_host FROM airbnb_search_details WHERE host_since IS NOT NULL GROUP BY izvleček (leto FROM host_since::DATE) ORDER BY leto vzpon

Nato nadaljujemo s štetjem ID-jev in nastavimo klavzulo GROUP BY za vsako leto. In naj se prikaže v naraščajočem vrstnem redu. 

To bi nam moralo dati tabelo z dvema stolpcema: leto in število gostiteljev, registriranih v tem letu. Še vedno nimamo popolne slike, potrebne za rešitev vprašanja, vendar je to korak v pravo smer. Potrebujemo tudi ločene stolpce za gostitelje, ki so bili prijavljeni v preteklem letu. Tu nastopi funkcija LAG().

SELECT Year, current_year_host, LAG(current_year_host, 1) OVER (ORDER BY year) as prev_year_host Izberite izvleček (leto FROM host_since::DATE) count(id) kot current_year_host FROM airbnb_search_details WHERE host_since IS NOT NULL GROUP BY izvleček(leto FROM host_since: :DATUM) RAZVRSTI PO letu narast

Tukaj smo dodali tretji stolpec, ki bo označen z 'prev_year_host' in njegove vrednosti bodo izhajale iz 'current_year_host', razen zakasnitve ene vrstice. Evo, kako bi to lahko izgledalo:

S takšno ureditvijo tabele je zelo priročno izračunati končno stopnjo rasti. Za vsako vrednost v enačbi imamo ločen stolpec. Na koncu bi morala naša koda izgledati nekako takole:

IZBERITE leto, gostitelj trenutnega_leta, gostitelj_prejšnjega_leta, krog(((gostitelj_trenutnega_leta - gostitelj_prejšnjega_leta)/(cast(gostitelj_prejšnjega_leta AS numerično)))*100) ocenjena_rast OD (IZBERITE leto, gostitelj_tekoče_leto, LAG(gostitelj_trenutnega_leta, 1) NAD (URD PO letu) KOT gostitelj_prejšnjega_leta FROM (SELECT izvleček(leto FROM host_since::date) AS year, count(id) current_year_host FROM airbnb_search_details WHERE host_since IS NOT NULL GROUP BY izvleček(leto FROM host_since::date) ORDER BY year) t1) t2

Tukaj dodamo še eno poizvedbo in še en stolpec, kjer izračunamo stopnjo rasti. Začetni rezultat moramo pomnožiti s 100 in ga zaokrožiti, da zadovoljimo zahteve naloge. 

To je rešitev te naloge. Jasno je, da so bile funkcije manipulacije datuma in časa bistvene za dokončanje naloge. 
 
 

Napredni koncept N2: okenske funkcije in particije

Funkcije oken SQL so eden najpomembnejših konceptov za pisanje zapletenih, a učinkovitih poizvedb SQL. Od izkušenih strokovnjakov se pričakuje, da imajo globoko praktično in teoretično znanje o funkcijah oken. To vključuje poznavanje klavzule over in obvladovanje njene uporabe. Anketarji se lahko vprašajo, kako lahko klavzula OVER pretvori agregatne funkcije v okenske funkcije. Morda vas bodo vprašali tudi o treh agregatnih funkcijah, ki jih je mogoče uporabiti kot okenske funkcije. Izkušeni podatkovni znanstveniki bi se morali zavedati tudi drugih, neagregiranih funkcij oken.

Da bi kar najbolje izkoristili okenske funkcije, moramo vedeti tudi, kaj je člen PARTITION BY in kako ga uporabljati. Morda boste morali to razložiti in navesti primere nekaj primerov uporabe. Včasih boste morali organizirati vrstice znotraj particij s klavzulo ORDER_BY.

Prednost bodo imeli kandidati, ki lahko izkažejo temeljito poznavanje vsake posamezne okenske funkcije, kot je ROW_NUMBER(). Ni treba posebej poudarjati, da samo teoretično znanje ni dovolj – strokovnjaki bi morali imeti tudi izkušnje z njihovo uporabo v praksi, z ali brez pregrad. Na primer, izkušen strokovnjak bi moral biti sposoben razložiti razlike med RANK() in DENSE_RANK(). Idealen kandidat bi moral poznati nekaj najnaprednejših konceptov, kot so okvirji znotraj particij, in jih znati jasno razložiti.

Odlični kandidati bi morali razložiti tudi uporabo funkcije NTH_VALUE(). Ne bi škodilo, če bi omenili alternative tej funkciji, kot sta funkciji FIRST_VALUE() in LAST_VALUE(). Podjetja pogosto rada merijo kvartile, kvantile in percentile na splošno. Za izvedbo te operacije morajo podatkovni znanstveniki znati uporabljati tudi okensko funkcijo NTILE().

V SQL običajno obstaja veliko načinov za pristop k nalogi. Kljub temu okenske funkcije zagotavljajo najlažji način za izvajanje običajnih, a zapletenih operacij. Dober primer takšne okenske funkcije je LAG() ali LEAD(), zato bi morali biti tudi seznanjeni z njimi. Oglejmo si na primer primer iz prejšnje rešitve težkega vprašanja za intervju z Airbnb:

Za prikaz števila gostiteljev v preteklem letu smo uporabili funkcijo LAG() s stavkom OVER. To bi lahko storili na številne druge načine, vendar so nam okenske funkcije omogočile, da smo dobili želeni rezultat v samo eni vrstici kode SQL:

LAG(trenutno_leto_gostitelj, 1) NAD (VRSTI PO letu) kot prejšnje_leto_gostitelj

Veliko podjetij mora izračunati rast v določenem časovnem obdobju. Funkcija LAG() je lahko neprecenljiva za dokončanje takšnih nalog.
 
 

Napredni koncept N3: rast iz meseca v mesec

Številne organizacije uporabljajo analizo podatkov za merjenje lastne uspešnosti. To lahko vključuje merjenje učinkovitosti marketinških kampanj ali donosnosti naložbe pri določeni naložbi. Za izvedbo takšne analize je potrebno poglobljeno poznavanje SQL, kot so datum, čas in funkcije oken.

Podatkovni znanstveniki bodo morali dokazati tudi svoje sposobnosti pri oblikovanju podatkov in prikazovanju v odstotkih ali v kateri koli drugi obliki. Na splošno morate za reševanje praktičnih vprašanj, pri katerih morate izračunati rast iz meseca v mesec, uporabiti kombinacijo več naborov spretnosti. Nekateri zahtevani koncepti bodo napredni (okenske funkcije, manipulacija datum-čas), drugi pa osnovni (združene funkcije in običajni stavki SQL).

Poglejmo si en primer vprašanja, ki so ga zastavili anketarji pri Amazonu.

Predpostavka:

 
Pri tem vprašanju moramo delati s tabelo nakupov in izračunati mesečno rast ali upad prihodkov. Končni rezultat mora biti oblikovan na poseben način (format LLLL-MM), odstotki pa morajo biti zaokroženi na drugo najbližjo decimalko. 

rešitev:

 
Ko delate na takšni nalogi, morate najprej razumeti tabelo. Določiti morate tudi stolpce, s katerimi morate delati, da odgovorite na vprašanje. In kakšen bo vaš rezultat.

V našem primeru imajo vrednosti podatkov tip objekta, zato bomo morali uporabiti funkcijo CAST(), da jih pretvorimo v datumske vrste.

IZBERI to_char(cast(created_at kot datum), 'LLLL-MM') FROM sf_transactions

Vprašanje določa tudi format za datume, tako da lahko uporabimo funkcijo TO_CHAR() v SQL za izpis datuma v tej obliki.

Za izračun rasti moramo izbrati tudi agregatni funkciji created_at in SUM(), da dobimo obseg celotne prodaje za ta datum. 

IZBERI to_char(cast(created_at kot datum), 'LLLL-MM'), created_at, sum(value) FROM sf_transactions

Na tej točki moramo znova uporabiti okenske funkcije. Natančneje, uporabili bomo funkcijo LAG() za dostop do obsega prejšnjega meseca in ga prikazali kot ločen stolpec. Za to bomo potrebovali tudi klavzulo OVER.

SELECT to_char(cast(created_at kot datum), 'LLLL-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

Na podlagi kode, ki smo jo napisali do sedaj, bo naša tabela izgledala nekako takole:

Tukaj imamo datume in ustrezne skupne vrednosti v stolpcu vsote, vrednosti zadnjega datuma pa v stolpcu zamika. Zdaj lahko dodamo vrednosti v formulo in prikažemo stopnjo rasti v ločenem stolpcu.

Prav tako bi morali odstraniti nepotreben stolpec created_at in spremeniti stavka GROUP BY in ORDER BY v year_month.

IZBERI to_char(cast(created_at kot datum), 'LLLL-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 leto_mesec

Ko zaženemo kodo, mora naša tabela vključevati samo stolpce, ki so bistveni za naš izračun.

Zdaj lahko končno pridemo do rešitve. Evo, kako bi izgledala končna koda:

SELECT to_char(created_at::date, 'LLLL-MM') AS year_month, round(((sum(value) - lag(sum(value), 1) OVER w) / (lag(sum(value), 1) OVER w)) * 100, 2) AS prihodek_diff_pct FROM sf_transactions GROUP BY year_month WINDOW w AS ( ORDER BY to_char(created_at::date, 'LLLL-MM')) ORDER BY year_month ASC

V tej kodi vzamemo dve vrednosti stolpcev iz prejšnjega primera in izračunamo razliko med njima. Upoštevajte, da uporabljamo tudi vzdevke oken, da zmanjšamo ponovitev naše kode.

Nato ga v skladu z algoritmom delimo s prihodkom tekočega meseca in ga pomnožimo s 100, da dobimo odstotno vrednost. Na koncu zaokrožimo odstotno vrednost na dve decimalni vejici. Pridemo do odgovora, ki izpolnjuje vse zahteve naloge. 

Napredni koncept N4: Stopnje odliva

 
Čeprav je nasprotje rasti, je tudi odliv pomembna metrika. Mnoga podjetja spremljajo svoje stopnje odliva, še posebej, če njihov poslovni model temelji na naročnini. Na ta način lahko spremljajo število izgubljenih naročnin ali računov in predvidevajo razloge, ki so to povzročili. Od izkušenega podatkovnega znanstvenika se pričakuje, da ve, katere funkcije, izjave in klavzule uporabiti za izračun stopnje odliva.

Podatki o naročnini so zelo zasebni in vsebujejo zasebne podatke o uporabnikih. Za podatkovne znanstvenike je pomembno tudi, da vedo, kako delati s takšnimi podatki, ne da bi jih izpostavili. Pogosto izračun stopnje odliva vključuje izraze običajnih tabel, ki so relativno nov koncept. Najboljši podatkovni znanstveniki bi morali vedeti, zakaj so CTE koristni in kdaj jih uporabiti. Pri delu s starejšimi bazami podatkov, kjer CTE niso na voljo, bi moral idealen kandidat še vedno biti sposoben opraviti delo.

Tukaj je primer težke naloge. Kandidati, ki opravljajo razgovore v Lyftu, prejmejo to nalogo za izračun stopnje odliva voznikov v podjetju.

Da bi rešili to težavo, morajo podatkovni znanstveniki uporabiti stavke case/when, okenske funkcije, kot je LAG(), kot tudi FROM/WHERE in druge osnovne klavzule. 

zaključek

 
Dolgoletno delo podatkovnega znanstvenika je na življenjepisu zagotovo videti impresivno in vam bo prineslo veliko intervjujev. Ko pa enkrat stopite v vrata, morate še vedno pokazati znanje, ki bo dopolnilo leta izkušenj. Tudi če imate bogate izkušnje v pisanje poizvedb v SQL, ne škodi uporaba virov, kot je StrataScratch za osvežitev znanja.

 
 
Nate Rosidi je podatkovni znanstvenik in v strategiji izdelkov. Je tudi izredni profesor, ki poučuje analitiko in je ustanovitelj StrataScratch, platforma, ki pomaga podatkovnim znanstvenikom pri pripravi na intervjuje z resničnimi vprašanji za intervjuje vrhunskih podjetij. Povežite se z njim Twitter: StrataScratch or LinkedIn.

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