SQL Interview Prep · Leçon

Reconnaître un problème de lacunes et d’îlots

Identifier le motif dans un énoncé et comprendre l’idée centrale du regroupement

Reconnaître un problème de lacunes et d’îlots est une leçon SQL Interview Prep gratuite sur CoddyKit. Ceci est la leçon 1 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.

Le motif que les intervieweurs cherchent à évaluer

Lorsqu’un intervieweur senior vous demande de trouver des suites consécutives d’un élément, vous êtes face à un problème d’intervalles et d’îlots. Le nom vient d’une représentation mentale : les lignes qui vont ensemble forment un îlot, et les ruptures entre elles sont des intervalles.

Un îlot est une suite maximale de lignes adjacentes selon une règle donnée (entiers consécutifs, dates consécutives ou même statut répété).
Un intervalle est l’espace manquant entre deux îlots.

Reconnaître immédiatement cette catégorie est déjà un signe d’expérience. De nombreux candidats se lancent dans un enchevêtrement d’auto-jointures ; la réponse élégante repose presque toujours sur les fonctions de fenêtre.

Les énoncés qui dissimulent un îlot

La difficulté vient du fait que les intervieweurs disent rarement « intervalles et îlots ». Ils le dissimulent. Habituez-vous à repérer des formulations comme :

« Trouvez chaque période pendant laquelle un utilisateur a été abonné sans interruption. »
« Combien de jours consécutifs le serveur est-il resté opérationnel ? »
« Quelles plages d’identifiants manquent dans cette table ? »
« Regroupez les lignes adjacentes ayant le même statut en une seule ligne. »

Dans tous les cas, la structure est la même : regrouper les lignes voisines, puis indiquer le début, la fin ou l’absence de ces groupes. Une fois les mots associés aux îlots, le SQL s’écrit presque tout seul.

L’idée centrale : créer une clé de groupe

Voici toute l’astuce en une phrase : si vous pouvez attribuer à chaque ligne d’un même îlot une clé de groupe identique, un simple GROUP BY permet alors de réduire chaque îlot à une ligne récapitulative.

Le véritable travail, dans tout problème d’intervalles et d’îlots, consiste donc à calculer cette clé de groupe. Les différentes variantes la calculent de manière différente, mais elles partagent toutes cet objectif. Une fois la clé obtenue, la dernière étape est triviale :

SELECT
  grp,
  MIN(value) AS island_start,
  MAX(value) AS island_end,
  COUNT(*)   AS island_length
FROM rows_with_group_key
GROUP BY grp
ORDER BY island_start;

Un jeu de données concret

Partons des données. Imaginez une table logins qui enregistre les numéros de jour auxquels un utilisateur s’est connecté :

Jours présents : 1, 2, 3, 7, 8, 10

À l’œil, les îlots sont {1,2,3}, {7,8} et {10}. Les intervalles correspondent aux jours 4 à 6 et au jour 9. Lors d’un entretien, votre tâche consiste à faire repérer ces trois îlots par la base de données, sans les lui indiquer manuellement. Gardez ce petit jeu de données à l’esprit pendant que nous examinons chaque technique.

CREATE TABLE logins (day_no INT);
INSERT INTO logins VALUES (1),(2),(3),(7),(8),(10);

Pourquoi les approches naïves échouent

Un premier réflexe courant consiste à comparer chaque ligne à la suivante avec une auto-jointure et à signaler les ruptures. Cela fonctionne pour trouver un seul intervalle, mais devient rapidement difficile à gérer :

Vous devez détecter à la fois le début et la fin de chaque îlot, ce qui nécessite deux parcours ou deux jointures.
Les lignes aux extrémités (la toute première et la toute dernière) nécessitent un traitement particulier.
Cette approche ne se généralise pas à « donnez-moi la longueur de chaque suite » sans ajouter d’autres mécanismes.

Les intervieweurs observent si vous vous engagez dans une guerre d’auto-jointures ou si vous reconnaissez qu’un seul parcours avec une fonction de fenêtre est plus clair.

Le modèle mental de détection des intervalles

Une formulation robuste est la suivante : un nouvel îlot commence dès que la ligne actuelle n’est pas adjacente à la ligne précédente. Utilisez LAG pour revenir d’une ligne en arrière et effectuer la comparaison.

Si day_no - LAG(day_no) est supérieur à 1 (ou vaut NULL pour la première ligne), cette ligne commence un nouvel îlot. Nous le signalons avec un indicateur égal à 1, et avec 0 dans le cas contraire. Observez l’aspect de ces indicateurs pour nos données.

SELECT
  day_no,
  CASE
    WHEN day_no - LAG(day_no) OVER (ORDER BY day_no) = 1 THEN 0
    ELSE 1
  END AS is_new_island
FROM logins
ORDER BY day_no;

Transformer les indicateurs en clé de groupe

Les indicateurs de l’étape précédente valent 1, 0, 0, 1, 0, 1 pour les jours 1,2,3,7,8,10. Remarquez qu’une somme cumulée de ces indicateurs produit un nombre qui reste constant dans un îlot et augmente à chaque nouvel îlot : 1,1,1,2,2,3.

Cette somme cumulée est notre clé de groupe créée artificiellement. Nous plaçons la requête des indicateurs dans une CTE et en calculons la somme avec une autre fonction de fenêtre :

WITH flagged AS (
  SELECT
    day_no,
    CASE WHEN day_no - LAG(day_no) OVER (ORDER BY day_no) = 1
         THEN 0 ELSE 1 END AS is_new_island
  FROM logins
)
SELECT
  day_no,
  SUM(is_new_island) OVER (ORDER BY day_no) AS grp
FROM flagged;

Terminer l’exemple détaillé

Ajoutez maintenant le GROUP BY final au-dessus de la clé de groupe. Chaque valeur distincte de grp correspond à un îlot, dont nous indiquons les limites et la taille :

Le résultat correspond exactement aux trois îlots repérés à l’œil : 1-3 (longueur 3), 7-8 (longueur 2) et 10-10 (longueur 1). Cette méthode en trois étapes (indicateur, somme cumulée, regroupement) constitue la base de presque toutes les réponses que vous écrirez sur les intervalles et les îlots.

WITH flagged AS (
  SELECT day_no,
    CASE WHEN day_no - LAG(day_no) OVER (ORDER BY day_no) = 1
         THEN 0 ELSE 1 END AS is_new
  FROM logins
),
keyed AS (
  SELECT day_no,
    SUM(is_new) OVER (ORDER BY day_no) AS grp
  FROM flagged
)
SELECT grp, MIN(day_no) AS start_day,
       MAX(day_no) AS end_day, COUNT(*) AS len
FROM keyed GROUP BY grp ORDER BY start_day;

L’adjacence dépend du domaine

La seule partie qui change d’un problème à l’autre est la définition de ce qui est adjacent. Reconnaître la bonne règle d’adjacence constitue la moitié du travail :

Entiers : ils sont adjacents lorsque leur différence vaut exactement 1.
Jours calendaires : ils sont adjacents lorsqu’une date est le jour suivant (date = prev + INTERVAL '1 day').
Périodes de statut : elles sont adjacentes lorsque la valeur du statut reste inchangée par rapport à la ligne précédente.

La structure reste la même, seule la comparaison à l’intérieur du CASE change. Identifier la règle d’adjacence applicable est la question de clarification que vous devez poser à voix haute lors de l’entretien.

Questions de clarification à poser

Avant d’écrire une ligne de SQL, marquez des points en clarifiant le périmètre. Voici de bonnes questions de clarification pour les problèmes d’intervalles et d’îlots :

« Dois-je traiter les données par utilisateur ou globalement ? » (Cela détermine si vous ajoutez PARTITION BY user_id.)
« Peut-il y avoir des valeurs en double le même jour, et interrompent-elles ou prolongent-elles une suite ? »
« Voulez-vous les îlots, les intervalles ou les deux ? »
« La séquence est-elle garantie comme étant triée, ou dois-je la trier moi-même ? »

Poser ces questions montre que vous avez déjà résolu cette catégorie de problème et que vous en comprenez les cas limites.

Des îlots par groupe avec PARTITION BY

Les données d’entretien réelles sont presque toujours regroupées, par exemple les connexions par utilisateur. La correction est mécanique : ajoutez PARTITION BY user_id à chaque fonction de fenêtre afin que les îlots ne s’étendent jamais d’un utilisateur à l’autre.

La structure reste identique ; vous ne faites que partitionner. C’est pourquoi il est utile de commencer par maîtriser le cas d’un flux unique : le passage à une analyse par groupe ne nécessite qu’une modification d’une seule clause.

SELECT
  user_id, day_no,
  CASE WHEN day_no - LAG(day_no)
         OVER (PARTITION BY user_id ORDER BY day_no) = 1
       THEN 0 ELSE 1 END AS is_new
FROM logins;

Vérification rapide

Testez votre instinct pour reconnaître les motifs.

Récapitulatif : reconnaître la structure

Vous pouvez désormais identifier un problème d’intervalles et d’îlots malgré sa formulation déguisée, et nommer la stratégie à appliquer :

Mots déclencheurs : consécutif, continu, ininterrompu, série, plages manquantes, regrouper les éléments adjacents.
Idée centrale : attribuer à chaque ligne d’une même suite une clé de groupe identique, puis effectuer un GROUP BY sur cette clé.
Méthode : signaler les nouveaux îlots avec LAG, transformer les indicateurs en clé par une somme cumulée, puis agréger.
L’adjacence dépend du domaine (entiers, dates ou statut inchangé).
Ajoutez PARTITION BY pour une analyse par groupe ; clarifiez le périmètre avant de coder.

Nous allons maintenant approfondir la méthode la plus élégante pour créer une clé : l’astuce de la différence avec ROW_NUMBER.

Questions Fréquemment Posées

La leçon « Reconnaître un problème de lacunes et d’îlots » est-elle gratuite ?

Oui — le texte complet de « Reconnaître un problème de lacunes et d’îlots » 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 « Reconnaître un problème de lacunes et d’îlots » ?

Identifier le motif dans un énoncé et comprendre l’idée centrale du regroupement 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 1 sur 4.

Combien de temps prend la leçon « Reconnaître un problème de lacunes et d’îlots » ?

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