Guide pratique · Lisibilité · Performance · Récursivité ·

Lecture : ~20 min

Teste ton niveau sur modai-lab.com/test-sql/

Pourquoi ce guide existe

La plupart des analysts imbriquent des sous-requĂȘtes Ă  l'infini. Ça marche. Mais personne ne comprend ce que ça fait — mĂȘme eux.

Quand tu te retrouves Ă  relire ta propre requĂȘte 3 fois pour comprendre ce qu'elle fait, c'est le signal.

La rĂšgle :

  • Logique simple et ponctuelle ? → Sous-requĂȘte

  • Logique rĂ©utilisĂ©e, complexe ou rĂ©cursive ? → CTE

Ce guide couvre :

  • CTE vs sous-requĂȘte : quand choisir quoi exactement

  • Les cas oĂč le CTE divise par 3 le temps d'exĂ©cution

  • Les CTEs rĂ©cursifs expliquĂ©s avec un cas concret

  • Un prompt Claude pour rĂ©Ă©crire tes sous-requĂȘtes en CTEs propres

đŸ§Ș Calibre ton niveau avant de commencer

Sais-tu dĂ©jĂ  quand une CTE est plus performante qu'une sous-requĂȘte ? Fais le test en 5 minutes et reçois ton score instantanĂ©ment.

👉 Faire le test sur modai-lab.com/test-sql/

_Résultat en direct · Niveau calibré · Parcours personnalisé_

PARTIE 1 — La diffĂ©rence fondamentale

Ce que fait une sous-requĂȘte

Une sous-requĂȘte s'imbrique Ă  l'intĂ©rieur d'une autre requĂȘte. Elle peut apparaĂźtre dans le SELECT, le FROM, le WHERE ou le HAVING.

-- Sous-requĂȘte dans WHERE
SELECT nom FROM users
WHERE id IN (SELECT user_id FROM orders WHERE montant > );
 
-- Sous-requĂȘte dans FROM (table dĂ©rivĂ©e)
SELECT u.nom, o.total
FROM users u
JOIN (
SELECT user_id, SUM(montant) AS total
FROM orders
GROUP BY user_id
) o ON u.id = o.user_id;
 
-- Sous-requĂȘte dans SELECT (corrĂ©lĂ©e)
SELECT
nom,
(SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS nb_commandes
FROM users u;
cte-vs-sous-requete · 01

Ce que fait une CTE

Une CTE (Common Table Expression) se dĂ©clare avant la requĂȘte principale avec WITH. Elle est nommĂ©e, lisible, et peut ĂȘtre rĂ©fĂ©rencĂ©e plusieurs fois.

-- MĂȘme rĂ©sultat qu'avec une sous-requĂȘte, mais lisible
WITH commandes_importantes AS (
SELECT user_id FROM orders WHERE montant >
)
SELECT nom FROM users
WHERE id IN (SELECT user_id FROM commandes_importantes);
 
-- CTE avec plusieurs Ă©tapes
WITH
ca_par_user AS (
SELECT user_id, SUM(montant) AS total
FROM orders
GROUP BY user_id
)
SELECT u.nom, c.total
FROM users u
JOIN ca_par_user c ON u.id = c.user_id;
cte-vs-sous-requete · 02

La comparaison cĂŽte Ă  cĂŽte

-- ❌ Sous-requĂȘte imbriquĂ©e — 3 niveaux de SELECT
SELECT nom, total_commandes, rang
FROM (
SELECT nom, total_commandes,
RANK() OVER (ORDER BY total_commandes DESC) AS rang
FROM (
SELECT u.nom, SUM(o.montant) AS total_commandes
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2024-01-01'
GROUP BY u.id, u.nom
) agg
) ranked
WHERE rang <= ;
-- Pour comprendre cette requĂȘte, tu dois lire de l'intĂ©rieur vers l'extĂ©rieur
-- 3 niveaux d'imbrication → illisible en revue de code
 
-- ✅ CTE — chaque Ă©tape est nommĂ©e et lisible
WITH
commandes_2024 AS (
SELECT u.nom, SUM(o.montant) AS total_commandes
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at >= '2024-01-01'
GROUP BY u.id, u.nom
),
classement AS (
SELECT nom, total_commandes,
RANK() OVER (ORDER BY total_commandes DESC) AS rang
FROM commandes_2024
)
SELECT nom, total_commandes, rang
FROM classement
WHERE rang <= ;
-- Tu lis de haut en bas, chaque Ă©tape a un nom qui dit ce qu'elle fait
cte-vs-sous-requete · 03

PARTIE 2 — Quand choisir quoi exactement

Utilise une sous-requĂȘte quand

1. La logique est simple et n'est utilisée qu'une seule fois

-- Simple, ponctuel, pas besoin de CTE
SELECT * FROM products
WHERE prix > (SELECT AVG(prix) FROM products);
 
-- Filtre rapide avec EXISTS
SELECT nom FROM users
WHERE EXISTS (SELECT FROM orders o WHERE o.user_id = users.id);
cte-vs-sous-requete · 04 · 1. La logique est simple et n'est utilisée qu'une seule fois

2. Le filtre est dans le WHERE et ne doit pas ĂȘtre rĂ©utilisĂ©

SELECT * FROM orders
WHERE user_id IN (
SELECT id FROM users WHERE pays = 'France'
);
-- Simple, lisible, pas de raison d'en faire une CTE
cte-vs-sous-requete · 05 · 2. Le filtre est dans le WHERE et ne doit pas ĂȘtre rĂ©utilisĂ©

3. La sous-requĂȘte est dans le SELECT pour une valeur scalaire simple

-- Acceptable si la table est petite et le besoin ponctuel
SELECT
categorie,
COUNT(*) AS nb_produits,
(SELECT COUNT(*) FROM products) AS total_produits
FROM products
GROUP BY categorie;
cte-vs-sous-requete · 06 · 3. La sous-requĂȘte est dans le SELECT pour une valeur scalai

Utilise une CTE quand

1. La mĂȘme logique est rĂ©fĂ©rencĂ©e plusieurs fois

-- ❌ Sans CTE : la sous-requĂȘte est rĂ©pĂ©tĂ©e 2 fois → exĂ©cutĂ©e 2 fois
SELECT *
FROM (SELECT user_id, COUNT(*) AS nb FROM orders GROUP BY user_id) t1
JOIN (SELECT user_id, COUNT(*) AS nb FROM orders GROUP BY user_id) t2
ON t1.user_id != t2.user_id
WHERE t1.nb > t2.nb;
 
-- ✅ Avec CTE : calculĂ© une seule fois, rĂ©utilisĂ© deux fois
WITH commandes_par_user AS (
SELECT user_id, COUNT(*) AS nb FROM orders GROUP BY user_id
)
SELECT *
FROM commandes_par_user t1
JOIN commandes_par_user t2 ON t1.user_id != t2.user_id
WHERE t1.nb > t2.nb;
cte-vs-sous-requete · 07 · 1. La mĂȘme logique est rĂ©fĂ©rencĂ©e plusieurs fois

2. La requĂȘte a plus de 2 niveaux d'imbrication

-- RĂšgle pratique : plus de 2 SELECT imbriquĂ©s → CTE obligatoire pour la lisibilitĂ©
-- Un collĂšgue doit pouvoir lire et corriger ta requĂȘte sans toi
cte-vs-sous-requete · 08 · 2. La requĂȘte a plus de 2 niveaux d'imbrication

3. Tu veux déboguer étape par étape

-- Avantage CTE : tu peux exécuter chaque bloc séparément pour vérifier
WITH
etape1 AS (
SELECT user_id, SUM(montant) AS ca
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY user_id
)
-- Exécute juste ça pour vérifier etape1 :
SELECT * FROM etape1 LIMIT ;
 
-- Puis continue avec etape2...
cte-vs-sous-requete · 09 · 3. Tu veux déboguer étape par étape

4. La logique est récursive (hiérarchies, graphes)

-- Les CTEs rĂ©cursives ne peuvent pas ĂȘtre remplacĂ©es par des sous-requĂȘtes
WITH RECURSIVE hierarchie AS (
SELECT id, nom, manager_id, AS niveau
FROM employes WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.nom, e.manager_id, h.niveau +
FROM employes e JOIN hierarchie h ON e.manager_id = h.id
)
SELECT * FROM hierarchie ORDER BY niveau, nom;
cte-vs-sous-requete · 10 · 4. La logique est récursive (hiérarchies, graphes)

Le tableau de décision

Situation

Recommandation

Logique utilisée 1 seule fois

Sous-requĂȘte

Logique utilisée 2+ fois

CTE

1 niveau d'imbrication

Sous-requĂȘte acceptable

2+ niveaux d'imbrication

CTE obligatoire

Récursivité (hiérarchies)

CTE récursive uniquement

DĂ©bogage Ă©tape par Ă©tape

CTE

RequĂȘte dans un rapport partagĂ©

CTE (lisibilité pour les autres)

Optimisation de performance

DĂ©pend du SGBD (voir Partie 3)

đŸ§Ș Teste tes rĂ©flexes sur les cas de choix

Ces dĂ©cisions de structure sont testĂ©es en entretien code review. Modai Lab te soumet des requĂȘtes Ă  refactoriser — score et feedback immĂ©diats.

👉 Pratiquer sur modai-lab.com

_Exercices interactifs · Datasets réels · Feedback instantané_

PARTIE 3 — Performance : quand le CTE change tout

Le cas oĂč le CTE divise le temps d'exĂ©cution par 3

-- Contexte : rapport de rétention mensuel avec plusieurs métriques
-- La mĂȘme agrĂ©gation lourde est utilisĂ©e 3 fois dans la requĂȘte
 
-- ❌ Sous-requĂȘte rĂ©pĂ©tĂ©e 3 fois → calculĂ©e 3 fois
SELECT
mois,
nb_users_actifs,
nb_users_actifs_precedent,
ROUND(. * nb_users_actifs / NULLIF(nb_users_actifs_precedent, ), ) AS retention
FROM (
SELECT
DATE_TRUNC('month', created_at) AS mois,
COUNT(DISTINCT user_id) AS nb_users_actifs,
LAG(COUNT(DISTINCT user_id)) OVER (ORDER BY DATE_TRUNC('month', created_at))
AS nb_users_actifs_precedent
FROM events
WHERE type = 'session'
GROUP BY
) monthly
-- Si on doit aussi calculer d'autres mĂ©triques avec la mĂȘme base,
-- on rĂ©pĂšte cette sous-requĂȘte autant de fois → N scans de la table events
 
-- ✅ CTE calculĂ©e une seule fois, rĂ©utilisĂ©e partout
WITH activite_mensuelle AS (
SELECT
DATE_TRUNC('month', created_at) AS mois,
COUNT(DISTINCT user_id) AS nb_actifs
FROM events
WHERE type = 'session'
GROUP BY
),
avec_precedent AS (
SELECT
mois,
nb_actifs,
LAG(nb_actifs) OVER (ORDER BY mois) AS nb_actifs_precedent
FROM activite_mensuelle
)
SELECT
mois,
nb_actifs,
nb_actifs_precedent,
ROUND(. * nb_actifs / NULLIF(nb_actifs_precedent, ), ) AS retention_pct
FROM avec_precedent
ORDER BY mois;
-- La table events n'est scannée qu'une seule fois
cte-vs-sous-requete · 11

La nuance importante : matérialisation des CTEs

-- Le comportement dépend du SGBD :
 
-- PostgreSQL < 12 : CTE toujours matérialisée (résultat stocké en mémoire)
-- PostgreSQL >= 12 : CTE inlinée par défaut si utilisée une seule fois
-- BigQuery : CTE inlinée par défaut
-- Snowflake : CTE inlinée par défaut
 
-- PostgreSQL : forcer la matérialisation si tu veux éviter le recalcul
WITH calcul_lourd AS MATERIALIZED (
SELECT user_id, complex_calculation(col) AS result
FROM large_table
)
SELECT * FROM calcul_lourd WHERE result >
UNION ALL
SELECT * FROM calcul_lourd WHERE result < ;
-- Avec MATERIALIZED : calcul_lourd est calculé une fois, résultat en cache
 
-- Forcer l'inlining (ne pas matérialiser)
WITH calcul_lourd AS NOT MATERIALIZED (
SELECT user_id, col FROM large_table WHERE statut = 'actif'
)
SELECT * FROM calcul_lourd;
-- Équivalent Ă  Ă©crire la sous-requĂȘte directement
cte-vs-sous-requete · 12

Quand la sous-requĂȘte est plus rapide

-- Cas oĂč une sous-requĂȘte scalaire permet un court-circuit
-- EXISTS s'arrĂȘte dĂšs la premiĂšre correspondance trouvĂ©e
 
-- ✅ Plus rapide que COUNT(*) > 0 ou que la CTE Ă©quivalente
SELECT nom FROM users u
WHERE EXISTS (
SELECT FROM orders o
WHERE o.user_id = u.id
AND o.montant >
AND o.created_at >= '2024-01-01'
LIMIT -- certains SGBD l'optimisent automatiquement
);
 
-- La CTE Ă©quivalente ferait un scan complet de orders
-- EXISTS s'arrĂȘte au premier rĂ©sultat : plus efficace pour un test de prĂ©sence
cte-vs-sous-requete · 13

VĂ©rifier la performance avec EXPLAIN

-- Toujours comparer les plans d'exĂ©cution pour les requĂȘtes critiques
EXPLAIN ANALYZE
WITH cte AS (
SELECT user_id, SUM(montant) AS ca
FROM orders GROUP BY user_id
)
SELECT * FROM cte WHERE ca > ;
 
-- Comparer avec la sous-requĂȘte Ă©quivalente
EXPLAIN ANALYZE
SELECT * FROM (
SELECT user_id, SUM(montant) AS ca
FROM orders GROUP BY user_id
) sub WHERE ca > ;
 
-- Sur PostgreSQL 12+ : les deux plans devraient ĂȘtre identiques (CTE inlinĂ©e)
-- Sur PostgreSQL < 12 : le CTE peut ĂȘtre plus lent (fence d'optimisation)
cte-vs-sous-requete · 14

PARTIE 4 — Les CTEs rĂ©cursives expliquĂ©es avec un cas concret

La structure d'une CTE récursive

WITH RECURSIVE nom_cte AS (
-- Ancre : la condition de départ (non récursive)
SELECT ...
 
UNION ALL
 
-- Partie rĂ©cursive : rĂ©fĂ©rence la CTE elle-mĂȘme
SELECT ...
FROM source
JOIN nom_cte ON ... -- ← rĂ©fĂ©rence Ă  elle-mĂȘme
WHERE ... -- condition d'arrĂȘt
)
SELECT * FROM nom_cte;
cte-vs-sous-requete · 15

RĂšgles :

  • L'ancre s'exĂ©cute une fois en premier

  • La partie rĂ©cursive s'exĂ©cute jusqu'Ă  ce qu'elle ne retourne plus de lignes

  • UNION ALL pour garder tous les rĂ©sultats, UNION pour dĂ©dupliquer

  • Toujours avoir une condition d'arrĂȘt pour Ă©viter une boucle infinie

Cas concret 1 — Parcourir une hiĂ©rarchie d'organisation

-- Table : employes (id, nom, poste, manager_id)
-- Objectif : afficher tous les subordonnés d'un directeur
 
WITH RECURSIVE equipe AS (
-- Ancre : le directeur de départ
SELECT id, nom, poste, manager_id, AS niveau,
nom AS chemin
FROM employes
WHERE id = -- id du directeur
 
UNION ALL
 
-- Récursion : les subordonnés directs de chaque niveau
SELECT e.id, e.nom, e.poste, e.manager_id,
eq.niveau + ,
eq.chemin || ' → ' || e.nom
FROM employes e
JOIN equipe eq ON e.manager_id = eq.id
)
SELECT
REPEAT(' ', niveau) || nom AS arbre,
poste,
niveau,
chemin
FROM equipe
ORDER BY chemin;
cte-vs-sous-requete · 16

RĂ©sultat :

Directeur Commercial | niveau 0
Alice Dupont | niveau 1
Marc Leroy | niveau 2
Julie Martin | niveau 2
Bob Durand | niveau 1
Nathalie Petit | niveau 2
cte-vs-sous-requete · 17 · Résultat :

Cas concret 2 — GĂ©nĂ©rer une sĂ©rie de dates sans trous

-- Générer tous les jours d'une année pour un rapport sans trous
WITH RECURSIVE calendrier AS (
SELECT '2024-01-01'::DATE AS jour
 
UNION ALL
 
SELECT jour + INTERVAL '1 day'
FROM calendrier
WHERE jour < '2024-12-31'
)
SELECT
c.jour,
COALESCE(SUM(o.montant), ) AS ca_journalier,
COUNT(o.id) AS nb_commandes
FROM calendrier c
LEFT JOIN orders o
ON DATE(o.created_at) = c.jour
AND o.statut = 'livré'
GROUP BY c.jour
ORDER BY c.jour;
-- Tous les jours apparaissent, mĂȘme ceux sans commandes (CA = 0)
cte-vs-sous-requete · 18

Cas concret 3 — Calcul de chemin dans un graphe (supply chain)

-- Table : flux (origine, destination, cout)
-- Objectif : trouver tous les chemins possibles d'un entrepĂŽt Ă  un client
-- avec leur coût total
 
WITH RECURSIVE chemins AS (
-- Ancre : départ depuis l'entrepÎt source
SELECT
origine,
destination,
cout,
ARRAY[origine, destination] AS chemin,
AS nb_etapes,
FALSE AS cycle
FROM flux
WHERE origine = 'EntrepĂŽt Paris'
 
UNION ALL
 
-- RĂ©cursion : continuer le chemin
SELECT
c.origine,
f.destination,
c.cout + f.cout,
c.chemin || f.destination,
c.nb_etapes + ,
f.destination = ANY(c.chemin) -- détection de cycle
FROM flux f
JOIN chemins c ON f.origine = c.destination
WHERE NOT c.cycle
AND c.nb_etapes < -- limite de profondeur
)
SELECT
chemin,
cout AS cout_total,
nb_etapes
FROM chemins
WHERE destination = 'Client Lyon'
ORDER BY cout_total ASC;
cte-vs-sous-requete · 19

Protéger une CTE récursive contre les boucles infinies

-- 3 protections Ă  toujours mettre en place :
 
-- 1. Condition d'arrĂȘt explicite dans le WHERE
WHERE niveau < -- limite de profondeur
 
-- 2. DĂ©tection de cycle avec un tableau de visites
ARRAY[id] AS visites, -- dans l'ancre
c.visites || e.id, -- dans la récursion
NOT (e.id = ANY(c.visites)) -- condition d'arrĂȘt
 
-- 3. LIMIT dans la CTE (PostgreSQL)
WITH RECURSIVE ... (
...
UNION ALL
SELECT ... FROM ... LIMIT -- ne fonctionne pas directement, utiliser WHERE
)
-- Mieux : utiliser une colonne compteur
WHERE iterations <
cte-vs-sous-requete · 20

đŸ§Ș Teste les CTEs rĂ©cursives en conditions rĂ©elles

Les CTEs récursives sont rarement enseignées mais souvent testées au niveau senior. Modai Lab propose des exercices sur les hiérarchies et les graphes avec des données réelles.

👉 Pratiquer sur modai-lab.com/test-sql/

_Score immédiat · Feedback senior · Parcours entretien_

PARTIE 5 — Prompt Claude pour rĂ©Ă©crire tes sous-requĂȘtes en CTEs

Prompt de refactoring complet

Tu es un expert SQL senior. RĂ©Ă©cris la requĂȘte suivante en remplaçant
les sous-requĂȘtes imbriquĂ©es par des CTEs propres et lisibles.
 
REQUÊTE ORIGINALE :
[coller ta requĂȘte]
 
CONTEXTE :
- SGBD : [PostgreSQL / BigQuery / Snowflake / autre]
- Objectif de la requĂȘte : [dĂ©crire en une phrase ce qu'elle fait]
- Contrainte de performance : [oui / non — si oui, prĂ©ciser le volume de donnĂ©es]
 
Pour chaque CTE créée :
1. Donne-lui un nom qui décrit exactement ce qu'elle calcule
2. Ajoute un commentaire d'une ligne expliquant son rĂŽle
3. Indique si elle remplace une sous-requĂȘte rĂ©pĂ©tĂ©e (gain de performance)
 
Format de réponse :
- La requĂȘte refactorisĂ©e avec CTEs
- Un tableau listant : [sous-requĂȘte originale → nom de la CTE → raison du refactoring]
- Les Ă©ventuels gains de performance attendus
cte-vs-sous-requete · 21

Prompt de diagnostic

Analyse cette requĂȘte SQL et dis-moi :
1. Combien de fois chaque sous-requĂȘte est exĂ©cutĂ©e ?
2. Quelles sous-requĂȘtes gagneraient Ă  ĂȘtre transformĂ©es en CTE ?
3. Y a-t-il des sous-requĂȘtes corrĂ©lĂ©es (exĂ©cutĂ©es une fois par ligne) ?
4. Quel est le niveau maximal d'imbrication et est-ce lisible ?
 
[coller ta requĂȘte]
 
SGBD : [PostgreSQL / BigQuery / Snowflake]
cte-vs-sous-requete · 22

Prompt d'optimisation performance

Cette requĂȘte est lente. Analyse-la et propose une version avec CTEs
qui réduit le nombre de scans de tables.
 
REQUÊTE LENTE :
[coller ta requĂȘte]
 
EXPLAIN ANALYZE (si disponible) :
[coller le plan d'exécution]
 
SGBD : [nom]
Volume de données : [ex: table orders = 50M lignes]
 
Propose :
1. La version refactorisée avec CTEs nommées
2. Les index à créer si nécessaire
3. L'explication du gain de performance attendu
cte-vs-sous-requete · 23

Les signaux qui indiquent qu'il faut refactoriser en CTE

□ Tu lis ta propre requĂȘte 3 fois pour comprendre ce qu'elle fait
□ Tu as plus de 2 niveaux de SELECT imbriquĂ©s
□ La mĂȘme sous-requĂȘte apparaĂźt 2+ fois dans la requĂȘte
□ Quelqu'un d'autre ne peut pas modifier ta requĂȘte sans toi
□ Tu ne sais plus quel alias appartient à quel niveau
□ Le EXPLAIN ANALYZE montre des scans rĂ©pĂ©tĂ©s sur la mĂȘme table
cte-vs-sous-requete · 24

PARTIE 6 — Patterns avancĂ©s avec les CTEs

Pattern 1 — CTE pour isoler la logique mĂ©tier

-- SĂ©parer la logique mĂ©tier (qui est un "bon client") de la requĂȘte finale
WITH
-- Définition métier : client actif = commande dans les 90 derniers jours
clients_actifs AS (
SELECT DISTINCT user_id
FROM orders
WHERE created_at >= NOW() - INTERVAL '90 days'
AND statut = 'livré'
),
-- DĂ©finition mĂ©tier : client premium = CA > 500€ sur 12 mois
clients_premium AS (
SELECT user_id
FROM orders
WHERE created_at >= NOW() - INTERVAL '12 months'
GROUP BY user_id
HAVING SUM(montant) >
)
SELECT
u.id,
u.nom,
CASE
WHEN ca.user_id IS NOT NULL AND cp.user_id IS NOT NULL THEN 'Actif Premium'
WHEN ca.user_id IS NOT NULL THEN 'Actif Standard'
WHEN cp.user_id IS NOT NULL THEN 'Inactif Premium'
ELSE 'Inactif Standard'
END AS segment
FROM users u
LEFT JOIN clients_actifs ca ON u.id = ca.user_id
LEFT JOIN clients_premium cp ON u.id = cp.user_id;
-- Si la définition de "premium" change, tu modifies une seule CTE
cte-vs-sous-requete · 25

Pattern 2 — CTE pour construire un rapport Ă©tape par Ă©tape

WITH
-- Étape 1 : donnĂ©es brutes filtrĂ©es
ventes_2024 AS (
SELECT user_id, produit_id, montant, DATE_TRUNC('month', created_at) AS mois
FROM orders
WHERE created_at >= '2024-01-01'
AND statut = 'livré'
),
-- Étape 2 : agrĂ©gation par user et par mois
ca_mensuel_user AS (
SELECT user_id, mois, SUM(montant) AS ca_mois
FROM ventes_2024
GROUP BY user_id, mois
),
-- Étape 3 : variation mois sur mois
avec_variation AS (
SELECT
user_id, mois, ca_mois,
LAG(ca_mois) OVER (PARTITION BY user_id ORDER BY mois) AS ca_mois_prec,
ca_mois - LAG(ca_mois) OVER (PARTITION BY user_id ORDER BY mois) AS variation
FROM ca_mensuel_user
),
-- Étape 4 : classement des meilleurs clients par mois
classement AS (
SELECT *,
RANK() OVER (PARTITION BY mois ORDER BY ca_mois DESC) AS rang_mois
FROM avec_variation
)
-- RĂ©sultat final : top 10 par mois avec Ă©volution
SELECT u.nom, c.mois, c.ca_mois, c.variation, c.rang_mois
FROM classement c
JOIN users u ON c.user_id = u.id
WHERE c.rang_mois <=
ORDER BY c.mois, c.rang_mois;
cte-vs-sous-requete · 26

Pattern 3 — CTE pour valider des donnĂ©es avant transformation

-- Pattern "CTE de qualité" : vérifier les données avant de les utiliser
WITH
-- Validation : détecter les anomalies avant de continuer
anomalies AS (
SELECT id, montant, 'montant négatif' AS type_anomalie
FROM orders WHERE montant <
UNION ALL
SELECT id, montant, 'montant NULL'
FROM orders WHERE montant IS NULL
UNION ALL
SELECT id, montant, 'date future'
FROM orders WHERE created_at > NOW()
),
-- Données propres seulement
commandes_valides AS (
SELECT o.*
FROM orders o
WHERE o.id NOT IN (SELECT id FROM anomalies)
)
-- Afficher les anomalies pour audit
SELECT * FROM anomalies;
 
-- Ou utiliser les données propres directement
-- SELECT * FROM commandes_valides WHERE ...;
cte-vs-sous-requete · 27

RĂ©capitulatif — CTE vs Sous-requĂȘte

CritĂšre

Sous-requĂȘte

CTE

Syntaxe

ImbriquĂ©e dans la requĂȘte

Déclarée avant avec WITH

Lisibilité

Difficile au-delĂ  de 2 niveaux

Excellente — chaque Ă©tape est nommĂ©e

RĂ©utilisation

Impossible — Ă  rĂ©pĂ©ter

RĂ©utilisable autant de fois

Performance

Peut ĂȘtre recalculĂ©e N fois

Matérialisable une seule fois

DĂ©bogage

Difficile — tout ou rien

Facile — tester chaque bloc sĂ©parĂ©ment

Récursivité

Impossible

Possible avec WITH RECURSIVE

Cas d'usage

Logique simple, 1 usage

Logique complexe, multi-étapes, récursive

✅ Checklist — Choisir entre CTE et sous-requĂȘte

  • ☐ Ma sous-requĂȘte est utilisĂ©e une seule fois et est simple → sous-requĂȘte acceptable

  • ☐ Ma sous-requĂȘte est rĂ©pĂ©tĂ©e 2+ fois → CTE obligatoire

  • ☐ J'ai plus de 2 niveaux d'imbrication → CTE obligatoire

  • ☐ J'ai besoin de rĂ©cursivitĂ© → CTE rĂ©cursive uniquement

  • ☐ Mes CTEs ont des noms qui disent ce qu'elles calculent

  • ☐ Je peux exĂ©cuter chaque CTE sĂ©parĂ©ment pour la dĂ©boguer

  • ☐ J'ai vĂ©rifiĂ© que les CTEs rĂ©pĂ©tĂ©es sont matĂ©rialisĂ©es si besoin (PostgreSQL MATERIALIZED)

🚀 Pratiquer sur Modai Lab

CTE vs sous-requĂȘte est un des critĂšres les plus discriminants entre un analyst junior et un analyst senior. La diffĂ©rence ne se voit pas juste dans le rĂ©sultat — elle se voit dans la lisibilitĂ©, la maintenabilitĂ© et la performance de tes requĂȘtes.

👉 modai-lab.com/test-sql/ — Exercices calibrĂ©s · Feedback senior · Parcours entretien

_Guide offert suite au post LinkedIn · EntraĂźnez-vous sur modai-lab.com/test-sql/ · Partagez librement ♻_