SQL-interviewspørgsmål til erfarne fagfolk

Introduktion

 
Hvis du er en erfaren dataforsker på udkig efter et job, kunne du ikke have valgt et bedre tidspunkt. Lige nu leder mange etablerede organisationer efter dataforskere, der kan deres håndværk ud og ind. Den høje efterspørgsel betyder dog ikke, at du kan eller bør springe gennem bøjlerne og søge ledende stillinger uden en vis kompetence. Når de ansætter erfarne dataforskere, forventer virksomheder, at de arbejder med de sværeste opgaver. Disse medarbejdere bør have et godt greb om selv de mest obskure funktioner, så de kan bruge dem, når det er nødvendigt.

Det burde ikke være overraskende, at erfarne dataforskere har en tendens til at blive stillet meget sværere spørgsmål, når de interviewer til ledende stillinger. Ofte, når de arbejder på et enkelt job i et par år, bliver dataforskere meget dygtige til at udføre visse gentagne opgaver. Det er vigtigt for fagfolk at indse, at SQL ikke stopper og slutter med deres eksisterende viden. Når det kommer til avancerede SQL-koncepter, kan der stadig være nogle huller i deres viden. Så det skader ikke at få hjælp til at få succes med dit data scientist-interview. 

SQL er det primære sprog til styring af databaser, så udførelse af SQL-handlinger er kernen i dataforskeres job. De fleste data scientist-interviews er arrangeret for at bestemme en kandidats viden om SQL. 

Det daglige arbejde omfatter måske ikke at skrive komplekse forespørgsler, men du skal vise, at hvis sådanne færdigheder er nødvendige, er du den person, der er i stand til at gøre det. Så det burde ikke være overraskende, at interviewere spørger en bred vifte af SQL interview spørgsmål at teste kandidatens flydende SQL.

I denne artikel ønskede vi at opsummere nogle af de komplekse spørgsmål og begreber, der stilles i interviews med erfarne fagfolk. Selvom du er sikker på din SQL-viden, skader det ikke at scanne søgeordene og sikre dig, at du har alt dækket.

Et minimum af koncepter for erfarne fagfolk

TILFÆLDE / HVORNÅR

 
En grundig forståelse af begrebet CASE (og dens medfølgende When-sætning) er afgørende for at opnå fuldstændig beherskelse af SQL. En case-erklæring giver os mulighed for at kontrollere visse betingelser og returnere en værdi baseret på, om disse betingelser vurderes som sande eller falske. I kombination med klausuler, såsom WHERE og ORDER BY, giver CASE os mulighed for at bringe logik, betingelser og orden ind i vores SQL-forespørgsler.

Værdien af ​​CASE-udsagn er ikke begrænset til at give en simpel betinget logik i vores forespørgsler. Erfarne dataforskere bør have mere end en forståelse på overfladeniveau af CASE-erklæringen og dens anvendelser. Interviewere vil sandsynligvis stille dig spørgsmål om forskellige typer CASE-udtryk, og hvordan man skriver dem. 

Erfarne kandidater bør være parate til at besvare teoretiske spørgsmål, såsom at forklare forskellene mellem Valued og Searched CASE-udsagn, hvordan de fungerer, og hvordan man skriver dem. Dette kræver en stærk forståelse af deres syntaks og almindelig praksis. Det er overflødigt at sige, at dette også inkluderer den korrekte brug af ELSE-klausulen.

Erfarne dataforskere forventes også at vide, hvordan man bruger CASE med aggregerede funktioner. Du kan også blive bedt om at skrive en stenografi CASE-erklæring, som er mindre gentagende og lettere at forstå. Du bør være i stand til intelligent at tale om forbehold og mulige risici ved at bruge stenografiske CASE-udsagn.

Generelt skal en erfaren dataforsker være i stand til at bruge CASE til at skrive mere effektive forespørgsler. Når alt kommer til alt, er hele formålet med CASE-erklæringen at undgå at skrive for mange individuelle forespørgsler for at konsolidere dataene.

Her er et eksempel på spørgsmålet, der kan løses ved hjælp af CASE/WHEN-udsagn: https://platform.stratascratch.com/coding/9634-host-response-rates-with-cleaning-fees?python= 

Dette er et vanskeligt spørgsmål, der stilles i Airbnb-interviews, hvor kandidater skal finde den gennemsnitlige værtssvarprocent, postnummeret og det tilsvarende rengøringsgebyr.

I dette tilfælde bruges CASE/WHEN-sætningen til at formatere resultatet som et tal og præsentere det som en procentværdi ud over postnummeret.

SQL joins

 
Det er nemt at føle sig sikker på din viden om SQL Joins, men jo mere du udforsker dette emne, jo mere vil du opdage, at du ikke ved. Interviewere spørger ofte interviewspørgsmål om avancerede aspekter af SQL Joins som ofte overses. Så det er vigtigt at dykke ned i dette koncept og mestre det grundigt.
 
Udover de grundlæggende begreber kan interviewere spørge, hvad selvkrydsforbindelser er, og finde ud af dybden af ​​din viden ved at bede om at løse praktiske spørgsmål. Du bør kende alle de forskellige typer joins, inklusive de mere komplekse typer, såsom hash joins eller composite joins. Du kan også blive bedt om at forklare, hvad naturlige sammenføjninger er, og hvornår de er mest nyttige. Nogle gange bliver du nødt til at forklare forskellene mellem naturlige og indre sammenføjninger.
 
Du bør generelt have en grundig erfaring og beherskelse af at bruge joins i kombination med andre udsagn for at opnå de ønskede resultater. For eksempel bør du vide, hvordan du bruger WHERE-klausulen til at bruge Cross Join, som om det var en Inner Join. Du forventes også at vide, hvordan man bruger joins til at producere nye tabeller uden at lægge for meget pres på serveren. Eller hvordan man bruger outer joins til at identificere og udfylde de manglende værdier, når der forespørges i databasen. Eller den indre funktion af ydre sammenføjninger, såsom det faktum, at omarrangering af deres rækkefølge kan ændre outputtet. 

Her er et eksempel på spørgsmålet, der involverer at skrive en indre fælles erklæring. 

Dette er et ret vanskeligt spørgsmål, hvor kandidaterne bliver bedt om at vise ordrestørrelsen som en procentdel af det samlede forbrug.
 
 

Avanceret koncept N1: Dato-tidsmanipulation

Det er almindeligt, at databaser inkluderer datoer og klokkeslæt, så enhver erfaren dataforsker bør have dyb viden om at arbejde med dem. Denne type data giver os mulighed for at spore den rækkefølge, hvori hændelserne forekommer, ændringer i frekvens, beregne intervaller og få anden vigtig indsigt. Mange gange kræver udførelse af disse operationer en fuldstændig beherskelse af dato-tid-manipulation i SQL. Så de professionelle med sådan et sæt færdigheder vil have en fordel i forhold til konkurrerende kandidater. Hvis du ikke er 100 % sikker på dine færdigheder, så se over de begreber, der er beskrevet nedenfor, og se, hvor mange af dem, der lyder bekendte.

Da der er mange forskellige (men gyldige) tilgange til formatering af data i SQL, bør gode kodere i det mindste være fortrolige med dem alle. Under samtalerne forventer ansættelsesledere viden om grundlæggende dataformateringskoncepter og evnen til at tale intelligent om at vælge den rigtige funktion til opgaven. Dette inkluderer viden om en vigtig FORMAT() funktion og den tilhørende syntaks for at udnytte funktionen fuldt ud. Kendskab til andre grundlæggende funktioner, såsom NOW() forventes også. Det ville heller ikke være ude af det blå for erfarne fagfolk at blive spurgt om grundlæggende begreber som tidsseriedata og deres formål.

Det er også vigtigt at overveje konteksten for det job, du søger. En AI- eller IoT-virksomhed ville være mere optaget af at spore data indsamlet fra sensorerne, hvorimod en aktiehandelsapp kan kræve, at du sporer prisudsvingene i løbet af dagen, ugen eller måneden.

I nogle tilfælde kan arbejdsgivere spørge om mere avancerede dato/tidsfunktioner i SQL, såsom CAST(), EXTRACT() eller DATE_TRUNC(). Disse funktioner kan være uvurderlige, når du arbejder med en stor mængde data, der indeholder datoer. En erfaren dataforsker bør kende formålet med hver funktion og dens anvendelser. I et ideelt scenarie bør han eller hun have erfaring med at bruge dem i fortiden.

Den mest komplekse dato-tid-manipulation i SQL kommer til at involvere kombinationen af ​​grundlæggende og avancerede funktioner. Så det er nødvendigt at kende dem alle, startende fra de mere grundlæggende FORMAT(), NOW(),CURRENT_DATE og CURRENT_TIME, og inklusive mere avancerede funktioner nævnt ovenfor. Som en erfaren dataforsker bør du også vide, hvad INTERVAL gør, og hvornår du skal bruge det.

Her er et eksempel på et spørgsmål stillet i Airbnb-interviews, hvor kandidaterne skal bruge de tilgængelige data til at spore Airbnbs vækst.
 
 

Præmissen:

 
I dette spørgsmål bliver kandidaterne bedt om at spore væksten i Airbnb baseret på ændringer i antallet af værter, der er tilmeldt hvert år. Med andre ord vil vi bruge antallet af nyligt registrerede værter som en indikator for vækst for hvert år. Vi finder vækstraten ved at beregne forskellen i antallet af værter mellem sidste og indeværende år og dividere dette antal med antallet af værter registreret i det foregående år. Så finder vi den procentvise værdi ved at gange resultatet med 100.

Outputtabellen skal have kolonner og de tilsvarende data for antallet af værter i indeværende år, i det foregående år og procentdelen af ​​vækst fra år til år. Procentdelen skal afrundes til nærmeste hele tal, og rækkerne skal sorteres i stigende rækkefølge alt efter år.
 
 

Opløsning:

 
For at besvare dette spørgsmål skal en kandidat arbejde med tabellen kaldet 'airbnb_search_details', som indeholder mange kolonner. Den kolonne, vi har brug for, er mærket 'host_since', som angiver året, måneden og dagen, hvor værten første gang tilmeldte sig webstedet. For denne øvelse er måneden og dagen irrelevante, så det første, vi skal gøre, er at udtrække året fra værdien. Så bliver vi nødt til at oprette en visning, der inkluderer separate kolonner for det aktuelle år, det foregående år og det samlede antal værter i det år. 

Vælg udtræk (år FRA host_since::DATE) FRA airbnb_search_details HVOR host_since IKKE ER NULL

Indtil videre har vi gjort to ting:

1. Vi har sørget for kun at inkludere de rækker, hvor kolonnen host_since ikke er tom.
2. Vi har udtrukket året fra dataene og brugt det som DATE-værdien.

Vælg 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

Derefter fortsætter vi med at tælle id'erne og opsætte GROUP BY-klausulen for hvert år. Og få det vist i stigende rækkefølge. 

Dette skulle give os en tabel med to kolonner: året og antallet af værter, der er registreret det år. Vi har stadig ikke et fuldstændigt billede, der skal til for at løse spørgsmålet, men det er et skridt i den rigtige retning. Vi har også brug for separate kolonner for værter, der er tilmeldt det foregående år. Det er her, LAG()-funktionen kommer ind.

SELECT Year, current_year_host, LAG(current_year_host, 1) OVER (ORDER BY year) as prev_year_host Vælg extract(year FROM host_since::DATE) count(id) as current_year_host FRA airbnb_search_details WHERE host_since IS NOT NULL GROUP BY extractsin(year FROM host_ :DATO) BESTIL EFTER år asc

Her tilføjede vi den tredje kolonne, som vil blive mærket 'prev_year_host', og dens værdier kommer fra 'current_year_host', bortset fra forsinkelsen af ​​en række. Sådan kan det se ud:

At arrangere tabellen på denne måde gør det meget bekvemt at beregne den endelige væksthastighed. Vi har en separat kolonne for hver værdi i ligningen. I sidste ende skulle vores kode se sådan ud:

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_)host AS prev_BY year_ 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

Her tilføjer vi endnu en forespørgsel og endnu en kolonne, hvor vi beregner vækstraten. Vi skal gange det indledende resultat med 100 og afrunde det for at opfylde opgavens krav. 

Det er løsningen på denne opgave. Det er klart, at dato- og klokkeslætsmanipulationsfunktioner var afgørende for at fuldføre opgaven. 
 
 

Avanceret koncept N2: Vinduesfunktioner og partitioner

SQL vinduesfunktioner er et af de vigtigste begreber til at skrive komplekse, men effektive SQL-forespørgsler. Erfarne fagfolk forventes at have en dyb praktisk og teoretisk viden om vinduesfunktioner. Dette inkluderer at vide, hvad over-klausulen er, og at mestre dens brug. Interviewere spørger måske, hvordan OVER-klausulen kan gøre aggregerede funktioner til vinduesfunktioner. Du kan også blive spurgt om de tre samlede funktioner, der kan bruges som vinduesfunktioner. Erfarne dataforskere bør også være opmærksomme på andre, ikke-aggregerede vinduesfunktioner.

For at udnytte vinduesfunktionerne bedst muligt, skal man også vide, hvad PARTITION BY-klausulen er, og hvordan man bruger den. Du kan blive bedt om at forklare det og give eksempler på nogle få use cases. Nogle gange bliver du nødt til at organisere rækker i partitioner ved hjælp af ORDER_BY-sætningen.

Kandidater, der kan demonstrere et indgående kendskab til hver enkelt vinduesfunktion, såsom ROW_NUMBER(), vil have en fordel. Det er overflødigt at sige, at den teoretiske viden alene ikke er nok – fagfolk bør også have erfaring med at bruge dem i praksis, med eller uden skillevægge. For eksempel bør en erfaren fagmand være i stand til at forklare forskellene mellem RANK() og DENSE_RANK(). En ideel kandidat bør kende nogle af de mest avancerede koncepter, såsom rammer i partitioner, og være i stand til at forklare dem klart.

Gode ​​kandidater bør også forklare brugen af ​​NTH_VALUE()-funktionen. Det ville ikke skade at nævne alternativerne til denne funktion, såsom FIRST_VALUE() og LAST_VALUE() funktioner. Virksomheder kan ofte lide at måle kvartiler, kvantiler og percentiler generelt. For at udføre denne operation skal dataforskere også vide, hvordan man bruger NTILE()-vinduefunktionen.

I SQL er der normalt mange måder at gribe en opgave an på. Alligevel giver vinduesfunktioner den nemmeste måde at udføre almindelige, men komplekse operationer på. Et godt eksempel på en sådan vinduesfunktion er LAG() eller LEAD(), så du bør også være bekendt med dem. Lad os for eksempel se på et eksempel fra den tidligere løsning på et vanskeligt Airbnb-interviewspørgsmål:

For at vise antallet af værter i det foregående år brugte vi funktionen LAG() med OVER-sætningen. Dette kunne have været gjort på mange andre måder, men vinduesfunktioner gjorde det muligt for os at få det ønskede resultat på kun én linje med SQL-kode:

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

Mange virksomheder skal beregne vækst over en vis periode. LAG()-funktionen kan være uvurderlig til at udføre sådanne opgaver.
 
 

Avanceret koncept N3: Måned over måned vækst

Mange organisationer bruger dataanalyse til at måle deres egen præstation. Dette kan indebære måling af effektiviteten af ​​marketingkampagner eller ROI på en specifik investering. Udførelse af en sådan analyse kræver et indgående kendskab til SQL, såsom dato, klokkeslæt og vinduesfunktioner.

Dataforskere skal også bevise deres færdigheder i at formatere dataene og vise dem som procenter eller i enhver anden form. Generelt, for at løse de praktiske spørgsmål, hvor du skal beregne måned-til-måned vækst, skal du bruge kombinationen af ​​flere færdighedssæt. Nogle af de påkrævede koncepter vil være avancerede (vinduefunktioner, manipulation af dato og klokkeslæt), mens andre vil være grundlæggende (aggregerede funktioner og almindelige SQL-sætninger).

Lad os se på et eksempelspørgsmål stillet af interviewere hos Amazon.

Præmissen:

 
I dette spørgsmål skal vi arbejde med en købstabel og beregne månedlig vækst eller fald i omsætning. Slutresultatet skal formateres på en bestemt måde (ÅÅÅÅ-MM-format), og procenterne skal afrundes til næstnærmeste decimal. 

Opløsning:

 
Når du arbejder med en opgave som denne, er det første du skal gøre at forstå tabellen. Du bør også identificere de kolonner, du skal arbejde med for at besvare spørgsmålet. Og hvordan dit output kommer til at se ud.

I vores eksempel har dataværdierne objekttypen, så vi bliver nødt til at bruge funktionen CAST() til at transformere dem til datotyper.

SELECT to_char(cast(created_at as date), 'ÅÅÅÅ-MM') FRA sf_transactions

Spørgsmålet specificerer også et format for datoer, så vi kan bruge funktionen TO_CHAR() i SQL til at udlæse datoen i dette format.

For at beregne væksten skal vi også vælge aggregatfunktionen create_at og SUM() for at få volumen af ​​det samlede salg for den dato. 

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

På dette tidspunkt er vi nødt til at bruge vinduesfunktioner igen. Specifikt skal vi bruge LAG()-funktionen til at få adgang til sidste måneds volumen og vise den som en separat kolonne. Til det har vi også brug for en OVER-klausul.

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

Baseret på den kode, vi har skrevet indtil videre, vil vores tabel se sådan ud:

Her har vi datoerne og de tilsvarende totalværdier i sumkolonnen, og den sidste datos værdier i lagkolonnen. Nu kan vi sætte værdierne ind i formlen og vise væksthastigheden i en separat kolonne.

Vi bør også fjerne den unødvendige create_at-kolonne og ændre GROUP BY- og ORDER BY-sætningerne til year_month.

SELECT to_char(cast(created_at as date), 'ÅÅÅÅ-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 år_måned

Når vi har kørt koden, bør vores tabel kun indeholde de kolonner, der er afgørende for vores beregning.

Nu kan vi endelig nå frem til løsningen. Sådan ser den endelige kode ud:

SELECT to_char(created_at::date, 'ÅÅÅÅ-MM') AS year_month, round(((sum(værdi) - lag(sum(værdi), 1) OVER w) / (forsinkelse(sum(værdi), 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, 'ÅÅÅÅ-MM')) BESTIL EFTER year_month ASC

I denne kode tager vi to kolonneværdier fra det foregående eksempel og beregner forskellen mellem dem. Bemærk, at vi også gør brug af vinduesaliaser for at reducere gentagelsen af ​​vores kode.

Derefter dividerer vi i overensstemmelse med algoritmen med den aktuelle måneds omsætning og multiplicerer den med 100 for at få den procentvise værdi. Til sidst afrunder vi procentværdien til to decimaler. Vi når frem til det svar, der opfylder alle opgavens krav. 

Avanceret koncept N4: Churn rates

 
Selvom det er det modsatte af vækst, er churn også en vigtig målestok. Mange virksomheder holder styr på deres churn rater, især hvis deres forretningsmodel er abonnementsbaseret. På denne måde kan de spore antallet af mistede abonnementer eller konti og forudsige årsagerne, der forårsagede det. En erfaren dataforsker forventes at vide, hvilke funktioner, udsagn og klausuler der skal bruges til at beregne churn-rater.

Abonnementsdata er meget private og indeholder private brugeroplysninger. Det er også vigtigt for dataforskere at vide, hvordan man arbejder med sådanne data uden at afsløre dem. Ofte involverer beregning af churn rater almindelige tabeludtryk, som er et relativt nyt koncept. De bedste dataforskere bør vide, hvorfor CTE'er er nyttige, og hvornår de skal bruges. Når man arbejder med ældre databaser, hvor CTE'er ikke er tilgængelige, bør en ideel kandidat stadig være i stand til at få arbejdet gjort.

Her er et eksempel på en svær opgave. Kandidater til samtale hos Lyft får denne opgave med at beregne chaufførers churn rate i virksomheden.

For at løse dette problem skal dataforskere bruge case/when-sætninger, vinduesfunktioner såsom LAG(), samt FROM / WHERE og andre grundlæggende klausuler. 

Konklusion

 
At arbejde som data scientist i mange år ser helt sikkert imponerende ud på et CV, og det vil give dig mange interviews. Men når først du har fået foden inden for døren, skal du stadig vise viden for at supplere mange års erfaring. Også selvom du har bred erfaring indenfor skrive forespørgsler i SQL, det skader ikke at bruge ressourcer som StrataScratch for at genopfriske din viden.

 
 
Nate Rosidi er data scientist og i produktstrategi. Han er også adjungeret professor, der underviser i analytics og er grundlæggeren af StrataScratch, en platform, der hjælper dataforskere med at forberede sig til deres interviews med rigtige interviewspørgsmål fra topvirksomheder. Forbind med ham Twitter: StrataScratch or LinkedIn.

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