0Pricing
Coding Interview Prep · Lezione

Eventi ordinati e finestre temporali

Verifica che i passaggi avvengano nella sequenza corretta e entro un limite di tempo usando le funzioni finestra.

Eventi ordinati e finestre temporali è una lezione Coding Interview Prep gratuita su CoddyKit. Questa è la lezione 2 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento Coding Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso Coding Interview Prep include 4 lezioni in totale.

Perché ordine e tempo sono importanti

Il funnel di base della lezione precedente verifica solo se un utente ha eseguito ogni passaggio. Un intervistatore più attento chiede: i passaggi si sono verificati nell'ordine corretto e in un intervallo di tempo ragionevole?

Un utente che ha effettuato un acquisto lunedì e visitato la pagina di marketing venerdì non ha seguito il percorso del funnel. Sequenza e tempistiche trasformano un funnel ingenuo basato sui flag in un funnel credibile.

L'idea del primo timestamp per utente

Per ragionare sull'ordine, acquisisca il primo timestamp di ogni utente per ciascun passaggio: la prima visita, la prima registrazione, il primo acquisto.

Una conversione corretta significa quindi che first_signup_time >= first_visit_time, e così via lungo la sequenza. Raggruppando MIN(event_time) per passaggio si ottengono questi punti di riferimento.

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;

Richiedere i passaggi in sequenza

Con il primo timestamp per ogni passaggio, imporre l'ordine diventa un confronto. Un utente ha effettuato davvero la conversione al passaggio 3 solo se ogni timestamp è diverso da NULL e la sequenza è monotonicamente crescente.

Noti come un timestamp NULL, che indica che il passaggio non si è mai verificato, faccia naturalmente fallire il confronto: è esattamente ciò che si desidera.

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;

Aggiungere una finestra temporale

La maggior parte dei funnel ha una scadenza: "convertire entro 7 giorni dalla prima visita". Aggiunga un limite di intervallo tra il primo passaggio e quello finale.

L'aritmetica delle date varia in base al dialetto. In Postgres può scrivere visit_t + INTERVAL '7 days'; in MySQL usi DATE_ADD(visit_t, INTERVAL 7 DAY). Dichiari sempre il dialetto utilizzato.

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';

Perché usare il primo timestamp e non uno qualsiasi

Una questione sottile da colloquio: la finestra deve partire dalla prima visita dell'utente o da quella più recente prima della registrazione? Dipende dalla domanda di prodotto.

  • Le finestre first-touch misurano il tempo dall'interesse iniziale alla conversione.
  • Le finestre last-touch misurano la rapidità della conversione dopo l'ultima visita.

Chieda all'intervistatore quale delle due intende; scegliere consapevolmente dimostra esperienza.

Eventi ordinati con LEAD

Per percorsi complessi a più passaggi, le funzioni finestra sono particolarmente utili. Ordini gli eventi di ciascun utente in base all'ora, quindi usi LEAD per esaminare l'evento successivo e verificare che sia il passaggio atteso.

Questo gestisce i percorsi in cui tra i passaggi si inseriscono eventi non correlati.

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;

Far corrispondere il passaggio successivo atteso

Partendo da LEAD, mantenga le righe in cui un 'visit' è seguito immediatamente da un 'signup'. In questo modo individua transizioni realmente sequenziali, non una semplice co-occorrenza.

Può concatenare questi controlli di transizione per convalidare un intero percorso ordinato, un passaggio alla volta.

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';

Tempo tra passaggi consecutivi

Gli intervistatori amano chiedere: "quanto tempo richiede ogni passaggio?" Usi LEAD sul timestamp e calcoli la differenza. La differenza tra eventi consecutivi è il tempo di permanenza in quella fase.

Calcoli la mediana o la media per ogni transizione per individuare la fase più lenta del funnel.

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;

Il caso limite dei timestamp coincidenti

Che cosa accade se due eventi hanno lo stesso identico event_time? In tal caso signup_t >= visit_t è vero anche se gli eventi sono simultanei, e il solo ordinamento temporale è ambiguo.

  • Usi deliberatamente >= invece di >, spiegandone il motivo.
  • Aggiunga a ORDER BY un criterio di spareggio, come un event sequence id, in modo che le funzioni finestra siano deterministiche.

Segnalare questo aspetto spontaneamente fa una buona impressione agli intervistatori.

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

Combinare ordine e finestra in un'unica query

Ecco il funnel completo con ordine e finestra temporale. Si basa sulla prima visita, richiede che la prima occorrenza di ogni passaggio successivo avvenga dopo quella precedente e limita l'intero percorso a 7 giorni.

Questa è la risposta che distingue chi comprende davvero i funnel da chi si limita a contare i flag.

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;

Note sui dialetti

Due promemoria sulla portabilità per il live coding:

  • FILTER (WHERE ...) sugli aggregati è SQL standard e funziona in Postgres; in MySQL o nei motori meno recenti usi SUM(CASE WHEN ... THEN 1 ELSE 0 END).
  • La sintassi degli intervalli varia: Postgres + INTERVAL '7 days', MySQL DATE_ADD(d, INTERVAL 7 DAY), SQL Server DATEADD(day, 7, d).

Dichiari la sua ipotesi: all'intervistatore interessa raramente quale dialetto scelga, ma che sappia che sono diversi.

Verifica rapida

È necessario contare gli utenti che hanno completato, nell'ordine, il percorso visita -> registrazione -> acquisto entro 7 giorni dalla prima visita. Quale approccio è corretto?

Riepilogo: eventi ordinati e finestre temporali

Punti chiave:

  • Acquisisca il primo timestamp per ogni passaggio di ciascun utente con MIN(CASE ...).
  • Impartisca la sequenza richiedendo che l'ora di ogni passaggio sia uguale o successiva a quella del passaggio precedente.
  • Limiti il percorso con un intervallo, specificando la sintassi del proprio dialetto.
  • Utilizzi LEAD/LAG per verificare le transizioni e il tempo di permanenza tra i passaggi.
  • Gestisca i casi di parità con lo stesso timestamp usando un criterio di spareggio in ORDER BY.

Successivo: passare dai funnel agli esperimenti e calcolare le metriche per variante.

Domande Frequenti

La lezione «Eventi ordinati e finestre temporali» è gratuita?

Sì — il testo completo di «Eventi ordinati e finestre temporali» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso Coding Interview Prep, passa a CoddyKit PRO. Il corso Coding Interview Prep include 4 lezioni in totale.

Cosa imparerò in «Eventi ordinati e finestre temporali»?

Verifica che i passaggi avvengano nella sequenza corretta e entro un limite di tempo usando le funzioni finestra. Eserciti Coding Interview Prep con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.

Ho bisogno di esperienza per iniziare Coding Interview Prep?

Non è richiesta alcuna esperienza precedente. Coding Interview Prep su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 2 di 4.

Quanto tempo richiede la lezione «Eventi ordinati e finestre temporali»?

La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.

Posso scrivere ed eseguire codice in questa lezione Coding Interview Prep?

Sì. Ogni lezione Coding Interview Prep include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.

Tutte le lezioni di questo corso

  1. Creare un funnel a più passaggi
  2. Eventi ordinati e finestre temporali
  3. Assegnazione e metriche dei test A/B
  4. Incremento, significatività e controlli di sicurezza in SQL
← Torna a Coding Interview Prep