0Pricing
SQL Interview Prep · Leçon

Définir une cohorte par sa première action

Attribuer à chaque utilisateur une cohorte selon la date de son premier événement

Définir une cohorte par sa première action est une leçon SQL Interview Prep gratuite sur CoddyKit. Ceci est la leçon 1 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 les cohortes sont abordées en entretien

Lorsqu'un recruteur en analyse produit vous demande de « construire une cohorte », il cherche à vérifier que vous savez attribuer chaque utilisateur à un groupe en fonction du moment où il a effectué une action pour la première fois, puis suivre ce groupe au fil du temps.

Une cohorte est un ensemble d'utilisateurs qui partagent un événement de départ au cours de la même période, généralement leur premier achat, leur inscription ou leur connexion. L'intérêt des cohortes est de pouvoir comparer les utilisateurs sur une base équitable : tous les utilisateurs de la cohorte de janvier sont mesurés à partir de leur propre début en janvier.

La première compétence à maîtriser, et celle que cette leçon approfondit, consiste à calculer de manière fiable la date de première action de chaque utilisateur.

La table source

Presque toute question sur les cohortes part d'une table d'événements : une ligne par action d'utilisateur, avec un horodatage. Imaginez une table events :

  • user_id — qui a effectué l'action
  • event_type — ce qui a été fait
  • event_at — quand l'action a eu lieu, sous forme d'horodatage

En entretien, précisez oralement la granularité : « Y a-t-il une ligne par événement, et un utilisateur peut-il apparaître plusieurs fois ? » La réponse est presque toujours oui, ce qui explique précisément pourquoi vous devez utiliser une agrégation pour ramener les données à une première action par utilisateur.

CREATE TABLE events (
  user_id    INT,
  event_type VARCHAR(50),
  event_at   TIMESTAMP
);

Première action = MIN de l'horodatage

L'opération centrale est simple : regroupez par user_id et prenez MIN(event_at). Cette valeur minimale correspond à la première action de l'utilisateur, c'est-à-dire au moment qui le place dans une cohorte.

C'est la première réponse que les recruteurs veulent entendre, avant toute utilisation sophistiquée de fonctions de fenêtre. Une simple clause GROUP BY est correcte, lisible et rapide.

SELECT
  user_id,
  MIN(event_at) AS first_action_at
FROM events
GROUP BY user_id;

Filtrer sur l'événement définissant la cohorte

Souvent, la cohorte est définie par une action précise, et non par n'importe quel événement. « Regrouper les utilisateurs dans une cohorte selon leur premier achat » signifie que vous devez filtrer les lignes correspondant aux achats avant de prendre le minimum.

Placez le filtre dans WHERE afin que MIN ne prenne en compte que les lignes admissibles. Un piège courant en entretien consiste à calculer MIN sur tous les événements, puis à filtrer ensuite ; la mauvaise date de début serait alors attribuée à toute personne ayant consulté le produit avant d'acheter.

SELECT
  user_id,
  MIN(event_at) AS first_purchase_at
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Regrouper dans une période de cohorte

Une cohorte correspond généralement à une période, et non à un horodatage précis : la « cohorte de mars 2024 » ou la « semaine du 4 mars 2024 ». Tronquez la date de première action pour la ramener à la granularité de la période.

Dans Postgres, utilisez DATE_TRUNC('month', ...). Dans MySQL, vous pouvez utiliser DATE_FORMAT(d, '%Y-%m-01') ; dans SQL Server, DATETRUNC(month, d) ou calculer le premier jour du mois. Indiquez votre dialecte pendant l'entretien afin que le choix de la syntaxe paraisse délibéré.

SELECT
  user_id,
  DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Encapsuler le tout dans un CTE

L'attribution d'une cohorte par utilisateur est un élément de base que vous réutiliserez dans les requêtes de rétention. Regroupez-la donc dans un CTE au nom explicite. Les étapes suivantes restent ainsi lisibles et vous montrez au recruteur que vous raisonnez en éléments que l'on peut combiner.

À partir de là, toute requête ultérieure peut faire une jointure avec user_cohort pour savoir à quel groupe appartient un utilisateur.

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

Taille de la cohorte : compter les membres

La première vérification de cohérence attendue par un recruteur est la taille de la cohorte : combien d'utilisateurs appartiennent à chaque cohorte. Regroupez le CTE d'attribution par cohort_month et comptez les utilisateurs distincts.

Utilisez COUNT(DISTINCT user_id) par précaution, même si le CTE contient déjà une ligne par utilisateur ; cela montre que vous tenez compte de la granularité. Ce décompte devient le dénominateur de chaque pourcentage de rétention par la suite.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT
  cohort_month,
  COUNT(DISTINCT user_id) AS cohort_size
FROM user_cohort
GROUP BY cohort_month
ORDER BY cohort_month;

Alternative avec une fonction de fenêtre

Les recruteurs demandent parfois que l'étiquette de cohorte soit associée à chaque ligne d'événement, et non à une table réduite. Une fonction de fenêtre est alors idéale : MIN(event_at) OVER (PARTITION BY user_id) calcule la première action sans supprimer de lignes.

C'est pratique lorsque vous avez besoin à la fois des événements détaillés et de l'étiquette de cohorte en un seul parcours, ce qui prépare le comptage de la rétention.

SELECT
  user_id,
  event_at,
  DATE_TRUNC('month',
    MIN(event_at) OVER (PARTITION BY user_id)
  ) AS cohort_month
FROM events
WHERE event_type = 'purchase';

Le piège des égalités et des doublons

Que se passe-t-il si un utilisateur a deux événements exactement au même horodatage le plus ancien ? MIN gère cela proprement : il renvoie cette valeur minimale unique, quel que soit le nombre de lignes ex æquo, de sorte que l'attribution de la cohorte reste limitée à une par utilisateur.

Comparez cette méthode à une approche utilisant ROW_NUMBER() ... ORDER BY event_at, où les égalités sont départagées arbitrairement et où vous devez ajouter un critère de départage déterministe, comme event_id, pour obtenir un résultat stable. Mentionner spontanément ce compromis témoigne d'un niveau confirmé.

SELECT user_id, event_at,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY event_at, event_id
  ) AS rn
FROM events
WHERE event_type = 'purchase';

Fuseaux horaires et limite du jour

Voici une question subtile posée en entretien : un achat effectué à 23 h 30 à New York a lieu le lendemain en UTC. Si les cohortes sont regroupées par jour calendaire, le fuseau horaire détermine la cohorte à laquelle l'utilisateur est affecté.

La réponse sûre consiste à stocker les horodatages en UTC, puis à les convertir dans le fuseau horaire métier avant de les tronquer. Indiquez explicitement quel fuseau définit le « jour » de la métrique, car cette seule décision peut faire passer des milliers d'utilisateurs d'une cohorte à une autre.

SELECT
  user_id,
  DATE_TRUNC('day',
    MIN(event_at AT TIME ZONE 'America/New_York')
  ) AS cohort_day
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Exclure les utilisateurs antérieurs à la période

Les analyses réelles limitent la cohorte à une plage de dates, par exemple les « cohortes ayant commencé au premier trimestre ». Filtrez sur la date de première action agrégée, ce qui signifie utiliser une clause HAVING ou un filtre externe sur le CTE, et non un WHERE appliqué aux événements bruts.

Filtrer les événements bruts par date permettrait à tort à un utilisateur ayant effectué son premier achat en décembre, mais ayant aussi effectué une action au premier trimestre, de s'introduire dans une cohorte du premier trimestre. Basez-vous toujours sur la première action calculée.

WITH user_cohort AS (
  SELECT user_id, MIN(event_at) AS first_at
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT user_id, DATE_TRUNC('month', first_at) AS cohort_month
FROM user_cohort
WHERE first_at >= DATE '2024-01-01'
  AND first_at <  DATE '2024-04-01';

Vérification rapide

Un recruteur vous demande : « Regroupez chaque utilisateur selon le mois de son premier achat. Les utilisateurs peuvent consulter le produit avant d'acheter. » Quelle approche est correcte ?

Récapitulatif : définir une cohorte

Points essentiels pour répondre à une question d'entretien sur la définition d'une cohorte :

  • Une cohorte regroupe les utilisateurs selon leur première action admissible.
  • Calculez-la avec MIN(event_at) après avoir filtré l'événement définissant la cohorte dans WHERE.
  • Regroupez-la dans une période avec DATE_TRUNC (ou l'équivalent de votre dialecte).
  • Encapsulez l'attribution dans un CTE pour la réutiliser ; COUNT(DISTINCT user_id) donne la taille de la cohorte.
  • Surveillez la limite du jour liée au fuseau horaire et appliquez les plages de dates à la première action calculée, jamais aux événements bruts.

Maîtrisez cette étape et la matrice de rétention de la prochaine leçon se résumera à une jointure.

Questions Fréquemment Posées

La leçon « Définir une cohorte par sa première action » est-elle gratuite ?

Oui — le texte complet de « Définir une cohorte par sa première action » 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 « Définir une cohorte par sa première action » ?

Attribuer à chaque utilisateur une cohorte selon la date de son premier événement 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 1 sur 4.

Combien de temps prend la leçon « Définir une cohorte par sa première action » ?

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

  1. Définir une cohorte par sa première action
  2. Construire une matrice de rétention
  3. Rétention au jour N et rétention glissante
  4. Requêtes sur l’attrition et le retour des utilisateurs
← Retour à SQL Interview Prep