Le 5 principali domande di intervista su SQL CTE

Ecco cinque domande (e soluzioni!) sulle CTE che (probabilmente) vi verranno poste durante un colloquio.

Non sto dicendo che riceverete queste domande sulle CTE SQL a ogni colloquio di lavoro. Ma quando vi capiterà, probabilmente saranno proprio come le cinque che sto per illustrarvi.

A parte le domande teoriche sulle CTE, non ci sono molte variazioni negli scenari CTE valutati dagli intervistatori. Esaminando questi cinque esempi, otterrete una buona base per superare il vostro colloquio!

Per questo, è necessario sapere cosa sono le espressioni comuni di tabella SQL e come funziona la loro sintassi.

Siete pronti a dare un'occhiata alle domande? Andiamo!

Tabella per le domande 1, 2 e 3

Per la prima serie di domande, utilizzeremo la tabella employees. Ecco come appare:

id	first_name	last_name	department	salary	manager_id
1	Angelika	Voules	Marketing	5,293.74	2
2	Rozelle	Swynley	Marketing	8,295.08	18
3	Warren	Willey	Engineering	9,126.72	19
4	Lynelle	Whiten	Management Board	10,716.15	NULL
5	Consolata	Roman	Legal	8,456.06	4
6	Hoebart	Baldock	Research and Development	4,817.34	20
7	Starlene	Watkiss	Accounting	6,541.48	4
8	Barde	Ribbens	Marketing	4,852.87	2
9	Lorne	Philipsen	Engineering	7,235.59	3
10	Pedro	Naldrett	Research and Development	5,471.62	20
11	Brina	Dillinger	Marketing	6,512.17	2
12	Verile	Sonley	Research and Development	4,574.41	20
13	Noble	Geerling	Research and Development	8,391.18	20
14	Garey	MacAdam	Accounting	3,829.88	7
15	Theo	Sorrell	Engineering	6,441.67	3
16	Erminie	Gelling	Research and Development	8,590.70	20
17	Loralie	Koop	Accounting	5,248.46	7
18	Cal	Andrey	Management Board	11,258.82	NULL
19	Quincey	Gamell	Management Board	11,366.52	NULL
20	Janith	McGiffie	Research and Development	7,428.83	19

Cosa dicono i dati? La prima riga, ad esempio, dice che Angelika Voules lavora nel settore Marketing e il suo stipendio è di 5.293,74. Il suo capo ha un ID manager pari a 2; consultando la colonna id, si scopre che il capo di Angelika Voules è Rozelle Swynley.

Ci sono tre righe con valori NULL nella colonna manager_id:

id	first_name	last_name	department	salary	manager_id
4	Lynelle	Whiten	Management Board	10,716.15	NULL
18	Cal	Andrey	Management Board	11,258.82	NULL
19	Quincey	Gamell	Management Board	11,366.52	NULL

Significa che questi tre employees non hanno manager. Questo ha senso perché tutti e tre fanno parte del consiglio di amministrazione.

Vediamo ora le domande del colloquio.

Domanda di intervista 1: Trovare lo stipendio medio per reparto

Utilizzando la tabella dipendenti, mostrare tutti i dipendenti, i loro reparti, gli stipendi e lo stipendio medio nel rispettivo reparto. Ordinare il risultato per reparto.

Soluzione Query

WITH avg_salary AS (
		SELECT	AVG(salary) AS average_salary,
				department
		FROM employees
		GROUP BY department)
		
SELECT	e.first_name,
		e.last_name,
		e.department,
		e.salary,
		avgs.average_salary	
FROM employees e
JOIN avg_salary avgs
ON e.department = avgs.department
ORDER BY department;

Spiegazione della soluzione

Questa query utilizza una CTE denominata avg_salary per calcolare la retribuzione media per reparto. L'istruzione SELECT nella CTE raggruppa le righe per reparto e utilizza la funzione aggregata AVG() per calcolare la media per ogni reparto.

Una volta ottenuta la media, la si combina con altre colonne della tabella employees per completare la risposta alla domanda dell'intervista. Per farlo, uniamo la tabella employees con la CTE, come faremmo con due tabelle qualsiasi. Selezioniamo le colonne first_name, last_name, department e salary dalla tabella employeese la colonna average_salary dalla CTE. Per comodità, utilizziamo degli alias per la tabella e la CTE. Infine, ordiniamo i risultati per reparto.

La tabella dei risultati

Ecco come appare il risultato:

first_name	last_name	department	salary	average_salary
Garey	MacAdam	Accounting	3,829.88	5,206.61
Loralie	Koop	Accounting	5,248.46	5,206.61
Starlene	Watkiss	Accounting	6,541.48	5,206.61
Warren	Willey	Engineering	9,126.72	7,601.33
Lorne	Philipsen	Engineering	7,235.59	7,601.33
Theo	Sorrell	Engineering	6,441.67	7,601.33
Consolata	Roman	Legal	8,456.06	8,456.06
Cal	Andrey	Management Board	11,258.82	11,113.83
Quincey	Gamell	Management Board	11,366.52	11,113.83
Lynelle	Whiten	Management Board	10,716.15	11,113.83
Angelika	Voules	Marketing	5,293.74	6,238.47
Rozelle	Swynley	Marketing	8,295.08	6,238.47
Barde	Ribbens	Marketing	4,852.87	6,238.47
Brina	Dillinger	Marketing	6,512.17	6,238.47
Noble	Geerling	Research and Development	8,391.18	6,545.68
Erminie	Gelling	Research and Development	8,590.70	6,545.68
Verile	Sonley	Research and Development	4,574.41	6,545.68
Pedro	Naldrett	Research and Development	5,471.62	6,545.68
Hoebart	Baldock	Research and Development	4,817.34	6,545.68
Janith	McGiffie	Research and Development	7,428.83	6,545.68

Spiegazione del risultato

Questa tabella ci dice che, ad esempio, lo stipendio di Starlene Watkiss è di 6.541,48, mentre lo stipendio medio nel suo reparto (Contabilità) è di 5.206,61.

first_name	last_name	department	salary	average_salary
Starlene	Watkiss	Accounting	6,541.48	5,206.61

È possibile interpretare allo stesso modo le altre righe del risultato.

Domanda di intervista 2: Trovare lo stipendio più alto per reparto

Trovate il dipendente con lo stipendio più alto in ogni reparto. Indicare nome e cognome, stipendio e reparto.

Soluzione Query

WITH highest_salary AS (
		SELECT	first_name,
				last_name,
				department,
				salary,
				RANK () OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
		FROM employees)

SELECT	first_name,
		last_name,
		salary,
		department
FROM highest_salary
WHERE salary_rank = 1;

Spiegazione della query risolutiva

Questa volta la CTE si chiama highest_salary. La utilizziamo per classificare i dipendenti di ciascun reparto in base allo stipendio. Come? Utilizzando la funzione finestra RANK().

Si partizionano i dati in base alla colonna reparto. Ciò significa che classifichiamo gli stipendi solo all'interno di ciascun reparto, non in tutta l'azienda. I dati sono ordinati in base alla colonna stipendio in ordine decrescente, perché vogliamo che il più alto salary nel reparto abbia il rango 1.

Selezioniamo anche diverse colonne dalla tabella employees nella stessa CTE. Abbiamo bisogno dei dati di questa CTE nella prossima istruzione SELECT.

In questa istruzione SELECT selezioniamo le colonne necessarie per rispondere alla domanda dell'intervista. Tutte le colonne provengono dalla CTE. Filtriamo il risultato utilizzando la clausola WHERE per ottenere solo le righe in cui il grado di stipendio è 1, ovvero le righe con lo stipendio più alto.

La tabella dei risultati

first_name	last_name	salary	department
Starlene	Watkiss	6,541.48	Accounting
Warren	Willey	9,126.72	Engineering
Consolata	Roman	8,456.06	Legal
Quincey	Gamell	11,366.52	Management Board
Rozelle	Swynley	8,295.08	Marketing
Erminie	Gelling	8,590.70	Research and Development

Spiegazione del risultato

Non è difficile interpretare il risultato. Prendiamo come esempio questo dipendente:

first_name	last_name	salary	department
Consolata	Roman	8,456.06	Legal

I dati mostrati ci dicono che Consolata Roman ha lo stipendio più alto nel reparto legale, pari a 8.456,06.

Passiamo alla terza domanda di intervista SQL CTE.

Domanda di intervista 3: Trovare tutti i dipendenti sotto un manager specifico

Trovare tutti i dipendenti che lavorano direttamente o indirettamente sotto il dipendente il cui ID è 18.

Soluzione Query

WITH RECURSIVE subordinates AS (
		SELECT	id,
				first_name,
				last_name,
				manager_id
		FROM employees
		WHERE id = 18

	UNION

		SELECT	e.id,
				e.first_name,
				e.last_name,
				e.manager_id
		FROM employees e
JOIN subordinates s
ON e.manager_id = s.id
)
		
SELECT *
FROM subordinates
WHERE id != 18;

Spiegazione della soluzione

In questo esempio, non si tratta di una normale CTE, ma di una CTE ricorsiva per ottenere il risultato. La differenza di sintassi è WITH RECURSIVE invece di WITH. La CTE si chiama subordinatese poi viene aggiunta l'istruzione SELECT, proprio come nelle CTE non ricorsive.

Questa istruzione seleziona alcune colonne della tabella employees ma solo per il dipendente il cui ID è 18. Quindi, si utilizza l'operatore UNION per collegare il risultato di questa istruzione SELECT con il risultato di un'altra istruzione SELECT. Per questo, entrambe le istruzioni SELECT devono avere le stesse colonne.

La seconda istruzione SELECT nella CTE seleziona le colonne in cui manager_id (dalla tabella employees) è uguale a id (dalla CTE).

Si ottengono tutti i dati relativi al dipendente il cui ID è 18. Poi troviamo i subordinati diretti e, per ricorsione, i subordinati dei subordinati, fino a scendere nella gerarchia dell'organizzazione.

Quindi prendiamo le colonne dei subordinati della CTE e rimuoviamo il dipendente il cui ID è 18. Ecco fatto!

La tabella dei risultati

id	first_name	last_name	manager_id
2	Rozelle	Swynley	18
1	Angelika	Voules	2
8	Barde	Ribbens	2
11	Brina	Dillinger	2

La spiegazione dei risultati

Il capo diretto di Rozelle Swynley è il dipendente il cui ID del manager è 18. Ma Rozelle ha anche dei subordinati. Si tratta di Angelika Voules, Barde Ribbens e Brina Dillinger. Lo sappiamo perché la tabella mostra che l'ID del loro manager è 2, ovvero l'ID di Rozelle Swynley. Sono i diretti subordinati di Rozelle Swynley e sono anche i subordinati indiretti del dipendente il cui ID è 18. Il dipendente il cui ID è 18 è Calle Ribbens e Brina Dillinger. Il dipendente il cui ID è 18 è Cal Andrey, che non viene mostrato nel risultato perché viene filtrato nella clausola WHERE.

Tabelle utilizzate per le domande 4 e 5

Per le ultime due domande del colloquio, utilizzeremo le tabelle customers e orders.

La tabella customers si presenta come segue:

id	first_name	last_name
1	Simon	Paulson
2	Dylan	Bobson
3	Reb	Mackennack

La tabella orders è un po' più grande:

id	order_date	order_amount	customer_id
1	2021-10-01	42.12	3
2	2021-10-01	415.63	1
3	2021-10-02	84.99	2
4	2021-10-02	28.96	3
5	2021-10-02	54.31	1
6	2021-10-03	74.26	1
7	2021-10-03	77.77	2
8	2021-10-03	55.70	3
9	2021-10-04	16.94	3
10	2021-10-04	51.44	1
11	2021-10-05	41.58	3
12	2021-10-06	95.00	1

Questa seconda tabella contiene i dati relativi agli ordini effettuati. La colonna customer_id è una chiave esterna alla chiave primaria della prima tabella, in modo da poter identificare il cliente che ha effettuato l'ordine. Prendiamo come esempio la prima riga:

id	order_date	order_amount	customer_id
1	2021-10-01	42.12	3

Si tratta di un ordine effettuato il 1° ottobre 2021. Il valore della merce ordinata è di 42,12 e l'ordine è stato effettuato da un cliente il cui ID è 3. Dalla tabella si evince che si tratta di Reb Mackackack. customerssi vede che si tratta di Reb Mackennack.

Vediamo i compiti da risolvere utilizzando queste tabelle.

Domanda di intervista 4: Trovare il numero medio di ordini

Questa domanda chiede di utilizzare un'espressione di tabella comune SQL per trovare il numero medio di ordini per cliente.

Soluzione Query

WITH orders_count AS (
		SELECT	customer_id,
				COUNT(*) AS no_of_orders
		FROM orders
		GROUP BY customer_id)

SELECT	AVG(no_of_orders) AS avg_no_of_orders
FROM orders_count;

Spiegazione della soluzione

Per ottenere il risultato, occorre innanzitutto contare il numero di ordini per cliente. A tale scopo si utilizza la funzione CTE orders_count e la funzione COUNT(). Questa funzione conta il numero di righe nella tabella orders. Poiché non siamo interessati al conteggio totale degli ordini, ma piuttosto al numero di ordini per cliente, raggruppo il risultato in base alla colonna customer_id.

Ora che abbiamo il numero di ordini, è facile calcolare il numero medio. È sufficiente utilizzare la funzione AVG() nell'istruzione SELECT per ottenere la risposta alla domanda.

La tabella dei risultati

avg_no_of_orders
4

La spiegazione del risultato

Una tabella molto piccola richiede una spiegazione molto breve. La tabella precedente mostra semplicemente che il numero medio di ordini per cliente è quattro.

Domanda di intervista 5: Trovare il numero di giorni consecutivi con un ordine

In questo esempio CTE, si deve calcolare per quanti giorni di fila ogni cliente effettua un ordine. In pratica, si chiede di calcolare la lunghezza di una serie contigua senza spazi vuoti. Si noti che tutti gli ordini si riferiscono allo stesso mese.

Soluzione Query

WITH groupings_by_date AS (
	SELECT	c.id,
			c.first_name,
			c.last_name,
			RANK() OVER (PARTITION BY c.id ORDER BY o.order_date) AS row_number,
			o.order_date,
			EXTRACT(DAY FROM o.order_date) - RANK() OVER (PARTITION BY c.id ORDER BY o.order_date) AS date_group
FROM customers c
JOIN orders o
ON c.id = o.customer_id
)

SELECT	id,
		first_name,
		last_name,
		COUNT(*) AS orders_in_row
FROM groupings_by_date
GROUP BY id, first_name, last_name, date_group;

Spiegazione della soluzione

Questa query piuttosto lunga inizia con una CTE. Seleziona alcune colonne dalle tabelle customers e orders. C'è la funzione RANK() come nella domanda 2. Questa volta, utilizziamo questa funzione per assegnare un numero di riga a ogni ordine all'interno dello stesso cliente. Per questo motivo, i dati vengono suddivisi in base all'id della colonna. La classificazione viene eseguita in base alla data dell'ordine (vogliamo che la classificazione sia sequenziale).

L'altra funzione utilizzata è EXTRACT(). Il suo scopo è quello di estrarre la parte del giorno dalla data dell'ordine in modo da poterne dedurre il numero di riga.

Perché lo facciamo? Stiamo semplicemente dando a un gruppo di ordini consecutivi un nome comune date_group. Se si esegue solo questa CTE, le prime due righe del risultato appaiono come queste:

id	first_name	last_name	row_number	order_date	date_group
1	Simon	Paulson	1	2021-10-01	0
1	Simon	Paulson	2	2021-10-02	0

Come si può notare, Simon Paulson ha effettuato ordini sia il 1° che il 2 ottobre 2021. Poiché sono stati effettuati due giorni di seguito, appartengono allo stesso date_group.

Come si ottiene questo dato? È il giorno estratto da order_date meno row_number.

Nella prima riga, il giorno della data '2021-10-01' è 1. Anche il numero della riga è 1. Quindi, è 1-1 = 0, che è anche il valore in date_group. La seconda riga è 2-2 = 0, che è lo stesso date_group di cui sopra.

Il valore specifico di date_group non ha molta importanza! È importante solo che i giorni consecutivi abbiano lo stesso valore date_group. Questo è un piccolo trucco per calcolare la lunghezza di una serie. Funziona perché, se gli ordini vengono effettuati ogni giorno, la differenza tra il numero di giorni in order_date e il valore in row_number è sempre la stessa per lo stesso cliente.

Si noti che questi dati non appaiono da nessuna parte. Lo mostro solo per spiegare meglio cosa fa la CTE.

È importante sottolineare che questo trucco funziona solo se i dati sono tutti dello stesso mese. Se la serie, ad esempio, inizia il 2021-10-31 e arriva al 2021-11-01, il trucco non funziona; questi due giorni, anche se sono uno dopo l'altro, non appartengono allo stesso date_group. Quindi, prima di decidere di utilizzare questo trucco per ottenere la lunghezza di una serie, è necessario comprendere i propri dati.

Una volta ottenuta la CTE, utilizzatela come una tabella nell'istruzione SELECT. Utilizzerò la funzione COUNT() per ottenere il numero di ordini in una riga. Voglio vedere il risultato per ogni cliente, quindi raggruppo i dati per id, first_name e last_name. Raggruppo anche per la colonna date_group, in modo che gli ordini effettuati dallo stesso cliente vengano separati se c'è uno spazio tra loro.

La tabella dei risultati

id	first_name	last_name	orders_in_row
1	Simon	Paulson	4
3	Reb	Mackennack	5
2	Dylan	Bobson	2
1	Simon	Paulson	1

Spiegazione del risultato

Questa tabella mostra che Simon Paulson ha effettuato quattro ordini di fila. Reb Mackennack lo ha fatto per cinque giorni di fila, mentre Dylan Bobson ha effettuato solo due ordini di fila. Infine, c'è un ulteriore ordine di Simon Paulson.

Quando sono utili i CTE?

Come si vede in questi esempi, le CTE sono molto utili quando si deve calcolare qualcosa in almeno due fasi. Si usa una CTE per preparare il calcolo preliminare e poi si usa semplicemente l'istruzione SELECT, si fa riferimento alla CTE e si esegue un altro livello di calcolo.

Nei calcoli più complessi, si possono usare anche più CTE o addirittura CTE annidate. È simile alle subquery, ma le CTE rendono il codice più leggibile e più facile da suddividere in fasi. Inoltre, con le CTE annidate, è possibile scrivere una query e fare immediatamente riferimento ad essa come a qualsiasi altra tabella.

Alcuni di questi esempi mostrano che è possibile utilizzare anche funzioni di aggregazione e di finestra. Questo rende le CTE più potenti.

Inoltre, le CTE sono ottime per i dati gerarchici, come le strutture organizzative, e per l'attraversamento di un grafo. In questi casi, tuttavia, è necessario scrivere una CTE ricorsiva, come ho fatto nella domanda 3.

Infine, se volete scrivere query ricorsive e utilizzarle, il primo passo è imparare le CTE. Se avete bisogno di aiuto, ecco una guida su come imparare le CTE.

Come si può vedere, le CTE hanno molti vantaggi e utilizzi pratici. Questi esempi mostrano solo un assaggio di soluzioni a problemi pratici che richiederebbero molto più tempo se non fosse per le CTE. Ne volete altri? Nessun problema, date un'occhiata ad altri usi pratici delle CTE.

Una volta imparate le CTE e iniziate a usarle, cercate di pensare prima al problema. Scomponete le fasi di calcolo e poi traducete questa logica in un codice di espressione di tabella comune SQL. Le CTE sono adatte a questo scopo.

Poiché si tratta di una versione delle tabelle temporanee, è necessario essere chiari nel nominarle: utilizzare un nome che indichi immediatamente l'attività della CTE ed essere coerenti con le convenzioni di denominazione. In generale, è bene seguire le best practice delle CTE, che facilitano la lettura del codice da parte vostra (e degli altri!).

Volete saperne di più sulle espressioni di tabella comuni di SQL?

Se vi state preparando per un colloquio di lavoro in SQL, se volete migliorare il vostro attuale lavoro o semplicemente se volete imparare qualcosa di nuovo nel tempo libero, date un'occhiata al nostro Recursive Queries corso.

Qui troverete un approccio sistematico alle CTE, che vi spiegherà la teoria alla base, vi mostrerà la sintassi e vi fornirà alcuni esempi per fare pratica. L'intero corso fa parte della Advanced SQL che tratta altri due argomenti: le funzioni di finestra (che abbiamo usato in questo articolo) e le estensioni di GROUP BY. Buona fortuna!