L'ère du printemps est ouverte 😎

Il est temps de renouveler le design avec des teintes chaudes qui réchauffent l'âme. J'ai créé un nouveau design pour que chaque post dégage une énergie printanière 🔥

Bon, passons maintenant aux questions produit, parlons-en 👇

On te donne deux tables - users et events. Et 20 minutes pour la tâche :
« Calcule, mon cher, la rétention par cohortes M0–M3 »

Et là, le blocage s'installe.
Par où commencer ? 🤔

Le plus simple - pas avec SQL, mais avec une image mentale. Imagine une salle de sport.
100 personnes ont acheté un abonnement - c'est ta cohorte.
Ensuite, on prend généralement cette cohorte comme 100%. C'est M0.
Mais voici le hic : ce n'est pas une loi de la nature, juste un accord tacite.
Le lendemain, 60 personnes viennent - Jour 1 = 60%.
Une semaine plus tard, 40 viennent - Jour 7 = 40%.

Ensuite, la logique ne change pas.
Simplement, au lieu des jours, ce sont des mois.
Et au lieu de la salle de sport, c'est le produit.

1️⃣ Niveau Junior - comprendre combien de personnes sont venues dans chaque cohorte
SELECT
  DATE_TRUNC('month', registration_date) AS cohort_month,
  COUNT(DISTINCT user_id) AS new_users
FROM users
GROUP BY 1
ORDER BY 1;

Si déjà ici il y a une erreur, tout est fichu. Parce que DATE_TRUNC et DISTINCT sont la base du bloc produit.
Du moins pour PostgreSQL et les dialectes similaires.

2️⃣ Niveau suivant - comprendre qui est revenu
WITH cohort_dec AS (
  SELECT user_id, registration_date
  FROM users
  WHERE registration_date >= '2023-12-01'
    AND registration_date < '2024-01-01'
)
SELECT
  COUNT(*) AS cohort_size,
  COUNT(DISTINCT CASE WHEN event_date = registration_date + 1 THEN user_id END) AS day1,
  COUNT(DISTINCT CASE WHEN event_date = registration_date + 7 THEN user_id END) AS day7
FROM cohort_dec u
LEFT JOIN events e ON u.user_id = e.user_id;

Ici, des pièges se cachent 🤔

Ce code est correct si tes dates sont stockées comme DATE, sans heure.
Mais si c'est TIMESTAMP, une simple égalité peut casser le calcul.
Parce que l'un a un événement à 2023-12-08 00:01, un autre à 2023-12-08 19:42, et formellement ce n'est plus la même valeur.

Donc, il faut soit convertir en date, soit calculer via des plages.
La logique est toujours la même :
✅ fixer la cohorte → regarder qui est revenu

Pour M1, M2, M3, c'est presque pareil, mais il y a un point crucial :
👉 on ne compare pas simplement les dates, mais le décalage en mois calendaires par rapport au mois d'inscription (oui, ça semble compliqué, je vais expliquer)

Ce n'est pas +30 jours. Les mois ont des durées différentes, donc ce calcul dérive.
Pour la rétention mensuelle, on regarde le décalage en mois calendaires, pas simplement +30 jours.

❌ Autres pièges courants

1️⃣ Compter les événements au lieu des personnes
Un utilisateur a fait 5 événements.
Et soudain, ta rétention dépasse 100%.
Donc, tu ne comptes pas le retour des personnes, mais l'activité.

2️⃣ Utiliser INNER JOIN
Et dans la sélection, il ne reste que ceux qui sont revenus.
Tous ceux qui ont abandonné disparaissent simplement.
On obtient un joli chiffre. Et un tableau complètement faux.

3️⃣ Ne pas fixer la base de calcul
La taille de la cohorte est généralement prise comme 100%, et on calcule tout à partir de là.
Si ta base varie, les pourcentages deviennent des déchets.

Et ensuite commence le vrai problème... le test de réflexion et les soft skills. C'est là que le chemin est le plus épineux si tu manques d'expérience.
Parce qu'en entretien, on ne te demandera pas :
« Comment calculer la rétention ? »

Mais on te demandera :
« D30 a chuté. Pourquoi ? »

Et voilà... Et là, SQL n'impressionne plus personne.

✅ Une bonne façon de penser est :
- décomposer la rétention par cohortes
- regarder les canaux d'acquisition
- vérifier l'activation
- comprendre si l'onboarding n'a pas été cassé
- comparer le comportement avant et après les releases
On peut creuser encore plus, mais pour commencer, ces bases suffisent.

❌ La réponse qui mettra fin à votre dialogue
« Eh bien… la rétention a baissé parce que les utilisateurs reviennent moins… »

Merci, Capitaine. Mais ce n'est pas une réponse d'analyste, c'est une supposition.

En termes simples :
La rétention, ce n'est pas une question de SQL, c'est une question de comportement.
SQL n'est qu'une pelle avec laquelle tu déterres le chiffre.

Si tu ne peux pas expliquer pourquoi les gens ont cessé de revenir,
alors tes pourcentages en eux-mêmes ne disent rien 🤷‍♀️

Avez-vous déjà eu des problèmes sur le bloc produit ? Et en général, aimez-vous le domaine produit ou préférez-vous la partie ingénierie ? 👇