2 Exemples importants de SQL CASE WHEN que vous devez connaître en 2023

'2 Exemples importants de SQL CASE WHEN en 2023'

Science des données

Maîtrisez l’approche pour résoudre de vraies questions SQL avec CASE WHEN

Photo de Vincent van Zalinge sur Unsplash

Vraies questions d’entretien utilisant CASE WHEN en SQL !

Dans l’article 5 concepts SQL avancés, vous pouvez explorer les bases de l’instruction CASE..WHEN et également les cas d’utilisation de celle-ci. Cependant, dans ces articles, je n’ai pas mentionné l’application du CASE WHEN dans le monde réel.

Par conséquent, j’ai contacté plusieurs professionnels de la science des données travaillant dans des entreprises de sport et de commerce électronique via LinkedIn et j’ai recueilli ces 2 exemples d’utilisation de CASE WHEN en SQL qui sont les plus fréquemment posés lors d’entretiens d’embauche.

Pourquoi CASE WHEN est-il l’un des concepts les plus demandés ?

Parce que l’instruction CASE WHEN en SQL permet de mettre en œuvre la logique If..Else lors de l’interrogation des données.

Vous devez souvent extraire ou agréger les données en fonction de certaines conditions. Bien sûr, vous pouvez utiliser la clause WHERE pour appliquer ces conditions, mais lorsque vous voulez créer de nouvelles colonnes en fonction de telles conditions, CASE..WHEN est pratique et vous devez l’utiliser.

Dans cet article, vous verrez 2 questions d’entretien réelles et couramment posées que vous pouvez résoudre en utilisant CASE WHEN en SQL.

Vous apprendrez une approche pour résoudre ces questions et finalement comment utiliser CASE WHEN pour obtenir le résultat souhaité. De plus, vous apprendrez comment décomposer des requêtes complexes en étapes simples et faciles à suivre.

Pour vous donner un aperçu rapide, voici les deux questions que vous explorerez dans cette lecture rapide.

· Exemple 1 : Créer un tableau des points pour un tournoi sportif · Exemple 2 : Trouver les nouveaux clients et les clients répétés pour un site web de commerce électronique

Obtenez les fichiers CSV des jeux de données d’exemple à la fin de cet article.

Commençons par la question suggérée par un analyste sportif. De temps en temps, leur équipe d’analyse doit créer un tableau des points basé sur le total des matchs joués entre différentes équipes.

C’est pourquoi ils posent cette question lors de chaque entretien d’embauche pour un poste d’analyste de données.

Exemple 1 : Créer un tableau des points pour un tournoi sportif

C’est un scénario classique de conversion d’un tableau d’une forme longue (nombre de lignes > nombre de colonnes) à une forme large (nombre de colonnes > nombre de lignes). C’est également ce qu’on appelle le pivot de données, qui est un cas d’utilisation important de CASE WHEN en SQL.

Dans ce scénario, vous disposez d’un tableau contenant les noms des équipes qui ont joué le match et le vainqueur. Vous devez créer un tableau des points où vous obtenez des informations sur le nombre de matchs joués par chaque équipe, le nombre de matchs gagnés, perdus et le nombre de matchs nuls.

Type d'exemple | Image de l'auteur

Voyons comment aborder ce type de question —

Voici le tableau d’entrée où chaque ligne correspond à un match entre deux équipes et la colonne “winner” indique quelle équipe a gagné. La valeur NULL dans la colonne “winner” indique que le match était un match nul, c’est-à-dire que l’une des équipes n’a pas remporté le match.

Données d'entrée | Image de l'auteur

Découpons cette question en sous-tâches suivantes.

  1. Trouver le nombre total de matchs remportés par chaque équipe
  2. Trouver le nombre total de matchs perdus par chaque équipe
  3. Trouver le nombre total de matchs où aucune des équipes n’a gagné
  4. Trouver le nombre total de matchs joués par chaque équipe

Pour comprendre combien de matchs une équipe a gagné, vous devez comprendre pour chaque match quelle équipe a remporté le match. Vous pouvez le faire en comparant les colonnes équipe_1 et équipe_2 avec la colonne gagnant.

Ainsi, pour une ligne spécifique lorsque les valeurs des colonnes équipe_1 et gagnant sont égales, alors équipe_1 est le gagnant.

Vous pouvez traduire exactement la même logique en utilisant l’instruction CASE..WHEN..THEN en SQL comme indiqué ci-dessous.

SELECT équipe_1      , équipe_2      , gagnant      , CASE WHEN équipe_1 = gagnant THEN 1 ELSE 0 END AS drapeau_victoire      , CASE WHEN gagnant IS NULL THEN 1 ELSE 0 END AS drapeau_nulFROM analyticswithsuraj.teams

Comme indiqué dans la requête ci-dessus, vous créerez une colonne supplémentaire drapeau_victoire. Lorsqu’une équipe est gagnante, vous attribuerez la valeur 1 à cette colonne. De même, si la colonne gagnant est NULL, vous attribuerez la valeur 1 à la colonne drapeau_nul.

Ainsi, la requête ci-dessus créera la sortie suivante pour toutes les équipes dans la colonne équipe_1.

Première sortie partielle pour équipe_1 | Image par Auteur

De même, lorsque les valeurs des colonnes équipe_2 et gagnant sont égales, alors équipe_2 est le gagnant. Vous pouvez donc écrire exactement la même requête pour toutes les équipes dans équipe_2

SELECT équipe_1      , équipe_2      , gagnant      , CASE WHEN équipe_2 = gagnant THEN 1 ELSE 0 END AS drapeau_victoire      , CASE WHEN gagnant IS NULL THEN 1 ELSE 0 END AS drapeau_nulFROM analyticswithsuraj.teams

où vous obtiendrez la sortie suivante pour les valeurs dans équipe_2

Sortie partielle pour équipe_2 | Image par Auteur

Eh bien, les deux requêtes ci-dessus sont juste pour votre compréhension. En réalité, vous pouvez créer un seul CTE pour le drapeau_victoire et le drapeau_nul de chaque équipe dans les colonnes équipe_1 et équipe_2 comme indiqué ci-dessous.

WITH drapeau_victoire_nul AS(SELECT équipe_1 as équipe      , CASE WHEN équipe_1 = gagnant THEN 1 ELSE 0 END AS drapeau_victoire      , CASE WHEN gagnant IS NULL THEN 1 ELSE 0 END AS drapeau_nulFROM analyticswithsuraj.teamsUNION ALLSELECT équipe_2 as équipe      , CASE WHEN équipe_2 = gagnant THEN 1 ELSE 0 END AS drapeau_victoire      , CASE WHEN gagnant IS NULL THEN 1 ELSE 0 END AS drapeau_nulFROM analyticswithsuraj.teams)

Cela créera un CTE comme ceci – Je l’ai montré uniquement pour votre compréhension.

La sortie du CTE | Image par Auteur

Rappelez-vous que vous êtes toujours dans le format long de la table et vous avez maintenant des informations sur si chaque équipe gagne ou non le match.

Ensuite, vous devez simplement agréger les colonnes pour obtenir le nombre total de matchs que chaque équipe a joués, gagnés et perdus. Vous pouvez le faire aussi simplement que la requête suivante.

SELECT équipe        , COUNT(*) AS matchs_joués        , SUM(drapeau_victoire) AS matchs_gagnés        , COUNT(*) - SUM(drapeau_victoire) - SUM(drapeau_nul) AS matchs_perdus        , SUM(drapeau_nul) AS matchs_nulsFROM drapeau_victoire_nulGROUP BY équipeORDER BY équipe

COUNT(*) vous donne le nombre total de fois où chaque équipe est apparue dans le CTE drapeau_victoire_nul et en soustrayant les matchs gagnés et les matchs nuls, vous obtiendrez le nombre total de matchs que chaque équipe a perdus.

Sortie finale - Tableau des points | Image par Auteur

Sans créer une CTE séparément, vous pouvez également écrire la requête comme suit et passer toute la requête CASE..WHEN comme sous-requête.

SELECT team        , COUNT(*) AS matches_played        , SUM(win_flag) AS matches_won        , COUNT(*) - SUM(win_flag) - SUM(draw_flag) AS matches_lost        , SUM(draw_flag) AS matches_drawFROM   (  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

Cela donnera également exactement la même sortie que celle mentionnée ci-dessus.

Eh bien, il peut y avoir plusieurs façons de résoudre cette question – j’ai trouvé cette approche plus facile. Si vous trouvez une autre solution à cette question, n’hésitez pas à la mentionner dans les commentaires.

Exemple 2: Trouver les nouveaux clients et les clients répétés pour un site web de commerce électronique

C’est l’un des exemples classiques de comparaison de dates et d’implémentation de logique If..Else en utilisant l’instruction CASE..WHEN. Vous pouvez rencontrer ce type de problème dans n’importe quelle entreprise qui traite avec des clients.

Le scénario est le suivant – Vous avez un site web de commerce électronique où chaque jour, les clients visitent et achètent des produits. Votre tâche est d’identifier chaque jour combien de clients sont nouveaux et combien de clients sont répétés.

Type de question | Image par l'auteur

Voici une table d’entrée – commandes – où vous pouvez voir les clients avec les identifiants de client ABC101, BCD201 et ABD101 qui ont visité le site web à plusieurs reprises et acheté différents produits.

Tableau d'entrée avec des données factices | Image par l'auteur

Décortiquons la question en sous-tâches suivantes –

  1. Trouver la première fois, c’est-à-dire la première date à laquelle le client a visité le site web
  2. Comparer la première date avec la date de commande pour décider si le client est un visiteur répété ou un visiteur de première fois

Vous pouvez facilement résoudre la première sous-tâche en utilisant GROUP BY pour regrouper tous les enregistrements par identifiant de client et trouver le minimum de la date de commande, comme indiqué ci-dessous.

SELECT customer_id      , MIN(order_date) as first_order_dateFROM analyticswithsuraj.ordersGROUP BY customer_id
Date de commande minimale | Image par l'auteur

C’était simple !

Ensuite, pour comparer first_order_date avec chaque order_date, d’abord, vous devez regrouper les deux colonnes dans une seule table.

Vous pouvez facilement le faire en utilisant JOIN sur customer_id comme indiqué ci-dessous. Ici, vous pouvez créer une CTE en utilisant la requête ci-dessus afin d’obtenir une table temporaire à joindre avec la table d’entrée.

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
Sortie de la jointure interne | Image par l'auteur

Maintenant, une fois que vous avez les deux colonnes dans une seule table, vous pouvez comparer order_date avec first_order_date et mettre en œuvre la logique suivante avec If..Else.

  1. Si first_order_date & order_date sont identiques, alors le client est un nouveau client
  2. Si first_order_date & order_date sont différents, alors le client est un client récurrent

Idéalement, vous devez créer deux colonnes pour mettre en œuvre les deux déclarations If..Else ci-dessus en utilisant CASE WHEN en SQL.

Vous n’avez pas besoin de créer une table séparée, vous pouvez plutôt ajouter deux colonnes supplémentaires dans la requête ci-dessus où vous avez joint les deux tables. Voici comment cela peut être fait.

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
Sortie de CASE WHEN créant deux nouvelles colonnes | Image par Auteur

En conséquence, la colonne new_customer_flag sera de 1 lorsque les colonnes first_order_date et order_date sont égales. De même, la colonne repeat_customer_flag sera de 1 lorsque les colonnes first_order_date et order_date sont différentes.

La dernière étape consiste simplement à regrouper tous les enregistrements par date de commande et à additionner les colonnes new_customer_flag et repeat_customer_flag.

Pour ce faire, vous aurez besoin de la table ci-dessus que vous pouvez obtenir en créant un autre CTE comme indiqué ci-dessous.

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 number_of_new_customers      , SUM(repeat_customer_flag) AS number_of_repeat_customersFROM customersGROUP BY order_dateORDER BY order_date
Nombre de nouveaux et de clients récurrents chaque jour | Image par Auteur

Voici comment vous obtiendrez la sortie requise. Vous pouvez vérifier les résultats en les comparant avec la table d’entrée.

Encore une fois, vous pouvez avoir une approche différente pour résoudre cette question – c’est l’approche la plus simple que j’ai trouvée. N’oubliez pas de mentionner votre approche dans les commentaires ci-dessous.

En résumé,

Dans cet article, vous avez appris comment aborder des scénarios réels d’utilisation de CASE WHEN en SQL et décomposer des requêtes SQL complexes en requêtes simples. J’espère que vous avez apprécié cet article.

Les études de cas et les questions basées sur CASE WHEN, RANK(), ROW_NUMBER() et GROUP BY sont courantes lors des entretiens d’embauche en science des données. Décomposer la question en sous-tâches plus petites montre à l’intervieweur votre approche et votre processus de réflexion pour résoudre la question.

Par conséquent, ce sujet sera certainement utile pour perfectionner vos compétences en SQL CASE WHEN, GROUP BY, et pour réussir votre prochain entretien d’embauche également.

Intéressé par la lecture d’autres histoires sur VoAGI ?

💡 Considérez devenir membre de VoAGI pour accéder à un nombre illimité d’histoires sur VoAGI et à la newsletter VoAGI quotidienne intéressante. Je recevrai une petite partie de votre frais et cela ne vous coûtera rien de plus.

💡 Assurez-vous de vous inscrire et de rejoindre plus de 200 autres pour ne manquer aucun autre article sur les guides, astuces et bonnes pratiques en science des données en SQL et Python.

Merci de votre lecture !

Jeux de données : Ce sont les jeux de données fictifs que j’ai créés pour ces exemples. Vous pouvez les télécharger gratuitement depuis mon dépôt Github — Exemple 1 et Exemple 2 .

We will continue to update IPGirl; if you have any questions or suggestions, please contact us!

Share:

Was this article helpful?

93 out of 132 found this helpful

Discover more

AI

Hugging Face présente SafeCoder une solution d'assistant de code conçue pour les entreprises.

Les solutions d’assistant de code sont des outils ou des applications logicielles qui aident les développeurs l...

AI

L'IA et l'équité éducative un plan pour combler l'écart

Dans un monde idéal, tout le monde aurait la même opportunité pour une éducation de qualité. Cependant, la réalité es...

AI

Les pirates informatiques exploitent une faille dans le logiciel Citrix malgré la correction.

Citrix Bleed, une faille critique dans le logiciel de Citrix Systems, est exploitée par des pirates soutenus par des ...

AI

SalesForce lance Einstein Studio et la possibilité d'apporter votre propre modèle (BYOM)

Dans le cadre de son service Data Cloud, Salesforce a dévoilé un nouvel outil de formation de modèles d’IA et d...

AI

Le magasin d'applications - mais pour les voitures - pourrait ne pas être loin

Le constructeur automobile General Motors a publié une suite d'interfaces de programmation d'applications open source...

AI

L'IA discrimine contre les locuteurs non natifs de l'anglais

Une étude récente a révélé une vérité inquiétante à propos de l’intelligence artificielle (IA) : ses algorithme...