Domande di pratica su SQL intermedio

Siete pronti a portare le vostre competenze SQL a un livello superiore? Queste domande intermedie vi aiuteranno ad esercitarvi in scenari reali utilizzando GROUP BY, CASE WHEN, subquery, CTE e altro ancora.

Se avete già acquisito le basi dell'SQL e vi sentite a vostro agio nella scrittura di semplici query, è il momento di salire di livello. Le competenze SQL intermedie sono fondamentali per gestire i report del mondo reale, le analisi più approfondite e la risoluzione di compiti più complessi che vanno oltre le istruzioni di base di SELECT.

In questo articolo, vi eserciterete su tecniche essenziali come GROUP BY, CASE WHEN, subquery e Common Table Expressions (CTE). Questi esercizi si basano su scenari realistici e sono quindi ideali per gli aspiranti analisti di dati, gli sviluppatori e tutti coloro che si preparano ai colloqui tecnici. Provate a risolvere ogni sfida prima di verificare la soluzione per affinare le vostre capacità di problem solving.

Se desiderate approfondire questi concetti in modo più strutturato, date un'occhiata al corso Come creare semplici report SQL su LearnSQL.it. Si tratta di un corso pratico, adatto ai principianti, incentrato sulla trasformazione di dati grezzi in report significativi: un passo successivo perfetto dopo aver completato questa serie di domande pratiche.

Database per gli esercizi

Per risolvere gli esercizi di questo articolo, si utilizzeranno tre tabelle: clienti, prodotti e acquisti.

• La tabella clienti include: ID cliente, nome, cognome, e-mail, data di iscrizione, città e paese.
• La tabella dei prodotti contiene: product_id, name, category, price e launch_date.
• La tabella purchase registra le transazioni e comprende: purchase_id, customer_id, product_id, quantity, total_amount, e purchase_date.
  

Esercizio 1: Riepilogo mensile delle entrate

Esercizio:
Suddividere i dati di acquisto per anno e mese e mostrare il numero di ordini (orders) e le entrate totali (revenue) per ogni periodo.

Soluzione:

SELECT
	EXTRACT(YEAR FROM purchase_date) AS purchase_year,
	EXTRACT(MONTH FROM purchase_date) AS purchase_month,
	COUNT(*) AS orders,
	SUM(total_amount) AS revenue
FROM purchase
GROUP BY
	EXTRACT(YEAR FROM purchase_date),
	EXTRACT(MONTH FROM purchase_date)
ORDER BY
	purchase_year,
	purchase_month;

Spiegazione:

Per risolvere questo problema, è necessario raggruppare gli acquisti per anno e per mese, in modo che ogni periodo (ad esempio, gennaio 2023, febbraio 2023) sia trattato separatamente. Si inizia estraendo l'anno e il mese da purchase_date utilizzando la funzione EXTRACT, che fa parte dello standard SQL ed è ampiamente supportata. Alcuni database possono avere delle alternative, come DATEPART o TO_CHAR. Raggruppando per purchase_year e purchase_month si evita che tutti gli acquisti di gennaio di anni diversi vengano combinati in un unico gruppo. Quindi contiamo il numero di ordini e sommiamo i ricavi totali per ogni periodo.

Esercizio 2: Trovare gli ordini recenti

Esercizio:

Trovare tutti gli acquisti effettuati negli ultimi 30 giorni. Visualizzare tutte le colonne della tabella degli acquisti.

Soluzione:

SELECT *
FROM purchase
WHERE purchase_date >= CURRENT_DATE - INTERVAL 30 DAY;

Spiegazione:

Per trovare gli acquisti degli ultimi 30 giorni, si filtra la tabella purchase utilizzando una condizione di data. Si confronta purchase_date con la data corrente meno un intervallo di 30 giorni. La parola chiave CURRENT_DATE indica la data odierna e INTERVAL 30 DAY è un modo standard per sottrarre i giorni in ANSI SQL. In questo modo si garantisce che vengano restituite solo le righe in cui l'acquisto è avvenuto negli ultimi 30 giorni. Alcuni database possono utilizzare una sintassi leggermente diversa per gli intervalli di date, ma la logica rimane la stessa.

Esercizio 3: Prodotti con prezzi superiori alla media della loro categoria

Esercizio:

Restituire tutti i prodotti che hanno un prezzo superiore alla media della loro categoria. Mostrare tutte le colonne della tabella degli acquisti.

Soluzione:

SELECT *
FROM product p
WHERE price >
  	(SELECT AVG(price)
   	FROM product
   	WHERE category = p.category);

Spiegazione:

Per risolvere questo problema, confrontiamo il prezzo di ogni prodotto con il prezzo medio dei prodotti della stessa categoria. Utilizziamo una sottoquery che calcola la media price per un determinato category e poi verifichiamo se il prezzo del prodotto corrente è superiore a tale media. Si tratta di una sottoquery correlata: viene eseguita una volta per ogni riga della query esterna, utilizzando p.category per abbinare le categorie. Utilizziamo SELECT * per restituire tutte le colonne della tabella dei prodotti che soddisfano la condizione.

Esercizio 4: Segmenti di prezzo: Budget, Middle, Premium

Esercizio:

Assegnare a ciascun prodotto un segmento di prezzo: "budget" per i prodotti di prezzo inferiore a 20 dollari, "middle" per i prodotti di prezzo compreso tra 20 e 99,99 dollari e "premium" per i prodotti di prezzo pari o superiore a 100 dollari.

Soluzione:

SELECT
	product_id,
	name,
	price,
	CASE
    		WHEN price < 20 THEN 'budget'
    		WHEN price BETWEEN 20 AND 99.99 THEN 'middle'
    		ELSE 'premium'
	END AS price_segment
FROM product;

Spiegazione:

In questa query, si usa l'espressione CASE per assegnare ogni prodotto a un segmento di prezzo in base al suo price. La sintassi CASE funziona come una struttura if-else: verifica le condizioni in ordine e restituisce il valore corrispondente. In questo caso, i prodotti sotto i 20 dollari sono etichettati come 'budget', quelli tra i 20 e i 99,99 dollari sono 'middle', e quelli con 100 dollari o più sono 'premium'. Il risultato include i dettagli del prodotto originale e una nuova colonna price_segment.

Esercizio 5: Raggruppare i clienti per regione

Esercizio:
Raggruppare i clienti in regioni in base al loro Paese. Si supponga che paesi come 'USA', 'Canada' e 'Mexico' appartengano a 'North America', mentre tutti gli altri rientrano in 'Other'. Per ogni regione, contare quanti clienti vi sono assegnati. Il risultato deve includere region e customer_count.

Soluzione:

SELECT
	CASE
    	WHEN country IN ('USA', 'Canada', 'Mexico') THEN 'North America'
    	ELSE 'Other'
	END AS region,
	COUNT(*) AS customer_count
FROM customer
GROUP BY
	CASE
    	WHEN country IN ('USA', 'Canada', 'Mexico') THEN 'North America'
    	ELSE 'Other'
	END;

Spiegazione:

Questa query utilizza l'espressione CASE per raggruppare i paesi in regioni personalizzate: 'North America' per 'USA', 'Canada', e 'Mexico', e 'Other' per il resto. CASE viene spesso utilizzato per creare categorie personalizzate in base ai valori delle colonne. Una volta definite queste regioni personalizzate, possiamo raggrupparle per contare quanti clienti rientrano in ciascuna di esse. Questo è uno schema comune quando si lavora con dati geografici, livelli di prezzo o qualsiasi logica di classificazione personalizzata.

Esercizio 6: Spesa dei clienti per categoria

Esercizio:

Per ogni cliente, calcolare quanto ha speso per i prodotti delle categorie "Elettronica", "Abbigliamento" e "Casa". Il risultato deve includere customer_id, electronics_spend, clothing_spend e home_spend come nomi di colonna.

Soluzione:

SELECT
	customer_id,
	SUM(CASE 
WHEN category = 'Electronics' 
THEN total_amount ELSE 0 END) AS electronics_spend,
	SUM(CASE 
WHEN category = 'Clothing'	
THEN total_amount ELSE 0 END) AS clothing_spend,
	SUM(CASE 
WHEN category = 'Home'    	
THEN total_amount ELSE 0 END) AS home_spend
FROM purchase  p
JOIN product pr 
ON p.product_id = pr.product_id
GROUP BY customer_id;

Spiegazione:

In questa query, calcoliamo quanto ogni cliente ha speso in prodotti di categorie specifiche utilizzando CASE WHEN all'interno di funzioni aggregate. CASE ci permette di controllare la categoria di ogni prodotto e di restituire total_amount solo se corrisponde a quella che ci interessa, altrimenti restituisce 0. Ripetiamo questo schema per 'Electronics', 'Clothing', e 'Home', e avvolgiamo ciascuno di essi in un SUM() per ottenere la spesa totale per categoria. Il risultato include una riga per customer_id con colonne separate per la spesa in ogni categoria. Questa tecnica è utile per creare riepiloghi in stile pivot direttamente in SQL.

Esercizio 7: Spesa media per cliente

Esercizio:
Calcolare l'importo totale speso da ciascun cliente, quindi restituire la media dei totali dei clienti. Il risultato finale deve includere una sola colonna: avg_customer_spend.

Soluzione:

WITH customer_spend AS (
	SELECT customer_id, SUM(total_amount) AS spend
	FROM purchase
	GROUP BY customer_id
)
SELECT AVG(spend) AS avg_customer_spend
FROM customer_spend;

Spiegazione:

Per risolvere questo problema, si calcola innanzitutto l'importo totale speso da ciascun cliente raggruppando i dati di purchase per customer_id e sommando i dati di total_amount. Questo viene fatto in una Common Table Expression (CTE) denominata customer_spend. Poi, nella query principale, calcoliamo la media di questi totali usando AVG(spend) e la restituiamo come avg_customer_spend. Questo approccio consente di separare i calcoli intermedi e di mantenere la query pulita e leggibile.

Esercizio 8: Clienti superiori alla media

Esercizio:

Trovare l'importo totale speso da ciascun cliente e la spesa media di tutti i clienti. Restituire solo i clienti la cui spesa totale è superiore alla media. Il risultato deve includere le colonne customer_id e total_spend.

Soluzione:

WITH customer_spend AS ( -- total spend per customer
	SELECT
    	customer_id,
    		SUM(total_amount) AS total_spend
	FROM purchase
	GROUP BY customer_id
), avg_spend AS ( -- 2) average of those totals
	SELECT
    	AVG(total_spend) AS avg_total_spend
	FROM customer_spend
)
SELECT
	cs.customer_id,
	cs.total_spend
FROM customer_spend cs, avg_spend a -- join to include the average in each row
WHERE cs.total_spend > a.avg_total_spend
ORDER BY cs.total_spend DESC;

Spiegazione:

Si inizia calcolando la spesa totale per cliente con una CTE chiamata customer_spend. Poi, in una seconda CTE chiamata avg_spend, si calcola la media di questi totali. Nella query principale, confrontiamo la spesa totale di ciascun cliente con la media unendo entrambe le CTE. I clienti il cui total_spend è maggiore della media vengono restituiti, insieme al loro customer_id. L'uso di due CTE rende la logica più facile da seguire e mantiene la query pulita.

Esercizio 9: Vendite di prodotti in percentuale del totale

Esercizio:

Calcolare il fatturato totale per ogni prodotto. Quindi, per ogni prodotto, calcolare la percentuale che contribuisce al fatturato complessivo. Il risultato deve includere product_id, revenue e pct_of_total.

Soluzione:

WITH product_sales AS (
	SELECT product_id, SUM(total_amount) AS revenue
	FROM purchase
	GROUP BY product_id
), total_revenue AS (
	SELECT SUM(revenue) AS total
	FROM product_sales
)
SELECT
	ps.product_id,
	ps.revenue,
	ROUND(100.0 * ps.revenue / tr.total, 2) AS pct_of_total
FROM product_sales ps
CROSS JOIN total_revenue tr
ORDER BY pct_of_total DESC;

Spiegazione:

Si inizia calcolando il fatturato totale per prodotto utilizzando una Common Table Expression (CTE) chiamata product_sales. Successivamente, si crea un'altra CTE denominata total_revenue per calcolare il fatturato complessivo di tutti i prodotti. Nella query finale, uniamo entrambe le CTE mediante CROSS JOIN, in modo che ogni riga di prodotto abbia accesso al valore del fatturato totale. Si calcola quindi il contributo percentuale di ciascun prodotto dividendo le entrate per il totale e moltiplicando per 100. Il risultato include , e . Il risultato include product_id, revenue e pct_of_total, ordinati dalla percentuale più alta a quella più bassa.

Esercizio 10: Attivo ma non a dicembre

Esercizio:

Trovare i clienti che hanno effettuato più di un ordine ma non hanno mai effettuato un acquisto nel mese di dicembre. Il risultato deve includere customer_id, first_name, e last_name.

Soluzione:

WITH multi_order AS (
	SELECT customer_id
	FROM purchase
	GROUP BY customer_id
	HAVING COUNT(*) > 1
), december_buyers AS (
	SELECT DISTINCT customer_id
	FROM purchase
	WHERE EXTRACT(MONTH FROM purchase_date) = 12
)
SELECT
	c.customer_id,
	c.first_name,
	c.last_name
FROM customer c
JOIN multi_order mo 
ON c.customer_id = mo.customer_id
LEFT JOIN december_buyers d 
ON c.customer_id = d.customer_id
WHERE d.customer_id IS NULL;

Spiegazione:

Questa query individua i clienti che hanno effettuato più di un ordine ma non hanno effettuato alcun acquisto nel mese di dicembre. Innanzitutto, la CTE multi_order identifica i clienti con più di un acquisto raggruppandoli per customer_id e utilizzando HAVING COUNT(*) > 1. La CTE december_buyers seleziona tutti i clienti che hanno effettuato almeno un acquisto nel mese di dicembre estraendo il mese da purchase_date. Nella query finale, uniamo la tabella customer con multi_order per ottenere i clienti interessati e poi utilizziamo una LEFT JOIN con december_buyers per verificare chi non ha effettuato acquisti nel mese di dicembre. Filtriamo quelli che l'hanno fatto controllando WHERE d.customer_id IS NULL. Il risultato include solo customer_id, first_name e last_name.

Altro Pratica su SQL

Esercitarsi regolarmente con SQL è uno dei modi più efficaci per passare dalla comprensione della teoria alla sicurezza in scenari reali. Gli esercizi di questo articolo hanno riguardato argomenti intermedi fondamentali come GROUP BY, CASE WHEN, le subquery e i CTE, tutte abilità che si presentano frequentemente nell'analisi dei dati, nei report e nei colloqui tecnici. Se siete arrivati fin qui, siete sulla buona strada per diventare esperti di SQL.

Ma non fermatevi qui. La costanza è fondamentale quando si impara l'SQL e il modo migliore per continuare a migliorare è risolvere problemi più pratici. Se siete alla ricerca di una pratica strutturata e pratica con un feedback immediato, vi consigliamo vivamente la traccia Pratica su SQL su LearnSQL.it. È stata progettata per aiutarvi a rafforzare le vostre competenze attraverso sfide di query reali che si basano l'una sull'altra: niente chiacchiere, solo pratica che conta.

Se state cercando di ottenere un posto di lavoro nel settore dei dati, di automatizzare i report o di lavorare in modo più efficace con i database, la traccia Pratica su SQL è il passo successivo che vale la pena fare.