2 Esempi importanti di SQL CASE WHEN che devi conoscere nel 2023

'2 importanti esempi di SQL CASE WHEN da conoscere nel 2023.'

Data Science

Padroneggia l’approccio alla risoluzione di vere domande CASE WHEN in SQL

Foto di Vincent van Zalinge su Unsplash

Vere domande di intervista che utilizzano CASE WHEN in SQL!

Nell’articolo 5 Concetti SQL Avanzati, puoi esplorare le basi dello statement CASE..WHEN e anche gli esempi di utilizzo dello stesso. Tuttavia, in questi articoli, non ho menzionato l’applicazione del CASE WHEN nel mondo reale.

Pertanto, mi sono collegato con diversi professionisti della scienza dei dati in aziende sportive ed eCommerce su LinkedIn e ho raccolto questi 2 esempi di CASE WHEN in SQL che vengono più frequentemente richiesti nei colloqui di lavoro.

Perché il CASE WHEN è uno dei concetti più richiesti?

Perché, lo statement CASE WHEN in SQL aiuta ad implementare la logica If..Else durante l’interrogazione dei dati.

Spesso è necessario estrarre o aggregare i dati in base a determinate condizioni. Certamente, è possibile utilizzare la clausola WHERE per applicare tali condizioni, ma quando si desidera creare nuove colonne basate su tali condizioni, il CASE..WHEN è utile e bisogna utilizzarlo.

In questo articolo, vedrai 2 domande di intervista reali e comunemente richieste che puoi risolvere utilizzando il CASE WHEN in SQL.

Imparerai un approccio per risolvere queste domande e, alla fine, come utilizzare il CASE WHEN per ottenere l’output desiderato. Inoltre, imparerai come scomporre le query complesse in semplici passaggi facili da seguire.

Per darti una panoramica veloce, ecco le due domande che esplorerai in questa lettura rapida.

· Esempio 1: Creare una tabella dei punti per un torneo sportivo · Esempio 2: Trovare nuovi e ripetuti clienti per un sito web di eCommerce

Ottieni i file CSV degli esempi di dataset alla fine di questo articolo.

Iniziamo con la domanda suggerita da un Analista Sportivo. Di tanto in tanto, il loro team di analisi ha bisogno di creare una tabella dei punti in base alle partite giocate tra diverse squadre.

Ecco perché fanno questa domanda in ogni colloquio di lavoro per analisti dei dati.

Esempio 1: Creare una tabella dei punti per un torneo sportivo

Questo è uno scenario classico di conversione di una tabella da una forma lunga (numero di righe > numero di colonne) a una forma ampia (numero di colonne > numero di righe). Questo è anche chiamato Pivot delle Dati, che è un caso d’uso importante del CASE WHEN in SQL.

In questo scenario, hai una tabella contenente i nomi delle squadre che hanno giocato la partita e il vincitore. Devi creare una tabella dei punti in cui ottieni informazioni su quante partite ha giocato ogni squadra, quante partite hanno vinto, perse e quante partite sono state pareggiate.

Tipo di esempio | Immagine di Autore

Vediamo come affrontare questo tipo di domanda —

Ecco la tabella di input in cui ogni riga corrisponde a una partita tra due squadre e la colonna vincitore indica quale squadra ha vinto. Il valore NULL nella colonna vincitore indica che la partita è finita in pareggio, ovvero nessuna delle squadre ha vinto la partita.

Dati di input | Immagine di Autore

Dividiamo questa domanda nei seguenti sottotask.

  1. Trovare il numero totale di partite vinte da ciascuna squadra
  2. Trovare il numero totale di partite perse da ciascuna squadra
  3. Trovare il numero totale di partite in cui nessuna delle squadre ha vinto
  4. Trovare il numero totale di partite giocate da ciascuna squadra

Per capire quante partite una squadra ha vinto, è necessario capire per ogni partita quale squadra ha vinto la partita. Puoi fare ciò confrontando le colonne team_1 e team_2 con la colonna vincitore.

Quindi, per una riga specifica, quando i valori nelle colonne team_1 e vincitore sono uguali, allora team_1 è il vincitore.

Puoi tradurre esattamente la stessa logica utilizzando l’istruzione CASE..WHEN..THEN in SQL come mostrato di seguito.

SELECT team_1      , team_2      , vincitore      , CASE WHEN team_1 = vincitore THEN 1 ELSE 0 END come bandiera_vittoria      , CASE WHEN vincitore IS NULL THEN 1 ELSE 0 END come bandiera_pareggioFROM analyticswithsuraj.teams

Come mostrato nella query sopra, creerai una colonna aggiuntiva bandiera_vittoria. Quando una squadra è vincitrice, assegnerai il valore 1 a questa colonna. Allo stesso modo, se la colonna vincitore è NULL, assegnerai il valore 1 alla colonna bandiera_pareggio.

Quindi, la query sopra creerà il seguente output per tutte le squadre nella colonna team_1 .

Primo output parziale per team_1 | Immagine dell'autore

Allo stesso modo, quando i valori nelle colonne team_2 e vincitore sono uguali, allora team_2 è il vincitore. Quindi puoi scrivere la stessa query per tutte le squadre in team_2

SELECT team_1      , team_2      , vincitore      , CASE WHEN team_2 = vincitore THEN 1 ELSE 0 END come bandiera_vittoria      , CASE WHEN vincitore IS NULL THEN 1 ELSE 0 END come bandiera_pareggioFROM analyticswithsuraj.teams

dove otterrai il seguente output per i valori in team_2

Output parziale per team_2 | Immagine dell'autore

Bene, le due query sopra sono solo per la tua comprensione. In realtà, puoi creare un singolo CTE per bandiera_vittoria e bandiera_pareggio di ogni squadra nelle colonne team_1 e team_2 come mostrato di seguito.

WITH win_draw_flag AS(SELECT team_1 come squadra      , CASE WHEN team_1 = vincitore THEN 1 ELSE 0 END come bandiera_vittoria      , CASE WHEN vincitore IS NULL THEN 1 ELSE 0 END come bandiera_pareggioFROM analyticswithsuraj.teamsUNION ALLSELECT team_2 come squadra      , CASE WHEN team_2 = vincitore THEN 1 ELSE 0 END come bandiera_vittoria      , CASE WHEN vincitore IS NULL THEN 1 ELSE 0 END come bandiera_pareggioFROM analyticswithsuraj.teams)

Questo creerà un CTE come questo – l’ho mostrato solo per la tua comprensione.

L'output di CTE | Immagine dell'autore

Ricorda che sei ancora nella forma lunga della tabella e ora hai informazioni su quante partite ogni squadra ha vinto.

Successivamente, devi semplicemente aggregare le colonne per ottenere il numero totale di partite che ogni squadra ha giocato, vinto e perso. Puoi farlo con una query semplice come la seguente.

SELECT squadra        , COUNT(*) AS partite_giocate        , SUM(bandiera_vittoria) AS partite_vinte        , COUNT(*) - SUM(bandiera_vittoria) - SUM(bandiera_pareggio) AS partite_perse        , SUM(bandiera_pareggio) AS partite_pareggioFROM win_draw_flagGROUP BY squadraORDER BY squadra

Dove COUNT(*) ti dà il numero totale di volte in cui ogni squadra è apparsa nel CTE win_draw_flag e sottraendo da esso partite vinte e pareggiate ti darà il numero totale di partite perse da ogni squadra.

Output finale - Tabella dei punti | Immagine dell'autore

Senza creare una CTE separatamente, è anche possibile scrivere la query come segue e passare l’intera query CASE..WHEN come una sub-query.

SELECT team        , COUNT(*) AS partite_giocate        , SUM(win_flag) AS partite_vinte        , COUNT(*) - SUM(win_flag) - SUM(draw_flag) AS partite_perse        , SUM(draw_flag) AS partite_pareggiateFROM   (  SELECT team_1 as team        , CASE WHEN team_1 = winner THEN 1 ELSE 0 END as win_flag        , CASE WHEN winner IS NULL THEN 1 ELSE 0 END as draw_flag  FROM analyticswithsuraj.teams  UNION ALL  SELECT team_2 as team        , CASE WHEN team_2 = winner THEN 1 ELSE 0 END as win_flag        , CASE WHEN winner IS NULL THEN 1 ELSE 0 END as draw_flag  FROM analyticswithsuraj.teams  ) AS win_draw_flagGROUP BY teamORDER BY team

Produrrà anche lo stesso output esattamente come sopra menzionato.

Bene, ci possono essere molti modi per risolvere questa domanda — Ho trovato questo approccio più semplice. Se trovi un’altra soluzione a questa domanda, sentiti libero di menzionarla nei commenti.

Esempio 2: Trova nuovi e clienti ripetuti per un sito web di e-commerce

Questo è uno degli esempi classici di confronto delle date e quindi di implementazione della logica If..Else utilizzando l’istruzione CASE..WHEN. Puoi incontrare questo tipo di problema in qualsiasi azienda che si occupa di clienti.

Lo scenario è il seguente — Hai un sito web di e-commerce in cui ogni giorno i clienti visitano e acquistano prodotti. Il tuo compito è identificare ogni giorno quanti clienti erano nuovi e quanti clienti erano ripetuti.

Tipo di domanda | Immagine di Autore

Ecco una tabella di input — ordini — in cui puoi vedere i clienti con l’ID cliente ABC101, BCD201 e ABD101 che hanno visitato il sito web in più giorni e acquistato prodotti diversi.

Tabella di input con dati di esempio | Immagine di Autore

Suddividiamo la domanda nei seguenti sotto-task —

  1. Trovare la prima volta, cioè la prima data in cui il cliente ha visitato il sito web
  2. Confrontare la prima data con la data dell’ordine per decidere se il cliente è un visitatore ripetuto o di prima volta

Puoi risolvere facilmente il primo sotto-task utilizzando GROUP BY per raggruppare tutti i record per customer_id e trovare il minimo di order_date, come mostrato di seguito.

SELECT customer_id      , MIN(order_date) as first_order_dateFROM analyticswithsuraj.ordersGROUP BY customer_id
Data minima dell'ordine | Immagine di Autore

Questo era semplice!

Successivamente, per confrontare first_order_date con ogni order_date, prima devi portare entrambe le colonne in una singola tabella.

Puoi farlo facilmente usando JOIN su customer_id come mostrato di seguito. Qui puoi creare una CTE utilizzando la query precedente in modo da ottenere una tabella temporanea da unire con la tabella di input.

WITH first_orders AS(SELECT customer_id      , MIN(order_date) as first_order_dateFROM analyticswithsuraj.ordersGROUP BY customer_id)SELECT t1.*      , t2.first_order_dateFROM analyticswithsuraj.orders AS t1INNER JOIN first_orders AS t2 ON t1.customer_id = t2.customer_id
Output Inner Join | Immagine di Autore

Ora, avendo entrambe le colonne in una singola tabella, puoi confrontare order_date con first_order_date e implementare la seguente logica If..Else.

  1. Se first_order_date & order_date sono uguali allora il cliente è un nuovo cliente
  2. Se first_order_date & order_date sono diversi allora il cliente è un cliente ripetuto

Quindi idealmente, è necessario creare due colonne per implementare le due dichiarazioni If..Else utilizzando CASE WHEN in SQL.

Non è necessario creare una tabella separata, ma è possibile aggiungere altre due colonne nella query sopra dove si sono unite due tabelle. Ecco come può essere fatto.

WITH first_orders AS(SELECT customer_id      , MIN(order_date) as first_order_dateFROM analyticswithsuraj.ordersGROUP BY customer_id)SELECT t1.*      , t2.first_order_date      , CASE WHEN t1.order_date=t2.first_order_date THEN 1 ELSE 0 END AS new_customer_flag      , CASE WHEN t1.order_date!=t2.first_order_date THEN 1 ELSE 0 END AS repeat_customer_flagFROM analyticswithsuraj.orders AS t1INNER JOIN first_orders AS t2 ON t1.customer_id = t2.customer_id
Output di CASE WHEN creazione di due nuove colonne | Immagine di Author

Come risultato, la colonna new_customer_flag sarà 1 quando le colonne first_order_date e order_date sono uguali. Allo stesso modo, la colonna repeat_customer_flag sarà 1 quando le colonne first_order_date e order_date sono diverse.

Ora l’ultimo passo è solo raggruppare tutti i record per data dell’ordine e sommare le colonne new_customer_flag e repeat_customer_flag.

Per fare questo, avrai bisogno della tabella sopra che puoi ottenere creando un’altra CTE come mostrato di seguito.

WITH first_orders AS(SELECT customer_id      , MIN(order_date) as first_order_dateFROM analyticswithsuraj.ordersGROUP BY customer_id),customers AS(SELECT t1.*      , t2.first_order_date      , CASE WHEN t1.order_date=t2.first_order_date THEN 1 ELSE 0 END AS new_customer_flag      , CASE WHEN t1.order_date!=t2.first_order_date THEN 1 ELSE 0 END AS repeat_customer_flagFROM analyticswithsuraj.orders AS t1INNER JOIN first_orders AS t2 ON t1.customer_id = t2.customer_id)SELECT order_date      , SUM(new_customer_flag) AS numero_di_nuovi_clienti      , SUM(repeat_customer_flag) AS numero_di_clienti_ripetutiFROM customersGROUP BY order_dateORDER BY order_date
Numero di nuovi e clienti ripetuti ogni giorno | Immagine di Author

In questo modo otterrai l’output richiesto. Puoi verificare i risultati confrontandoli con la tabella di input.

Di nuovo, puoi avere un approccio diverso per risolvere questa domanda – questo è l’approccio più semplice che ho trovato. Non dimenticare di menzionare il tuo approccio nei commenti qui sotto.

In definitiva,

In questo articolo hai imparato come affrontare scenari reali di utilizzo di CASE WHEN in SQL e scomporre query SQL complesse in query semplici. Spero che tu abbia apprezzato questo articolo.

Case study e domande basate su CASE WHEN, RANK(), ROW_NUMBER() e GROUP BY sono comuni nei colloqui di lavoro nel campo della scienza dei dati. Scomporre la domanda in sotto-task più piccoli mostra all’intervistatore il tuo approccio e il tuo processo di pensiero per risolvere la domanda.

Pertanto, questo argomento sarà sicuramente utile per affinare le tue competenze in SQL CASE WHEN, GROUP BY e per avere successo nel tuo prossimo colloquio di lavoro.

Interessato a leggere altre storie su VoAGI??

💡 Considera diventare un membro di VoAGI per accedere a storie illimitate su VoAGI e alla newsletter giornaliera di VoAGI. Riceverò una piccola parte della tua quota e non avrai alcun costo aggiuntivo.

💡 Assicurati di registrarti e unirti ad altre 200 persone per non perdere altri articoli su guide, trucchi e consigli di scienza dei dati e le migliori pratiche in SQL e Python.

Grazie per aver letto!

Dataset: Questi sono i dataset di prova che ho creato per questi esempi. Puoi scaricarli gratuitamente dal mio repository su Github – Esempio 1 e Esempio 2.