Coding Interview Prep · Leçon

Construire une matrice de rétention

Compter les utilisateurs actifs par cohorte et décalage de période pour former un tableau de rétention

Construire une matrice de rétention est une leçon Coding 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 Coding Interview Prep, et ta progression se synchronise sur le web et l'application CoddyKit. Le cours Coding Interview Prep comprend 4 leçons au total.

Ce qu'est une matrice de rétention

Après avoir défini une cohorte vient la célèbre matrice de rétention : les lignes correspondent aux cohortes, les colonnes aux décalages de période (mois 0, 1, 2, ...), et chaque cellule compte le nombre d'utilisateurs de cette cohorte qui sont encore actifs à ce décalage.

Les recruteurs l'apprécient, car elle vous oblige à combiner l'attribution d'une cohorte, une jointure avec l'activité, un calcul de différence entre périodes et une opération de pivotement. C'est la requête la plus représentative de l'analyse produit.

Les deux entrées

Vous avez besoin de deux éléments : la période de cohorte de chaque utilisateur (issue de la leçon précédente) et de l'enregistrement de chaque période d'activité pour chaque utilisateur. L'activité provient de la même table d'événements, ramenée à la granularité de la période.

Prévoyez donc la requête ainsi : un CTE pour les cohortes, puis un CTE d'activité qui répertorie les mois pendant lesquels chaque utilisateur a été actif, avant de les relier par une jointure.

WITH user_cohort AS (
  SELECT user_id,
    DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events
  GROUP BY user_id
)
SELECT * FROM user_cohort;

Répertorier les périodes d'activité

Le CTE d'activité répond à la question « Au cours de quels mois chaque utilisateur a-t-il été actif ? » Tronquez chaque événement au mois, puis éliminez les doublons avec DISTINCT ou GROUP BY, afin qu'un utilisateur actif 40 fois en mars ne produise qu'une seule ligne pour mars.

Cette liste par utilisateur et par mois est celle que vous reliez à la cohorte pour mesurer la rétention selon les décalages.

WITH activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT * FROM activity;

Calculer le décalage entre périodes

Le cœur de la matrice est le numéro de période : combien de mois après le début de sa cohorte une activité donnée a-t-elle eu lieu ? Soustrayez le mois de la cohorte du mois d'activité.

Dans Postgres, une méthode claire consiste à compter le nombre de mois entiers entre les deux dates. Une formule portable consiste à multiplier la différence d'années par 12, puis à ajouter la différence de mois ; de nombreux moteurs proposent également des fonctions utilitaires. Le décalage 0 correspond au mois de départ de la cohorte.

-- months between two month-truncated dates (Postgres)
SELECT
  (EXTRACT(YEAR  FROM active_month) - EXTRACT(YEAR  FROM cohort_month)) * 12
+ (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month))
  AS period_number;

Joindre la cohorte à l’activité

Effectuez une jointure entre la CTE de cohorte et la CTE d’activité sur user_id. Chaque ligne de sortie indique : cet utilisateur, né dans la cohorte X, était actif au décalage N. Le comptage des utilisateurs distincts pour chaque couple (cohorte, décalage) constitue la matrice sous forme longue.

Comme chaque membre d’une cohorte est actif durant son propre mois de départ, le décalage 0 doit être égal à la taille de la cohorte : c’est une vérification de cohérence intégrée.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT c.cohort_month, a.active_month, c.user_id
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id;

La table de rétention sous forme longue

Ajoutez le calcul du décalage et agrégez les données. Vous obtenez maintenant un résultat propre sous forme longue : une ligne par cohorte et par décalage, avec un nombre d’utilisateurs conservés. De nombreux recruteurs acceptent directement ce résultat, car le pivotement n’est qu’une question de présentation.

Notez que l’expression de décalage apparaît à la fois dans SELECT et GROUP BY, puisqu’elle est calculée et non stockée dans une colonne.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT
  c.cohort_month,
  (EXTRACT(YEAR FROM a.active_month)-EXTRACT(YEAR FROM c.cohort_month))*12
  +(EXTRACT(MONTH FROM a.active_month)-EXTRACT(MONTH FROM c.cohort_month)) AS period_number,
  COUNT(DISTINCT c.user_id) AS retained_users
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, period_number
ORDER BY c.cohort_month, period_number;

Pivoter vers des colonnes larges

Pour obtenir la grille classique, transformez les décalages en colonnes avec une agrégation conditionnelle : une somme d’un CASE par décalage. Ce schéma portable fonctionne dans tous les dialectes, sans syntaxe PIVOT particulière.

Chaque CASE renvoie 1 lorsque le numéro de période de la ligne correspond à cette colonne ; la SUM compte donc les utilisateurs conservés pour ce décalage.

SELECT
  cohort_month,
  COUNT(DISTINCT CASE WHEN period_number = 0 THEN user_id END) AS m0,
  COUNT(DISTINCT CASE WHEN period_number = 1 THEN user_id END) AS m1,
  COUNT(DISTINCT CASE WHEN period_number = 2 THEN user_id END) AS m2,
  COUNT(DISTINCT CASE WHEN period_number = 3 THEN user_id END) AS m3
FROM retention_long
GROUP BY cohort_month
ORDER BY cohort_month;

Des comptages aux taux de rétention

Les recruteurs veulent généralement des pourcentages, et non des comptages bruts. Divisez le nombre d’utilisateurs conservés pour chaque décalage par la taille de la cohorte (celle du décalage 0). Convertissez une valeur en nombre à virgule flottante ou multipliez par 1.0 pour éviter la division entière, l’erreur silencieuse la plus courante ici.

Le résultat est une courbe de rétention : 100 % au mois 0, puis une décroissance vers un plateau. C’est ce plateau qui constitue réellement la mesure importante pour les parties prenantes.

SELECT
  cohort_month,
  period_number,
  retained_users,
  ROUND(
    100.0 * retained_users
    / MAX(retained_users) OVER (PARTITION BY cohort_month),
    1
  ) AS retention_pct
FROM retention_long
ORDER BY cohort_month, period_number;

Le piège de la division entière

Un piège garanti en entretien : dans la plupart des moteurs, 120 / 500 est égal à 0, et non à 0,24, car les deux opérandes sont des entiers. Les pourcentages de rétention ressortent alors discrètement tous à zéro.

Corrigez cela en convertissant un côté en nombre : multipliez par 100.0, utilisez CAST pour convertir un opérande en NUMERIC ou divisez par NULLIF(size, 0) afin de gérer également une cohorte vide. Préciser que « NULLIF empêche la division par zéro » vous fera gagner des points supplémentaires.

SELECT
  retained_users,
  cohort_size,
  100.0 * retained_users / NULLIF(cohort_size, 0) AS pct
FROM retention_long;

Remplir les décalages manquants avec zéro

Si une cohorte comptait zéro utilisateur conservé au décalage 2, la jointure ne produit aucune ligne et laisse un trou dans la matrice. Pour afficher explicitement un 0, générez la grille complète des combinaisons (cohorte, décalage), puis effectuez une LEFT JOIN avec les comptages.

Construisez la grille en effectuant une CROSS JOIN entre les cohortes et une liste de nombres ou de décalages, puis remplacez les comptages manquants par zéro. Les recruteurs apprécient que vous ayez remarqué cette lacune.

WITH offsets AS (SELECT generate_series(0, 6) AS period_number),
cohorts AS (SELECT DISTINCT cohort_month FROM retention_long)
SELECT
  c.cohort_month, o.period_number,
  COALESCE(r.retained_users, 0) AS retained_users
FROM cohorts c
CROSS JOIN offsets o
LEFT JOIN retention_long r
  ON r.cohort_month = c.cohort_month
 AND r.period_number = o.period_number
ORDER BY c.cohort_month, o.period_number;

Forme triangulaire et biais de récence

Autre point à mentionner : la matrice est triangulaire. Une cohorte commencée le mois dernier ne peut pas encore avoir de valeur pour le mois 3 ; les décalages plus tardifs reposent donc sur moins de cohortes.

Comparer la moyenne d’une colonne entre les cohortes favorise ainsi les cohortes plus anciennes. Précisez que vous afficheriez honnêtement le triangle ou que vous limiteriez les comparaisons aux décalages que toutes les cohortes ont atteints. Cette prise de conscience distingue les analystes des simples rédacteurs de requêtes.

Vérification rapide

Votre requête de rétention divise les utilisateurs conservés par la taille de la cohorte, mais tous les pourcentages s’affichent comme 0, sauf celui du mois 0. Quelle est la cause la plus probable ?

Récapitulatif : la matrice de rétention

Pour construire une matrice de rétention en entretien :

Attribuez à chaque utilisateur une période de cohorte, puis listez les périodes d’activité de chaque utilisateur après déduplication.
Effectuez une jointure entre ces données et calculez le décalage de période (le nombre de mois entre la cohorte et l’activité).
Agrégez sous forme longue avec COUNT(DISTINCT user_id) ; utilisez CASE pour effectuer un pivot si une grille est nécessaire.
Convertissez soigneusement les comptages en taux, en évitant la division entière et la division par zéro avec 100.0 et NULLIF.
Effectuez une LEFT JOIN avec une grille générée pour remplir les cellules à zéro, et souvenez-vous que la matrice est triangulaire.

Questions Fréquemment Posées

La leçon « Construire une matrice de rétention » est-elle gratuite ?

Oui — le texte complet de « Construire une matrice de rétention » 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 Coding Interview Prep, passe à CoddyKit PRO. Le cours Coding Interview Prep comprend 4 leçons au total.

Qu'est-ce que j'apprendrai dans « Construire une matrice de rétention » ?

Compter les utilisateurs actifs par cohorte et décalage de période pour former un tableau de rétention Tu pratiques Coding 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 Coding Interview Prep ?

Aucune expérience préalable n'est requise. Coding 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 « Construire une matrice de rétention » ?

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 Coding Interview Prep ?

Oui. Chaque leçon Coding 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

← Retour à Coding Interview Prep