6 requêtes SQL avancées pour l'analyse des données financières

Êtes-vous un utilisateur de SQL avancé qui débute dans la finance ? Vous voulez apprendre à utiliser vos connaissances pour analyser des données financières ? Cet article vous donnera quelques conseils.

Vous savez probablement que SQL est un outil puissant pour l'analyse de toutes sortes de données. Plus les données sont volumineuses et complexes, plus SQL devient avantageux. Dans certaines situations, les données financières peuvent être très compliquées ; produire une analyse sophistiquée nécessite des outils sophistiqués. SQL est l'un de ces outils, et plus vous connaissez certaines pratiques SQL avancées, plus il devient facile d'effectuer des analyses de données financières complexes et de créer des rapports financiers. Je vais vous montrer quelques exemples de la manière de procéder, tirés de ma propre expérience.

Le niveau de connaissances SQL requis dans cet article - telles que les fonctions de fenêtre, les extensions GROUP BY et les requêtes récursives - est couvert par le programme SQL avancé.

Présentation de la base de données

Les six exemples utiliseront cette base de données, qui se compose de cinq tables :

• country
• card_type
• customer
• card_number
• card_transaction

Nous imaginerons qu'il s'agit de la base de données d'une société de traitement des cartes de crédit et que vous êtes leur employé de référence pour l'analyse des données financières.

Entrons maintenant dans les détails de chaque table.

La table country possède les attributs suivants :

• id - L'ID du pays et la clé primaire (PK) de cette table.
• country_name - Le nom du pays.

La table suivante est card_typeavec ces attributs :

• id - L'ID du type de carte ; la clé primaire (PK).
• card_type_name - Le nom du type de carte.

Le tableau customer est composé de colonnes :

• id - L'ID du client ; la clé primaire (PK).
• NIN - Le numéro d'identification national du client.
• first_name - Le prénom du client.
• last_name - Le nom de famille du client.
• country_id - L'ID du pays ; il s'agit d'une clé étrangère (FK) qui fait référence à la table country.

Vient ensuite le tableau card_number avec les attributs :

• id - L'ID de la carte ; la clé primaire (PK).
• card_number - Le numéro de la carte.
• customer_id - L'ID du client ; il s'agit d'une clé étrangère (FK) qui fait référence à la table. customer.
• card_type_id - L'ID du type de carte ; il s'agit d'une clé étrangère (FK) qui fait référence à la table. card_type.

Le dernier est le tableau card_transaction qui contient ces colonnes :

• id - L'ID de la transaction ; la clé primaire (PK).
• date - La date de la transaction.
• amount - Le montant de la transaction, en dollars.
• card_number_id - ID de la carte ; clé étrangère (FK) qui fait référence au tableau card_number.

Maintenant que vous êtes familiarisé avec la base de données, passons aux exemples !

6 exemples de requêtes SQL avancé pour l'analyse financière

1 : Regroupement des données par année et par trimestre

Les données financières doivent généralement être regroupées par périodes ou par tranches de temps spécifiques. Vous aurez certainement besoin de regrouper les données par année et par trimestre si vous créez des rapports financiers. Je vais vous montrer comment faire. L'apprentissage de ce principe vous permettra de regrouper des données à tout autre niveau (c'est-à-dire en mois, semaines ou jours, selon les données dont vous disposez).

Dans cet exemple, je vais regrouper les données par années et par trimestres et afficher le nombre de transactions. Pour cela, je vais utiliser le tableau card_transaction et ce code :

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);

Le code utilise la fonction EXTRACT() pour obtenir les années et les trimestres. Il s'agit d'une fonction SQL standard, qui fonctionne avec MySQL et PostgreSQL. Cependant, si vous utilisez SQL Server, vous devrez utiliser une fonction différente - je vous expliquerai laquelle dans un instant.

Lorsque vous utilisez la fonction EXTRACT(), vous devez spécifier la période que vous souhaitez et la colonne que cette fonction utilisera pour renvoyer la période souhaitée. Tout d'abord, je veux obtenir les années à partir de la colonne date. Ensuite, je veux des trimestres, également à partir de la colonne date. Après cela, je dois compter le nombre de transactions, ce que j'ai fait en utilisant la fonction COUNT(). Les données sont regroupées par les fonctions EXTRACT(), qui représentent les années et les trimestres. Enfin, les données sont classées par années et par trimestres de manière ascendante, puisque je veux voir le premier trimestre de la première année en haut du rapport. Je suis sûr que vous voulez voir le résultat :

Ce rapport est très joli, je dois dire !

Maintenant, si vous utilisez SQL Server, vous devrez utiliser la fonction DATEPART() au lieu de EXTRACT(). Voici la première partie du code, juste pour être sûr que vous avez compris. Vous suivez le même principe dans le reste du code :

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

2 : Calculer les totaux courants

Votre tâche consiste maintenant à afficher les totaux courants de toutes les transactions effectuées en décembre 2020. Le rapport doit être agrégé au niveau du type de carte. Pour créer ce rapport, vous devrez introduire des fonctions de fenêtre et JOINs dans le code.

Avant d'aller plus loin, vous devriez peut-être vérifier vos connaissances de SQL. Vous pouvez le faire dans la piste SQL Reporting. Cette piste vous apprendra également à créer des rapports SQL de base et à effectuer des analyses de tendances de revenus et de comportement des clients.

Le code qui renverra les données souhaitées est le suivant :

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;

Le code sélectionne d'abord la date spécifique, car il peut y avoir plusieurs transactions quotidiennes par le même type de carte, voire le même numéro de carte. Vient ensuite la colonne card_type_name. La dernière colonne de la requête est transaction_running_total. Pour calculer le total courant, j'ai utilisé une fonction de fenêtre. Tout d'abord, les données de la colonne montant sont additionnées. Ensuite, en utilisant la clause OVER(), j'ai spécifié que le total courant devait être calculé au niveau du type de carte, d'où PARTITION BY cty.card_type_name. Enfin, je souhaite que le total courant soit calculé de la date la plus ancienne à la date la plus récente : ORDER BY date ASC.

Pour obtenir les données, j'ai dû joindre trois tables. La première jointure relie les tables card_transaction et card_number tables. La deuxième jointure fait référence à la table card_typeC'est ainsi que j'obtiens le nom du type de carte dans mon rapport. J'ai attribué des alias à toutes les tables jointes ; ainsi, j'ai dû taper moins de données. Le résultat est filtré à l'aide de la clause WHERE, ce qui me permet d'obtenir uniquement les transactions de décembre 2020. J'ai décidé d'ordonner les données par le nom du type de carte dans un tri ascendant.

En exécutant ce code, vous obtiendrez le rapport ci-dessous :

Si vous voulez en savoir plus sur les totaux cumulatifs en SQL, lisez cet article pour apprendre ce que sont les totaux courants et comment les calculer.

3 : Calculer des moyennes courantes

L'exemple suivant consiste à créer un rapport indiquant chaque transaction effectuée en décembre 2020 et son montant pour les cartes Visa Electron. En outre, vous afficherez le montant moyen des transactions quotidiennes en utilisant une moyenne mobile. Jetez un coup d'œil au code :

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;

J'ai d'abord sélectionné les colonnes qui afficheront la date de la transaction et le nom du type de carte. Ensuite, j'ai additionné les montants des transactions quotidiennes, et le résultat est affiché dans la colonne daily_sum. J'ai dû procéder ainsi parce que plusieurs transactions peuvent être effectuées quotidiennement par Visa Electron. J'ai ensuite utilisé cette somme quotidienne des valeurs des transactions pour calculer la moyenne. Toutefois, pour obtenir la moyenne mobile, je dois utiliser la clause OVER(). Je veux que la moyenne mobile soit calculée du premier au dernier jour de décembre 2020, l'opération est donc ordonnée par date croissante.

Pour calculer les moyennes mobiles, j'ai décidé d'utiliser une moyenne mobile sur trois jours, définie par la partie suivante du code : ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. Cela indique au code d'utiliser la ligne actuelle et les deux lignes précédentes (trois lignes/trois dates au total) pour calculer la moyenne mobile.

Les tableaux sont ensuite joints exactement de la même manière que dans l'exemple précédent. Il y a deux conditions dans la clause WHERE; l'une définit les dates prises en compte ; l'autre définit le type de carte.

Cette requête vous donnera le tableau suivant :

Si vous n'êtes pas familier avec les moyennes mobiles, laissez-moi vous expliquer comment elles fonctionnent. Vous savez déjà qu'il s'agit d'une moyenne mobile sur trois jours, qui utilise trois lignes pour calculer la moyenne.

Pour la date '2020-12-01', la moyenne dans le tableau ci-dessus est la même que la somme quotidienne. Cela s'explique par le fait que la requête n'a que les données de la ligne actuelle à utiliser - il n'y a pas de lignes précédentes.

Pour la date du "2020-12-03", la moyenne mobile est calculée comme suit : (16,881.70 + 17,375.79) / 2 = 17,128.75. Attention ! La moyenne mobile n'est pas calculée de la manière suivante : (16,881,70 + 17,375.79) / 3 = 11,419.16. C'est parce qu'il n'y a qu'une seule ligne précédente, ou seulement deux valeurs à moyenner.

La moyenne mobile pour la date "2020-12-13" est calculée de la manière suivante : (16,881.70 + 17,375.79 + 17,725.49) / 3 = 17,327.66.

4 : Analyse des séries chronologiques

Lorsqu'on travaille avec des données financières, il est très souvent nécessaire d'analyser une série chronologique (c'est-à-dire la différence entre deux périodes de temps, par exemple d'un jour à l'autre ou d'un mois à l'autre). Par exemple, supposons que votre patron vous ait demandé de créer un rapport montrant l'évolution quotidienne des valeurs des transactions effectuées par les clients de Chine en décembre 2020. Le département des ventes en Chine n'est pas satisfait des performances de décembre 2020, il veut donc analyser ce mois en détail pour découvrir où il y a eu une baisse des transactions.

Pour créer un tel rapport, vous aurez à nouveau besoin d'une fonction de fenêtre SQL. Cette fois, il s'agira de la fonction LAG(), qui vous permettra d'extraire les données de la ligne précédente. Voici comment vous devez procéder :

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 requête commence par un processus familier : elle sélectionne la date, puis elle calcule la somme quotidienne des transactions (dans le cas où il y a plusieurs transactions quotidiennes en provenance de Chine). Pour calculer la différence quotidienne, vous devez déduire la somme des transactions du jour précédent de la somme du jour en cours. Ce calcul est effectué par cette partie de la requête :

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

La somme des transactions du jour précédent est renvoyée par la fonction LAG() combinée à la clause OVER(). L'opération doit être effectuée de la date la plus ancienne à la date la plus récente, que nous spécifions dans ORDER BY ct.date ASC. La dernière colonne de la requête est la colonne country_name de la table country.

Trois tables sont jointes pour obtenir les données requises ; une fois de plus, j'ai utilisé des alias. Il y a deux conditions dans la clause WHERE, l'une définissant la date et l'autre définissant le pays. Enfin, le résultat est groupé par date et par nom de pays.

Exécutez la requête pour obtenir ce tableau :

La première valeur est NULL car il n'y a pas de ligne avant la première, c'est-à-dire que la première ligne n'a pas de ligne avec laquelle elle peut être comparée.

Pour en savoir plus sur la fonction LAG(), consultez cet article sur le calcul de la différence entre deux lignes.

Si vous aimez les fonctions de fenêtre, vous pouvez en savoir plus sur qui doit les utiliser et quand. Si vous souhaitez vous entraîner aux fonctions de fenêtre, essayez notre cours SQL Fonctions de fenêtrage .

5 : Ajouter plusieurs niveaux de regroupement

Le rapport que je vous ai montré dans le premier exemple est assez bon, mais cela ne veut pas dire qu'il ne peut pas être meilleur. Ce qui me manque, c'est, par exemple, un sous-total pour 2019 et 2020 et un total général - un peu comme les tableaux croisés dynamiques d'Excel. Ce rapport peut être facilement amélioré en utilisant la fonction ROLLUP(). Ajoutons quelques sous-totaux et affichons également toutes les valeurs au niveau du type de carte. Voici comment procéder :

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 première partie du code est directement tirée du premier exemple. Elle utilise la fonction EXTRACT() pour classer les dates en années et en trimestres, puis elle compte le nombre de transactions à l'aide de la fonction COUNT(). La dernière colonne que le code va sélectionner est card_type_name dans le tableau card_type.

Les données sont sélectionnées dans les tableaux card_transaction, card_number, et card_typequi sont jointes. Vient maintenant la partie magique - l'utilisation de ROLLUP(), qui est une extension de la clause GROUP BY. Après avoir écrit GROUP BY dans le code, il vous suffit d'utiliser la fonction ROLLUP() pour spécifier plusieurs niveaux de regroupement dans votre rapport. Puisque votre tâche consiste à regrouper les données au niveau annuel, trimestriel et du type de carte, ces colonnes doivent se trouver dans le fichier ROLLUP(). Exécutez le code ci-dessus et vous obtiendrez ce joli rapport :

Ne laissez pas les valeurs NULL vous effrayer ! Tout va bien dans le rapport ; les valeurs NULL apparaissent lorsqu'il y a un sous-total, un total ou un grand total. Voici la première partie du tableau :

Ce tableau indique le nombre de transactions pour le premier trimestre de 2019 par type de carte. Le sous-total de toutes les transactions du premier trimestre est de 49 + 46 +36 = 131. La logique est la même pour le reste du tableau. Ainsi, par exemple, lorsque vous voyez une ligne comme celle ci-dessous, cela signifie que le nombre total de transactions pour l'année 2019 est de 500 :

ROLLUP() et les autres extensions de GROUP BY sont cool ; je les aime beaucoup ! Si vous voulez voir à quel point elles peuvent être utiles, alors notre cours sur les extensions GROUP BYest fait pour vous.

Passons maintenant à notre dernier exemple de requêtes SQL d'analyse de données financières.

6 : Créer un rapport sur les revenus au niveau annuel

Cette requête sera probablement la plus complexe que je vous montrerai, mais je pense qu'elle en vaut la peine. Apprendre ce que fait cette requête vous permettra de créer des rapports complexes et d'effectuer des analyses statistiques, deux choses qui sont très souvent nécessaires dans le secteur financier.

Votre tâche consiste à créer un rapport qui affichera les recettes de 2020, les recettes de 2019 et les recettes totales. Ces trois catégories doivent être affichées au niveau du client. N'oubliez pas que vous travaillez pour une société de traitement des cartes de crédit, et que le revenu correspond donc au montant de la transaction. Vous devez également attribuer des catégories au client ; si le client a généré un revenu total de 1 000 000 $ ou plus, il est classé dans la catégorie "Platine". Si le revenu total est inférieur à 1 000 000 $, le client doit être classé dans la catégorie "Gold". Voici la requête :

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;

Commençons par la partie la plus facile : la requête sélectionne les colonnes NIN, first_name et last_name dans la table. customer. Ensuite, elle additionne les montants, qui seront le revenu total. Ensuite, on procède à la catégorisation des clients à l'aide d'une instruction CASE WHEN. La première déclaration WHEN attribue la catégorie 'Platinum', tandis que l'autre attribue la catégorie 'Gold'. Ces déclarations sont fermées par END, et cette nouvelle colonne sera nommée customer_category.

Ensuite, j'ai dû spécifier des conditions pour les colonnes qui contiendront les chiffres des revenus 2019 et 2020. Pour ce faire, j'ai de nouveau utilisé l'instruction CASE WHEN. Pour les recettes de 2019, la condition est que les dates soient égales à 2019-01-01 ou supérieures/nouvelles, mais inférieures/inférieures à 2020-01-01. Cette colonne est nommée revenue_2019.

Le même principe est appliqué lors de la création de la colonne revenue_2020.

Pour obtenir les données, vous devez joindre trois tables : card_transaction, card_number, et customer. Ces trois tables sont associées à des alias.

À la fin de la requête, les données sont regroupées par les colonnes NIN, first_name et last_name car vous souhaitez obtenir les données au niveau du client. De plus, le résultat est ordonné par le revenu total en ordre décroissant pour être plus joli.

Voici le tableau sexy qui vous rendra cool parmi les geeks des données :

Que pensez-vous de l'utilisation de SQL avancé Queries dans l'analyse des données financières ?

J'avais l'intention de vous montrer des pratiques SQL relativement avancées qui vous permettent d'analyser des données financières et de créer des rapports. Ce n'est pas tout ce que SQL peut faire, mais j'ai peut-être réussi à vous intéresser à certaines fonctionnalités SQL avancées. Les six exemples sont basés sur mon expérience d'analyste de données. J'ai réalisé de nombreux rapports de ce type dans ma vie, et j'aurais été ravi de connaître toutes ces possibilités SQL lorsque j'ai commencé à travailler ; cela m'aurait été beaucoup plus facile.

Il n'est jamais trop tard pour apprendre le SQL avancé, mais il est toujours préférable de commencer plus tôt. Si vous en avez l'occasion, inscrivez-vous au cours SQL avancé ; cela vous sera profitable à l'avenir.