LAG, LEAD et NTILE en SQL : guide pratique complet

Les fonctions LAG, LEAD et NTILE font partie des fonctions fenêtre les plus demandées en entretien SQL. Elles permettent d'accéder à des lignes voisines ou de découper un ensemble de résultats en segments égaux — sans recourir à une sous-requête coûteuse. Si vous savez déjà utiliser ROW_NUMBER ou RANK, ces 3 fonctions sont l'étape suivante pour passer au niveau supérieur. Dans ce guide, vous apprendrez leur syntaxe exacte, leurs cas d'usage concrets et les pièges à éviter en situation d'entretien.

Pourquoi LAG, LEAD et NTILE sont incontournables en entretien

Les recruteurs en data testent régulièrement la capacité à calculer des variations entre périodes (croissance mois sur mois, taux de churn, évolution des ventes). Sans LAG ou LEAD, ce type de calcul impose une auto-jointure ou une sous-requête corrélée — deux approches nettement plus lentes et plus difficiles à lire.

De même, segmenter des utilisateurs en quartiles (Top 25 %, Bottom 25 %) est un classique des tests techniques en analytics. NTILE résout ce problème en une seule ligne là où un CASE WHEN à la main nécessite plusieurs étapes.

Selon le classement des compétences SQL publié par Mode Analytics (2024), les fonctions fenêtre avancées figurent dans 68 % des tests techniques envoyés aux candidats data analyst. Maîtriser LAG, LEAD et NTILE vous place donc immédiatement dans le top des profils évalués.

Si vous n'avez pas encore lu notre introduction sur les fonctions fenêtre SQL, commencez par là avant de poursuivre ici.

La syntaxe commune des trois fonctions

Les 3 fonctions partagent la même structure de base : elles s'exécutent dans une clause OVER() qui définit la partition (équivalent d'un GROUP BY local) et le tri appliqué à la fenêtre.

FONCTION(expression, offset, valeur_défaut)
  OVER (
    PARTITION BY colonne_partition
    ORDER BY colonne_tri
  )

• PARTITION BY : regroupe les lignes par catégorie (utilisateur, produit, région…). Sans cette clause, toute la table forme une seule partition.
• ORDER BY : détermine l'ordre dans lequel les lignes sont parcourues à l'intérieur d'une partition.
• offset (LAG / LEAD uniquement) : nombre de lignes à sauter. Par défaut : 1.
• valeur_défaut (LAG / LEAD uniquement) : valeur renvoyée quand il n'existe pas de ligne précédente / suivante. Par défaut : NULL.

LAG : comparer chaque ligne à la précédente

Syntaxe détaillée

LAG(expression [, offset [, valeur_défaut]])
  OVER (PARTITION BY ... ORDER BY ...)

Exemple 1 : évolution du chiffre d'affaires mois par mois

Imaginons une table ventes avec les colonnes mois (DATE), region (VARCHAR) et ca (NUMERIC).

SELECT
  mois,
  region,
  ca,
  LAG(ca, 1, 0) OVER (
    PARTITION BY region
    ORDER BY mois
  ) AS ca_mois_precedent,
  ca - LAG(ca, 1, 0) OVER (
    PARTITION BY region
    ORDER BY mois
  ) AS variation_absolue,
  ROUND(
    (ca - LAG(ca, 1, 0) OVER (PARTITION BY region ORDER BY mois))
    / NULLIF(LAG(ca, 1, 0) OVER (PARTITION BY region ORDER BY mois), 0) * 100,
    2
  ) AS variation_pct
FROM ventes
ORDER BY region, mois;

Ce que fait cette requête :

• Elle calcule le CA du mois précédent pour chaque région.
• Elle calcule la variation absolue et le pourcentage d'évolution.
• NULLIF(..., 0) évite une division par zéro quand le CA précédent est nul.

Exemple 2 : détecter des ruptures dans une série temporelle

SELECT
  date_commande,
  id_client,
  LAG(date_commande) OVER (
    PARTITION BY id_client
    ORDER BY date_commande
  ) AS commande_precedente,
  date_commande - LAG(date_commande) OVER (
    PARTITION BY id_client
    ORDER BY date_commande
  ) AS jours_depuis_derniere_commande
FROM commandes;

Ce pattern est typique des exercices sur le taux de rétention ou la fréquence d'achat. Consultez notre article dédié sur l'exercice SQL du taux de rétention utilisateurs pour un cas complet.

LEAD : anticiper la ligne suivante

Syntaxe détaillée

LEAD(expression [, offset [, valeur_défaut]])
  OVER (PARTITION BY ... ORDER BY ...)

LEAD est le miroir exact de LAG : au lieu de regarder en arrière, il regarde en avant dans la fenêtre triée.

Exemple 1 : calculer la durée de chaque session utilisateur

SELECT
  id_session,
  id_utilisateur,
  debut_session,
  LEAD(debut_session) OVER (
    PARTITION BY id_utilisateur
    ORDER BY debut_session
  ) AS debut_session_suivante,
  LEAD(debut_session) OVER (
    PARTITION BY id_utilisateur
    ORDER BY debut_session
  ) - debut_session AS duree_entre_sessions
FROM sessions;

Exemple 2 : identifier la prochaine étape dans un funnel

SELECT
  id_utilisateur,
  etape,
  timestamp_etape,
  LEAD(etape) OVER (
    PARTITION BY id_utilisateur
    ORDER BY timestamp_etape
  ) AS etape_suivante,
  LEAD(timestamp_etape) OVER (
    PARTITION BY id_utilisateur
    ORDER BY timestamp_etape
  ) - timestamp_etape AS temps_avant_etape_suivante
FROM funnel_events;

Ce cas d'usage complète parfaitement notre guide sur l'analyse d'un tunnel de conversion e-commerce.

NTILE : segmenter en groupes égaux

Syntaxe détaillée

NTILE(n)
  OVER (PARTITION BY ... ORDER BY ...)

NTILE(n) attribue un numéro de groupe entre 1 et n à chaque ligne, en les répartissant le plus équitablement possible. Si le nombre de lignes n'est pas divisible par n, les premiers groupes reçoivent une ligne supplémentaire.

Exemple 1 : segmenter les clients en quartiles de dépenses

SELECT
  id_client,
  total_depenses,
  NTILE(4) OVER (ORDER BY total_depenses DESC) AS quartile
FROM (
  SELECT id_client, SUM(montant) AS total_depenses
  FROM commandes
  GROUP BY id_client
) AS depenses_client;

Résultat attendu :

• Quartile 1 = Top 25 % des clients les plus dépensiers.
• Quartile 4 = Bottom 25 % des clients les moins dépensiers.

Exemple 2 : créer des déciles de performance par équipe commerciale

SELECT
  id_commercial,
  equipe,
  ca_annuel,
  NTILE(10) OVER (
    PARTITION BY equipe
    ORDER BY ca_annuel DESC
  ) AS decile_performance
FROM performances_commerciales;

Ce pattern est très utilisé dans les dashboards RH et les analyses de performance commerciale.

Tableau comparatif : LAG vs LEAD vs NTILE