Guide pratique · E-commerce · SaaS · Finance · RH · Lecture : ~20 min Teste ton niveau sur modai-lab.com/test-sql/

Pourquoi ce guide existe

Tout le monde connaît SUM(), AVG(), COUNT(). Presque personne ne maîtrise LAG() et LEAD().

Et pourtant c'est ce qu'on te demande en entretien senior.

Ce que la plupart font sans LAG() et LEAD() : trois sous-requêtes imbriquées, une jointure sur elle-même, une requête illisible que personne ne veut maintenir.

Ce que tu fais avec : une fenêtre. Une ligne. Un résultat propre.

Ce guide couvre :

  • La syntaxe complète avec les paramètres optionnels

  • 5 cas concrets : e-commerce, SaaS, finance, RH, trafic web

  • Les erreurs classiques et comment les éviter

  • Un prompt Claude pour générer tes propres exercices

🧪 Calibre ton niveau avant de commencer

Sais-tu déjà expliquer ce que fait LAG(col, 2, 0) exactement ? 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 syntaxe complète

Ce que font LAG() et LEAD()

LAG() → accède à la valeur d'une ligne PRÉCÉDENTE dans la fenêtre
LEAD() → accède à la valeur d'une ligne SUIVANTE dans la fenêtre
lag-lead-fonctions-sql · 01

Sans jointure. Sans sous-requête. En une ligne de SQL.

La syntaxe avec tous les paramètres

LAG(expression, offset, default_value) OVER (
PARTITION BY col_groupe
ORDER BY col_tri
)
 
LEAD(expression, offset, default_value) OVER (
PARTITION BY col_groupe
ORDER BY col_tri
)
lag-lead-fonctions-sql · 02

Les 3 paramètres :

Paramètre

Rôle

Valeur par défaut

expression

La colonne ou valeur à récupérer

Obligatoire

offset

Combien de lignes en arrière (LAG) ou en avant (LEAD)

1

default_value

Ce qui est retourné si la ligne n'existe pas (première/dernière ligne)

NULL

-- Exemples de syntaxe
LAG(montant) -- valeur de la ligne précédente, NULL si première ligne
LAG(montant, ) -- identique au précédent (offset = 1 par défaut)
LAG(montant, , ) -- valeur précédente, 0 si première ligne (pas NULL)
LAG(montant, ) -- valeur d'il y a 3 lignes
LAG(montant, , ) -- valeur il y a 12 lignes (ex: même mois l'année dernière)
 
LEAD(date_evenement) -- date du prochain événement
LEAD(date_evenement, , NULL) -- identique
LEAD(statut, , 'aucun') -- prochain statut, 'aucun' si dernière ligne
lag-lead-fonctions-sql · 03

La démonstration de base

-- Données : CA mensuel
-- mois | ca
-- 2024-01 | 10 000
-- 2024-02 | 12 000
-- 2024-03 | 9 000
-- 2024-04 | 14 000
-- 2024-05 | 13 000
 
SELECT
mois,
ca,
LAG(ca) OVER (ORDER BY mois) AS ca_mois_precedent,
LEAD(ca) OVER (ORDER BY mois) AS ca_mois_suivant,
ca - LAG(ca) OVER (ORDER BY mois) AS variation_absolue,
ROUND(
. * (ca - LAG(ca) OVER (ORDER BY mois))
/ NULLIF(LAG(ca) OVER (ORDER BY mois), ),
) AS variation_pct
FROM revenus_mensuels
ORDER BY mois;
lag-lead-fonctions-sql · 04

Résultat :

mois

ca

ca_mois_precedent

variation_absolue

variation_pct

2024-01

10 000

NULL

NULL

NULL

2024-02

12 000

10 000

+2 000

+20.0%

2024-03

9 000

12 000

-3 000

-25.0%

2024-04

14 000

9 000

+5 000

+55.6%

2024-05

13 000

14 000

-1 000

-7.1%

PARTITION BY : comparer dans des groupes séparés

-- Sans PARTITION BY : compare toutes les lignes ensemble
-- Avec PARTITION BY : repart de zéro pour chaque groupe
 
-- CA mensuel par pays avec variation pour chaque pays séparément
SELECT
pays,
mois,
ca,
LAG(ca) OVER (PARTITION BY pays ORDER BY mois) AS ca_precedent,
-- Ici, la première ligne de France et la première ligne de Belgique
-- ont toutes les deux ca_precedent = NULL (pas de ligne précédente DANS ce pays)
ca - LAG(ca) OVER (PARTITION BY pays ORDER BY mois) AS variation
FROM revenus_mensuels_par_pays
ORDER BY pays, mois;
lag-lead-fonctions-sql · 05

L'offset : remonter N lignes en arrière

-- Comparaison avec la même période de l'année précédente
-- (offset = 12 pour des données mensuelles)
SELECT
mois,
ca,
LAG(ca, , ) OVER (ORDER BY mois) AS ca_annee_precedente,
ROUND(
. * (ca - LAG(ca, , ) OVER (ORDER BY mois))
/ NULLIF(LAG(ca, , ) OVER (ORDER BY mois), ),
) AS evolution_annuelle_pct
FROM revenus_mensuels
ORDER BY mois;
lag-lead-fonctions-sql · 06

PARTIE 2 — Les 5 cas business concrets

Cas 1 — E-commerce : progression du CA mois par mois

Objectif : rapport mensuel avec variation absolue, variation en %, et indicateur de tendance.

WITH ca_mensuel AS (
SELECT
DATE_TRUNC('month', created_at) AS mois,
SUM(montant) AS ca,
COUNT(DISTINCT order_id) AS nb_commandes,
COUNT(DISTINCT user_id) AS nb_clients
FROM orders
WHERE statut = 'livré'
GROUP BY
),
avec_variation AS (
SELECT
mois,
ca,
nb_commandes,
nb_clients,
LAG(ca) OVER (ORDER BY mois) AS ca_m1,
LAG(nb_commandes) OVER (ORDER BY mois) AS commandes_m1,
LAG(nb_clients) OVER (ORDER BY mois) AS clients_m1,
-- Même mois l'an dernier (offset = 12)
LAG(ca, ) OVER (ORDER BY mois) AS ca_n1
FROM ca_mensuel
)
SELECT
TO_CHAR(mois, 'Mon YYYY') AS periode,
ca,
ROUND(. * (ca - ca_m1) / NULLIF(ca_m1, ), ) AS vs_mois_precedent_pct,
ROUND(. * (ca - ca_n1) / NULLIF(ca_n1, ), ) AS vs_annee_precedente_pct,
ROUND(ca::NUMERIC / NULLIF(nb_commandes, ), ) AS panier_moyen,
CASE
WHEN ca > ca_m1 THEN '↑ Hausse'
WHEN ca < ca_m1 THEN '↓ Baisse'
ELSE '→ Stable'
END AS tendance
FROM avec_variation
ORDER BY mois;
lag-lead-fonctions-sql · 07

Cas 2 — Trafic web : détecter les chutes brutales entre deux semaines

Objectif : alerter automatiquement quand le trafic chute de plus de 20% d'une semaine à l'autre.

WITH trafic_hebdo AS (
SELECT
DATE_TRUNC('week', date_session) AS semaine,
COUNT(*) AS nb_sessions,
COUNT(DISTINCT user_id) AS visiteurs_uniques,
AVG(duree_session) AS duree_moyenne
FROM sessions
GROUP BY
),
avec_variation AS (
SELECT
semaine,
nb_sessions,
visiteurs_uniques,
LAG(nb_sessions) OVER (ORDER BY semaine) AS sessions_semaine_precedente,
ROUND(
. * (nb_sessions - LAG(nb_sessions) OVER (ORDER BY semaine))
/ NULLIF(LAG(nb_sessions) OVER (ORDER BY semaine), ),
) AS variation_sessions_pct
FROM trafic_hebdo
)
SELECT
semaine,
nb_sessions,
sessions_semaine_precedente,
variation_sessions_pct,
CASE
WHEN variation_sessions_pct < - THEN '🔴 ALERTE : Chute > 20%'
WHEN variation_sessions_pct < - THEN '🟡 Attention : Baisse > 10%'
WHEN variation_sessions_pct > THEN '🟢 Pic : Hausse > 20%'
ELSE '⚪ Normal'
END AS alerte
FROM avec_variation
WHERE semaine >= NOW() - INTERVAL '12 weeks'
ORDER BY semaine DESC;
lag-lead-fonctions-sql · 08

Cas 3 — SaaS : mesurer le temps entre deux événements utilisateur

Objectif : calculer le délai entre chaque action d'un utilisateur pour identifier les frictions dans le funnel.

WITH events_ordonnes AS (
SELECT
user_id,
event_type,
created_at,
LAG(created_at) OVER (
PARTITION BY user_id
ORDER BY created_at
) AS event_precedent_at,
LAG(event_type) OVER (
PARTITION BY user_id
ORDER BY created_at
) AS event_precedent_type,
LEAD(created_at) OVER (
PARTITION BY user_id
ORDER BY created_at
) AS prochain_event_at,
LEAD(event_type) OVER (
PARTITION BY user_id
ORDER BY created_at
) AS prochain_event_type
FROM user_events
)
SELECT
user_id,
event_precedent_type AS de,
event_type AS vers,
ROUND(
EXTRACT(EPOCH FROM (created_at - event_precedent_at)) / ,
) AS delai_minutes,
created_at
FROM events_ordonnes
WHERE event_precedent_at IS NOT NULL -- exclure le premier événement de chaque user
ORDER BY user_id, created_at;
lag-lead-fonctions-sql · 09

Agréger pour trouver les frictions :

-- Temps moyen entre chaque paire d'événements dans le funnel
WITH delais AS (
SELECT
user_id,
event_type,
LAG(event_type) OVER (PARTITION BY user_id ORDER BY created_at) AS event_prec,
EXTRACT(EPOCH FROM (
created_at - LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at)
)) / AS delai_minutes
FROM user_events
)
SELECT
event_prec || ' → ' || event_type AS transition,
COUNT(*) AS nb_transitions,
ROUND(AVG(delai_minutes), ) AS delai_moyen_minutes,
ROUND(PERCENTILE_CONT(.) WITHIN GROUP (ORDER BY delai_minutes), ) AS delai_median_minutes,
ROUND(PERCENTILE_CONT(.) WITHIN GROUP (ORDER BY delai_minutes), ) AS delai_p90_minutes
FROM delais
WHERE event_prec IS NOT NULL
GROUP BY
ORDER BY delai_moyen_minutes DESC;
-- Les transitions avec le plus grand délai = les frictions dans ton funnel
lag-lead-fonctions-sql · 10 · Agréger pour trouver les frictions :

Cas 4 — Finance : identifier les clients qui n'ont pas commandé depuis X jours

Objectif : pour chaque client, calculer le gap entre ses commandes et signaler les risques de churn.

WITH commandes_avec_gap AS (
SELECT
user_id,
order_id,
created_at AS date_commande,
LAG(created_at) OVER (
PARTITION BY user_id
ORDER BY created_at
) AS commande_precedente,
LEAD(created_at) OVER (
PARTITION BY user_id
ORDER BY created_at
) AS commande_suivante
FROM orders
WHERE statut = 'livré'
),
avec_delais AS (
SELECT
user_id,
order_id,
date_commande,
commande_precedente,
EXTRACT(EPOCH FROM (date_commande - commande_precedente)) / AS jours_depuis_precedente,
EXTRACT(EPOCH FROM (COALESCE(commande_suivante, NOW()) - date_commande)) / AS jours_avant_suivante,
commande_suivante IS NULL AS est_derniere_commande
FROM commandes_avec_gap
)
SELECT
user_id,
MAX(date_commande) AS derniere_commande,
ROUND(AVG(jours_depuis_precedente), ) AS intervalle_moyen_jours,
ROUND(MAX(jours_depuis_precedente), ) AS plus_long_gap_jours,
EXTRACT(EPOCH FROM (NOW() - MAX(date_commande))) / AS jours_depuis_derniere,
CASE
WHEN EXTRACT(EPOCH FROM (NOW() - MAX(date_commande))) / > * AVG(jours_depuis_precedente)
THEN '🔴 Churn probable : gap 3× supérieur à la normale'
WHEN EXTRACT(EPOCH FROM (NOW() - MAX(date_commande))) / > * AVG(jours_depuis_precedente)
THEN '🟡 Risque : gap 2× supérieur à la normale'
ELSE '🟢 Normal'
END AS statut_churn
FROM avec_delais
WHERE est_derniere_commande = TRUE
GROUP BY user_id
HAVING COUNT(*) > -- au moins 2 commandes pour calculer un intervalle moyen
ORDER BY jours_depuis_derniere DESC;
lag-lead-fonctions-sql · 11

Cas 5 — RH : comparer les performances d'une cohorte à la précédente

Objectif : comparer les performances de chaque vague de recrutement avec la vague précédente.

WITH performance_cohorte AS (
SELECT
DATE_TRUNC('quarter', date_embauche) AS cohorte,
COUNT(*) AS nb_recrues,
AVG(score_performance_1an) AS score_moyen_1an,
AVG(salaire_negociation) AS salaire_moyen,
COUNT(*) FILTER (WHERE date_depart IS NULL) AS encore_presents,
ROUND(
. * COUNT(*) FILTER (WHERE date_depart IS NULL) / COUNT(*),
) AS taux_retention_pct
FROM employes
WHERE date_embauche IS NOT NULL
GROUP BY
),
avec_comparaison AS (
SELECT
cohorte,
nb_recrues,
score_moyen_1an,
taux_retention_pct,
salaire_moyen,
-- Comparaison avec la cohorte précédente
LAG(score_moyen_1an) OVER (ORDER BY cohorte) AS score_cohorte_prec,
LAG(taux_retention_pct) OVER (ORDER BY cohorte) AS retention_cohorte_prec,
LAG(salaire_moyen) OVER (ORDER BY cohorte) AS salaire_cohorte_prec,
LAG(nb_recrues) OVER (ORDER BY cohorte) AS recrues_cohorte_prec
FROM performance_cohorte
)
SELECT
TO_CHAR(cohorte, '"Q"Q YYYY') AS periode,
nb_recrues,
ROUND(score_moyen_1an, ) AS score_moyen,
ROUND(score_moyen_1an - COALESCE(score_cohorte_prec, score_moyen_1an), ) AS delta_score,
taux_retention_pct AS retention_pct,
ROUND(taux_retention_pct - COALESCE(retention_cohorte_prec, taux_retention_pct), ) AS delta_retention_pts,
ROUND(salaire_moyen, ) AS salaire_moyen,
CASE
WHEN score_moyen_1an > score_cohorte_prec AND taux_retention_pct > retention_cohorte_prec
THEN '✅ Meilleure cohorte'
WHEN score_moyen_1an < score_cohorte_prec AND taux_retention_pct < retention_cohorte_prec
THEN '⚠️ Cohorte en régression'
ELSE '→ Mitigé'
END AS evaluation
FROM avec_comparaison
ORDER BY cohorte;
lag-lead-fonctions-sql · 12

🧪 Teste LAG et LEAD sur des données réelles

Ces 5 cas sont exactement ce qu'on teste en entretien data senior. Modai Lab te soumet des datasets réels avec des séries temporelles — score immédiat.

👉 Pratiquer sur modai-lab.com

_Exercices interactifs · Données réelles · Feedback instantané_

PARTIE 3 — Les erreurs classiques

Erreur 1 — Oublier ORDER BY dans la fenêtre

-- ❌ Sans ORDER BY : résultat non déterministe
SELECT
mois,
ca,
LAG(ca) OVER (PARTITION BY pays) AS ca_precedent -- quel ordre ?
FROM revenus;
-- Le résultat dépend de l'ordre physique des lignes → non reproductible
 
-- ✅ ORDER BY toujours présent dans une window function avec LAG/LEAD
SELECT
mois,
ca,
LAG(ca) OVER (PARTITION BY pays ORDER BY mois) AS ca_precedent
FROM revenus;
lag-lead-fonctions-sql · 13

Erreur 2 — Division par NULL sur la première ligne

-- ❌ La première ligne a LAG() = NULL → division par NULL → NULL silencieux
SELECT
mois,
ca,
ROUND(. * (ca - LAG(ca) OVER (ORDER BY mois)) / LAG(ca) OVER (ORDER BY mois), ) AS pct
FROM revenus;
-- La première ligne retourne NULL pour pct — pas d'erreur, juste une donnée manquante
 
-- ✅ Option 1 : NULLIF pour éviter la division par zéro ET gérer les NULL
SELECT
mois,
ca,
ROUND(. * (ca - LAG(ca) OVER (ORDER BY mois))
/ NULLIF(LAG(ca) OVER (ORDER BY mois), ), ) AS pct
FROM revenus;
-- La première ligne retourne toujours NULL — c'est le comportement attendu
 
-- ✅ Option 2 : valeur par défaut avec le 3ème paramètre de LAG
SELECT
mois,
ca,
LAG(ca, , ca) OVER (ORDER BY mois) AS ca_precedent_ou_courant
-- Si première ligne : LAG retourne ca lui-même → variation = 0%
FROM revenus;
lag-lead-fonctions-sql · 14

Erreur 3 — Confondre PARTITION BY et ORDER BY

-- PARTITION BY : définit les groupes (repart de zéro pour chaque groupe)
-- ORDER BY : définit l'ordre dans lequel LAG/LEAD navigue
 
-- ❌ Erreur : ORDER BY avec la mauvaise colonne
SELECT
user_id,
event_type,
created_at,
LAG(event_type) OVER (PARTITION BY user_id ORDER BY event_type) AS prec
-- ORDER BY event_type = ordre alphabétique des types d'événements
-- Pas l'ordre chronologique → résultat faux pour l'analyse de funnel
FROM user_events;
 
-- ✅ Toujours ORDER BY sur la colonne temporelle pour les séries chronologiques
SELECT
user_id,
event_type,
created_at,
LAG(event_type) OVER (PARTITION BY user_id ORDER BY created_at) AS event_precedent
FROM user_events;
lag-lead-fonctions-sql · 15

Erreur 4 — Utiliser LAG dans un WHERE (impossible)

-- ❌ Impossible : les window functions ne peuvent pas être dans WHERE
SELECT mois, ca
FROM revenus
WHERE LAG(ca) OVER (ORDER BY mois) < ca;
-- Erreur SQL : window functions not allowed in WHERE
 
-- ✅ Envelopper dans une sous-requête ou une CTE
WITH avec_lag AS (
SELECT
mois,
ca,
LAG(ca) OVER (ORDER BY mois) AS ca_precedent
FROM revenus
)
SELECT mois, ca, ca_precedent
FROM avec_lag
WHERE ca > ca_precedent -- maintenant on peut filtrer
AND ca_precedent IS NOT NULL;
lag-lead-fonctions-sql · 16

Erreur 5 — Réutiliser LAG() plusieurs fois au lieu d'une CTE

-- ❌ Performance : LAG() calculé 4 fois sur la même fenêtre
SELECT
mois,
ca,
ca - LAG(ca) OVER (ORDER BY mois) AS variation,
ca / NULLIF(LAG(ca) OVER (ORDER BY mois), ) AS ratio,
CASE WHEN ca > LAG(ca) OVER (ORDER BY mois) THEN 'Hausse' ELSE 'Baisse' END AS tendance,
ca - LAG(ca, ) OVER (ORDER BY mois) AS vs_annee
FROM revenus;
-- Le moteur calcule LAG() 4 fois sur la même colonne
 
-- ✅ CTE pour calculer une fois, réutiliser partout
WITH avec_references AS (
SELECT
mois,
ca,
LAG(ca, , ) OVER (ORDER BY mois) AS ca_m1,
LAG(ca, , ) OVER (ORDER BY mois) AS ca_n1
FROM revenus
)
SELECT
mois,
ca,
ca - ca_m1 AS variation_absolue,
ROUND(. * (ca - ca_m1) / NULLIF(ca_m1, ), ) AS variation_pct,
CASE WHEN ca > ca_m1 THEN 'Hausse' ELSE 'Baisse' END AS tendance,
ca - ca_n1 AS vs_annee_precedente
FROM avec_references;
lag-lead-fonctions-sql · 17

🧪 Teste les erreurs classiques sur Modai Lab

Oublier ORDER BY, diviser par NULL, filtrer sur une window function : ces 5 erreurs sont exactement celles que Modai Lab détecte et explique dans son feedback.

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

_Score immédiat · Feedback senior · Niveau calibré_

PARTIE 4 — Patterns avancés

Pattern 1 — Détecter les séquences consécutives

-- Détecter les jours consécutifs où un utilisateur s'est connecté
WITH connexions AS (
SELECT
user_id,
DATE(created_at) AS jour,
LAG(DATE(created_at)) OVER (PARTITION BY user_id ORDER BY DATE(created_at)) AS jour_prec
FROM logins
GROUP BY user_id, DATE(created_at) -- dédupliquer les connexions multiples le même jour
),
avec_ecart AS (
SELECT
user_id,
jour,
jour - jour_prec AS ecart_jours,
CASE WHEN jour - jour_prec = THEN ELSE END AS nouvelle_serie
FROM connexions
),
series AS (
SELECT *,
SUM(nouvelle_serie) OVER (PARTITION BY user_id ORDER BY jour) AS id_serie
FROM avec_ecart
)
SELECT
user_id,
MIN(jour) AS debut_serie,
MAX(jour) AS fin_serie,
COUNT(*) AS longueur_serie_jours
FROM series
GROUP BY user_id, id_serie
ORDER BY longueur_serie_jours DESC;
lag-lead-fonctions-sql · 18

Pattern 2 — First / Last value sans window function dédiée

-- LAG avec offset = ROW_NUMBER - 1 → valeur de la première ligne
-- Alternative à FIRST_VALUE pour plus de flexibilité
 
WITH classe AS (
SELECT
user_id,
created_at,
montant,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS rn,
COUNT(*) OVER (PARTITION BY user_id) AS total
FROM orders
)
SELECT
user_id,
LAG(montant, rn - ) OVER (PARTITION BY user_id ORDER BY created_at) AS premier_montant,
LEAD(montant, total - rn) OVER (PARTITION BY user_id ORDER BY created_at) AS dernier_montant,
montant AS montant_actuel
FROM classe;
lag-lead-fonctions-sql · 19

Pattern 3 — Comparer avec la moyenne mobile des N périodes précédentes

-- Variante de LAG : comparer avec la moyenne des 3 mois précédents
-- (plutôt qu'avec un seul mois précédent)
WITH ca_mensuel AS (
SELECT
mois,
ca,
LAG(ca, ) OVER (ORDER BY mois) AS m1,
LAG(ca, ) OVER (ORDER BY mois) AS m2,
LAG(ca, ) OVER (ORDER BY mois) AS m3
FROM revenus
)
SELECT
mois,
ca,
ROUND((COALESCE(m1,ca) + COALESCE(m2,ca) + COALESCE(m3,ca)) / ., ) AS moy_3_precedents,
CASE
WHEN ca > (COALESCE(m1,ca) + COALESCE(m2,ca) + COALESCE(m3,ca)) / . * .
THEN '↑ +10% vs moyenne 3M'
WHEN ca < (COALESCE(m1,ca) + COALESCE(m2,ca) + COALESCE(m3,ca)) / . * .
THEN '↓ -10% vs moyenne 3M'
ELSE '→ Dans la norme'
END AS signal
FROM ca_mensuel
ORDER BY mois;
lag-lead-fonctions-sql · 20

Pattern 4 — Transition de statut et durée dans chaque statut

-- Calculer le temps passé dans chaque statut d'un ticket ou d'une commande
WITH transitions AS (
SELECT
ticket_id,
statut,
changed_at,
LEAD(changed_at) OVER (
PARTITION BY ticket_id
ORDER BY changed_at
) AS prochain_changement,
LEAD(statut) OVER (
PARTITION BY ticket_id
ORDER BY changed_at
) AS prochain_statut
FROM ticket_status_history
)
SELECT
ticket_id,
statut,
changed_at AS debut_statut,
COALESCE(prochain_changement, NOW()) AS fin_statut,
ROUND(
EXTRACT(EPOCH FROM (COALESCE(prochain_changement, NOW()) - changed_at)) / ,
) AS heures_dans_statut,
prochain_statut
FROM transitions
ORDER BY ticket_id, changed_at;
lag-lead-fonctions-sql · 21

PARTIE 5 — Prompt Claude pour générer tes propres exercices

Prompt de génération d'exercices

Tu es un expert SQL data analytics. Génère 5 exercices progressifs sur
LAG() et LEAD() pour un analyst intermédiaire.
 
CONTEXTE MÉTIER : [e-commerce / SaaS / finance / RH — choisir]
 
Pour chaque exercice :
1. Un contexte business réaliste en 2-3 phrases
2. Le schéma de la table concernée (4-5 colonnes, types inclus)
3. Un jeu de données d'exemple (5-8 lignes)
4. La question métier posée en français
5. La requête correcte avec commentaires sur chaque clause OVER()
6. Le résultat attendu (3-4 lignes exemple)
7. Une question de relance légèrement plus difficile
 
Progression :
- Exercice 1 : LAG simple avec variation M/M
- Exercice 2 : LAG avec PARTITION BY (plusieurs groupes)
- Exercice 3 : LAG avec offset > 1 (comparaison N-12 pour données mensuelles)
- Exercice 4 : LEAD pour détecter ce qui vient après
- Exercice 5 : Combinaison LAG + LEAD + CTE pour un cas avancé
 
Inclure les erreurs classiques à éviter pour chaque exercice.
lag-lead-fonctions-sql · 22

Prompt d'audit de tes requêtes existantes

Audite cette requête SQL qui utilise LAG() ou LEAD() :
 
[coller ta requête]
 
Vérifie :
1. ORDER BY présent dans chaque clause OVER() ?
2. Gestion des NULL sur la première/dernière ligne (NULLIF, valeur par défaut) ?
3. PARTITION BY correct pour les groupes ?
4. Les window functions sont-elles dans une CTE plutôt qu'en WHERE ?
5. Y a-t-il des répétitions inutiles de la même window function ?
 
Si des problèmes sont trouvés : montrer la version corrigée avec explications.
lag-lead-fonctions-sql · 23

Prompt "Remplace mes sous-requêtes par LAG/LEAD"

Cette requête utilise des sous-requêtes ou des self-joins pour comparer
des valeurs dans le temps. Réécris-la avec LAG() ou LEAD().
 
REQUÊTE ORIGINALE :
[coller ta requête avec les sous-requêtes imbriquées]
 
Objectif : obtenir le même résultat avec une syntaxe plus lisible et plus performante.
Explique chaque paramètre de la clause OVER() que tu utilises.
lag-lead-fonctions-sql · 24

Récapitulatif — LAG() et LEAD() en une page

Paramètre

Rôle

Exemple

expression

Colonne à récupérer

LAG(montant)

offset

Nombre de lignes en arrière/avant

LAG(montant, 3) → 3 lignes en arrière

default_value

Valeur si ligne inexistante

LAG(montant, 1, 0) → 0 si première ligne

PARTITION BY

Groupes séparés

Repart à NULL pour chaque groupe

ORDER BY

Ordre de navigation

Toujours obligatoire pour LAG/LEAD

Les 5 cas d'usage principaux :

Cas

Fonction

Paramètre clé

Variation M/M

LAG(ca, 1)

offset = 1

Comparaison N-1

LAG(ca, 12)

offset = 12 (données mensuelles)

Délai entre événements

LAG(date) avec soustraction

PARTITION BY user_id

Chute brutale

LAG(trafic) + CASE WHEN seuil

Seuil de % calculé

Prochain statut

LEAD(statut)

Pour les transitions

✅ Checklist entretien — LAG() et LEAD()

  • ☐ Je sais expliquer la différence entre LAG() et LEAD()

  • ☐ Je sais utiliser les 3 paramètres : expression, offset, default_value

  • ☐ Je sais toujours mettre ORDER BY dans la clause OVER()

  • ☐ Je sais utiliser PARTITION BY pour comparer dans des groupes séparés

  • ☐ Je sais que LAG() ne peut pas être dans un WHERE → je passe par une CTE

  • ☐ Je sais gérer les NULL de la première ligne avec NULLIF() ou la valeur par défaut

  • ☐ Je peux réécrire une sous-requête corrélée de comparaison temporelle avec LAG()

🚀 Pratiquer sur Modai Lab

LAG() et LEAD() sont les fonctions qui distinguent les analysts juniors des seniors en entretien. La seule façon de les maîtriser vraiment : pratiquer sur des séries temporelles réelles avec du feedback immédiat.

👉 modai-lab.com/test-sql/ — Exercices calibrés · Séries temporelles réelles · Feedback senior

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