Rétention au jour N et rétention glissante
La différence entre les définitions classique, glissante et limitée de la rétention
Rétention au jour N et rétention glissante est une leçon SQL Interview Prep gratuite sur CoddyKit. Ceci est la leçon 3 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.
Pourquoi la rétention a plusieurs définitions
Un recruteur vous demandera rarement simplement de « calculer la rétention ». La question complémentaire pertinente est : quelle rétention ? Les mêmes données produisent des nombres très différents selon la définition utilisée.
Les trois définitions à connaître sont la rétention du jour N (classique), la rétention glissante (sans borne) et la rétention sur fenêtre bornée. Comprendre la différence et demander laquelle intéresse l’entreprise constitue en soi la compétence évaluée.
Rétention du jour N (classique)
La rétention du jour N pose la question suivante : l’utilisateur était-il actif exactement le jour N après sa première action ? Les mesures du jour 1, du jour 7 et du jour 30 sont les indicateurs de référence des applications mobiles.
Le mot essentiel est exactement. Un utilisateur actif aux jours 6 et 8, mais pas au jour 7, n’est pas considéré comme conservé au jour 7 selon la définition classique. Cette précision rend le comptage strict et la courbe irrégulière.
Calculer la différence en jours
La rétention du jour N repose sur le nombre de jours entre le début de la cohorte et chaque jour d’activité. Dans PostgreSQL, soustraire deux dates donne directement un nombre entier de jours.
Autres dialectes : DATEDIFF(day, start, d) dans SQL Server, et DATEDIFF(d, start) dans MySQL. Précisez le dialecte ; le concept, à savoir un décalage en jours, reste identique.
WITH cohort AS (
SELECT user_id, MIN(event_at::date) AS day0
FROM events GROUP BY user_id
),
act AS (
SELECT DISTINCT user_id, event_at::date AS day
FROM events
)
SELECT c.user_id, (a.day - c.day0) AS day_n
FROM cohort c
JOIN act a ON a.user_id = c.user_id;Une requête de rétention au jour 7
Pour obtenir le taux de rétention au jour 7, comptez les utilisateurs distincts dont le décalage en jours est égal à 7, puis divisez par la taille de la cohorte. Utilisez une agrégation conditionnelle afin que le numérateur et le dénominateur proviennent d’un seul parcours des données.
L’égalité = 7, et non >= 7, caractérise la rétention classique. Remplacer l’égalité par une inégalité modifie discrètement la définition.
WITH dn AS (
SELECT c.user_id, (a.day - c.day0) AS day_n
FROM cohort c JOIN act a ON a.user_id = c.user_id
)
SELECT
COUNT(DISTINCT CASE WHEN day_n = 7 THEN user_id END) AS d7_retained,
COUNT(DISTINCT user_id) AS cohort_size,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN day_n = 7 THEN user_id END)
/ NULLIF(COUNT(DISTINCT user_id), 0), 1) AS d7_pct
FROM dn;Rétention glissante (sans borne)
La rétention glissante au jour N pose une question moins stricte : l’utilisateur était-il actif le jour N ou n’importe quel jour suivant ? Un utilisateur est considéré comme conservé au jour 7 s’il est revenu le jour 7, le jour 20 ou à n’importe quel moment ultérieur.
Cette définition produit une courbe plus lisse et plus élevée ; elle est souvent privilégiée pour mesurer l’adhérence à long terme. Le changement caractéristique consiste à remplacer = N par >= N sur le dernier jour d’activité.
Rétention glissante avec le jour MAX
La méthode la plus claire pour calculer la rétention glissante consiste à trouver le dernier décalage de jour actif de chaque utilisateur avec MAX, puis à considérer l’utilisateur comme conservé au jour N si ce maximum est >= N.
Après MAX, chaque utilisateur correspond à une seule ligne, ce qui simplifie le comptage. Cela montre également que la rétention glissante est monotone : si vous êtes conservé au jour 30, vous l’êtes aussi pour tout N inférieur.
WITH last_day AS (
SELECT c.user_id, MAX(a.day - c.day0) AS max_day_n
FROM cohort c JOIN act a ON a.user_id = c.user_id
GROUP BY c.user_id
)
SELECT
COUNT(*) AS cohort_size,
COUNT(*) FILTER (WHERE max_day_n >= 7) AS rolling_d7,
ROUND(100.0 * COUNT(*) FILTER (WHERE max_day_n >= 7)
/ NULLIF(COUNT(*), 0), 1) AS rolling_d7_pct
FROM last_day;Rétention sur fenêtre bornée
Le compromis intermédiaire est la rétention bornée : l’utilisateur doit avoir été actif au moins une fois dans une fenêtre autour du jour N, par exemple entre les jours 5 et 9 pour mesurer la « semaine 1 ». Cette définition tolère qu’un utilisateur ne soit pas actif le jour exact, tout en lissant moins les résultats que la rétention totalement glissante.
C’est la définition la plus réaliste pour l’entreprise, car l’utilisation réelle se fait souvent par à-coups. La requête utilise BETWEEN sur le décalage en jours.
SELECT
COUNT(DISTINCT CASE WHEN day_n BETWEEN 5 AND 9
THEN user_id END) AS week1_retained,
COUNT(DISTINCT user_id) AS cohort_size
FROM dn;Trois définitions, un même utilisateur
Rendez la différence concrète. Un utilisateur commence au jour 0, puis n’est actif qu’au jour 9.
- Jour 7 classique : NON conservé (aucune activité exactement au jour 7).
- Jour 7 glissant : conservé (le jour maximal est 9 >= 7).
- Semaine 1 bornée, du jour 5 au jour 9 : conservé (le jour 9 se trouve dans la fenêtre).
Les mêmes données, trois réponses. En entretien, racontez un exemple de ce type pour montrer que vous comprenez la sémantique, et pas seulement la syntaxe.
Granularité de période : jour, semaine ou mois
« Jour N » se généralise en période N. Pour un produit B2B utilisé chaque mois, la rétention au niveau du jour est du bruit ; vous regrouperiez les données par mois et parleriez du mois N. Le mécanisme est identique ; seule la granularité de la troncature change.
Choisissez la granularité qui correspond au rythme naturel d’utilisation du produit, et précisez-le. Une granularité quotidienne pour une application mobile grand public, hebdomadaire ou mensuelle pour des outils B2B utilisés plus lentement.
-- weekly grain: offset in whole weeks
SELECT
c.user_id,
FLOOR((a.day - c.day0) / 7) AS week_n
FROM cohort c
JOIN act a ON a.user_id = c.user_id;Le piège de la survie et de la maturité
Une question subtile destinée aux profils expérimentés : ne présentez pas la rétention au jour 30 pour une cohorte âgée de seulement 10 jours. Ses membres n’ont pas encore eu la possibilité d’être actifs au jour 30 ; leur valeur est donc artificiellement égale à 0, et ne reflète pas une rétention réellement faible.
Pour éviter cela, n’incluez dans le calcul du jour N que les cohortes dont l’âge est >= N. Filtrez avec CURRENT_DATE - day0 >= N. Oublier cette précaution donne une image catastrophique des cohortes récentes.
WITH cohort AS (
SELECT user_id, MIN(event_at::date) AS day0
FROM events GROUP BY user_id
)
SELECT *
FROM cohort
WHERE (CURRENT_DATE - day0) >= 30; -- mature enough for Day-30Choisir une définition à voix haute
La meilleure réponse en entretien n’est pas une requête, mais une question en retour : « Voulez-vous le jour N classique, une rétention glissante ou une fenêtre bornée ? Et quelle est la période naturelle ? »
Exposez ensuite le compromis : la définition classique est stricte et adaptée aux fonctionnalités qui reposent sur une action effectuée un jour précis ; la définition glissante surestime le court terme, mais mesure l’adhérence sur la durée ; la définition bornée constitue le compromis réaliste. Montrer que vous choisissez volontairement la mesure est l’objectif même de cette leçon.
Vérification rapide
La première action d’un utilisateur a lieu au jour 0 ; sa seule autre activité a lieu au jour 12. Selon chaque définition, est-il considéré comme conservé au jour 7 ?
Récapitulatif : les définitions de la rétention
Points essentiels sur la rétention du jour N et la rétention glissante :
- Jour N classique : actif exactement au jour N (décalage
= N) — courbe stricte et irrégulière. - Rétention glissante : actif au jour N ou ultérieurement (
MAX offset >= N) — courbe plus lisse et monotone, qui mesure l’adhérence. - Rétention bornée : actif dans une fenêtre (
BETWEEN) — le compromis réaliste. - Généralisez le jour à n’importe quelle granularité de période correspondant au rythme d’utilisation du produit.
- Ne présentez la rétention du jour N que pour les cohortes matures (âge >= N), afin d’éviter le piège de la survie, et demandez toujours quelle définition l’entreprise entend utiliser.
Questions Fréquemment Posées
La leçon « Rétention au jour N et rétention glissante » est-elle gratuite ?
Oui — le texte complet de « Rétention au jour N et rétention glissante » 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 « Rétention au jour N et rétention glissante » ?
La différence entre les définitions classique, glissante et limitée de la rétention 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 3 sur 4.
Combien de temps prend la leçon « Rétention au jour N et rétention glissante » ?
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éfinir une cohorte par sa première action
- Construire une matrice de rétention
- Rétention au jour N et rétention glissante
- Requêtes sur l’attrition et le retour des utilisateurs