Domande di intervista SQL per professionisti esperti

Introduzione

 
Se sei un data scientist esperto in cerca di lavoro, non avresti potuto scegliere un momento migliore. A partire da ora, molte organizzazioni affermate sono alla ricerca di data scientist che conoscano il loro mestiere dentro e fuori. Tuttavia, l'elevata domanda non significa che puoi, o dovresti fare i salti mortali e candidarti per posizioni senior senza un certo insieme di competenze. Quando assumono data scientist esperti, le aziende si aspettano che lavorino sui compiti più difficili da svolgere. Questi dipendenti dovrebbero avere una buona conoscenza anche delle funzionalità più oscure in modo da poterle utilizzare quando necessario.

Non dovrebbe sorprendere che durante i colloqui per posizioni senior, ai data scientist esperti tendano a ricevere domande molto più difficili. Spesso, quando lavorano su un unico lavoro per un paio d’anni, i data scientist diventano molto abili nell’eseguire determinati compiti ripetitivi. È essenziale che i professionisti si rendano conto che SQL non si ferma e non termina con le loro conoscenze esistenti. Quando si tratta di concetti SQL avanzati, potrebbero esserci ancora alcune lacune nelle loro conoscenze. Quindi non fa male ricevere aiuto per avere successo nel tuo colloquio da data scientist. 

SQL è il linguaggio principale per la gestione dei database, quindi l'esecuzione di operazioni SQL è al centro del lavoro dei data scientist. La maggior parte dei colloqui con i data scientist sono organizzati per determinare la conoscenza di SQL da parte del candidato. 

Il lavoro quotidiano potrebbe non includere la scrittura di query complesse, ma devi dimostrare che se tali competenze sono necessarie, sei la persona in grado di farlo. Quindi non dovrebbe sorprendere che gli intervistatori chiedano una serie diversificata di Domande di intervista SQL per testare la fluidità del candidato in SQL.

In questo articolo abbiamo voluto riassumere alcune delle domande e dei concetti complessi posti nelle interviste a professionisti esperti. Anche se sei sicuro delle tue conoscenze SQL, non fa male scansionare le parole chiave e assicurarti di aver tutto coperto.

Concetti minimi per professionisti esperti

CASO/QUANDO

 
Comprendere a fondo il concetto di CASE (e la relativa istruzione When) è essenziale per ottenere la completa padronanza di SQL. Un'istruzione case ci consente di verificare determinate condizioni e restituire un valore in base al fatto che tali condizioni siano valutate come vere o false. In combinazione con clausole come WHERE e ORDER BY, CASE ci consente di introdurre logica, condizioni e ordine nelle nostre query SQL.

Il valore delle istruzioni CASE non si limita a fornire una semplice logica condizionale nelle nostre query. I data scientist esperti dovrebbero avere una comprensione più che superficiale della dichiarazione CASE e dei suoi usi. È probabile che gli intervistatori ti facciano domande sui diversi tipi di espressioni CASE e su come scriverle. 

I candidati esperti dovrebbero essere preparati a rispondere a domande teoriche, come spiegare le differenze tra le dichiarazioni CASE Valutato e Cercato, come funzionano e come scriverle. Ciò richiede una forte comprensione della loro sintassi e delle pratiche comuni. Inutile dire che ciò include anche l’uso corretto della clausola ELSE.

Ci si aspetta che i data scientist esperti sappiano come utilizzare CASE anche con funzioni aggregate. Potrebbe anche venirti chiesto di scrivere un'istruzione CASE abbreviata, che è meno ripetitiva e più facile da capire. Dovresti essere in grado di parlare in modo intelligente degli avvertimenti e dei possibili rischi derivanti dall'utilizzo di istruzioni CASE abbreviate.

In generale, un data scientist esperto deve essere in grado di utilizzare CASE per scrivere query più efficienti. Dopo tutto, lo scopo dell'istruzione CASE è evitare di scrivere troppe query individuali per consolidare i dati.

Ecco un esempio della domanda che può essere risolta utilizzando le istruzioni CASE / WHEN: https://platform.stratascratch.com/coding/9634-host-response-rates-with-cleaning-fees?python= 

Questa è una domanda difficile posta nelle interviste di Airbnb, in cui i candidati devono trovare il tasso medio di risposta dell'host, il codice postale e le spese di pulizia corrispondenti.

In questo caso, l'istruzione CASE/WHEN viene utilizzata per formattare il risultato come numero e presentarlo come valore percentuale, oltre al codice postale.

SQL entra a far parte

 
È facile avere fiducia nella propria conoscenza dei join SQL, ma più esplori questo argomento, più scoprirai di non saperlo. Lo chiedono spesso gli intervistatori domande dell'intervista sugli aspetti avanzati dei join SQL che spesso vengono trascurati. Quindi è importante approfondire questo concetto e padroneggiarlo a fondo.
 
Oltre ai concetti di base, gli intervistatori potrebbero chiederti cosa sono i self cross join e scoprire la profondità delle tue conoscenze chiedendoti di risolvere domande pratiche. Dovresti conoscere tutti i diversi tipi di join, inclusi quelli più complessi, come hash join o compositi. Potrebbe anche esserti chiesto di spiegare cosa sono i natural join e quando sono più utili. A volte dovrai spiegare le differenze tra i join naturali e quelli interni.
 
In generale, dovresti avere un'esperienza approfondita e una padronanza nell'uso dei join in combinazione con altre istruzioni per ottenere i risultati desiderati. Ad esempio, dovresti sapere come utilizzare la clausola WHERE per utilizzare Cross Join come se fosse un Inner Join. Dovrai anche sapere come utilizzare i join per produrre nuove tabelle senza esercitare troppa pressione sul server. O come utilizzare i join esterni per identificare e inserire i valori mancanti durante l'esecuzione di query sul database. Oppure il funzionamento interno degli outside join, ad esempio il fatto che la riorganizzazione del loro ordine può modificare l'output. 

Ecco un esempio della domanda che prevede la scrittura di una dichiarazione congiunta interna. 

Questa è una domanda abbastanza difficile, in cui ai candidati viene chiesto di visualizzare la dimensione dell'ordine come percentuale della spesa totale.
 
 

Concetto Avanzato N1: Manipolazione Data-Ora

È normale che i database includano date e orari, quindi qualsiasi data scientist esperto dovrebbe avere una conoscenza approfondita di come lavorare con essi. Questo tipo di dati ci consente di monitorare l'ordine in cui si verificano gli eventi, i cambiamenti nella frequenza, calcolare gli intervalli e ottenere altre informazioni importanti. Molte volte l'esecuzione di queste operazioni richiede una completa padronanza della manipolazione di data e ora in SQL. Quindi i professionisti con un tale insieme di competenze avranno un vantaggio rispetto ai candidati concorrenti. Se non sei sicuro al 100% delle tue capacità, esamina i concetti descritti di seguito e vedi quanti di essi ti suonano familiari.

Poiché esistono molti approcci diversi (ma validi) alla formattazione dei dati in SQL, i bravi programmatori dovrebbero almeno avere familiarità con tutti. Durante le interviste, i responsabili delle assunzioni si aspettano la conoscenza dei concetti di base della formattazione dei dati e la capacità di parlare in modo intelligente della scelta della funzione giusta per l'attività. Ciò include la conoscenza di un'importante funzione FORMAT() e della sintassi associata per sfruttare appieno la funzione. E' prevista anche la conoscenza di altre funzioni base, come ad esempio NOW(). Inoltre, non sarebbe inaspettato che professionisti esperti venissero interrogati su concetti di base come i dati delle serie temporali e il loro scopo.

È anche importante considerare il contesto del lavoro per cui ti stai candidando. Un’azienda di intelligenza artificiale o IoT sarebbe più interessata a monitorare i dati raccolti dai sensori, mentre un’app di compravendita di azioni potrebbe richiedere di monitorare le fluttuazioni dei prezzi durante il giorno, la settimana o il mese.

In alcuni casi, i datori di lavoro potrebbero chiedere informazioni su funzioni di data/ora più avanzate in SQL, come CAST(), EXTRACT() o DATE_TRUNC(). Queste funzioni possono essere preziose quando lavori con un grande volume di dati che contengono date. Uno scienziato dei dati esperto dovrebbe conoscere lo scopo di ciascuna funzione e le sue applicazioni. In uno scenario ideale, lui o lei dovrebbe avere esperienza nell'usarli in passato.

La manipolazione di data e ora più complessa in SQL coinvolgerà la combinazione di funzioni di base e avanzate. Quindi è necessario conoscerli tutti, a partire dai più basilari FORMAT(), NOW(),CURRENT_DATE e CURRENT_TIME, fino alle funzioni più avanzate menzionate sopra. In qualità di data scientist esperto, dovresti anche sapere cosa fa INTERVAL e quando utilizzarlo.

Ecco un esempio di una domanda posta nelle interviste di Airbnb, dove i candidati devono utilizzare i dati disponibili per monitorare la crescita di Airbnb.
 
 

La premessa:

 
In questa domanda, ai candidati viene chiesto di monitorare la crescita di Airbnb in base alle variazioni del numero di host iscritti ogni anno. In altre parole, utilizzeremo il numero di nuovi host registrati come indicatore di crescita per ogni anno. Troveremo il tasso di crescita calcolando la differenza nel numero di host tra l'anno scorso e quello in corso e dividendo quel numero per il numero di host registrati durante l'anno precedente. Quindi troveremo il valore percentuale moltiplicando il risultato per 100.

La tabella di output dovrebbe contenere colonne e i dati corrispondenti per il numero di host nell'anno corrente, nell'anno precedente e la percentuale di crescita di anno in anno. La percentuale deve essere arrotondata all'intero più vicino e le righe devono essere ordinate in modo crescente a seconda dell'anno.
 
 

Soluzione:

 
Per rispondere a questa domanda, un candidato deve lavorare con la tabella chiamata "airbnb_search_details", che include molte colonne. La colonna di cui abbiamo bisogno è etichettata "host_since", che indica l'anno, il mese e il giorno in cui l'host si è registrato per la prima volta al sito web. Per questo esercizio il mese e il giorno sono irrilevanti, quindi la prima cosa che dovremo fare è estrarre l'anno dal valore. Quindi dovremo creare una visualizzazione che includa colonne separate per l'anno corrente, l'anno precedente e il numero totale di host in quell'anno. 

Seleziona estratto (anno DA host_since::DATA) DA airbnb_search_details DOVE host_since NON È NULL

Finora abbiamo fatto due cose:

1. Ci siamo assicurati di includere solo le righe in cui la colonna host_since non è vuota.
2. Abbiamo estratto l'anno dai dati e lo abbiamo scelto come valore DATE.

Seleziona extract(year FROM host_since::DATE) count(id) as current_year_host FROM airbnb_search_details DOVE host_since NON È NULL GROUP BY extract(year FROM host_since::DATE) ORDER BY anno asc

Quindi procediamo a contare gli ID e impostiamo la clausola GROUP BY per ogni anno. E fallo visualizzare in ordine crescente. 

Questo dovrebbe darci una tabella con due colonne: l'anno e il numero di host registrati quell'anno. Non abbiamo ancora un quadro completo necessario per risolvere la questione, ma è un passo nella giusta direzione. Abbiamo anche bisogno di colonne separate per gli host registrati durante l'anno precedente. È qui che entra in gioco la funzione LAG().

SELECT Anno, current_year_host, LAG(current_year_host, 1) OVER (ORDER BY anno) come prev_year_host Seleziona extract(year FROM host_since::DATE) count(id) come current_year_host FROM airbnb_search_details DOVE host_since NON È NULL GROUP BY extract(year FROM host_since: :DATA) ORDINA PER anno asc

Qui abbiamo aggiunto la terza colonna, che sarà etichettata "prev_year_host" e i suoi valori proverranno da "current_year_host", ad eccezione del ritardo di una riga. Ecco come potrebbe apparire:

Disporre la tabella in questo modo rende molto conveniente calcolare il tasso di crescita finale. Abbiamo una colonna separata per ogni valore nell'equazione. Alla fine, il nostro codice dovrebbe assomigliare a questo:

SELECT anno, host_anno_corrente, host_anno_prec, round(((host_anno_corrente - host_anno_prec)/(cast(host_anno_prec AS numerico)))*100) crescita_ stimata FROM (SELECT anno, host_anno_corrente, LAG(host_anno_corrente, 1) OVER (ORDINA PER anno) AS host_anno_prec FROM (SELECT extract(year FROM host_since::date) AS anno, count(id) current_year_host FROM airbnb_search_details WHERE host_since NON È NULL GROUP BY extract(year FROM host_since::date) ORDER BY year) t1) t2

Qui aggiungiamo un'altra query e un'altra colonna in cui calcoliamo il tasso di crescita. Dobbiamo moltiplicare il risultato iniziale per 100 e arrotondarlo per soddisfare i requisiti dell'attività. 

Questa è la soluzione a questo compito. È chiaro che le funzioni di manipolazione della data e dell'ora erano essenziali per completare l'attività. 
 
 

Advanced Concept N2: Funzioni delle finestre e partizioni

Funzioni della finestra SQL sono uno dei concetti più importanti per scrivere query SQL complesse ma efficienti. Ci si aspetta che i professionisti esperti abbiano una profonda conoscenza pratica e teorica delle funzioni delle finestre. Ciò include sapere cos'è la clausola over e padroneggiarne l'uso. Gli intervistatori potrebbero chiedersi come la clausola OVER possa trasformare le funzioni aggregate in funzioni finestra. Potrebbero anche essere chieste informazioni sulle tre funzioni aggregate che possono essere utilizzate come funzioni di finestra. I data scientist esperti dovrebbero essere consapevoli anche di altre funzioni delle finestre non aggregate.

Per utilizzare al meglio le funzioni della finestra, è necessario anche sapere cos'è la clausola PARTITION BY e come utilizzarla. Potrebbe esserti chiesto di spiegarlo e fornire esempi di alcuni casi d'uso. A volte dovrai organizzare le righe all'interno delle partizioni utilizzando la clausola ORDER_BY.

I candidati che possono dimostrare una conoscenza approfondita di ogni singola funzione della finestra, come ROW_NUMBER() avranno un vantaggio. Inutile dire che le conoscenze teoriche da sole non sono sufficienti: i professionisti dovrebbero anche avere esperienza nell'utilizzo pratico, con o senza pareti divisorie. Ad esempio, un professionista esperto dovrebbe essere in grado di spiegare le differenze tra RANK() e DENSE_RANK(). Un candidato ideale dovrebbe conoscere alcuni dei concetti più avanzati, come i frame all'interno delle partizioni, ed essere in grado di spiegarli chiaramente.

I candidati migliori dovrebbero anche spiegare l'uso della funzione NTH_VALUE(). Non sarebbe male menzionare le alternative a questa funzione, come le funzioni FIRST_VALUE() e LAST_VALUE(). Le aziende spesso preferiscono misurare quartili, quantili e percentili in generale. Per eseguire questa operazione, i data scientist devono sapere come utilizzare anche la funzione della finestra NTILE().

In SQL, di solito ci sono molti modi per affrontare un'attività. Tuttavia, le funzioni della finestra forniscono il modo più semplice per eseguire operazioni comuni ma complesse. Un buon esempio di tale funzione di finestra è LAG() o LEAD(), quindi dovresti avere familiarità anche con loro. Ad esempio, diamo un'occhiata a un esempio tratto dalla soluzione precedente a una domanda difficile per un'intervista su Airbnb:

Per visualizzare il numero di host dell'anno precedente, abbiamo utilizzato la funzione LAG() con l'istruzione OVER. Questo avrebbe potuto essere fatto in molti altri modi, ma le funzioni della finestra ci hanno permesso di ottenere il risultato desiderato in una sola riga di codice SQL:

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

Molte aziende devono calcolare la crescita in un determinato periodo di tempo. La funzione LAG() può essere preziosa per completare tali incarichi.
 
 

Concetto avanzato N3: crescita mese dopo mese

Molte organizzazioni utilizzano l'analisi dei dati per misurare le proprie prestazioni. Ciò può comportare la misurazione dell’efficacia delle campagne di marketing o del ROI su un investimento specifico. L'esecuzione di tale analisi richiede una conoscenza approfondita di SQL, come data, ora e funzioni della finestra.

I data scientist dovranno inoltre dimostrare le proprie capacità nel formattare i dati e visualizzarli come percentuali o in qualsiasi altra forma. In generale, per risolvere le questioni pratiche in cui è necessario calcolare la crescita mese su mese, è necessario utilizzare la combinazione di più competenze. Alcuni dei concetti richiesti saranno avanzati (funzioni di finestra, manipolazione di data e ora), mentre altri saranno di base (funzioni aggregate e istruzioni SQL comuni).

Diamo un'occhiata a una domanda di esempio posta dagli intervistatori di Amazon.

La premessa:

 
In questa domanda, dobbiamo lavorare con una tabella di acquisti e calcolare la crescita mensile o il calo delle entrate. Il risultato finale deve essere formattato in un modo specifico (formato AAAA-MM) e le percentuali devono essere arrotondate al secondo decimale più vicino. 

Soluzione:

 
Quando lavori su un'attività come questa, la prima cosa che devi fare è comprendere la tabella. Dovresti anche identificare le colonne con cui devi lavorare per rispondere alla domanda. E come sarà il tuo output.

Nel nostro esempio, i valori dei dati hanno il tipo oggetto, quindi dovremo utilizzare la funzione CAST() per trasformarli in tipi di data.

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

La domanda specifica anche un formato per le date, quindi possiamo utilizzare la funzione TO_CHAR() in SQL per restituire la data in questo formato.

Per calcolare la crescita, dovremmo anche selezionare la funzione di aggregazione create_at e SUM() per ottenere il volume delle vendite totali per quella data. 

SELEZIONA to_char(cast(created_at come data), 'AAAA-MM'), create_at, sum(valore) FROM sf_transactions

A questo punto dobbiamo utilizzare nuovamente le funzioni della finestra. Nello specifico, utilizzeremo la funzione LAG() per accedere al volume del mese scorso e visualizzarlo come una colonna separata. Per questo avremo bisogno anche di una clausola OVER.

SELECT to_char(cast(created_at as date), 'YYYY-MM') AS anno_mese, create_at, sum(value) lag(sum(value), 1) OVER (ORDINE PER create_at::data) FROM sf_transactions GROUP BY create_at

In base al codice che abbiamo scritto finora, la nostra tabella sarà simile a questa:

Qui abbiamo le date e i valori totali corrispondenti nella colonna della somma e i valori dell'ultima data nella colonna del ritardo. Ora possiamo inserire i valori nella formula e visualizzare il tasso di crescita in una colonna separata.

Dovremmo anche rimuovere la colonna create_at non necessaria e modificare le clausole GROUP BY e ORDER BY in year_month.

SELECT to_char(cast(created_at as date), 'YYYY-MM') AS anno_mese, sum(value), lag(sum(value), 1) OVER (ORDER BY to_char(cast(created_at as date)) FROM sf_transactions GROUP BY anno mese

Una volta eseguito il codice, la nostra tabella dovrebbe includere solo le colonne essenziali per il nostro calcolo.

Ora possiamo finalmente arrivare alla soluzione. Ecco come apparirebbe il codice finale:

SELECT to_char(created_at::date, 'YYYY-MM') AS anno_mese, round(((sum(valore) - lag(sum(valore), 1) OVER w) / (lag(sum(valore), 1) OVER w)) * 100, 2) AS revenue_diff_pct FROM sf_transactions GRUPPO PER anno_mese WINDOW w AS ( ORDER BY to_char(created_at::date, 'YYYY-MM')) ORDER BY anno_mese ASC

In questo codice, prendiamo due valori di colonna dall'esempio precedente e calcoliamo la differenza tra loro. Tieni presente che utilizziamo anche alias di finestra per ridurre la ripetitività del nostro codice.

Quindi, secondo l'algoritmo, lo dividiamo per le entrate del mese corrente e lo moltiplichiamo per 100 per ottenere il valore percentuale. Infine, arrotondiamo il valore percentuale a due decimali. Arriviamo alla risposta che soddisfa tutti i requisiti del compito. 

Concetto avanzato N4: tassi di abbandono

 
Anche se è l'opposto della crescita, anche il tasso di abbandono è un parametro importante. Molte aziende tengono traccia dei propri tassi di abbandono, soprattutto se il loro modello di business è basato su abbonamento. In questo modo, possono tenere traccia del numero di abbonamenti o account persi e prevederne i motivi. Ci si aspetta che uno scienziato esperto di dati sappia quali funzioni, dichiarazioni e clausole utilizzare per calcolare i tassi di abbandono.

I dati di abbonamento sono molto privati ​​e contengono informazioni private sull'utente. È anche importante che i data scientist sappiano come lavorare con tali dati senza esporli. Spesso il calcolo dei tassi di abbandono coinvolge espressioni di tabella comuni, che sono un concetto relativamente nuovo. I migliori data scientist dovrebbero sapere perché i CTE sono utili e quando utilizzarli. Quando si lavora con database più vecchi, dove i CTE non sono disponibili, un candidato ideale dovrebbe essere comunque in grado di portare a termine il lavoro.

Ecco un esempio di un compito difficile. I candidati che fanno il colloquio presso Lyft ricevono questo incarico per calcolare il tasso di abbandono dei conducenti presso l'azienda.

Per risolvere questo problema, i data scientist devono utilizzare istruzioni case/when, funzioni finestra come LAG(), nonché FROM / WHERE e altre clausole di base. 

Conclusione

 
Lavorare come data scientist per molti anni sembra sicuramente impressionante in un CV e ti farà ottenere molti colloqui. Tuttavia, una volta messo piede nella porta, è comunque necessario mostrare le conoscenze per integrare anni di esperienza. Anche se hai una vasta esperienza in scrivere query in SQL, non fa male usare risorse come Strata Scratch per rinfrescare le tue conoscenze.

 
 
Nato Rosidi è un data scientist e nella strategia di prodotto. È anche un professore a contratto che insegna analisi ed è il fondatore di Strata Scratch, una piattaforma che aiuta i data scientist a prepararsi per le loro interviste con domande di interviste reali delle migliori aziende. Connettiti con lui su Twitter: Strata Scratch or LinkedIn.

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