6 query SQL avanzate per l'analisi dei dati finanziari

Siete utenti avanzati di SQL e siete alle prime armi con la finanza? Volete imparare a utilizzare le vostre conoscenze per analizzare i dati finanziari? L'articolo vi fornirà alcune indicazioni.

Probabilmente sapete che SQL è uno strumento potente per analizzare qualsiasi tipo di dati. Più i dati sono grandi e complessi, più SQL diventa utile. In alcune situazioni, i dati finanziari possono essere molto complicati e la produzione di un'analisi sofisticata richiede strumenti sofisticati. SQL è uno di questi strumenti e più si conoscono alcune pratiche avanzate di SQL, più diventa facile eseguire analisi complesse di dati finanziari e creare report finanziari. Vi mostrerò alcuni esempi di come farlo, tratti dalla mia esperienza personale.

Il livello di conoscenza dell'SQL richiesto in questo articolo - come le funzioni di finestra, le estensioni GROUP BY e le query ricorsive - è trattato nella traccia LearnSQL.it Advanced SQL .

Introduzione al database

Tutti e sei gli esempi utilizzeranno questo database, composto da cinque tabelle:

• country
• card_type
• customer
• card_number
• card_transaction

Immaginiamo che questo sia il database di una società di elaborazione di carte di credito e che voi siate il loro dipendente di riferimento per l'analisi dei dati finanziari.

Entriamo ora nei dettagli di ciascuna tabella.

La tabella country ha i seguenti attributi:

• id - L'ID del Paese e la chiave primaria (PK) di questa tabella.
• country_name - Il nome del Paese.

La tabella successiva è card_typecon questi attributi:

• id - L'ID del tipo di carta; la chiave primaria (PK).
• card_type_name - Il nome del tipo di carta.

La tabella customer è composta dalle colonne:

• id - L'ID del cliente; la chiave primaria (PK).
• NIN - Il numero di identificazione nazionale del cliente.
• first_name - Il nome del cliente.
• last_name - Il cognome del cliente.
• country_id - L'ID del Paese; si tratta di una chiave esterna (FK) che fa riferimento alla tabella country.

Segue la tabella card_number con gli attributi:

• id - L'ID della carta; la chiave primaria (PK).
• card_number - Il numero della carta.
• customer_id - L'ID del cliente; una chiave esterna (FK) che fa riferimento alla tabella customer.
• card_type_id - L'ID del tipo di carta; una chiave esterna (FK) che fa riferimento alla tabella. card_type.

L'ultima è la tabella card_transaction che contiene le colonne:

• id - L'ID della transazione; la chiave primaria (PK).
• date - La data della transazione.
• amount - L'importo della transazione, in dollari.
• card_number_id - ID della carta; una chiave esterna (FK) che fa riferimento alla tabella . card_number.

Ora che avete familiarizzato con il database, passiamo agli esempi!

6 esempi di query Advanced SQL per l'analisi finanziaria

1: Raggruppare i dati per anno e trimestre

Di solito i dati finanziari devono essere raggruppati in periodi o in fasce temporali specifiche. Se state creando dei report finanziari, avrete sicuramente bisogno di raggruppare i dati in anni e trimestri. Vi mostrerò come fare. L'apprendimento di questo principio vi consentirà di raggruppare i dati a qualsiasi altro livello (ad esempio mesi, settimane o giorni, a seconda dei dati in vostro possesso).

In questo esempio, raggrupperò i dati per anni e trimestri e mostrerò il numero di transazioni. Per ottenere questo risultato, utilizzerò la tabella card_transaction e questo codice:

SELECT	EXTRACT(YEAR FROM date) AS year,
		EXTRACT(QUARTER FROM date) AS quarter,
		COUNT(amount) AS number_of_transactions
FROM card_transaction
GROUP BY EXTRACT(YEAR FROM date), EXTRACT(QUARTER FROM date)
ORDER BY EXTRACT(YEAR FROM date) ASC, EXTRACT(QUARTER FROM date);

Il codice utilizza la funzione EXTRACT() per ottenere gli anni e i trimestri. Si tratta di una funzione SQL standard, che funziona in MySQL e PostgreSQL. Tuttavia, se si utilizza SQL Server, è necessario utilizzare una funzione diversa, che verrà spiegata tra poco.

Quando si utilizza la funzione EXTRACT(), è necessario specificare il periodo desiderato e la colonna che la funzione utilizzerà per restituire il periodo desiderato. Innanzitutto, voglio ottenere gli anni dalla colonna della data. Poi voglio i trimestri, sempre dalla colonna della data. Successivamente, devo contare il numero di transazioni, cosa che ho fatto utilizzando la funzione COUNT(). I dati vengono raggruppati dalle funzioni EXTRACT(), che rappresentano gli anni e i trimestri. Infine, i dati vengono ordinati per anni e trimestri in modo crescente, poiché voglio vedere il primo trimestre del primo anno in cima al rapporto. Sono sicuro che volete vedere il risultato:

Questo report ha un aspetto molto bello, devo dire!

Ora, se utilizzate SQL Server, dovrete utilizzare la funzione DATEPART() invece di EXTRACT(). Ecco la prima parte del codice, per essere sicuri di aver capito. Nel resto del codice si segue lo stesso principio:

SELECT	DATEPART(YEAR, date) AS year,
		DATEPART(QUARTER, date) AS quarter,
		COUNT(amount) AS number_of_transactions
...

2: Calcolo dei totali progressivi

Il vostro compito ora è quello di mostrare i totali correnti per tutte le transazioni effettuate nel dicembre 2020. Il report deve essere aggregato a livello di tipo di carta. Per creare questo report, è necessario introdurre nel codice le funzioni delle finestre e JOINs.

Prima di andare avanti, forse è il caso di verificare le proprie conoscenze di SQL. È possibile farlo nella traccia SQL Reporting. Questa traccia vi insegnerà anche a creare report SQL di base e a eseguire analisi sull'andamento dei ricavi e sul comportamento dei clienti.

Il codice che restituirà i dati desiderati è il seguente:

SELECT	DISTINCT (ct.date),
		cty.card_type_name,
		SUM (ct.amount) OVER (PARTITION BY cty.card_type_name ORDER BY ct.date ASC) AS transaction_running_total
FROM card_transaction ct 
JOIN card_number cn ON ct.card_number_id = cn.id 
JOIN card_type cty ON cn.card_type_id = cty.id
WHERE date > '2020-11-30' AND date <= '2020-12-31'
ORDER BY cty.card_type_name ASC;

Il codice seleziona innanzitutto la data specifica, poiché possono esserci più transazioni giornaliere con lo stesso tipo di carta e persino con lo stesso numero di carta. Segue poi la colonna card_type_name. L'ultima colonna della query è transaction_running_total. Per calcolare il totale progressivo, ho utilizzato una funzione a finestra. Innanzitutto, i dati della colonna importo vengono sommati. Poi, utilizzando la clausola OVER(), ho specificato che il totale progressivo deve essere calcolato a livello di tipo di carta, quindi PARTITION BY cty.card_type_name. Infine, voglio che il totale progressivo sia calcolato dalla data più vecchia a quella più recente: ORDER BY date ASC.

Per ottenere i dati, ho dovuto unire tre tabelle. La prima unione collega le tabelle card_transaction e card_number e le tabelle. La seconda unione fa riferimento alla tabella card_typeche è il modo in cui ottengo il nome del tipo di carta nel mio report. Ho assegnato degli alias a tutte le tabelle unite; in questo modo ho dovuto digitare di meno. Il risultato viene filtrato utilizzando la clausola WHERE, che mi permette di ottenere solo le transazioni di dicembre 2020. Ho deciso di ordinare i dati in base al nome del tipo di carta in ordine crescente.

Eseguendo il codice si otterrà il report mostrato di seguito:

Se volete saperne di più sui totali cumulativi in SQL, leggete questo articolo per capire cosa sono i totali progressivi e come calcolarli.

3: Calcolo delle medie consecutive

Il prossimo esempio prevede la creazione di un report che mostri tutte le transazioni del dicembre 2020 e il loro importo per le carte Visa Electron. Inoltre, verrà mostrato l'importo medio delle transazioni giornaliere utilizzando una media mobile. Date un'occhiata al codice:

SELECT	ct.date,
		cty.card_type_name,
		SUM(ct.amount) AS daily_sum,
		AVG(SUM(ct.amount)) OVER (ORDER BY ct.date ASC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS transaction_running_average
FROM card_transaction ct 
JOIN card_number cn ON ct.card_number_id = cn.id 
JOIN card_type cty ON cn.card_type_id = cty.id
WHERE ct.date > '2020-11-30' AND date <= '2020-12-31' 
AND cty.card_type_name = 'visa-electron'
GROUP BY ct.date, cty.card_type_name
ORDER BY cty.card_type_name;

Ho prima selezionato le colonne che mostreranno la data della transazione e il nome del tipo di carta. Poi ho sommato gli importi delle transazioni giornaliere e il risultato è stato mostrato nella colonna daily_sum. Questa operazione è stata necessaria perché è possibile che vengano effettuate diverse transazioni giornaliere con Visa Electron. Poi ho usato questa somma giornaliera dei valori delle transazioni per calcolare la sua media. Tuttavia, per ottenere la media mobile, devo utilizzare la clausola OVER(). Voglio che la media mobile sia calcolata dal primo all'ultimo giorno di dicembre 2020, quindi l'operazione è ordinata per data crescente.

Per il calcolo delle medie mobili, ho deciso di utilizzare una media mobile a tre giorni, definita dalla seguente parte del codice: ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Questo indica al codice di utilizzare la riga corrente e le due righe precedenti (tre righe/tre date in totale) per calcolare la media mobile.

Le tabelle vengono poi unite esattamente come nell'esempio precedente. Nella clausola WHERE sono presenti due condizioni: una definisce le date prese in considerazione, l'altra definisce il tipo di carta.

Questa query darà come risultato la seguente tabella:

Se non avete familiarità con le medie mobili, vi spiego come funzionano. Sapete già che si tratta di una media mobile a tre giorni, che utilizza tre righe per calcolare la media.

Per la data '2020-12-01', la media nella tabella precedente è uguale alla somma giornaliera. Questo perché la query può utilizzare solo i dati della riga corrente, senza le righe precedenti.

Per la data '2020-12-03', la media mobile viene calcolata come segue: (16,881.70 + 17,375.79) / 2 = 17,128.75. Attenzione! La media mobile non viene calcolata nel seguente modo: (16.881,70 + 17.375,79) / 3 = 11.419,16. Questo perché c'è solo una riga precedente, o solo due valori da calcolare.

La media mobile per la data "2020-12-13" viene calcolata nel modo seguente: (16.881,70 + 17.375,79 + 17.725,49) / 3 = 17.327,66.

4: Analisi delle serie temporali

Un'esigenza molto comune quando si lavora con i dati finanziari è quella di analizzare una serie temporale (cioè la differenza tra periodi di tempo, come giorno per giorno o mese per mese). Ad esempio, supponiamo che il vostro capo vi abbia chiesto di creare un report che mostri le variazioni giornaliere dei valori delle transazioni effettuate dai clienti cinesi nel mese di dicembre 2020. Il reparto vendite in Cina non è soddisfatto delle prestazioni di dicembre 2020, quindi vuole analizzare questo mese in dettaglio per scoprire dove si è verificato un calo delle transazioni.

Per creare un report di questo tipo, è necessaria una funzione SQL window. Questa volta si tratta della funzione LAG(), che consente di recuperare i dati dalla riga precedente. Ecco come procedere:

SELECT	ct.date,
		SUM(ct.amount) AS daily_sum,
		(SUM(ct.amount)-LAG(SUM(ct.amount)) OVER (ORDER BY ct.date ASC)) 
AS daily_difference,
		co.country_name
FROM card_transaction ct 
JOIN card_number cn ON ct.card_number_id = cn.id 
JOIN customer cu ON cn.customer_id = cu.id 
JOIN country co ON cu.country_id = co.id
WHERE ct.date > '2020-11-30' AND date <= '2020-12-31'
AND co.country_name = 'China'
GROUP BY ct.date, co.country_name;

La query inizia con un processo già noto: seleziona la data, quindi calcola la somma giornaliera delle transazioni (nel caso in cui ci siano diverse transazioni giornaliere dalla Cina). Per calcolare la differenza giornaliera, è necessario dedurre la somma delle transazioni del giorno precedente da quella del giorno corrente. Il calcolo viene effettuato da questa parte della query:

(SUM(ct.amount)-LAG(SUM(ct.amount)) OVER (ORDER BY ct.date ASC)) AS daily_difference. 

La somma delle transazioni del giorno precedente viene restituita dalla funzione LAG() combinata con la clausola OVER(). L'operazione deve essere eseguita dalla data più vecchia a quella più recente, specificata in ORDER BY ct.date ASC. L'ultima colonna della query è la colonna country_name della tabella Paese.

Per ottenere i dati richiesti vengono unite tre tabelle; ancora una volta ho usato degli alias. Nella clausola WHERE sono presenti due condizioni, una che definisce la data e l'altra che definisce il paese. Infine, il risultato è raggruppato per data e nome del paese.

Eseguire la query per ottenere questa tabella:

Il primo valore è NULL perché non ci sono righe precedenti alla prima, cioè la prima riga non ha una riga con cui essere confrontata.

Per ulteriori informazioni sulla funzione LAG(), consultare questo articolo sul calcolo della differenza tra due righe.

Se volete esercitarvi con le funzioni finestra, provate il nostro corso SQL Window Functions .

5: Aggiungere più livelli di raggruppamento

Il report che vi ho mostrato nel primo esempio è abbastanza buono, ma questo non significa che non possa essere migliore. Manca, ad esempio, un subtotale del conteggio per il 2019 e il 2020 e un totale generale, come le tabelle pivot di Excel. Questo report può essere facilmente migliorato utilizzando la funzione ROLLUP(). Aggiungiamo alcuni subtotali e mostriamo tutti i valori anche a livello di tipo di tessera. Ecco come fare:

SELECT	EXTRACT(YEAR FROM ct.date) AS year,
		EXTRACT(QUARTER FROM ct.date) AS quarter,
		COUNT(ct.amount) AS number_of_transactions,
		cty.card_type_name
FROM card_transaction ct 
JOIN card_number cn ON ct.card_number_id = cn.id 
JOIN card_type cty ON cn.card_type_id = cty.id
GROUP BY ROLLUP(EXTRACT(YEAR FROM ct.date), EXTRACT(QUARTER FROM ct.date), cty.card_type_name);

La prima parte del codice è tratta direttamente dal primo esempio. Utilizza la funzione EXTRACT() per classificare le date in anni e trimestri; quindi conta il numero di transazioni utilizzando la funzione COUNT(). L'ultima colonna selezionata dal codice è card_type_name dalla tabella card_type.

I dati vengono selezionati dalle tabelle card_transaction, card_number, e card_type, che vengono unite. Ora viene la parte magica: l'uso di ROLLUP(), che è un'estensione della clausola GROUP BY. Dopo aver scritto GROUP BY nel codice, è sufficiente utilizzare la funzione ROLLUP() per specificare più livelli di raggruppamento nel report. Dato che il vostro compito è quello di raggruppare i dati a livello annuale, trimestrale e di tipo di carta, queste colonne dovrebbero essere contenute in ROLLUP(). Eseguite il codice sopra riportato e otterrete questo bel report:

Non lasciatevi spaventare dai valori NULL! È tutto a posto nel report; i valori NULL appaiono quando c'è un subtotale, un totale o un totale generale. Ecco la prima parte della tabella:

Questa tabella mostra il numero di transazioni del primo trimestre del 2019 per tipo di carta. Il totale parziale di tutte le transazioni del primo trimestre è 49 + 46 +36 = 131. La logica è la stessa per il resto della tabella. Quindi, ad esempio, quando si vede una riga come quella sottostante, significa che il numero totale di transazioni per l'anno 2019 è 500:

ROLLUP() e le altre estensioni di GROUP BY sono interessanti; mi piacciono molto! Se volete vedere quanto possono essere utili, il nostro corso sulle estensioni GROUP BY fa al caso vostro.

Passiamo ora all'ultimo esempio di query SQL per l'analisi dei dati finanziari.

6: Creare un rapporto sulle entrate a livello annuale

Questa sarà probabilmente la query più complessa che vi mostrerò, ma credo che ne valga la pena. Imparare cosa fa questa query vi consentirà di creare rapporti complessi e di eseguire analisi statistiche, due operazioni molto spesso richieste nel settore finanziario.

Il vostro compito è creare un report che mostri le entrate del 2020, le entrate del 2019 e le entrate totali. Tutte e tre le categorie devono essere visualizzate a livello di cliente. Ricordate che state lavorando per una società di elaborazione di carte di credito, quindi le entrate corrispondono all'importo della transazione. È inoltre necessario assegnare le categorie al cliente; se il cliente ha realizzato un fatturato totale di 1.000.000 dollari o più, viene classificato come "Platino". Se il fatturato totale è inferiore a 1.000.000 di dollari, il cliente deve essere classificato come "Oro". Ecco la query:

SELECT	cu.NIN,
		cu.first_name,
		cu.last_name,
		SUM(ct.amount) AS total_revenue_per_customer,
		CASE
			WHEN SUM(ct.amount) >= 1000000 THEN 'Platinum'
			WHEN SUM(ct.amount) < 1000000 THEN 'Gold'
		END AS customer_category,
		SUM(CASE WHEN ct.date >= '2019-01-01' AND ct.date < '2020-01-01' THEN ct.amount ELSE 0 END) AS revenue_2019,
		SUM(CASE WHEN ct.date >= '2020-01-01' AND ct.date < '2021-01-01' THEN ct.amount ELSE 0 END) AS revenue_2020
FROM card_transaction ct 
JOIN card_number cn ON ct.card_number_id = cn.id 
JOIN customer cu ON cn.customer_id = cu.id
GROUP BY cu.NIN, cu.first_name, cu.last_name
ORDER BY total_revenue_per_customer DESC;

Cominciamo con la parte più semplice: la query seleziona le colonne NIN, first_name e last_name dalla tabella customer. Poi somma gli importi, che saranno le entrate totali. Segue la categorizzazione dei clienti tramite un'istruzione CASE WHEN. La prima WHEN assegna la categoria "Platino", mentre l'altra assegna la categoria "Oro". Queste dichiarazioni sono chiuse da END, e questa nuova colonna si chiamerà customer_category.

Successivamente, ho dovuto specificare le condizioni per le colonne che conterranno i numeri relativi al fatturato 2019 e al fatturato 2020. Per farlo, ho utilizzato ancora una volta l'istruzione CASE WHEN. Per le entrate del 2019, la condizione è che le date siano uguali a 2019-01-01 o superiori/nuove, ma inferiori/superiori al 2020-01-01. Questa colonna è denominata revenue_2019.

Lo stesso principio viene applicato alla creazione della colonna revenue_2020.

Per ottenere i dati, è necessario unire tre tabelle: card_transaction, card_number, e customer. A tutte e tre le tabelle sono associati degli alias.

Alla fine della query, i dati vengono raggruppati in base alle colonne NIN, first_name e last_name perché si vogliono i dati a livello di cliente. Inoltre, i risultati sono ordinati in base al fatturato totale in ordine decrescente per avere un aspetto più gradevole.

Ecco la tabella sexy che vi renderà famosi tra i geek dei dati:

Vi piace usare le query di Advanced SQL nell'analisi dei dati finanziari?

Ho inteso mostrare pratiche SQL relativamente avanzate che consentono di analizzare i dati finanziari e creare report. Non si tratta di tutto ciò che SQL può fare, ma forse vi ho fatto interessare ad alcune funzioni SQL avanzate. Tutti e sei gli esempi si basano sulla mia esperienza di analista di dati. Ho realizzato molti report di questo tipo nella mia vita e sarei stato entusiasta se avessi conosciuto tutte queste possibilità di SQL quando ho iniziato a lavorare; sarebbe stato molto più facile per me.

Non è mai troppo tardi per imparare l'SQL avanzato, ma è sempre meglio iniziare prima. Se ne avete la possibilità, iscrivetevi al corso Advanced SQL ; vi ripagherà in futuro.