0Pricing
SQL Interview Prep · Leçon

Événements ordonnés et fenêtres temporelles

Garantir que les étapes se déroulent dans l’ordre et dans une limite de temps à l’aide de fonctions de fenêtre

Événements ordonnés et fenêtres temporelles est une leçon SQL Interview Prep gratuite sur CoddyKit. Ceci est la leçon 2 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 l’ordre et le temps comptent

L’entonnoir élémentaire de la leçon précédente vérifie seulement si un utilisateur a effectué chaque étape. Un intervieweur plus exigeant demande : les étapes ont-elles eu lieu dans le bon ordre et dans un délai raisonnable ?

Un utilisateur qui a effectué un achat lundi et visité la page marketing vendredi n’est pas passé par votre entonnoir de conversion. L’ordre et le temps transforment un entonnoir naïf fondé sur des indicateurs en un entonnoir crédible.

L’idée du premier horodatage par utilisateur

Pour raisonner sur l’ordre, enregistrez le premier horodatage de chaque utilisateur pour chaque étape. Le premier horodatage de visite, le premier horodatage d’inscription, le premier horodatage d’achat.

Une conversion valide signifie alors que first_signup_time >= first_visit_time, et ainsi de suite dans la chaîne. MIN(event_time) regroupé par étape fournit ces points de référence.

SELECT
  user_id,
  MIN(CASE WHEN event_name = 'visit'    THEN event_time END) AS first_visit,
  MIN(CASE WHEN event_name = 'signup'   THEN event_time END) AS first_signup,
  MIN(CASE WHEN event_name = 'purchase' THEN event_time END) AS first_purchase
FROM events
GROUP BY user_id;

Exiger les étapes dans l’ordre

Avec les premiers horodatages de chaque étape, imposer l’ordre revient à effectuer une comparaison. Un utilisateur n’est réellement converti à l’étape 3 que si chaque horodatage est non NULL et augmente de façon monotone.

Remarquez qu’un horodatage NULL (l’étape n’a jamais eu lieu) fait naturellement échouer la comparaison, ce qui est exactement le comportement recherché.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS visit_t,
    MIN(CASE WHEN event_name='signup'   THEN event_time END) AS signup_t,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS purchase_t
  FROM events GROUP BY user_id
)
SELECT COUNT(*) AS converted_in_order
FROM t
WHERE visit_t IS NOT NULL
  AND signup_t  >= visit_t
  AND purchase_t >= signup_t;

Ajouter une fenêtre temporelle

La plupart des entonnoirs ont une échéance : « convertir dans les 7 jours suivant la première visite ». Ajoutez une borne d’intervalle entre la première étape et la dernière.

Le calcul des dates varie selon le dialecte. Dans Postgres, vous pouvez écrire visit_t + INTERVAL '7 days' ; dans MySQL, utilisez DATE_ADD(visit_t, INTERVAL 7 DAY). Indiquez toujours votre dialecte.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS visit_t,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS purchase_t
  FROM events GROUP BY user_id
)
SELECT COUNT(*) AS purchased_within_7d
FROM t
WHERE purchase_t >= visit_t
  AND purchase_t <  visit_t + INTERVAL '7 days';

Pourquoi utiliser le premier horodatage plutôt qu’un horodatage quelconque

Un point subtil en entretien : la fenêtre doit-elle partir de la première visite de l’utilisateur ou de sa visite la plus récente avant l’inscription ? Cela dépend de la question produit.

  • Les fenêtres de premier contact mesurent le délai entre l’intérêt initial et la conversion.
  • Les fenêtres de dernier contact mesurent l’accélération de la conversion après la dernière visite.

Demandez à l’intervieweur ce qu’il entend par là ; faire un choix réfléchi témoigne d’un niveau d’expérience élevé.

Événements ordonnés avec LEAD

Pour les parcours complexes à plusieurs étapes, les fonctions de fenêtre sont particulièrement utiles. Ordonnez les événements de chaque utilisateur par heure, puis utilisez LEAD pour examiner l’événement suivant et vérifier qu’il correspond à l’étape suivante attendue.

Cette méthode prend en charge les parcours où les étapes s’entrecroisent avec des événements sans rapport.

SELECT
  user_id,
  event_name,
  event_time,
  LEAD(event_name) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event,
  LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_time
FROM events;

Faire correspondre l’étape suivante attendue

À partir de LEAD, conservez les lignes où une « visite » est immédiatement suivie d’une « inscription ». Vous identifiez ainsi les véritables transitions séquentielles, et pas seulement une simple coexistence.

Vous pouvez enchaîner ces vérifications de transition pour valider un parcours ordonné complet, étape par étape.

WITH seq AS (
  SELECT user_id, event_name, event_time,
    LEAD(event_name) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event
  FROM events
)
SELECT COUNT(DISTINCT user_id) AS visit_then_signup
FROM seq
WHERE event_name = 'visit' AND next_event = 'signup';

Le temps entre deux étapes consécutives

Les recruteurs aiment demander : « combien de temps prend chaque étape ? » Utilisez LEAD sur l’horodatage, puis soustrayez les valeurs. La différence entre deux événements consécutifs correspond au temps passé à cette étape.

Agrégez la médiane ou la moyenne de chaque transition afin de trouver l’étape la plus lente de votre entonnoir.

WITH seq AS (
  SELECT user_id, event_name, event_time,
    LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_time
  FROM events
)
SELECT
  event_name,
  AVG(EXTRACT(EPOCH FROM (next_time - event_time)) / 3600.0) AS avg_hours_to_next
FROM seq
WHERE next_time IS NOT NULL
GROUP BY event_name;

Le cas particulier des horodatages identiques

Que se passe-t-il si deux événements partagent exactement le même event_time ? Alors signup_t >= visit_t est vrai même s’ils sont simultanés, et l’ordre fondé uniquement sur le temps est ambigu.

  • Choisissez délibérément entre >= et >, et expliquez pourquoi.
  • Ajoutez un critère de départage, comme un identifiant de séquence d’événement, à ORDER BY afin que les fenêtres soient déterministes.

Aborder spontanément ce point impressionne les intervieweurs.

SELECT user_id, event_name,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time, event_id) AS step_seq
FROM events;

Combiner l’ordre et la fenêtre dans une seule requête

Voici l’entonnoir complet, ordonné et limité à une fenêtre temporelle. Il s’appuie sur la première visite, exige que la première occurrence de chaque étape suivante arrive après la précédente et limite l’ensemble du parcours à 7 jours.

C’est la réponse qui distingue un candidat qui comprend les entonnoirs d’un candidat qui se contente de compter des indicateurs.

WITH t AS (
  SELECT user_id,
    MIN(CASE WHEN event_name='visit'    THEN event_time END) AS v,
    MIN(CASE WHEN event_name='signup'   THEN event_time END) AS s,
    MIN(CASE WHEN event_name='purchase' THEN event_time END) AS p
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (WHERE v IS NOT NULL)                                   AS visited,
  COUNT(*) FILTER (WHERE s >= v AND s < v + INTERVAL '7 days')            AS signed_up,
  COUNT(*) FILTER (WHERE s >= v AND p >= s AND p < v + INTERVAL '7 days') AS purchased
FROM t;

Remarques sur les dialectes

Deux rappels de portabilité pour le codage en direct :

  • FILTER (WHERE ...) sur les agrégats fait partie du SQL standard et fonctionne dans Postgres ; dans MySQL ou les moteurs plus anciens, utilisez la solution de repli SUM(CASE WHEN ... THEN 1 ELSE 0 END).
  • La syntaxe des intervalles diffère : Postgres + INTERVAL '7 days', MySQL DATE_ADD(d, INTERVAL 7 DAY), SQL Server DATEADD(day, 7, d).

Annoncez votre hypothèse : l’intervieweur se soucie rarement du dialecte choisi, mais seulement du fait que vous savez qu’ils diffèrent.

Vérification rapide

Vous devez compter les utilisateurs qui ont effectué visite -> inscription -> achat dans cet ordre, dans les 7 jours suivant leur première visite. Quelle approche est correcte ?

Récapitulatif : événements ordonnés et fenêtres temporelles

Points essentiels :

  • Capturez le premier horodatage de chaque étape pour chaque utilisateur avec MIN(CASE ...).
  • Imposez la séquence en exigeant que l'heure de chaque étape soit égale ou postérieure à celle de l'étape précédente.
  • Limitez le parcours avec un intervalle, en indiquant la syntaxe de votre dialecte.
  • Utilisez LEAD/LAG pour vérifier les transitions et mesurer le temps passé entre les étapes.
  • Gérez les égalités d'horodatage avec un critère de départage dans ORDER BY.

Ensuite : passer des entonnoirs aux expériences et calculer les indicateurs par variante.

Questions Fréquemment Posées

La leçon « Événements ordonnés et fenêtres temporelles » est-elle gratuite ?

Oui — le texte complet de « Événements ordonnés et fenêtres temporelles » 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 « Événements ordonnés et fenêtres temporelles » ?

Garantir que les étapes se déroulent dans l’ordre et dans une limite de temps à l’aide de fonctions de fenêtre 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 2 sur 4.

Combien de temps prend la leçon « Événements ordonnés et fenêtres temporelles » ?

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