SQL-intervjufrågor för erfarna proffs

Beskrivning

 
Om du är en erfaren dataforskare som letar efter ett jobb, kunde du inte ha valt en bättre tidpunkt. Från och med nu letar många etablerade organisationer efter datavetare som kan sitt hantverk utan och innan. Den höga efterfrågan betyder dock inte att du kan, eller bör hoppa igenom ringarna och söka ledande befattningar utan en viss kompetens. När de anställer erfarna datavetare förväntar sig företagen att de ska arbeta med de svåraste uppgifterna. Dessa anställda bör ha ett bra grepp om även de mest obskyra funktionerna så att de kan använda dem när det behövs.

Det borde inte vara förvånande att erfarna dataforskare tenderar att få mycket svårare frågor när de intervjuar för ledande befattningar. Ofta, när de arbetar på ett enda jobb under ett par år, blir datavetare väldigt skickliga på att utföra vissa repetitiva uppgifter. Det är viktigt för proffs att inse att SQL inte stannar och slutar med deras befintliga kunskap. När det kommer till avancerade SQL-koncept kan det fortfarande finnas några luckor i deras kunskap. Så det skadar inte att få hjälp för att lyckas med din data scientist-intervju. 

SQL är det primära språket för att hantera databaser, så att utföra SQL-operationer är kärnan i datavetares jobb. De flesta intervjuer med dataforskare arrangeras för att fastställa en kandidats kunskaper om SQL. 

Det vardagliga jobbet kanske inte inkluderar att skriva komplexa frågor, men du måste visa att om sådana färdigheter behövs så är du den person som kan göra det. Så det borde inte vara förvånande att intervjuare frågar en mängd olika SQL-intervjufrågor för att testa kandidatens flyt i SQL.

I den här artikeln ville vi sammanfatta några av de komplexa frågor och begrepp som ställs i intervjuer med erfarna proffs. Även om du är säker på din SQL-kunskap skadar det inte att skanna sökorden och se till att du har allt täckt.

Minimikoncept för erfarna proffs

FALL / NÄR

 
Att noggrant förstå begreppet CASE (och dess medföljande When-sats) är avgörande för att uppnå fullständig behärskning av SQL. En fallbeskrivning låter oss kontrollera vissa villkor och returnera ett värde baserat på om dessa villkor utvärderas som sant eller falskt. I kombination med klausuler, som WHERE och ORDER BY, tillåter CASE oss att föra in logik, villkor och ordning i våra SQL-frågor.

Värdet av CASE-satser är inte begränsat till att tillhandahålla en enkel villkorlig logik i våra frågor. Erfarna dataforskare bör ha mer än en förståelse på ytan av CASE-satsen och dess användningsområden. Intervjuare kommer sannolikt att ställa frågor till dig om olika typer av CASE-uttryck och hur man skriver dem. 

Erfarna kandidater bör vara beredda att svara på teoretiska frågor, som att förklara skillnaderna mellan värderade och sökta CASE-uttalanden, hur de fungerar och hur man skriver dem. Detta kräver en stark förståelse för deras syntax och vanliga metoder. Det behöver inte sägas att detta även inkluderar korrekt användning av ELSE-satsen.

Erfarna dataforskare förväntas också veta hur man använder CASE med aggregerade funktioner. Du kan också bli ombedd att skriva ett stenografiskt CASE-uttalande, som är mindre repetitivt och lättare att förstå. Du bör kunna prata intelligent om varningar och möjliga risker med att använda stenografiska CASE-uttalanden.

I allmänhet måste en erfaren dataforskare kunna använda CASE för att skriva mer effektiva frågor. När allt kommer omkring är hela syftet med CASE-satsen att undvika att skriva för många individuella frågor för att konsolidera data.

Här är ett exempel på frågan som kan lösas med CASE/WHEN-satser: https://platform.stratascratch.com/coding/9634-host-response-rates-with-cleaning-fees?python= 

Detta är en svår fråga som ställs i Airbnb-intervjuer, där kandidater måste hitta den genomsnittliga svarsfrekvensen för värden, postnummer och motsvarande städavgift.

I det här fallet används CASE/WHEN-satsen för att formatera resultatet som ett tal och presentera det som ett procentvärde, förutom postnumret.

SQL Fogar

 
Det är lätt att känna sig säker på din kunskap om SQL Joins, men ju mer du utforskar det här ämnet, desto mer kommer du att upptäcka att du inte vet. Intervjuare frågar ofta intervjufrågor om avancerade aspekter av SQL Joins som ofta förbises. Så det är viktigt att fördjupa sig i detta koncept och behärska det grundligt.
 
Förutom de grundläggande begreppen kan intervjuare fråga sig vad självkorskopplingar är och ta reda på djupet i dina kunskaper genom att be om att lösa praktiska frågor. Du bör känna till alla olika typer av kopplingar, inklusive de mer komplexa typerna, som hash-kopplingar eller sammansatta kopplingar. Du kan också bli ombedd att förklara vad naturliga sammanfogningar är och när de är mest användbara. Ibland måste du förklara skillnaderna mellan naturliga och inre sammanfogningar.
 
Du bör generellt sett ha en gedigen erfarenhet och behärskning av att använda sammanfogningar i kombination med andra påståenden för att uppnå önskat resultat. Till exempel bör du veta hur man använder WHERE-satsen för att använda Cross Join som om det vore en Inner Join. Du förväntas också veta hur man använder joins för att skapa nya tabeller utan att sätta för mycket press på servern. Eller hur man använder yttre kopplingar för att identifiera och fylla i de saknade värdena när man frågar databasen. Eller de inre funktionerna hos yttre sammanfogningar, som det faktum att omarrangering av deras ordning kan ändra utmatningen. 

Här är ett exempel på frågan som involverar att skriva ett inre gemensamt uttalande. 

Detta är en ganska svår fråga, där kandidaterna uppmanas att visa orderstorleken som en procentandel av totala utgifter.
 
 

Avancerat koncept N1: Datum-tid-manipulation

Det är vanligt att databaser innehåller datum och tider, så alla erfarna dataforskare bör ha djup kunskap om att arbeta med dem. Denna typ av data låter oss spåra i vilken ordning händelserna inträffar, förändringar i frekvens, beräkna intervaller och få andra viktiga insikter. Många gånger kräver dessa operationer en fullständig behärskning av datum-tid-manipulation i SQL. Så proffsen med en sådan uppsättning färdigheter kommer att ha en fördel framför konkurrerande kandidater. Om du inte är 100 % säker på dina färdigheter, titta igenom begreppen som beskrivs nedan och se hur många av dem som låter bekanta.

Eftersom det finns många olika (men giltiga) metoder för att formatera data i SQL, bör bra kodare åtminstone känna till dem alla. Under intervjuerna förväntar sig rekryterande chefer kunskap om grundläggande dataformateringskoncept och förmåga att prata intelligent om att välja rätt funktion för uppgiften. Detta inkluderar kunskapen om en viktig FORMAT()-funktion och tillhörande syntax för att utnyttja funktionen fullt ut. Kunskap om andra grundläggande funktioner, såsom NOW() förväntas också. Det skulle inte heller vara helt i luften för erfarna proffs att få frågor om grundläggande begrepp som tidsseriedata och dess syfte.

Det är också viktigt att tänka på sammanhanget för jobbet du söker. Ett AI- eller IoT-företag skulle vara mer angelägna om att spåra data som samlas in från sensorerna, medan en aktiehandelsapp kan kräva att du spårar prisfluktuationerna under dagen, veckan eller månaden.

I vissa fall kan arbetsgivare fråga om mer avancerade datum/tid-funktioner i SQL, som CAST(), EXTRACT() eller DATE_TRUNC(). Dessa funktioner kan vara ovärderliga när du arbetar med en stor mängd data som innehåller datum. En erfaren dataforskare bör känna till syftet med varje funktion och dess tillämpningar. I ett idealiskt scenario bör han eller hon ha erfarenhet av att använda dem tidigare.

Den mest komplexa manipulationen av datum och tid i SQL kommer att involvera kombinationen av grundläggande och avancerade funktioner. Så det är nödvändigt att känna till dem alla, med början från de mer grundläggande FORMAT(), NOW(),CURRENT_DATE och CURRENT_TIME, och inklusive mer avancerade funktioner som nämns ovan. Som en erfaren dataforskare bör du också veta vad INTERVAL gör och när du ska använda det.

Här är ett exempel på en fråga som ställs i Airbnb-intervjuer, där kandidaterna måste använda tillgänglig data för att spåra Airbnbs tillväxt.
 
 

Villkoret:

 
I denna fråga ombeds kandidaterna att spåra tillväxten av Airbnb baserat på förändringar i antalet värdar som registrerats varje år. Med andra ord kommer vi att använda antalet nyregistrerade värdar som en indikator på tillväxt för varje år. Vi hittar tillväxttakten genom att beräkna skillnaden i antalet värdar mellan det senaste och innevarande året och dividera det antalet med antalet registrerade värdar under föregående år. Sedan hittar vi det procentuella värdet genom att multiplicera resultatet med 100.

Utdatatabellen bör ha kolumner och motsvarande data för antalet värdar under innevarande år, föregående år och procentandelen tillväxt från år till år. Procentsatsen ska avrundas till närmaste heltal och raderna ska sorteras i stigande ordning beroende på år.
 
 

Lösning:

 
För att svara på denna fråga måste en kandidat arbeta med tabellen som heter 'airbnb_search_details', som innehåller många kolumner. Kolumnen som vi behöver är märkt "host_since", vilket anger året, månaden och dagen då värden först registrerade sig för webbplatsen. För den här övningen är månaden och dagen irrelevanta, så det första vi behöver göra är att extrahera året från värdet. Sedan måste vi skapa en vy som innehåller separata kolumner för innevarande år, föregående år och det totala antalet värdar under det året. 

Välj       extrahera(år FROM host_since::DATE) FROM airbnb_search_details WHERE host_since IS NOT NULL

Hittills har vi gjort två saker:

1. Vi har sett till att bara ta med de rader där kolumnen host_since inte är tom.
2. Vi har extraherat årtalet från data och angett det som DATE-värdet.

Välj       extrahera(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

Sedan fortsätter vi med att räkna id:n och ställa in GROUP BY-satsen för varje år. Och få det att visas i stigande ordning. 

Detta bör ge oss en tabell med två kolumner: året och antalet registrerade värdar det året. Vi har fortfarande inte en fullständig bild som behövs för att lösa frågan, men det är ett steg i rätt riktning. Vi behöver också separata kolumner för värdar som registrerats under föregående år. Det är här funktionen LAG() kommer in.

SELECT       Year,       current_year_host,       LAG(current_year_host, 1) OVER (ORDER BY year) as prev_year_host Välj      extract(year FROM host_since::DATE)       count(id) as current_year_host IS GROUP_ROM arch_year_b_se airsin F NU_B_FROM arch_year_host ROM-värd_sedan: :DATE) BESTÄLL EFTER år asc

Här lade vi till den tredje kolumnen, som kommer att märkas 'prev_year_host' och dess värden kommer att komma från 'current_year_host', förutom fördröjningen av en rad. Så här kan det se ut:

Att arrangera tabellen på detta sätt gör det mycket bekvämt att beräkna den slutliga tillväxthastigheten. Vi har en separat kolumn för varje värde i ekvationen. I slutändan borde vår kod se ut ungefär så här:

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,          AG  värd _host, 1) ÖVER (ORDER BY year) AS prev_year_host FRÅN     (VÄLJ extrakt(år                     FROM host_since::date) AS year,             count(id) current_year_host       FRÅN airbnb_search_details       VAR värd_sedan   INTE   VAR NU LL IS             FROM host_since::date)       BESTÄLL EFTER år) t1) t2

Här lägger vi till ytterligare en fråga och ytterligare en kolumn där vi beräknar tillväxttakten. Vi måste multiplicera det initiala resultatet med 100 och avrunda det för att uppfylla kraven för uppgiften. 

Det är lösningen på denna uppgift. Det är tydligt att funktioner för manipulering av datum och tid var avgörande för att slutföra uppgiften. 
 
 

Avancerat koncept N2: Fönsterfunktioner och partitioner

SQL-fönsterfunktioner är ett av de viktigaste koncepten för att skriva komplexa, men ändå effektiva SQL-frågor. Erfarna yrkesmän förväntas ha en djup praktisk och teoretisk kunskap om fönsterfunktioner. Detta inkluderar att veta vad överklausulen är och behärska dess användning. Intervjuare kanske frågar hur OVER-satsen kan förvandla aggregerade funktioner till fönsterfunktioner. Du kan också få frågan om de tre sammanlagda funktionerna som kan användas som fönsterfunktioner. Erfarna dataforskare bör också vara medvetna om andra, icke-aggregerade fönsterfunktioner.

För att utnyttja fönsterfunktionerna på bästa sätt måste man också veta vad PARTITION BY-satsen är och hur man använder den. Du kan bli ombedd att förklara det och ge exempel på några användningsfall. Ibland måste du organisera rader inom partitioner med hjälp av ORDER_BY-satsen.

Kandidater som kan visa en grundlig kunskap om varje enskild fönsterfunktion, såsom ROW_NUMBER() kommer att ha en fördel. Naturligtvis räcker inte den teoretiska kunskapen ensam – proffs bör också ha erfarenhet av att använda dem i praktiken, med eller utan skiljeväggar. Till exempel bör en erfaren proffs kunna förklara skillnaderna mellan RANK() och DENSE_RANK(). En idealisk kandidat bör känna till några av de mest avancerade koncepten, såsom ramar inom partitioner, och kunna förklara dem tydligt.

Bra kandidater bör också förklara användningen av funktionen NTH_VALUE(). Det skulle inte skada att nämna alternativen till denna funktion, såsom funktionerna FIRST_VALUE() och LAST_VALUE(). Företag gillar ofta att mäta kvartiler, kvantiler och percentiler i allmänhet. För att utföra denna operation måste datavetare också veta hur man använder NTILE()-fönsterfunktionen.

I SQL finns det vanligtvis många sätt att närma sig en uppgift. Ändå ger fönsterfunktioner det enklaste sättet att utföra vanliga, men komplexa operationer. Ett bra exempel på en sådan fönsterfunktion är LAG() eller LEAD(), så du bör vara bekant med dem också. Låt oss till exempel titta på ett exempel från den tidigare lösningen på en svår Airbnb-intervjufråga:

För att visa antalet värdar under föregående år använde vi funktionen LAG() med OVER-satsen. Detta kunde ha gjorts på många andra sätt, men fönsterfunktioner gjorde det möjligt för oss att få det önskade resultatet på bara en rad med SQL-kod:

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

Många företag behöver beräkna tillväxt över en viss tidsperiod. Funktionen LAG() kan vara ovärderlig för att slutföra sådana uppdrag.
 
 

Avancerat koncept N3: Tillväxt månad över månad

Många organisationer använder dataanalys för att mäta sin egen prestation. Detta kan innebära att man mäter effektiviteten av marknadsföringskampanjer eller avkastning på investeringen på en specifik investering. Att utföra en sådan analys kräver en djupgående kunskap om SQL, såsom datum, tid och fönsterfunktioner.

Dataforskare måste också bevisa sina färdigheter i att formatera data och visa dem i procent eller i någon annan form. I allmänhet, för att lösa de praktiska frågorna där du måste beräkna tillväxt från månad till månad, måste du använda kombinationen av flera färdigheter. Vissa av de erforderliga koncepten kommer att vara avancerade (fönsterfunktioner, datum-tid-manipulation), medan andra kommer att vara grundläggande (aggregatfunktioner och vanliga SQL-satser).

Låt oss titta på en exempelfråga som ställdes av intervjuare på Amazon.

Villkoret:

 
I den här frågan måste vi arbeta med en tabell över inköp och beräkna månatlig tillväxt eller nedgång i intäkter. Slutresultatet måste formateras på ett specifikt sätt (ÅÅÅÅ-MM-format) och procentsatserna ska avrundas till näst närmaste decimal. 

Lösning:

 
När du arbetar med en uppgift som denna är det första du behöver göra att förstå tabellen. Du bör också identifiera de kolumner du behöver arbeta med för att svara på frågan. Och hur din produktion kommer att se ut.

I vårt exempel har datavärdena objekttypen, så vi måste använda funktionen CAST() för att omvandla dem till datumtyper.

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

Frågan anger också ett format för datum, så vi kan använda funktionen TO_CHAR() i SQL för att mata ut datumet i detta format.

För att beräkna tillväxten bör vi också välja aggregatfunktionen create_at och SUM() för att få volymen av den totala försäljningen för det datumet. 

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

Vid det här laget måste vi använda fönsterfunktioner igen. Specifikt kommer vi att använda LAG()-funktionen för att komma åt förra månadens volym och visa den som en separat kolumn. För det behöver vi också en OVER-klausul.

VÄLJ   to_char(cast(created_at as date), 'YYYY-MM') AS year_month,   created_at,   summa(value)   lag(sum(värde), 1) OVER (ORDER BY created_at::date) FROM sf_transactions GROUP BY created_at

Baserat på koden vi har skrivit hittills kommer vår tabell att se ut ungefär så här:

Här har vi datum och motsvarande totala värden i summakolumnen och det senaste datumets värden i lagkolumnen. Nu kan vi koppla in värdena i formeln och visa tillväxthastigheten i en separat kolumn.

Vi bör också ta bort den onödiga kolumnen create_at och ändra satserna GROUP BY och ORDER BY till year_month.

VÄLJ   to_char(cast(created_at as date), 'YYYY-MM') AS year_month,   summa(value),  lag(sum(value), 1) OVER (ORDER BY to_char(cast(created_at as date)) FROM sf_transactions GROUP BY år månad

När vi väl har kört koden bör vår tabell bara inkludera de kolumner som är viktiga för vår beräkning.

Nu kan vi äntligen komma fram till lösningen. Så här skulle den slutliga koden se ut:

SELECT to_char(created_at::date, 'YYYY-MM') AS year_month,       round(((summa(värde) - lag(summa(värde), 1) ÖVER w) / (eftersläpning(summa(värde), 1) ÖVER w)) * 100, 2) AS revenue_diff_pct FROM sf_transactions GROUP BY year_month WINDOW w AS (         BESTÄLL AV to_char(created_at::date, 'ÅÅÅÅ-MM')) BESTÄLL EFTER year_month ASC

I den här koden tar vi två kolumnvärden från föregående exempel och beräknar skillnaden mellan dem. Observera att vi också använder oss av fönsteralias för att minska upprepningen av vår kod.

Sedan, i enlighet med algoritmen, dividerar vi den med innevarande månads intäkter och multiplicerar den med 100 för att få det procentuella värdet. Slutligen avrundar vi procentvärdet till två decimaler. Vi kommer fram till ett svar som uppfyller alla krav för uppgiften. 

Avancerat koncept N4: Churn rates

 
Även om det är motsatsen till tillväxt är churn också ett viktigt mått. Många företag håller koll på sina churn rater, särskilt om deras affärsmodell är prenumerationsbaserad. På så sätt kan de spåra antalet förlorade prenumerationer eller konton och förutsäga orsakerna som orsakade det. En erfaren dataforskare förväntas veta vilka funktioner, påståenden och satser som ska användas för att beräkna churn-hastigheter.

Prenumerationsdata är mycket privat och innehåller privat användarinformation. Det är också viktigt för datavetare att veta hur man arbetar med sådan data utan att avslöja den. Att beräkna churn-hastigheter involverar ofta vanliga tabelluttryck, som är ett relativt nytt koncept. De bästa dataforskarna borde veta varför CTE är användbara och när de ska användas. När man arbetar med äldre databaser, där CTE:er inte är tillgängliga, bör en idealisk kandidat fortfarande kunna få jobbet gjort.

Här är ett exempel på en svår uppgift. Kandidater som intervjuar på Lyft får detta uppdrag att beräkna förares churn rate på företaget.

För att lösa detta problem måste datavetare använda fall/när-satser, fönsterfunktioner som LAG(), såväl som FROM / WHERE och andra grundläggande satser. 

Slutsats

 
Att arbeta som datavetare i många år ser verkligen imponerande ut på ett CV och kommer att ge dig många intervjuer. Men när du väl har fått in en fot inom dörren behöver du fortfarande visa upp kunskapen för att komplettera år av erfarenhet. Även om du har lång erfarenhet av skriva frågor i SQL, det skadar inte att använda resurser som StrataScratch för att fräscha upp dina kunskaper.

 
 
Nate Rosidi är datavetare och inom produktstrategi. Han är också adjungerad professor som undervisar i analys och är grundaren av StrataScratch, en plattform som hjälper datavetare att förbereda sig för sina intervjuer med riktiga intervjufrågor från toppföretag. Ta kontakt med honom Twitter: StrataScratch or LinkedIn.

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