SQL-intervjuspørsmål for erfarne fagfolk

Introduksjon

 
Hvis du er en erfaren dataforsker på jakt etter en jobb, kunne du ikke ha valgt et bedre tidspunkt. Per nå er mange etablerte organisasjoner på jakt etter dataforskere som kan faget sitt ut og inn. Den høye etterspørselen betyr imidlertid ikke at du kan eller bør hoppe gjennom bøyler og søke på ledende stillinger uten en viss kompetanse. Når de ansetter erfarne dataforskere, forventer bedrifter at de jobber med de vanskeligste oppgavene. Disse ansatte bør ha et godt grep om selv de mest obskure funksjonene slik at de kan bruke dem når det er nødvendig.

Det burde ikke være overraskende at erfarne dataforskere har en tendens til å bli stilt mye vanskeligere spørsmål når de intervjuer for ledende stillinger. Ofte, når de jobber på en enkelt jobb i et par år, blir dataforskere svært dyktige til å utføre visse repeterende oppgaver. Det er viktig for fagfolk å innse at SQL ikke stopper og slutter med deres eksisterende kunnskap. Når det gjelder avanserte SQL-konsepter, kan det fortsatt være noen hull i kunnskapen deres. Så det skader ikke å få hjelp for å lykkes med dataforskerintervjuet ditt. 

SQL er det primære språket for administrasjon av databaser, så det å utføre SQL-operasjoner er kjernen i dataforskernes jobber. De fleste dataforskerintervjuer arrangeres for å fastslå en kandidats kunnskap om SQL. 

Den daglige jobben inkluderer kanskje ikke å skrive komplekse spørsmål, men du må vise at hvis slike ferdigheter er nødvendig, er du personen som er i stand til å gjøre det. Så det burde ikke være overraskende at intervjuere spør et mangfoldig sett av SQL-intervjuspørsmål for å teste kandidatens flyt i SQL.

I denne artikkelen ønsket vi å oppsummere noen av de komplekse spørsmålene og konseptene som ble stilt i intervjuer med erfarne fagpersoner. Selv om du er trygg på SQL-kunnskapen din, skader det ikke å skanne søkeordene og sørge for at du har dekket alt.

Et minimumskonsept for erfarne fagfolk

CASE / NÅR

 
Grundig forståelse av konseptet CASE (og dens medfølgende When-setning) er avgjørende for å oppnå fullstendig mestring av SQL. En saksuttalelse lar oss sjekke visse forhold og returnere en verdi basert på om disse betingelsene vurderes som sanne eller usanne. I kombinasjon med klausuler, som WHERE og ORDER BY, lar CASE oss bringe logikk, betingelser og orden inn i SQL-spørringene våre.

Verdien av CASE-setninger er ikke begrenset til å gi en enkel betinget logikk i våre spørringer. Erfarne dataforskere bør ha mer enn en forståelse på overflatenivå av CASE-setningen og dens bruk. Intervjuere vil sannsynligvis stille deg spørsmål om forskjellige typer CASE-uttrykk og hvordan du skriver dem. 

Erfarne kandidater bør være forberedt på å svare på teoretiske spørsmål, for eksempel å forklare forskjellene mellom verdifulle og søkte CASE-utsagn, hvordan de fungerer og hvordan de skal skrives. Dette krever en sterk forståelse av deres syntaks og vanlige praksiser. Unødvendig å si inkluderer dette også riktig bruk av ELSE-klausulen.

Det forventes at erfarne dataforskere også vet hvordan de skal bruke CASE med aggregerte funksjoner. Du kan også bli bedt om å skrive en stenografi CASE-uttalelse, som er mindre repeterende og lettere å forstå. Du bør være i stand til å snakke intelligent om forbehold og mulige risikoer ved å bruke stenografiske CASE-utsagn.

Generelt må en erfaren dataforsker kunne bruke CASE til å skrive mer effektive spørringer. Tross alt er hele formålet med CASE-setningen å unngå å skrive for mange individuelle spørringer for å konsolidere dataene.

Her er et eksempel på spørsmålet som kan løses ved å bruke CASE / WHEN-utsagn: https://platform.stratascratch.com/coding/9634-host-response-rates-with-cleaning-fees?python= 

Dette er et vanskelig spørsmål som stilles i Airbnb-intervjuer, der kandidater må finne den gjennomsnittlige vertens svarfrekvens, postnummeret og tilhørende rengjøringsgebyr.

I dette tilfellet brukes CASE/ WHEN-setningen til å formatere resultatet som et tall og presentere det som en prosentverdi, i tillegg til postnummeret.

SQL tiltrer

 
Det er lett å føle seg trygg på kunnskapen din om SQL Joins, men jo mer du utforsker dette emnet, jo mer vil du oppdage at du ikke vet. Intervjuere spør ofte intervjuspørsmål om avanserte aspekter ved SQL Joins som ofte blir oversett. Så det er viktig å fordype seg i dette konseptet og mestre det grundig.
 
I tillegg til de grunnleggende konseptene, kan intervjuere spørre hva selvkryssforbindelser er, og finne ut dybden av kunnskapen din ved å spørre om å løse praktiske spørsmål. Du bør kjenne til alle de forskjellige typene sammenføyninger, inkludert de mer komplekse typene, for eksempel hash-sammenføyninger eller sammensatte sammenføyninger. Du kan også bli bedt om å forklare hva naturlige sammenføyninger er, og når de er mest nyttige. Noen ganger må du forklare forskjellene mellom naturlige og indre sammenføyninger.
 
Du bør generelt sett ha grundig erfaring og mestring av å bruke sammenføyninger i kombinasjon med andre utsagn for å oppnå ønskede resultater. For eksempel bør du vite hvordan du bruker WHERE-klausulen for å bruke Cross Join som om det var en Inner Join. Det forventes også at du vet hvordan du bruker joins til å produsere nye tabeller uten å legge for mye press på serveren. Eller hvordan du bruker ytre sammenføyninger for å identifisere og fylle ut de manglende verdiene når du spør etter databasen. Eller den indre funksjonen til ytre sammenføyninger, for eksempel det faktum at omorganisering av rekkefølgen kan endre utgangen. 

Her er et eksempel på spørsmålet som involverer å skrive en indre felles uttalelse. 

Dette er et ganske vanskelig spørsmål, der kandidatene blir bedt om å vise bestillingsstørrelsen som en prosentandel av det totale forbruket.
 
 

Avansert konsept N1: Dato-tidsmanipulasjon

Det er vanlig at databaser inkluderer datoer og klokkeslett, så enhver erfaren dataforsker bør ha dyp kunnskap om å jobbe med dem. Denne typen data lar oss spore rekkefølgen hendelsene skjer i, endringer i frekvens, beregne intervaller og få annen viktig innsikt. Mange ganger krever å utføre disse operasjonene en fullstendig beherskelse av dato-tid-manipulasjon i SQL. Så fagfolk med et slikt sett med ferdigheter kommer til å ha en fordel fremfor konkurrerende kandidater. Hvis du ikke er 100 % sikker på ferdighetene dine, se over konseptene beskrevet nedenfor og se hvor mange av dem som høres kjent ut.

Siden det er mange forskjellige (men gyldige) tilnærminger til formatering av data i SQL, bør gode kodere i det minste være kjent med dem alle. Under intervjuene forventer ansettelsesledere kunnskap om grunnleggende dataformateringskonsepter og evnen til å snakke intelligent om å velge riktig funksjon for oppgaven. Dette inkluderer kunnskap om en viktig FORMAT()-funksjon og den tilhørende syntaksen for å utnytte funksjonen fullt ut. Kunnskap om andre grunnleggende funksjoner, som NOW() forventes også. Det ville heller ikke være ut av det blå for erfarne fagfolk å bli spurt om grunnleggende konsepter som tidsseriedata og deres formål.

Det er også viktig å vurdere konteksten til jobben du søker på. Et AI- eller IoT-selskap vil være mer opptatt av å spore data samlet inn fra sensorene, mens en aksjehandelsapp kan kreve at du sporer prissvingningene i løpet av dagen, uken eller måneden.

I noen tilfeller kan arbeidsgivere spørre om mer avanserte dato/tidsfunksjoner i SQL, for eksempel CAST(), EXTRACT() eller DATE_TRUNC(). Disse funksjonene kan være uvurderlige når du arbeider med et stort datavolum som inneholder datoer. En erfaren dataforsker bør vite formålet med hver funksjon og dens applikasjoner. I et ideelt scenario bør han eller hun ha erfaring med å bruke dem tidligere.

Den mest komplekse dato-tidsmanipulasjonen i SQL kommer til å involvere kombinasjonen av grunnleggende og avanserte funksjoner. Så det er nødvendig å kjenne dem alle, fra de mer grunnleggende FORMAT(), NOW(),CURRENT_DATE og CURRENT_TIME, og inkludert mer avanserte funksjoner nevnt ovenfor. Som en erfaren dataforsker bør du også vite hva INTERVAL gjør og når du skal bruke det.

Her er et eksempel på et spørsmål stilt i Airbnb-intervjuer, hvor kandidatene må bruke tilgjengelige data for å spore veksten til Airbnb.
 
 

Premisset:

 
I dette spørsmålet blir kandidatene bedt om å spore veksten til Airbnb basert på endringer i antall verter påmeldt hvert år. Med andre ord vil vi bruke antall nyregistrerte verter som en indikator på vekst for hvert år. Vi finner veksthastigheten ved å beregne forskjellen i antall verter mellom siste og inneværende år og dele dette tallet med antall verter registrert i løpet av det foregående året. Deretter finner vi prosentverdien ved å multiplisere resultatet med 100.

Utdatatabellen skal ha kolonner og tilsvarende data for antall verter i inneværende år, i foregående år, og prosentandelen av veksten fra år til år. Prosentandelen skal avrundes til nærmeste hele tall og radene skal sorteres i stigende rekkefølge avhengig av år.
 
 

Løsning:

 
For å svare på dette spørsmålet må en kandidat jobbe med tabellen kalt 'airbnb_search_details', som inkluderer mange kolonner. Kolonnen vi trenger er merket 'host_since', som angir året, måneden og dagen da verten først registrerte seg for nettstedet. For denne øvelsen er måneden og dagen irrelevante, så det første vi må gjøre er å trekke ut året fra verdien. Da må vi lage en visning som inkluderer separate kolonner for inneværende år, forrige år og det totale antallet verter i det året. 

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

Så langt har vi gjort to ting:

1. Vi har sørget for å bare inkludere radene der vert_siden-kolonnen ikke er tom.
2. Vi har hentet ut året fra dataene og lagt det til DATE-verdien.

Velg       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

Deretter fortsetter vi med å telle IDene og sette opp GROUP BY-klausulen for hvert år. Og få den til å vises i stigende rekkefølge. 

Dette skulle gi oss en tabell med to kolonner: året, og antall verter registrert det året. Vi har fortsatt ikke et fullstendig bilde som trengs for å løse spørsmålet, men det er et skritt i riktig retning. Vi trenger også egne kolonner for verter påmeldt i løpet av det foregående året. Det er her LAG()-funksjonen kommer inn.

SELECT       Year,       current_year_host,       LAG(current_year_host, 1) OVER (ORDER BY year) as prev_year_host Velg       extract(year FROM host_since::DATE)       count(id) as current_year_vert, XNUMX) OVER (ORDER BY year) as prev_year_host Velg       extract(year FROM host_since::DATE)       count(id) as current_year_host. ROM vert_siden: :DATO) BESTILL ETTER år asc

Her la vi til den tredje kolonnen, som vil bli merket 'prev_year_host' og verdiene kommer fra 'current_year_host', bortsett fra forsinkelsen på en rad. Slik kan det se ut:

Å arrangere tabellen på denne måten gjør det veldig praktisk å beregne den endelige vekstraten. Vi har en egen kolonne for hver verdi i ligningen. Til syvende og sist skal koden vår se omtrent slik ut:

SELECT year,       current_year_host,       prev_year_host,       round(((current_year_host - prev_year_host)/(cast(prev_year_host AS numeric))*100) estimert_vekst FRA   (SELECT year,      _ _vert, 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       IKKE HVOR NU LL IS              FROM host_since::date)       BESTILL ETTER år) t1) t2

Her legger vi til en ny spørring og en annen kolonne der vi beregner vekstraten. Vi må gange det første resultatet med 100 og runde det for å tilfredsstille kravene til oppgaven. 

Det er løsningen på denne oppgaven. Det er tydelig at dato- og klokkeslettmanipulasjonsfunksjoner var avgjørende for å fullføre oppgaven. 
 
 

Avansert konsept N2: Vindufunksjoner og partisjoner

SQL-vindusfunksjoner er et av de viktigste konseptene for å skrive komplekse, men effektive SQL-spørringer. Erfarne fagfolk forventes å ha en dyp praktisk og teoretisk kunnskap om vindusfunksjoner. Dette inkluderer å vite hva over-klausulen er og mestre bruken av den. Intervjuere kan spørre hvordan OVER-klausulen kan gjøre aggregerte funksjoner til vindusfunksjoner. Du kan også bli spurt om de tre samlede funksjonene som kan brukes som vindusfunksjoner. Erfarne dataforskere bør også være oppmerksomme på andre, ikke-aggregerte vindusfunksjoner.

For å utnytte vindusfunksjonene best mulig, må man også vite hva PARTITION BY-klausulen er og hvordan man bruker den. Du kan bli bedt om å forklare det og gi eksempler på noen få brukstilfeller. Noen ganger må du organisere rader i partisjoner ved å bruke ORDER_BY-leddet.

Kandidater som kan demonstrere en grundig kjennskap til hver enkelt vindusfunksjon, slik som ROW_NUMBER() vil ha en fordel. Unødvendig å si at den teoretiske kunnskapen alene ikke er nok – fagfolk bør også ha erfaring med å bruke dem i praksis, med eller uten skillevegger. For eksempel bør en erfaren fagperson kunne forklare forskjellene mellom RANK() og DENSE_RANK(). En ideell kandidat bør kunne noen av de mest avanserte konseptene, for eksempel rammer i partisjoner, og kunne forklare dem tydelig.

Gode ​​kandidater bør også forklare bruken av funksjonen NTH_VALUE(). Det ville ikke skade å nevne alternativene til denne funksjonen, for eksempel FIRST_VALUE() og LAST_VALUE() funksjoner. Bedrifter liker ofte å måle kvartiler, kvantiler og persentiler generelt. For å utføre denne operasjonen må dataforskere også vite hvordan de bruker NTILE()-vindusfunksjonen.

I SQL er det vanligvis mange måter å nærme seg en oppgave på. Likevel gir vindusfunksjoner den enkleste måten å utføre vanlige, men komplekse operasjoner på. Et godt eksempel på en slik vindusfunksjon er LAG() eller LEAD(), så du bør være kjent med dem også. La oss for eksempel se på et eksempel fra den forrige løsningen på et vanskelig Airbnb-intervjuspørsmål:

For å vise antall verter i året før, brukte vi LAG()-funksjonen med OVER-setningen. Dette kunne vært gjort på mange andre måter, men vindusfunksjoner tillot oss å få ønsket resultat på bare én linje med SQL-kode:

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

Mange selskaper må beregne vekst over en viss tidsperiode. LAG()-funksjonen kan være uvurderlig for å fullføre slike oppdrag.
 
 

Avansert konsept N3: Måned over måned vekst

Mange organisasjoner bruker dataanalyse for å måle sin egen ytelse. Dette kan innebære å måle effektiviteten til markedsføringskampanjer eller avkastning på en spesifikk investering. Å utføre en slik analyse krever inngående kunnskap om SQL, som dato, klokkeslett og vindusfunksjoner.

Dataforskere må også bevise ferdighetene sine i å formatere dataene og vise dem i prosent eller i annen form. Generelt, for å løse de praktiske spørsmålene der du må beregne månedlig vekst, må du bruke kombinasjonen av flere ferdighetssett. Noen av de nødvendige konseptene vil være avanserte (vindusfunksjoner, manipulering av dato og klokkeslett), mens andre vil være grunnleggende (aggregerte funksjoner og vanlige SQL-setninger).

La oss se på et eksempelspørsmål stilt av intervjuere hos Amazon.

Premisset:

 
I dette spørsmålet må vi jobbe med en kjøpstabell og beregne månedlig vekst eller nedgang i inntekter. Sluttresultatet må formateres på en bestemt måte (ÅÅÅÅ-MM-format), og prosentene bør avrundes til nest nærmeste desimal. 

Løsning:

 
Når du jobber med en oppgave som dette, er det første du må gjøre å forstå tabellen. Du bør også identifisere kolonnene du må jobbe med for å svare på spørsmålet. Og hvordan produksjonen din kommer til å se ut.

I vårt eksempel har dataverdiene objekttypen, så vi må bruke CAST()-funksjonen for å transformere dem til datotyper.

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

Spørsmålet spesifiserer også et format for datoer, så vi kan bruke TO_CHAR()-funksjonen i SQL for å sende ut datoen i dette formatet.

For å beregne veksten bør vi også velge aggregatfunksjonene create_at og SUM() for å få volumet av totalt salg for den datoen. 

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

På dette tidspunktet må vi bruke vindusfunksjoner igjen. Spesifikt skal vi bruke LAG()-funksjonen for å få tilgang til forrige måneds volum og vise det som en egen kolonne. For det trenger vi også 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

Basert på koden vi har skrevet så langt, vil tabellen vår se omtrent slik ut:

Her har vi datoene og tilsvarende totalverdier i sumkolonnen, og siste datos verdier i lagkolonnen. Nå kan vi plugge verdiene inn i formelen og vise veksthastigheten i en egen kolonne.

Vi bør også fjerne den unødvendige create_at-kolonnen, og endre GROUP BY- og ORDER BY-klausulene til 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 år_måned

Når vi har kjørt koden, skal tabellen vår bare inkludere kolonnene som er avgjørende for beregningen vår.

Nå kan vi endelig komme frem til løsningen. Slik vil den endelige koden se ut:

SELECT to_char(created_at::date, 'ÅÅÅÅ-MM') AS year_month,       round(((sum(verdi) - lag(sum(verdi), 1) OVER w) / (etterslep(sum(verdi), 1) OVER w)) * 100, 2) AS revenue_diff_pct FROM sf_transactions GROUP BY year_month WINDOW w AS (         BESTILL ETTER to_char(created_at::date, 'YYYY-MM')) BESTILL ETTER year_month ASC

I denne koden tar vi to kolonneverdier fra forrige eksempel og beregner forskjellen mellom dem. Merk at vi også bruker vindusaliaser for å redusere repetisjonen til koden vår.

Deretter, i samsvar med algoritmen, deler vi den med gjeldende måneds inntekt og multipliserer den med 100 for å få prosentverdien. Til slutt runder vi av prosentverdien til to desimaler. Vi kommer frem til svaret som tilfredsstiller alle kravene til oppgaven. 

Avansert konsept N4: Churn rates

 
Selv om det er det motsatte av vekst, er churn også en viktig beregning. Mange selskaper holder styr på avgangsprosenten, spesielt hvis forretningsmodellen deres er abonnementsbasert. På denne måten kan de spore antall tapte abonnementer eller kontoer, og forutsi årsakene som forårsaket det. En erfaren dataforsker vil forventes å vite hvilke funksjoner, utsagn og klausuler som skal brukes til å beregne churn-rater.

Abonnementsdata er svært private og inneholder privat brukerinformasjon. Det er også viktig for dataforskere å vite hvordan man jobber med slike data uten å avsløre dem. Ofte innebærer beregning av churn-rater vanlige tabelluttrykk, som er et relativt nytt konsept. De beste dataforskerne bør vite hvorfor CTE-er er nyttige og når de skal brukes. Når du arbeider med eldre databaser, der CTE-er ikke er tilgjengelige, bør en ideell kandidat fortsatt kunne få jobben gjort.

Her er et eksempel på en vanskelig oppgave. Kandidater som intervjuer i Lyft får denne oppgaven for å beregne sjåførers churn rate i selskapet.

For å løse dette problemet må dataforskere bruke case/when-setninger, vindusfunksjoner som LAG(), samt FROM / WHERE og andre grunnleggende klausuler. 

konklusjonen

 
Å jobbe som dataforsker i mange år ser absolutt imponerende ut på en CV, og vil gi deg mange intervjuer. Men når du først har fått foten innenfor døren, må du fortsatt vise kunnskapen for å utfylle mange års erfaring. Selv om du har bred erfaring innen skrive spørringer i SQL, det skader ikke å bruke ressurser som StrataScratch for å friske opp kunnskapen din.

 
 
Nate Rosidi er dataviter og innen produktstrategi. Han er også adjungert professor som underviser i analyse, og er grunnleggeren av StrataScratch, en plattform som hjelper dataforskere med å forberede seg til intervjuene sine med ekte intervjuspørsmål fra toppbedrifter. Ta kontakt med ham Twitter: StrataScratch or Linkedin.

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