SQL Interview Prep · Leçon

Îlots avec changements de date et de statut

Regrouper les périodes consécutives ayant le même statut, un cas courant concernant l’état d’un abonnement

Îlots avec changements de date et de statut est une leçon SQL Interview Prep gratuite sur CoddyKit. Ceci est la leçon 4 sur 4. Tu peux lire la leçon complète ci-dessous gratuitement — puis la pratiquer en direct dans le navigateur avec un éditeur de code intégré et un tuteur IA 24/7. Elle fait partie du parcours d'apprentissage SQL Interview Prep, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours SQL Interview Prep comprend 4 leçons au total.

Des îlots définis par une valeur qui change

La variante des îlots et lacunes la plus utile pour le métier regroupe les lignes consécutives qui partagent le même état, transformant un journal d'événements bruité en périodes d'état claires. Question classique : « À partir d'un journal d'événements d'abonnement, renvoyez une ligne par période continue pendant laquelle l'utilisateur est resté dans chaque état. »

Ici, l'adjacence ne signifie pas « les valeurs diffèrent de 1 ». Elle signifie que l'état reste inchangé par rapport à la ligne précédente. Un nouvel îlot commence dès que l'état change. C'est dans ce cas que la technique fondée sur LAG est plus efficace que l'astuce pure avec le numéro de ligne.

L'exemple d'abonnement

Considérez une table sub_events pour un utilisateur, ordonnée par date :

2026-01-01 actif
2026-02-01 actif
2026-03-01 en pause
2026-04-01 actif
2026-05-01 actif

Le résultat attendu est constitué de trois périodes d'état : actif de janvier à février, en pause en mars, puis actif d'avril à mai. Remarquez que les deux périodes actives forment des îlots distincts, car une période de pause les sépare. Un même état, lorsqu'il n'est pas consécutif, correspond à des îlots différents.

CREATE TABLE sub_events (
  user_id INT, status TEXT, event_date DATE
);
INSERT INTO sub_events VALUES
 (1,'active','2026-01-01'),(1,'active','2026-02-01'),
 (1,'paused','2026-03-01'),(1,'active','2026-04-01'),
 (1,'active','2026-05-01');

Marquer les changements d'état

Utilisez LAG pour comparer l'état de chaque ligne à celui de la ligne précédente. Lorsqu'ils diffèrent (ou lorsque la valeur précédente est NULL pour la première ligne), un nouvel îlot commence. Nous produisons 1 en cas de changement et 0 dans le cas contraire.

Ordonnez strictement les lignes par date au sein de chaque utilisateur. Pour nos données, les indicateurs de changement sont 1,0,1,1,0, ce qui marque les trois limites de période.

SELECT
  user_id, status, event_date,
  CASE
    WHEN status = LAG(status)
      OVER (PARTITION BY user_id ORDER BY event_date)
    THEN 0 ELSE 1
  END AS is_change
FROM sub_events;

Transformer la somme cumulée en clé de période

Comme précédemment, une somme cumulée des indicateurs de changement produit une clé de groupe constante au sein de chaque période d'état : 1,1,2,3,3 pour nos lignes. Chaque clé distincte correspond à une période continue.

L'astuce de la différence avec le numéro de ligne ne fonctionne pas ici, car l'état n'est pas un nombre qui progresse de 1 ; la méthode LAG avec somme cumulée est l'outil adapté lorsque l'adjacence signifie « valeur inchangée ».

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS is_change
  FROM sub_events
)
SELECT user_id, status, event_date,
  SUM(is_change)
    OVER (PARTITION BY user_id ORDER BY event_date) AS grp
FROM flagged;

Regrouper les périodes par état

Utilisez maintenant GROUP BY sur user_id, status et la clé de somme cumulée afin de signaler la plage de chaque période. Inclure l'état dans le GROUP BY est sûr, car il reste constant au sein d'une période, et cela vous permet de le sélectionner sans fonction d'agrégation.

Le résultat comporte exactement trois lignes : actif du 01-01 au 02-01, en pause du 03-01 au 03-01, actif du 04-01 au 05-01.

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
),
keyed AS (
  SELECT user_id, status, event_date,
    SUM(chg) OVER (PARTITION BY user_id ORDER BY event_date) AS grp
  FROM flagged
)
SELECT user_id, status,
  MIN(event_date) AS period_start,
  MAX(event_date) AS period_end
FROM keyed
GROUP BY user_id, status, grp
ORDER BY user_id, period_start;

Des événements aux intervalles semi-ouverts

Un point subtil souvent abordé en entretien : la date d'un événement indique quand un état a commencé, et la période se termine réellement lorsque l'état suivant commence, et non à la date du dernier événement associé au même état. La bonne fin de période est souvent le début de la période suivante, modélisé par un intervalle semi-ouvert [début, début_suivant).

Calculez le début de la période suivante avec LEAD sur les périodes fusionnées, en laissant la dernière période sans borne (NULL ou « actuelle »).

WITH periods AS (
  -- output of the previous collapse step
  SELECT user_id, status, period_start FROM collapsed
)
SELECT user_id, status, period_start,
  LEAD(period_start)
    OVER (PARTITION BY user_id ORDER BY period_start)
    AS period_end_exclusive
FROM periods;

Gérer les états répétés à la suite

Que faire si le journal contient des lignes redondantes comme actif, actif, actif, sans aucun changement entre elles ? L'indicateur de changement vaut 0 pour les répétitions, donc la somme cumulative les conserve automatiquement dans un même îlot. C'est le comportement souhaité : les états identiques consécutifs sont fusionnés en une seule période.

Ce dédoublonnage naturel des répétitions est un avantage essentiel de la méthode fondée sur l'indicateur de changement, et il vaut la peine de le signaler à la personne qui mène l'entretien.

Quand les intervalles de temps doivent interrompre une période

Parfois, le fait d'avoir le même état ne suffit pas ; un intervalle de temps important doit également interrompre la période, même si l'état est identique. Par exemple, un état actif en janvier, puis de nouveau après six mois de silence, peut compter comme deux périodes.

Étendez l'indicateur de changement avec une deuxième condition : démarrez un nouvel îlot lorsque l'état change ou lorsque le temps écoulé depuis l'événement précédent dépasse un seuil. Cette composition des deux règles de contiguïté reste claire.

CASE
  WHEN status = LAG(status)
         OVER (PARTITION BY user_id ORDER BY event_date)
   AND event_date - LAG(event_date)
         OVER (PARTITION BY user_id ORDER BY event_date) <= 31
  THEN 0 ELSE 1
END AS is_change

Compter les changements d'état distincts

Une question de suivi naturelle : « Combien de fois cet utilisateur a-t-il changé d'état ? » Il suffit de compter les indicateurs de changement et de soustraire le tout premier (qui marque l'état initial, pas un changement).

De manière équivalente, c'est le nombre de périodes moins 1. La clé de somme cumulative encode déjà cela, donc la réponse découle du même mécanisme que celui construit pour les périodes.

WITH flagged AS (
  SELECT user_id,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
)
SELECT user_id, SUM(chg) - 1 AS status_switches
FROM flagged GROUP BY user_id;

Pourquoi cette approche est meilleure que les auto-jointures ici

Une solution par auto-jointure pour les périodes d'état devrait associer chaque ligne à sa voisine, détecter les changements, puis relier les bornes : une épreuve en plusieurs étapes sujette aux erreurs, qui devient difficile à gérer dès qu'il y a trois périodes ou plus.

La chaîne LAG-indicateur-somme cumulative-regroupement traite n'importe quel nombre de périodes en un seul passage, sans jointures. Expliquer ce contraste — parcours linéaire en un seul passage contre auto-jointure quadratique — correspond exactement au raisonnement de niveau confirmé que les personnes qui mènent les entretiens valorisent.

Un modèle réutilisable

Mémorisez ce modèle en quatre clauses ; il résout toute la famille des îlots d'état en ne modifiant que le test de contiguïté dans le CASE :

indicateur : CASE avec LAG pour détecter un nouvel îlot.
clé : somme cumulative SUM de l'indicateur, partitionnée et ordonnée.
fusion : GROUP BY sur la colonne de partitionnement, l'état et la clé.
intervalle (facultatif) : LEAD pour les fins de période semi-ouvertes.

La même structure permet de traiter les entiers, les dates et les états ; seule la condition du CASE change.

Vérification rapide

Vérifiez que vous avez compris la règle de regroupement des îlots d'état.

Récapitulatif : îlots d'état et de dates

Vous pouvez maintenant résoudre la variante la plus riche des lacunes et des îlots :

La contiguïté signifie que l'état reste inchangé par rapport à la ligne précédente ; l'indicateur change avec LAG.
Calculez la somme cumulative des indicateurs de changement pour obtenir une clé de groupe par période.
Fusionnez avec GROUP BY user_id, status, key pour obtenir les intervalles des périodes.
Utilisez LEAD pour les fins d'intervalles semi-ouverts ; étendez l'indicateur pour interrompre les périodes en cas de grands intervalles de temps.
Les lignes identiques répétées sont fusionnées automatiquement ; le nombre de changements découle des mêmes indicateurs.
Un modèle réutilisable couvre les entiers, les dates et les états ; seule la condition du CASE change.

Vous avez ainsi terminé le cours sur les lacunes et les îlots, un indicateur fiable d'un niveau confirmé lors des entretiens SQL.

Questions Fréquemment Posées

La leçon « Îlots avec changements de date et de statut » est-elle gratuite ?

Oui — le texte complet de « Îlots avec changements de date et de statut » est gratuit à lire ici sur le web. Pour la pratiquer de manière interactive (un éditeur de code intégré et un tuteur IA 24/7) et déverrouiller le reste du cours SQL Interview Prep, passe à CoddyKit PRO. Le cours SQL Interview Prep comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Îlots avec changements de date et de statut » ?

Regrouper les périodes consécutives ayant le même statut, un cas courant concernant l’état d’un abonnement Tu pratiques SQL Interview Prep avec du code pratique que tu exécutes directement dans le navigateur, et un tuteur IA 24/7 répond à tes questions au fur et à mesure que tu avances dans la leçon.

Dois-je avoir de l'expérience pour commencer SQL Interview Prep ?

Aucune expérience préalable n'est requise. SQL Interview Prep sur CoddyKit est structuré pour les débutants jusqu'aux apprenants avancés, donc tu peux commencer ici ou depuis le début et avancer à ton rythme. Ceci est la leçon 4 sur 4.

Combien de temps prend la leçon « Îlots avec changements de date et de statut » ?

La plupart des leçons CoddyKit prennent environ 5–10 minutes. Chacune est courte et interactive, tu progresses régulièrement et tu repiques exactement où tu t'es arrêté sur le web et l'app.

Peux-tu écrire et exécuter du code dans cette leçon SQL Interview Prep ?

Oui. Chaque leçon SQL Interview Prep inclut un éditeur de code intégré, tu écris et exécutes du vrai code directement dans ton navigateur et tu reçois des retours IA instantanés — aucune configuration locale requise.

Toutes les leçons de ce cours

Reconnaître un problème de lacunes et d’îlots
Astuce de la différence entre numéros de ligne
Trouver les lacunes d’une séquence
Îlots avec changements de date et de statut

← Retour à SQL Interview Prep