0Pricing
SQL Interview Prep · Leçon

Construire un entonnoir en plusieurs étapes

Compter les utilisateurs qui atteignent chaque étape ordonnée et calculer les taux de conversion entre les étapes

Construire un entonnoir en plusieurs étapes 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.

Ce qu’évalue réellement une question sur un entonnoir

Lorsqu’un intervieweur vous dit « construisez-moi un entonnoir d’inscription », il évalue votre capacité à compter les utilisateurs distincts qui atteignent chaque étape dans l’ordre et à exprimer la perte entre ces étapes.

Un entonnoir comporte des étapes comme visit -> signup -> activate -> purchase. Le résultat attendu est généralement une ligne par étape, avec un nombre d’utilisateurs et un taux de conversion.

  • Comptez les utilisateurs, pas les événements : si un utilisateur déclenche deux fois un événement, cela reste un seul utilisateur.
  • Les étapes sont ordonnées ; atteindre l’étape 3 implique d’avoir franchi les étapes 1 et 2.

La table d’événements qui vous sera fournie

Presque toute question sur un entonnoir vous fournit une seule table d’événements au format long. Imaginez cette structure :

  • user_id, l’utilisateur qui a effectué l’action
  • event_name, comme « visite », « inscription » ou « achat »
  • event_time, un horodatage

Une ligne par action. Votre tâche consiste à transformer ces données en un décompte étape par étape. Confirmez toujours les noms exacts des événements avec l’intervieweur avant d’écrire la requête SQL.

CREATE TABLE events (
  user_id    INT,
  event_name VARCHAR(50),
  event_time TIMESTAMP
);

Compter les utilisateurs d’une étape

Commencez simplement : combien d’utilisateurs distincts ont atteint une étape donnée ? Utilisez COUNT(DISTINCT user_id) avec un filtre sur le nom de l’événement.

C’est l’élément de base de tout entonnoir. Si vous savez compter proprement une étape, vous pouvez toutes les compter.

SELECT COUNT(DISTINCT user_id) AS users_who_signed_up
FROM events
WHERE event_name = 'signup';

Agrégation conditionnelle pour toutes les étapes

La réponse claire en entretien consiste à compter chaque étape en un seul passage grâce à une agrégation conditionnelle : un CASE à l’intérieur de COUNT(DISTINCT ...).

Pour chaque étape, comptez les utilisateurs distincts dont l’événement correspond à cette étape. Un balayage, une ligne de totaux pour les étapes.

SELECT
  COUNT(DISTINCT CASE WHEN event_name = 'visit'    THEN user_id END) AS step1_visit,
  COUNT(DISTINCT CASE WHEN event_name = 'signup'   THEN user_id END) AS step2_signup,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS step3_purchase
FROM events;

Le problème caché : les étapes ne sont pas ordonnées

La requête que vous venez de voir contient un piège que les intervieweurs apprécient particulièrement. Elle compte toute personne ayant déclenché « achat », même si elle n’a jamais visité le site ni ne s’est inscrite dans les données.

Un véritable entonnoir exige que chaque étape ultérieure soit un sous-ensemble de l’étape précédente. Compter les événements indépendamment peut produire une étape 3 plus grande que l’étape 2, ce qui est logiquement impossible pour un entonnoir.

La correction consiste à relier les étapes de chaque utilisateur, généralement en réduisant d’abord les données à une ligne par utilisateur.

Une ligne par utilisateur avec des indicateurs

Le schéma robuste consiste à réduire le journal d’événements à une ligne par utilisateur, avec un indicateur booléen (sous forme de 0/1) précisant s’il a déjà effectué chaque étape. MAX(CASE ...) transforme le journal au format long en un récapitulatif large par utilisateur.

WITH user_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS did_visit,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS did_signup,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
  FROM events
  GROUP BY user_id
)
SELECT * FROM user_steps;

Imposer l’ordre des étapes

Imposez maintenant la règle de l’entonnoir : un utilisateur ne compte pour l’étape N que s’il a également effectué toutes les étapes précédentes. Atteindre « achat » n’a de sens que si l’utilisateur a aussi effectué une visite et une inscription.

Additionnez les indicateurs en combinant les conditions préalables avec ET, afin que chaque étape soit un véritable sous-ensemble de la précédente.

WITH user_steps AS (
  SELECT
    user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS did_visit,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS did_signup,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
  FROM events
  GROUP BY user_id
)
SELECT
  SUM(did_visit)                                       AS step1_visit,
  SUM(CASE WHEN did_visit = 1 AND did_signup = 1 THEN 1 ELSE 0 END)                       AS step2_signup,
  SUM(CASE WHEN did_visit = 1 AND did_signup = 1 AND did_purchase = 1 THEN 1 ELSE 0 END)  AS step3_purchase
FROM user_steps;

Transformer les comptes en résultat long bien structuré

Les intervieweurs préfèrent souvent une ligne par étape plutôt qu’une seule ligne large. Transformez les totaux au format large en format long à l’aide d’un petit UNION ALL, en associant un numéro à chaque étape pour définir l’ordre.

Les calculs de taux de conversion et la création de graphiques sont ainsi beaucoup plus simples à l’étape suivante.

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT step_no, step_name, users
FROM funnel
ORDER BY step_no;

Taux de conversion d’une étape à la suivante

Deux taux sont importants, et les intervieweurs vous demanderont lequel vous utilisez :

  • Conversion d’une étape : utilisateurs à cette étape divisés par les utilisateurs de l’étape précédente.
  • Conversion globale : utilisateurs à cette étape divisés par ceux du début de l’entonnoir.

Utilisez LAG pour récupérer le nombre de l’étape précédente et calculer le taux d’une étape à la suivante. Convertissez en nombre décimal pour éviter la division entière.

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT
  step_name,
  users,
  ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_conv_pct
FROM funnel
ORDER BY step_no;

Conversion globale depuis le début

Pour calculer la conversion globale, divisez le nombre de chaque étape par le nombre de la première étape. FIRST_VALUE appliqué à l’entonnoir ordonné fixe cette valeur initiale pour chaque ligne.

Mentionnez toujours à l’intervieweur que vous avez évité la division entière en multipliant par 100.0.

WITH funnel AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, 1000 AS users UNION ALL
  SELECT 2,           'signup',                   420  UNION ALL
  SELECT 3,           'purchase',                 95
)
SELECT
  step_name,
  users,
  ROUND(100.0 * users / FIRST_VALUE(users) OVER (ORDER BY step_no), 1) AS overall_pct
FROM funnel
ORDER BY step_no;

Assembler l’entonnoir complet

Voici la réponse complète que les intervieweurs attendent : réduisez les données en indicateurs par utilisateur, imposez l’ordre, passez au format long, puis calculez les deux taux. Expliquez chaque étape à voix haute en précisant le rôle de chaque CTE.

Cette structure s’adapte facilement : ajoutez une étape en ajoutant un indicateur et une ligne UNION ALL.

WITH user_steps AS (
  SELECT user_id,
    MAX(CASE WHEN event_name = 'visit'    THEN 1 ELSE 0 END) AS s1,
    MAX(CASE WHEN event_name = 'signup'   THEN 1 ELSE 0 END) AS s2,
    MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS s3
  FROM events GROUP BY user_id
),
totals AS (
  SELECT 1 AS step_no, 'visit'    AS step_name, SUM(s1) AS users FROM user_steps UNION ALL
  SELECT 2, 'signup',   SUM(CASE WHEN s1=1 AND s2=1 THEN 1 ELSE 0 END) FROM user_steps UNION ALL
  SELECT 3, 'purchase', SUM(CASE WHEN s1=1 AND s2=1 AND s3=1 THEN 1 ELSE 0 END) FROM user_steps
)
SELECT step_name, users,
  ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_pct
FROM totals ORDER BY step_no;

Vérification rapide

Un intervieweur remarque que votre entonnoir affiche 95 utilisateurs à l’étape « achat », mais seulement 80 à l’étape « inscription ». Quelle est la cause la plus probable ?

Récapitulatif : entonnoirs à plusieurs étapes

Vous savez maintenant construire un entonnoir comme l’attendent les entretiens :

  • Comptez les utilisateurs distincts pour chaque étape ordonnée, jamais les événements bruts.
  • Réduisez le journal d’événements à une ligne par utilisateur avec des indicateurs MAX(CASE ...).
  • Imposez l’ordre afin que chaque étape soit un sous-ensemble de la précédente.
  • Calculez la conversion d’une étape à la suivante (LAG) et la conversion globale (FIRST_VALUE), en évitant la division entière.

Ensuite : vérifiez que ces étapes ont réellement eu lieu dans le bon ordre et dans une fenêtre temporelle donnée.

Questions Fréquemment Posées

La leçon « Construire un entonnoir en plusieurs étapes » est-elle gratuite ?

Oui — le texte complet de « Construire un entonnoir en plusieurs étapes » 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 « Construire un entonnoir en plusieurs étapes » ?

Compter les utilisateurs qui atteignent chaque étape ordonnée et calculer les taux de conversion entre les étapes 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 « Construire un entonnoir en plusieurs étapes » ?

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. Construire un entonnoir en plusieurs étapes
  2. Événements ordonnés et fenêtres temporelles
  3. Attribution des tests A/B et indicateurs
  4. Gain, significativité et garde-fous en SQL
← Retour à SQL Interview Prep