Préparation aux entretiens

SQL - Cet article fait partie d'une série.

Partie 9: Cet article

Partie 15: Ordre d’écriture & d’exécution

Guide de préparation pour les entretiens, rédigé par Dylan ARNAUD :

Le SQL est la compétence fondamentale pour les métiers de la donnée. Que vous visiez un poste de Data Analyst, Data Engineer ou Développeur SQL, votre capacité à manipuler et interroger les données sera rigoureusement évaluée.
Ce guide rassemble 37 exercices concrets, de difficulté progressive, pour couvrir un large spectre des cas que vous rencontrerez en entretien. Chaque solution est pensée pour être claire, professionnelle et refléter une solide culture SQL.

1. Trouver les enregistrements en double
#

-- Compte les occurrences de chaque combinaison de colonnes.
-- Ne retourne que celles qui apparaissent plus d'une fois.

SELECT
  colonne1,
  colonne2,
  COUNT(*) AS occurrences
FROM
  votre_table
GROUP BY
  colonne1,
  colonne2
HAVING
  COUNT(*) > 1;

2. Calculer le revenu total par produit
#

-- Calcule le revenu pour chaque ligne puis l'agrège par produit.

SELECT
  id_produit,
  SUM(quantite * prix) AS revenu_total
FROM
  Ventes
GROUP BY
  id_produit;

3. Afficher le nombre de commandes par client
#

SELECT
  id_client,
  COUNT(id_commande) AS nombre_commandes
FROM
  Commandes
GROUP BY
  id_client;

4. Compter les clients ayant passé plus de 5 commandes
#

-- Utilise HAVING pour filtrer les résultats d'une agrégation.

SELECT
  id_client,
  COUNT(id_commande) AS nombre_commandes
FROM
  Commandes
GROUP BY
  id_client
HAVING
  COUNT(id_commande) > 5;

5. Trouver les employés sans département assigné
#

-- Une jointure externe gauche (LEFT JOIN) permet de garder tous les employés,
-- même ceux sans correspondance dans la table des départements.

SELECT
  e.*
FROM
  Employe e
LEFT JOIN
  Departement d ON e.id_departement = d.id_departement
WHERE
  d.id_departement IS NULL;

6. Trouver les produits qui n’ont jamais été vendus
#

-- Même logique que la précédente, mais appliquée aux produits et aux ventes.

SELECT
  p.id_produit,
  p.nom_produit
FROM
  Produits p
LEFT JOIN
  Ventes v ON p.id_produit = v.id_produit
WHERE
  v.id_produit IS NULL;

7. Identifier les employés gagnant plus que leur manager
#

-- L'auto-jointure (SELF JOIN) consiste à joindre une table avec elle-même.
-- On la traite comme deux tables distinctes en utilisant des alias (e et m).

SELECT
  e.nom AS nom_employe,
  e.salaire AS salaire_employe,
  m.nom AS nom_manager,
  m.salaire AS salaire_manager
FROM
  Employe e
JOIN
  Employe m ON e.id_manager = m.id_employe
WHERE
  e.salaire > m.salaire;

8. Récupérer le deuxième salaire le plus élevé
#

-- Solution avec une sous-requête : on cherche le salaire maximum
-- qui est inférieur au salaire maximum global.

SELECT MAX(salaire) AS deuxieme_plus_haut_salaire
FROM Employe
WHERE salaire < (SELECT MAX(salaire) FROM Employe);
-- Solution plus moderne et flexible avec OFFSET (PostgreSQL/MySQL)
SELECT salaire
FROM Employe
ORDER BY salaire DESC
LIMIT 1 OFFSET 1;

9. Obtenir les 3 employés les mieux payés
#

-- La clause LIMIT (ou TOP pour SQL Server) est la plus simple pour ce cas.
-- Pour MySQL / PostgreSQL
SELECT * FROM Employe ORDER BY salaire DESC LIMIT 3;
-- Pour SQL Server
SELECT TOP 3 * FROM Employe ORDER BY salaire DESC;

10. Lister les commandes d’un montant supérieur à la moyenne
#

-- Une sous-requête dans la clause WHERE permet de calculer une valeur
-- (ici la moyenne) à la volée pour l'utiliser comme seuil de filtrage.

SELECT
  id_commande,
  montant_total
FROM
  Commandes
WHERE
  montant_total > (SELECT AVG(montant_total) FROM Commandes);

11. Sélectionner les employés embauchés en 2023
#

-- La fonction YEAR() extrait l'année d'une date.
SELECT *
FROM Employe
WHERE YEAR(date_embauche) = 2023;

12. Obtenir la dernière commande passée par chaque client
#

SELECT
  id_client,
  MAX(date_commande) AS date_derniere_commande
FROM
  Commandes
GROUP BY
  id_client;

13. Identifier la première et la dernière commande de chaque client
#

SELECT
  id_client,
  MIN(date_commande) AS date_premiere_commande,
  MAX(date_commande) AS date_derniere_commande
FROM
  Commandes
GROUP BY
  id_client;

14. Calculer le revenu mensuel et le nombre de commandes
#

-- L'extraction du mois peut varier selon le système de BDD.
-- Pour PostgreSQL:

SELECT
  TO_CHAR(date_commande, 'YYYY-MM') AS mois,
  SUM(montant) AS revenu_total,
  COUNT(id_commande) AS nombre_commandes
FROM
  Commandes
GROUP BY
  mois
ORDER BY
  mois;

-- Pour MySQL:
SELECT
  DATE_FORMAT(date_commande, '%Y-%m') AS mois,
  SUM(montant) AS revenu_total,
  COUNT(id_commande) AS nombre_commandes
FROM
  Commandes
GROUP BY
  mois
ORDER BY
  mois;

15. Trouver les clients inactifs (churn)
#

-- On considère inactif un client dont la dernière commande date de plus de 6 mois.
-- Pour PostgreSQL:

SELECT id_client
FROM Commandes
GROUP BY id_client
HAVING MAX(date_commande) < NOW() - INTERVAL '6 months';

-- Pour SQL Server:
SELECT id_client
FROM Commandes
GROUP BY id_client
HAVING MAX(date_commande) < DATEADD(MONTH, -6, GETDATE());

16. Classer les employés par salaire au sein de chaque département
#

-- PARTITION BY segmente les données (par département),
-- ORDER BY définit l'ordre pour le classement à l'intérieur de chaque segment.

SELECT
  nom,
  salaire,
  id_departement,
  RANK() OVER (PARTITION BY id_departement ORDER BY salaire DESC) AS rang_salaire
FROM
  Employe;

17. Afficher le dernier achat de chaque client et son montant
#

-- On utilise une CTE (Common Table Expression) pour numéroter les lignes
-- de chaque client par ordre antéchronologique de commande.
WITH CommandesClassees AS (
SELECT
  id_client,
  id_commande,
  montant_total,
  ROW_NUMBER() OVER(PARTITION BY id_client ORDER BY date_commande DESC) as rn
FROM
  Commandes
)
SELECT
  id_client,
  id_commande,
  montant_total
FROM
  CommandesClassees
WHERE
  rn = 1;

18. Calculer le revenu cumulé par jour
#

-- La clause OVER peut définir une fenêtre cumulative.
-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW est souvent implicite.

SELECT
  date_commande,
  SUM(montant_total) OVER (ORDER BY date_commande) AS revenu_cumule
FROM
  Commandes;

19. Calculer la moyenne mobile des ventes sur 3 jours
#

-- Ici, on définit une fenêtre glissante de 3 jours
-- (les 2 précédents + le jour actuel).

SELECT
  date_commande,
  montant_total,
  AVG(montant_total) OVER (ORDER BY date_commande ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS FROM
  Commandes;

20. Calculer l’écart entre la commande actuelle et la précédente
#

-- LAG() permet d'accéder à la valeur d'une ligne précédente dans la même fenêtre.
WITH CommandesAvecDatePrecedente AS (
SELECT
  id_client,
  date_commande,
  LAG(date_commande, 1) OVER (PARTITION BY id_client ORDER BY date_commande) AS date_commande_FROM
  Commandes
)
SELECT
  id_client,
  date_commande,
  date_commande_precedente,
  DATEDIFF(day, date_commande_precedente, date_commande) AS jours_depuis_derniere_commande
FROM
  CommandesAvecDatePrecedente;

21. Trouver les clients qui ont commandé chaque mois de l’année 2023
#

-- On compte le nombre de mois distincts de commande pour chaque client en 2023.
SELECT id_client
FROM Commandes
WHERE YEAR(date_commande) = 2023
GROUP BY id_client
HAVING COUNT(DISTINCT MONTH(date_commande)) = 12;

22. Calculer la distribution des ventes par produit en pourcentage
#

-- On utilise une CTE pour le revenu total, puis on calcule le pourcentage.
-- Une fonction fenêtre est aussi une excellente alternative.
WITH RevenuParProduit AS (
SELECT
id_produit,
SUM(quantite * prix) as revenu
FROM Ventes
GROUP BY id_produit
)
SELECT
id_produit,
revenu,
(revenu * 100.0 / (SELECT SUM(revenu) FROM RevenuParProduit)) AS pourcentage_total
FROM
RevenuParProduit
ORDER BY
pourcentage_total DESC;

23. Calculer le revenu généré par les nouveaux clients
#

-- On identifie la date de la première commande de chaque client,
-- puis on joint cette information pour sommer les montants.
WITH PremieresCommandes AS (
SELECT
id_client,
MIN(date_commande) AS date_premiere_commande
FROM Commandes
GROUP BY id_client
)
SELECT
SUM(c.montant_total) AS revenu_nouveaux_clients
FROM
Commandes c
JOIN
PremieresCommandes pc ON c.id_client = pc.id_client
AND c.date_commande = pc.date_premiere_commande;

24. Calculer le pourcentage d’employés par département
#

-- Une fonction fenêtre évite une sous-requête coûteuse.
SELECT
id_departement,
COUNT(*) AS nombre_employes,
(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER ()) AS pourcentage_effectif
FROM
Employe
GROUP BY
id_departement;

25. Calculer la croissance du revenu d’une année sur l’autre (YoY)
#

WITH RevenuAnnuel AS (
SELECT
YEAR(date_commande) AS annee,
SUM(montant_total) AS revenu
FROM Commandes
GROUP BY YEAR(date_commande)
),
RevenuAvecAnneePrecedente AS (
SELECT
annee,
revenu,
LAG(revenu, 1, 0) OVER (ORDER BY annee) AS revenu_annee_precedente
FROM RevenuAnnuel
)
SELECT
annee,
revenu,
revenu_annee_precedente,
(revenu - revenu_annee_precedente) * 100.0 / revenu_annee_precedente AS croissance_pct
FROM
RevenuAvecAnneePrecedente
WHERE
revenu_annee_precedente > 0;

26. Calculer le temps moyen entre les commandes pour chaque client
#

WITH DelaisEntreCommandes AS (
SELECT
id_client,
DATEDIFF(day, LAG(date_commande, 1) OVER (PARTITION BY id_client ORDER BY date_commande), FROM
Commandes
)
SELECT
id_client,
AVG(jours_ecart) AS delai_moyen_jours
FROM
DelaisEntreCommandes
WHERE
jours_ecart IS NOT NULL
GROUP BY
id_client;

27. Identifier les produits contribuant à 80% du revenu (Loi de Pareto)
#

WITH RevenuParProduit AS (
SELECT
id_produit,
SUM(quantite * prix) AS revenu
FROM Ventes
GROUP BY id_produit
),
DistributionCumulee AS (
SELECT
id_produit,
revenu,
SUM(revenu) OVER (ORDER BY revenu DESC) AS revenu_cumule,
SUM(revenu) OVER () AS revenu_total
FROM RevenuParProduit
)
SELECT
id_produit,
revenu,
(revenu_cumule / revenu_total) * 100 AS pourcentage_cumule
FROM
DistributionCumulee
WHERE
(revenu_cumule / revenu_total) <= 0.8;

28. Clients ayant acheté mais jamais retourné de produits
#

SELECT id_client FROM Commandes
EXCEPT
SELECT id_client FROM Retours;

29. Calculer la valeur moyenne des commandes par client
#

SELECT id_client, AVG(montant_total) AS valeur_moyenne_commande
FROM Commandes
GROUP BY id_client;

30. Identifier le produit le plus vendu en quantité
#

SELECT id_produit, SUM(quantite) AS total_quantite
FROM Ventes
GROUP BY id_produit
ORDER BY total_quantite DESC
LIMIT 1;

31. Obtenir le revenu total et le nombre de commandes par région
#

SELECT region, SUM(montant_total) AS revenu_total, COUNT(*) AS nombre_commandes
FROM Commandes
GROUP BY region;

32. Trouver les employés embauchés le week-end
#

-- Pour PostgreSQL
SELECT * FROM Employe WHERE EXTRACT(ISODOW FROM date_embauche) IN (6, 7);
-- Pour MySQL
SELECT * FROM Employe WHERE DAYOFWEEK(date_embauche) IN (1, 7);

33. Filtrer les salaires dans une fourchette donnée
#

SELECT * FROM Employe WHERE salaire BETWEEN 50000 AND 80000;

34. Identifier les départements les plus performants par salaire moyen
#

SELECT id_departement, AVG(salaire) AS salaire_moyen
FROM Employe
GROUP BY id_departement
ORDER BY salaire_moyen DESC;

35. Calculer l’écart de salaire maximal par département
#

SELECT id_departement, MAX(salaire) - MIN(salaire) AS ecart_salaire
FROM Employe
GROUP BY id_departement;

36. Détecter les achats supérieurs au 90ème centile du client
#

WITH CommandesAvecDecile AS (
SELECT
id_client,
montant_total,
NTILE(10) OVER (PARTITION BY id_client ORDER BY montant_total) AS decile
FROM Commandes
)
SELECT id_client, montant_total
FROM CommandesAvecDecile
WHERE decile = 10;

37. Récupérer le plus long intervalle entre deux commandes pour chaque client
#

WITH DelaisEntreCommandes AS (
SELECT
id_client,
DATEDIFF(day, LAG(date_commande, 1) OVER (PARTITION BY id_client ORDER BY date_commande), FROM Commandes
)
SELECT id_client, MAX(jours_ecart) AS intervalle_max_jours
FROM DelaisEntreCommandes
GROUP BY id_client;