Exercice SQL : calculer un taux de rétention utilisateur

Le calcul du taux de rétention est l'un des exercices les plus demandés en entretien data analyst. Il combine des CTE, des JOINs, des agrégations et parfois des fonctions fenêtre. C'est un exercice complet qui montre votre capacité à résoudre un vrai problème business en SQL.

Thomas Leroy vous guide pas à pas, de l'énoncé à la solution optimisée, avec des exemples nominatifs et des pièges courants à éviter.

L'énoncé

Vous travaillez pour une application SaaS. Votre manager veut comprendre la rétention des utilisateurs : parmi les utilisateurs qui se sont inscrits un mois donné, combien reviennent les mois suivants ?

Le schéma

Table users :

Table events :

Les données d'exemple

users :

events :

La question

Calculez le taux de rétention mensuel par cohorte d'inscription. Pour chaque cohorte (mois d'inscription), affichez :

• Le mois d'inscription (cohorte)
• Le mois d'activité
• Le nombre de mois écoulés depuis l'inscription
• Le nombre d'utilisateurs actifs
• Le taux de rétention (% des inscrits de la cohorte)

Étape 1 : Comprendre le problème

Avant de coder, réfléchissons à ce qu'on veut obtenir :

La cohorte de janvier contient les utilisateurs 1, 2 et 3 (inscrits en janvier).

• Mois 0 (janvier) : 3 utilisateurs actifs → 100%
• Mois 1 (février) : utilisateurs 1 et 2 sont actifs → 66.7%
• Mois 2 (mars) : utilisateur 1 est actif → 33.3%

La cohorte de février contient les utilisateurs 4 et 5.

• Mois 0 (février) : 2 utilisateurs actifs → 100%
• Mois 1 (mars) : utilisateurs 4 et 5 sont actifs → 100%

Exemple concret : Slack

Imaginons que Slack compte ses cohortes par trimestre :

• Q1 2026 : 10 000 utilisateurs inscrits
  • Q1 : 10 000 actifs (100%)
  • Q2 : 8 500 actifs (85%)
  • Q3 : 6 200 actifs (62%)

Un taux de rétention qui chute de 100% à 62% sur 6 mois est typique pour une app SaaS. Les produits leaders (Figma, Notion) maintiennent 70-80% à M6.

Étape 2 : Identifier la cohorte de chaque utilisateur

La première CTE détermine le mois d'inscription de chaque utilisateur :

WITH cohortes AS (
    SELECT
        id AS user_id,
        DATE_TRUNC('month', created_at) AS mois_cohorte
    FROM users
)
SELECT * FROM cohortes;

<div style="overflow-x:auto;margin:20px 0;"><table>
<thead style="background-color:#0F172A;color:white;">
<tr><th style="padding:12px 16px;text-align:left">user_id</th><th style="padding:12px 16px;text-align:left">mois_cohorte</th></tr>
</thead><tbody>
<tr style="background:#f8fafc"><td style="padding:12px 16px">1</td><td style="padding:12px 16px">2026-01-01</td></tr>
<tr><td style="padding:12px 16px">2</td><td style="padding:12px 16px">2026-01-01</td></tr>
<tr style="background:#f8fafc"><td style="padding:12px 16px">3</td><td style="padding:12px 16px">2026-01-01</td></tr>
<tr><td style="padding:12px 16px">4</td><td style="padding:12px 16px">2026-02-01</td></tr>
<tr style="background:#f8fafc"><td style="padding:12px 16px">5</td><td style="padding:12px 16px">2026-02-01</td></tr>
</tbody></table></div>

## Étape 3 : Identifier l'activité mensuelle

La deuxième CTE identifie les mois où chaque utilisateur a été actif :

```sql
WITH activite_mensuelle AS (
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('month', event_date) AS mois_activite
    FROM events
)
SELECT * FROM activite_mensuelle ORDER BY user_id, mois_activite;
<div style="overflow-x:auto;margin:20px 0;"><table>
<thead style="background-color:#0F172A;color:white;">
<tr><th style="padding:12px 16px;text-align:left">user_id</th><th style="padding:12px 16px;text-align:left">mois_activite</th></tr>
</thead><tbody>
<tr style="background:#f8fafc"><td style="padding:12px 16px">1</td><td style="padding:12px 16px">2026-01-01</td></tr>
<tr><td style="padding:12px 16px">1</td><td style="padding:12px 16px">2026-02-01</td></tr>
<tr style="background:#f8fafc"><td style="padding:12px 16px">1</td><td style="padding:12px 16px">2026-03-01</td></tr>
<tr><td style="padding:12px 16px">2</td><td style="padding:12px 16px">2026-01-01</td></tr>
<tr style="background:#f8fafc"><td style="padding:12px 16px">2</td><td style="padding:12px 16px">2026-02-01</td></tr>
<tr><td style="padding:12px 16px">3</td><td style="padding:12px 16px">2026-01-01</td></tr>
<tr style="background:#f8fafc"><td style="padding:12px 16px">4</td><td style="padding:12px 16px">2026-02-01</td></tr>
<tr><td style="padding:12px 16px">4</td><td style="padding:12px 16px">2026-03-01</td></tr>
<tr style="background:#f8fafc"><td style="padding:12px 16px">5</td><td style="padding:12px 16px">2026-03-01</td></tr>
</tbody></table></div>

Le `DISTINCT` est important : un utilisateur qui a plusieurs événements dans le même mois ne doit être compté qu'une fois.

### Cas réel : Discord

Discord a lancé une initiative pour **réduire les événements dupliqués**. Avant correction, certains utilisateurs étaient comptabilisés 3-5 fois par jour (un login = plusieurs événements système). Le `DISTINCT` corrigeait cela en entrepôt, mais l'analyse était biaisée à la source. Les données "propres" montrent une rétention 15-20% inférieure à celle perçue.

## Étape 4 : Calculer la taille de chaque cohorte

```sql
WITH taille_cohortes AS (
    SELECT
        DATE_TRUNC('month', created_at) AS mois_cohorte,
        COUNT(*) AS nb_inscrits
    FROM users
    GROUP BY DATE_TRUNC('month', created_at)
)
SELECT * FROM taille_cohortes;

Étape 5 : Assembler la requête complète

Maintenant, combinons tout :

WITH cohortes AS (
    SELECT
        id AS user_id,
        DATE_TRUNC('month', created_at) AS mois_cohorte
    FROM users
),
activite_mensuelle AS (
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('month', event_date) AS mois_activite
    FROM events
),
taille_cohortes AS (
    SELECT
        mois_cohorte,
        COUNT(*) AS nb_inscrits
    FROM cohortes
    GROUP BY mois_cohorte
),
retention_brute AS (
    SELECT
        c.mois_cohorte,
        a.mois_activite,
        COUNT(DISTINCT a.user_id) AS nb_actifs
    FROM cohortes c
    INNER JOIN activite_mensuelle a ON c.user_id = a.user_id
    WHERE a.mois_activite >= c.mois_cohorte
    GROUP BY c.mois_cohorte, a.mois_activite
)
SELECT
    r.mois_cohorte,
    r.mois_activite,
    EXTRACT(YEAR FROM AGE(r.mois_activite, r.mois_cohorte)) * 12
        + EXTRACT(MONTH FROM AGE(r.mois_activite, r.mois_cohorte)) AS mois_depuis_inscription,
    r.nb_actifs,
    t.nb_inscrits,
    ROUND(100.0 * r.nb_actifs / t.nb_inscrits, 1) AS taux_retention
FROM retention_brute r
INNER JOIN taille_cohortes t ON r.mois_cohorte = t.mois_cohorte
ORDER BY r.mois_cohorte, r.mois_activite;

Résultat

Note sur le résultat

La cohorte de février au mois 0 montre 50% (1 utilisateur actif sur 2) parce que l'utilisateur 5 n'a pas d'événement en février dans nos données d'exemple — son premier événement est en mars. C'est un cas réaliste : un utilisateur peut s'inscrire mais ne devenir actif que plus tard.

Tableau comparatif : Rétention par secteur

Pour contextualiser les taux que vous calculerez, voici les benchmarks observés en janvier 2026 :

Interprétation : si vos calculs montrent 35% de rétention M1 pour une app SaaS, c'est en-dessous de la médiane. Cela signale un problème d'onboarding ou d'UX à investiguer.

Variante : calcul avec numéro de mois relatif

Une variante plus propre pour le calcul du mois relatif, compatible avec tous les SGBD :

WITH cohortes AS (
    SELECT
        id AS user_id,
        DATE_TRUNC('month', created_at) AS mois_cohorte
    FROM users
),
activite_mensuelle AS (
    SELECT DISTINCT
        user_id,
        DATE_TRUNC('month', event_date) AS mois_activite
    FROM events
),
retention AS (
    SELECT
        c.mois_cohorte,
        a.mois_activite,
        (EXTRACT(YEAR FROM a.mois_activite) - EXTRACT(YEAR FROM c.mois_cohorte)) * 12
            + EXTRACT(MONTH FROM a.mois_activite) - EXTRACT(MONTH FROM c.mois_cohorte) AS mois_n,
        COUNT(DISTINCT a.user_id) AS nb_actifs
    FROM cohortes c
    INNER JOIN activite_mensuelle a ON c.user_id = a.user_id
    WHERE a.mois_activite >= c.mois_cohorte
    GROUP BY c.mois_cohorte, a.mois_activite
),
taille_cohortes AS (
    SELECT mois_cohorte, COUNT(*) AS nb_inscrits
    FROM cohortes GROUP BY mois_cohorte
)
SELECT
    r.mois_cohorte,
    r.mois_n,
    r.nb_actifs,
    t.nb_inscrits,
    ROUND(100.0 * r.nb_actifs / t.nb_inscrits, 1) AS taux_retention
FROM retention r
INNER JOIN taille_cohortes t ON r.mois_cohorte = t.mois_cohorte
ORDER BY r.mois_cohorte, r.mois_n;

Variante avancée : matrice de rétention pivotée

Pour un affichage plus compact (style tableau de bord) :

-- Suite de la CTE retention ci-dessus
SELECT
    r.mois_cohorte,
    t.nb_inscrits,
    MAX(CASE WHEN r.mois_n = 0 THEN ROUND(100.0 * r.nb_actifs / t.nb_inscrits, 1) END) AS m0,
    MAX(CASE WHEN r.mois_n = 1 THEN ROUND(100.0 * r.nb_actifs / t.nb_inscrits, 1) END) AS m1,
    MAX(CASE WHEN r.mois_n = 2 THEN ROUND(100.0 * r.nb_actifs / t.nb_inscrits, 1) END) AS m2,
    MAX(CASE WHEN r.mois_n = 3 THEN ROUND(100.0 * r.nb_actifs / t.nb_inscrits, 1) END) AS m3
FROM retention r
INNER JOIN taille_cohortes t ON r.mois_cohorte = t.mois_cohorte
GROUP BY r.mois_cohorte, t.nb_inscrits
ORDER BY r.mois_cohorte;

Ce pattern de pivot avec CASE WHEN + MAX + GROUP BY est exactement ce qu'on vous demandera de produire en entretien.

Exemple de sortie pivotée

Imaginons une vraie SaaS avec 12 mois d'historique :

Lecture : la cohorte de juillet 2025 a perdu 52% de ses utilisateurs entre M0 et M6. Normal. Ceux de septembre maintenaient 84% au M1 (meilleur onboarding qu'août).

Les pièges à connaître

Piège 1 : Oublier le DISTINCT

Sans DISTINCT dans la CTE activite_mensuelle, un utilisateur qui se connecte 10 fois en janvier sera compté 10 fois au lieu de 1.

-- ❌ MAUVAIS
SELECT user_id, DATE_TRUNC('month', event_date) AS mois
FROM events;
-- user_id=1 apparaît 3 fois en janvier → comptage faux

-- ✅ BON
SELECT DISTINCT user_id, DATE_TRUNC('month', event_date) AS mois
FROM events;
-- user_id=1 apparaît 1 fois en janvier

Piège 2 : Inclure l'activité avant l'inscription

La condition WHERE a.mois_activite >= c.mois_cohorte est importante. Sans elle, un utilisateur qui a des événements avant son inscription (données legacy, import) fausserait le calcul.

-- ❌ SANS LA CONDITION
-- Un utilisateur inscrit en février mais avec un event en janvier
-- serait associé à la cohorte de janvier

-- ✅ AVEC LA CONDITION
WHERE a.mois_activite >= c.mois_cohorte
-- L'utilisateur n'est associé qu'à sa vraie cohorte (février)

Piège 3 : Division par zéro

Si une cohorte n'a aucun inscrit (improbable mais possible avec des filtres), la division échoue. Utilisez NULLIF :

ROUND(100.0 * r.nb_actifs / NULLIF(t.nb_inscrits, 0), 1)

Piège 4 : Confondre rétention et activation

Exemple de confusion :

-- RÉTENTION (ce qu'on fait dans cet exercice)
-- Q: "Sur les 1000 inscrits en janvier, combien reviennent en février ?"
-- R: 750 → taux = 75%

-- ACTIVATION (problème différent)
-- Q: "Sur les 1000 inscrits en janvier, combien ont eu au moins 1 event en janvier ?"
-- R: 850 → taux d'activation = 85%

Points bonus en entretien

Si vous terminez rapidement, proposez ces améliorations :

1. Filtrer par type d'événement : « Voulez-vous compter uniquement les logins ou toute activité ? »
2. Rétention par source d'acquisition : ajoutez une colonne acquisition_source et partitionnez par source
3. Rolling retention vs strict retention :
   • Strict = actif EXACTEMENT au mois N
   • Rolling = actif N'IMPORTE QUAND après le mois N (plus courant)
4. Rétention dès le jour 1 : modifier la granularité en jours pour une analyse fine des 7 premiers jours critiques
5. Analyse par persona : ajouter une dimension user_plan ou company_size pour segmenter la rétention

Pour d'autres exercices du même type, consultez notre exercice sur le tunnel de conversion e-commerce ou nos packs d'exercices SQL corrigés.

Questions fréquentes {#faq}

Comment calculer la rétention à 7 jours plutôt qu'à 30 jours ?

Le principe est identique, mais on travaille à la granularité journalière : tronquez les dates au jour (plutôt qu'au mois avec DATE_TRUNC('month', ...)), et calculez le nombre de jours écoulés depuis l'inscription plutôt que le nombre de mois. La difficulté supplémentaire est le volume : une analyse sur 7 jours glissants produit beaucoup plus de lignes. Pensez à filtrer sur une fenêtre de temps raisonnable et à utiliser DISTINCT sur l'activité journalière pour éviter les doublons.

Exemple : pour un utilisateur inscrit le 2026-01-05, l'utilisateur est « actif à J1 » s'il a un événement entre 2026-01-05 et 2026-01-11 (les 7 premiers jours incluent J0). Adaptez la WHERE selon la définition métier.

Quelle est la différence entre rétention et churn ?

La rétention mesure la proportion d'utilisateurs qui reviennent sur une période donnée. Le churn est son complément : churn = 1 - rétention. Si 66.7% des utilisateurs sont retenus au mois 1, le churn est de 33.3%. En pratique, la rétention est préférée pour les analyses positives (croissance, engagement), et le churn pour les analyses de risque (départ des clients). Les deux métriques se calculent avec la même requête SQL de base : seul l'affichage change (100 - taux_retention pour le churn).

Comment gérer les fuseaux horaires dans un calcul de rétention ?

Les données d'événements stockées en UTC peuvent créer des décalages selon la localisation des utilisateurs : un utilisateur de Los Angeles actif à 23h UTC est actif le lendemain en heure locale. Pour une rétention précise, convertissez les timestamps dans le fuseau horaire de référence avant de tronquer à la granularité mois ou jour. En PostgreSQL : DATE_TRUNC('month', event_date AT TIME ZONE 'America/Los_Angeles'). En MySQL : DATE_FORMAT(CONVERT_TZ(event_date, 'UTC', 'America/Los_Angeles'), '%Y-%m-01').

Pour une entreprise globale (ex. Figma), il est courant de maintenir deux analyses en parallèle : une en UTC (technique) et une par région (métier).

Peut-on calculer une rétention « rolling » (glissante) en SQL ?

Oui, mais c'est plus complexe. La rétention rolling M1 compte tous les utilisateurs actifs dans les 30 jours suivant l'inscription, pas juste ceux actifs au mois 1. Utilisez une fenêtre temporelle :

WHERE a.event_date >= c.created_at
  AND a.event_date < c.created_at + INTERVAL '30 days'

Avec cette approche, vous obtenez des courbes de rétention lissées. Utile pour les dashboards exécutifs.