SQL Interview Prep · Leçon

Requêtes sur l’attrition et le retour des utilisateurs

Identifier les utilisateurs partis et ceux revenus après une interruption

Requêtes sur l’attrition et le retour des utilisateurs est une leçon SQL Interview Prep gratuite sur CoddyKit. Ceci est la leçon 4 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.

L’autre face de la rétention

Si la rétention mesure qui est resté, l’abandon mesure qui est parti, et la réactivation mesure qui est revenu. Les recruteurs associent ces mesures à la rétention, car elles montrent si vous savez raisonner sur l’absence d’activité, ce qui est plus difficile que de compter une présence.

L’astuce récurrente est la suivante : vous ne pouvez pas filtrer des lignes qui n’existent pas. Les requêtes d’abandon consistent essentiellement à trouver le trou entre la dernière activité d’un utilisateur et aujourd’hui, ou entre cette activité et la suivante.

Définir précisément l’abandon

Le terme « abandonné » n’a aucun sens sans fenêtre temporelle. Une définition courante est la suivante : un utilisateur est considéré comme ayant abandonné s’il n’a eu aucune activité au cours des 30 derniers jours. Le seuil d’inactivité de 30 jours est la décision métier que vous devez préciser.

Pour les produits par abonnement, l’abandon peut plutôt désigner un abonnement résilié ou arrivé à expiration : il s’agit alors d’un changement d’état plutôt que d’un intervalle sans activité. Clarifiez le modèle applicable avant d’écrire la requête SQL.

Dernière activité de chaque utilisateur

Le fondement de l’abandon mesuré par intervalle d’inactivité est le dernier événement de chaque utilisateur. Regroupez les données par utilisateur et prenez le MAX de la date de l’événement.

Cette valeur unique, comparée à la date du jour, indique depuis combien de temps l’utilisateur est silencieux. Tout le traitement qui suit consiste à comparer cette date de dernière activité à une autre date.

SELECT
  user_id,
  MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id;

La requête des utilisateurs ayant abandonné

Un utilisateur est considéré comme ayant abandonné si sa dernière activité remonte à plus de 30 jours. Comparez last_active à CURRENT_DATE - 30. Toute personne dont l’événement le plus récent est antérieur à ce seuil est devenue inactive.

Remarquez que le traitement a lieu après l’agrégation : vous réduisez les données à une ligne par utilisateur, puis vous vérifiez l’intervalle. Filtrer les événements bruts par date indiquerait uniquement qui était inactif pendant une fenêtre, et non qui a globalement abandonné.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events
  GROUP BY user_id
)
SELECT user_id, last_active
FROM last_seen
WHERE last_active < CURRENT_DATE - INTERVAL '30 days';

Calculer le taux d’abandon

Le taux d’abandon correspond au nombre d’utilisateurs ayant abandonné divisé par la base pertinente, souvent les utilisateurs qui étaient actifs au début de la période. Utilisez une agrégation conditionnelle pour compter les utilisateurs ayant abandonné et le total en un seul parcours, puis effectuez soigneusement la division avec 100.0 et NULLIF.

Précisez le dénominateur en entretien : l’abandon parmi tous les utilisateurs historiques et l’abandon parmi les utilisateurs auparavant actifs sont deux mesures différentes.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days'
  ) AS churned,
  COUNT(*) AS total_users,
  ROUND(100.0 * COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days')
    / NULLIF(COUNT(*), 0), 1) AS churn_pct
FROM last_seen;

Abandon d’une période à l’autre avec la logique des ensembles

Une autre formulation consiste à demander : qui était actif le mois dernier, mais pas ce mois-ci ? Il s’agit d’une différence d’ensembles. Construisez l’ensemble des utilisateurs actifs le mois dernier et celui des utilisateurs actifs ce mois-ci, puis trouvez les membres du premier qui ne figurent pas dans le second.

Vous pouvez l’exprimer avec EXCEPT, une LEFT JOIN / IS NULL anti-jointure ou NOT EXISTS. L’anti-jointure est la solution la plus portable et celle que les recruteurs veulent le plus souvent voir.

WITH last_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-04-01' AND event_at < DATE '2024-05-01'
),
this_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-05-01' AND event_at < DATE '2024-06-01'
)
SELECT user_id FROM last_month
EXCEPT
SELECT user_id FROM this_month;

La forme de l’anti-jointure

La même requête d’attrition pour cette période, écrite sous forme d’anti-jointure : effectuez une LEFT JOIN des utilisateurs actifs ce mois-ci sur ceux du mois dernier, puis conservez les lignes pour lesquelles la correspondance est NULL. Il s’agit des utilisateurs présents le mois dernier mais absents ce mois-ci : les utilisateurs en attrition.

NOT EXISTS est une réponse tout aussi valable et gère les valeurs NULL en toute sécurité. Précisez que NOT IN serait risqué si l’ensemble interne pouvait contenir des valeurs NULL : c’est un piège classique.

SELECT lm.user_id
FROM last_month lm
LEFT JOIN this_month tm ON tm.user_id = lm.user_id
WHERE tm.user_id IS NULL;

Définir la résurrection

La résurrection (aussi appelée réactivation) désigne un utilisateur qui était en attrition et qui est ensuite redevenu actif. Sa signature est un intervalle dans sa chronologie : actif, puis une période de silence plus longue que le seuil d’attrition, puis de nouveau actif.

Un utilisateur réactivé ce mois-ci est donc actif maintenant, était inactif lors de la période précédente, mais avait été actif au cours d’une période encore antérieure. C’est l’image inversée de l’attrition.

Détecter les intervalles avec LAG

La manière élégante de détecter une résurrection consiste à utiliser la fonction de fenêtre LAG : pour chaque période d’activité d’un utilisateur, examinez la période active précédente. Si l’intervalle entre les deux dépasse le seuil, la période actuelle correspond à une réactivation.

LAG évite une auto-jointure et se lit clairement. Partitionnez par utilisateur, triez par période active, puis comparez chaque période à celle qui la précède.

WITH monthly AS (
  SELECT DISTINCT user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
),
gaps AS (
  SELECT user_id, active_month,
    LAG(active_month) OVER (
      PARTITION BY user_id ORDER BY active_month
    ) AS prev_month
  FROM monthly
)
SELECT user_id, active_month AS resurrected_month
FROM gaps
WHERE prev_month IS NOT NULL
  AND active_month > prev_month + INTERVAL '1 month';

Nouveaux, réactivés ou restés actifs

Une requête complète de classification de l’activité étiquette chaque utilisateur actif pendant cette période comme étant : nouveau (aucune activité antérieure), resté actif (également actif lors de la période précédente) ou réactivé (activité antérieure, mais avec un intervalle). La valeur prev_month issue de LAG permet de distinguer les trois cas.

prev_month IS NULL → nouveau
prev_month = active_month - 1 → resté actif
sinon (un intervalle) → réactivé

Produire cette répartition constitue une réponse solide et complète.

SELECT user_id, active_month,
  CASE
    WHEN prev_month IS NULL THEN 'new'
    WHEN active_month = prev_month + INTERVAL '1 month' THEN 'retained'
    ELSE 'resurrected'
  END AS user_state
FROM gaps;

Le piège de NOT IN avec NULL

Voici un dernier piège. Si vous écrivez l’attrition ainsi : WHERE user_id NOT IN (SELECT user_id FROM this_month), et que cette sous-requête renvoie ne serait-ce qu’un NULL, l’ensemble du résultat devient vide, car NOT IN s’évalue à UNKNOWN face à NULL.

Préférez NOT EXISTS ou une anti-jointure LEFT JOIN / IS NULL, qui se comportent correctement avec les valeurs NULL. Signaler spontanément cette différence est un indicateur fiable d’un profil expérimenté lors des entretiens sur la rétention.

-- safe anti-join instead of NOT IN
SELECT lm.user_id
FROM last_month lm
WHERE NOT EXISTS (
  SELECT 1 FROM this_month tm
  WHERE tm.user_id = lm.user_id
);

Vérification rapide

Vous voulez les utilisateurs actifs le mois dernier mais pas ce mois-ci. Un collègue a écrit WHERE user_id NOT IN (SELECT user_id FROM this_month), et la requête renvoie zéro ligne alors que certains utilisateurs sont clairement en attrition. Quelle est la correction la plus sûre ?

Récapitulatif : attrition et résurrection

Les notions essentielles de l’attrition et de la résurrection :

Définissez l’attrition à l’aide d’un seuil d’inactivité (par exemple, aucune activité pendant 30 jours) ou d’un changement d’état de l’abonnement — précisez lequel.
Calculez le MAX(dernière activité) de chaque utilisateur, puis comparez-le à CURRENT_DATE - threshold.
L’attrition d’une période à l’autre est une différence d’ensembles : utilisez EXCEPT, NOT EXISTS ou une anti-jointure LEFT JOIN / IS NULL.
La résurrection correspond à un intervalle dans la chronologie ; détectez-la avec LAG pour classer les utilisateurs comme nouveaux, restés actifs ou réactivés.
Évitez NOT IN lorsque des valeurs NULL sont possibles — cette expression vide silencieusement le résultat.

Questions Fréquemment Posées

La leçon « Requêtes sur l’attrition et le retour des utilisateurs » est-elle gratuite ?

Oui — le texte complet de « Requêtes sur l’attrition et le retour des utilisateurs » 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 « Requêtes sur l’attrition et le retour des utilisateurs » ?

Identifier les utilisateurs partis et ceux revenus après une interruption 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 4 sur 4.

Combien de temps prend la leçon « Requêtes sur l’attrition et le retour des utilisateurs » ?

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

← Retour à SQL Interview Prep