SQL Interview Prep · Leçon

Plus longue série par utilisateur

Calcul de la longueur maximale d’une série consécutive dans chaque groupe

Plus longue série par utilisateur est une leçon SQL Interview Prep gratuite sur CoddyKit. Ceci est la leçon 2 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.

La question

Une question de suivi fréquente après la détection des jours consécutifs : « Pour chaque utilisateur, quelle est sa plus longue série de jours consécutifs où il a été actif ? » Les équipes produit et croissance posent constamment cette question pour mesurer l'engagement.

Vous savez déjà comment identifier chaque série. La nouvelle étape consiste à trouver la longueur maximale par utilisateur et, souvent, à renvoyer aussi les dates de cette meilleure série. Cette leçon s'appuie directement sur la structure des lacunes et des îlots.

Rappel : construire les îlots

Dans la leçon précédente, le regroupement par série utilise login_date - ROW_NUMBER() comme ancre de l'îlot. Chaque utilisateur peut avoir plusieurs îlots ; nous calculerons d'abord une ligne par îlot, puis réduirons le résultat à une ligne par utilisateur.

Gardez à l'esprit ce plan en deux niveaux : construisez d'abord les îlots, puis agrégez-les.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
)
SELECT user_id, login_date - rn AS grp
FROM numbered;

Une ligne par îlot

Réduisez chaque îlot à une seule ligne récapitulative contenant sa longueur et son intervalle de dates. Regroupez par utilisateur et par ancre, puis calculez les mesures.

Nous nommons cette CTE islands afin que la couche suivante puisse s'appuyer dessus clairement.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
)
SELECT * FROM islands;

Réponse simple : longueur MAX

Si la personne qui mène l'entretien ne demande que la longueur, la dernière étape tient en une seule ligne : regroupez les îlots par utilisateur et prenez la longueur maximale.

C'est la réponse la plus claire lorsque les dates de début et de fin ne sont pas nécessaires.

-- ...numbered and islands CTEs as before...
SELECT
  user_id,
  MAX(streak_len) AS longest_streak
FROM islands
GROUP BY user_id
ORDER BY user_id;

Renvoyer aussi les dates

Souvent, la personne qui mène l'entretien ajoute : « et indiquez quand cette série a eu lieu ». Un MAX simple ne peut pas vous dire quel îlot l'a emporté. Vous devez classer les îlots au sein de chaque utilisateur et conserver le rang 1.

Utilisez ROW_NUMBER, ordonné par longueur décroissante, afin que la meilleure série de chaque utilisateur obtienne le rang 1. Ajoutez un critère de départage pour résoudre les égalités de manière déterministe.

ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC, streak_start ASC
) AS rnk

Classement et filtrage

Placez le classement dans une CTE, puis filtrez sur rnk = 1. Vous ne pouvez pas filtrer directement dans WHERE sur une fonction de fenêtrage ; la couche supplémentaire est donc obligatoire.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1;

RANK ou ROW_NUMBER en cas d'égalité

Que faire si un utilisateur a deux séries de longueur maximale égale et que la personne qui mène l'entretien veut que les deux soient renvoyées ? Remplacez ROW_NUMBER par RANK et conservez rnk = 1.

ROW_NUMBER — exactement une série gagnante par utilisateur (choisie arbitrairement en cas d'égalité, sauf si vous ajoutez un critère de départage).
RANK — toutes les séries les plus longues à égalité partagent le rang 1 et sont toutes conservées.

Précisez le comportement souhaité ; cela montre que vous prêtez attention aux cas limites.

RANK() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC
) AS rnk  -- keep all rnk = 1

Exemple détaillé

Supposons que l'utilisateur 7 se soit connecté du 1er au 4 janvier, puis les 10 et 11 janvier, puis du 20 au 23 janvier. Il y a trois îlots de longueur 4, 2 et 4. La longueur maximale est 4, et il y a égalité.

Avec ROW_NUMBER et le critère de départage streak_start : seule la série du 1er au 4 janvier est renvoyée.
Avec RANK : les séries du 1er au 4 janvier et du 20 au 23 janvier sont toutes deux renvoyées.

Énoncer cela à voix haute montre que vous avez réfléchi aux doublons.

Gérer les utilisateurs sans connexion

Un recruteur peut vous demander : « Que faire des utilisateurs qui ne se sont jamais connectés ? » Ces utilisateurs n'ont aucune ligne dans logins et disparaissent donc du résultat. S'ils doivent apparaître avec une série de longueur 0, utilisez LEFT JOIN sur la table complète users et COALESCE.

SELECT u.user_id,
  COALESCE(MAX(i.streak_len), 0) AS longest_streak
FROM users u
LEFT JOIN islands i ON i.user_id = u.user_id
GROUP BY u.user_id;

Notes de performance

Ce modèle effectue un seul parcours ordonné des données, suivi d'un regroupement. Pour le garder rapide :

Assurez-vous qu'un index existe sur (user_id, login_date), afin que le ORDER BY de la fenêtre évite un tri.
Dédupliquez tôt si la source contient plusieurs événements par jour.
Évitez d'entourer login_date de fonctions dans le ORDER BY, car cela peut empêcher l'utilisation de l'index.

Pour des tables très volumineuses, cette approche surpasse largement toute méthode par auto-jointure.

Réponse complète pour l'entretien

Voici la requête complète et soignée qui renvoie la plus longue série de chaque utilisateur avec ses dates — la version à écrire au tableau blanc.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1
ORDER BY user_id;

Vérification rapide

Choisissez le bon outil pour répondre à l'exigence.

Récapitulatif

Pour calculer la plus longue série par utilisateur :

Construisez des îlots avec l'ancre login_date - ROW_NUMBER().
Réduisez chaque îlot à sa longueur et à son intervalle de dates.
Pour obtenir uniquement la longueur, utilisez MAX(streak_len) en regroupant par utilisateur.
Pour obtenir aussi les dates, classez les îlots par utilisateur et conservez le rang 1 — utilisez RANK pour inclure les ex æquo, et ROW_NUMBER pour ne garder qu'un seul gagnant.
Utilisez LEFT JOIN sur users pour faire apparaître les utilisateurs dont la série est de longueur 0.

Ensuite : détecter N lignes consécutives qui satisfont à une condition.

Questions Fréquemment Posées

La leçon « Plus longue série par utilisateur » est-elle gratuite ?

Oui — le texte complet de « Plus longue série par utilisateur » 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 « Plus longue série par utilisateur » ?

Calcul de la longueur maximale d’une série consécutive dans chaque groupe 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 2 sur 4.

Combien de temps prend la leçon « Plus longue série par utilisateur » ?

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

Détecter les jours consécutifs du calendrier
Plus longue série par utilisateur
N lignes consécutives remplissant une condition
Série active en cours aujourd’hui

← Retour à SQL Interview Prep