0Pricing
SQL Interview Prep · Lezione

Definire una coorte in base alla prima azione

Assegnazione di ogni utente a una coorte in base alla data del suo primo evento.

Definire una coorte in base alla prima azione è una lezione SQL Interview Prep gratuita su CoddyKit. Questa è la lezione 1 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 SQL Interview Prep, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Interview Prep include 4 lezioni in totale.

Perché le coorti compaiono nei colloqui

Quando un intervistatore di product analytics dice «crei una coorte», verifica se sa assegnare ogni utente a un gruppo in base a quando ha compiuto per la prima volta una certa azione, per poi monitorare quel gruppo nel tempo.

Una coorte è un insieme di utenti che condividono un evento iniziale nello stesso periodo, di solito il primo acquisto, la registrazione o l'accesso. Il vantaggio delle coorti è che consentono di confrontare gli utenti in condizioni equivalenti: ogni utente della coorte di gennaio viene misurato a partire dal proprio inizio a gennaio.

La prima abilità, e quella su cui si esercita questa lezione, consiste nel calcolare in modo affidabile la data della prima azione di ogni utente.

La tabella sorgente

Quasi ogni domanda sulle coorti parte da una tabella degli eventi: una riga per ogni azione dell'utente con un timestamp. Si consideri una tabella events:

  • user_id — chi ha eseguito l'azione
  • event_type — che cosa ha fatto
  • event_at — quando, sotto forma di timestamp

In un colloquio, chiarisca ad alta voce la granularità: «È presente una riga per ogni evento e un utente può comparire molte volte?» La risposta è quasi sempre sì, ed è proprio per questo che serve un'aggregazione per ridurre i dati alla prima azione di ogni utente.

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

Prima azione = MIN del timestamp

Il passaggio fondamentale è semplice: raggruppare per user_id e calcolare MIN(event_at). Questo valore minimo rappresenta la prima azione dell'utente, l'istante che lo inserisce in una coorte.

Questa è la risposta che gli intervistatori vogliono sentire per prima, prima di qualsiasi elaborazione con funzioni finestra. Un semplice GROUP BY è corretto, leggibile e veloce.

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

Filtrare per un evento definitorio

Spesso la coorte viene definita da un'azione specifica, non da un evento qualsiasi. «Raggruppare gli utenti in base al loro primo acquisto» significa filtrare le righe relative agli acquisti prima di calcolare il minimo.

Inserisca il filtro in WHERE, così MIN considera solo le righe pertinenti. Un errore tipico nei colloqui consiste nel calcolare MIN su tutti gli eventi e filtrare in seguito: in questo modo si assegnerebbe la data di inizio sbagliata a chiunque abbia navigato prima di acquistare.

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

Raggruppare in un periodo di coorte

Una coorte è di solito un periodo, non un timestamp preciso: la «coorte 2024-03» o la «settimana del 2024-03-04». Tronchi la data della prima azione alla granularità del periodo.

In Postgres utilizzi DATE_TRUNC('month', ...). In MySQL potrebbe usare DATE_FORMAT(d, '%Y-%m-01'); in SQL Server, DATETRUNC(month, d) oppure il primo giorno del mese calcolato. Dichiari il dialetto durante il colloquio, così la scelta della sintassi apparirà intenzionale.

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

Racchiudere in una CTE

L'assegnazione della coorte per utente è un elemento costitutivo che verrà riutilizzato nelle query di retention, quindi la includa in una CTE con un nome chiaro. In questo modo i passaggi successivi restano leggibili e dimostra di ragionare in termini di componenti componibili.

Da qui in poi, ogni query successiva può eseguire un join con user_cohort per sapere a quale gruppo appartiene un utente.

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;

Dimensione della coorte: conteggio dei membri

Il primo controllo di coerenza che un intervistatore si aspetta è la dimensione della coorte: quanti utenti appartengono a ciascuna coorte. Raggruppi la CTE di assegnazione per cohort_month e conti gli utenti distinti.

Utilizzi COUNT(DISTINCT user_id) in modo prudenziale, anche se la CTE contiene già una riga per utente; questo segnala che sta tenendo presente la granularità. Questo conteggio diventerà il denominatore di ogni percentuale di retention successiva.

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;

Alternativa con funzioni finestra

A volte gli intervistatori chiedono l'etichetta della coorte associata a ogni riga degli eventi, non una tabella aggregata. In questo caso una funzione finestra è ideale: MIN(event_at) OVER (PARTITION BY user_id) calcola la prima azione senza rimuovere le righe.

È utile quando servono sia gli eventi dettagliati sia l'etichetta della coorte in un'unica passata, la configurazione di partenza per il conteggio della retention.

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

Il problema di ex aequo e duplicati

Che cosa accade se un utente ha due eventi con lo stesso identico timestamp iniziale? MIN gestisce il caso in modo pulito: restituisce quell'unico valore minimo indipendentemente dal numero di righe a esso associate, quindi l'assegnazione della coorte resta una per utente.

Al contrario, con un approccio basato su ROW_NUMBER() ... ORDER BY event_at, gli ex aequo vengono risolti arbitrariamente e occorre aggiungere un criterio di spareggio deterministico, come event_id, per ottenere un risultato stabile. Menzionare spontaneamente questo compromesso dimostra esperienza.

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

Fusi orari e confine del giorno

Una sottile verifica da colloquio: un acquisto effettuato alle 23:30 a New York corrisponde al giorno successivo in UTC. Se le coorti vengono raggruppate per giorno del calendario, è il fuso orario a determinare la coorte in cui finirà l'utente.

La risposta sicura è: memorizzare i timestamp in UTC e poi convertirli nel fuso orario dell'attività prima di troncarli. Specifichi esplicitamente quale fuso definisce il «giorno» per la metrica, perché questa singola decisione può spostare migliaia di utenti da una coorte all'altra.

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;

Escludere gli utenti precedenti all'intervallo

Le analisi reali limitano la coorte a un intervallo di date, per esempio «coorti iniziate nel primo trimestre». Applichi il filtro alla data aggregata della prima azione, cioè tramite una clausola HAVING o un filtro esterno sulla CTE, non tramite un WHERE sugli eventi grezzi.

Filtrare gli eventi grezzi per data consentirebbe erroneamente a un utente che ha effettuato il primo acquisto a dicembre, ma ha compiuto anche un'azione nel primo trimestre, di entrare in una coorte del primo trimestre. Applichi sempre il filtro alla prima azione calcolata.

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

Verifica rapida

Un intervistatore chiede: «Raggruppi ogni utente in base al suo mese del primo acquisto. Gli utenti potrebbero aver navigato prima di acquistare». Quale approccio è corretto?

Riepilogo: definire una coorte

Punti chiave per la domanda da colloquio sulla definizione di una coorte:

  • Una coorte raggruppa gli utenti in base alla loro prima azione pertinente.
  • La si calcola con MIN(event_at) dopo aver filtrato l'evento definitorio in WHERE.
  • La si raggruppa in un periodo con DATE_TRUNC o l'equivalente del dialetto.
  • Si racchiude l'assegnazione in una CTE per poterla riutilizzare; COUNT(DISTINCT user_id) restituisce la dimensione della coorte.
  • Presti attenzione al confine del giorno determinato dal fuso orario e applichi i limiti dell'intervallo di date alla prima azione calcolata, mai agli eventi grezzi.

Se questo passaggio è corretto, nella lezione successiva la matrice di retention diventa un semplice join.

Domande Frequenti

La lezione «Definire una coorte in base alla prima azione» è gratuita?

Sì — il testo completo di «Definire una coorte in base alla prima azione» è 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 SQL Interview Prep, passa a CoddyKit PRO. Il corso SQL Interview Prep include 4 lezioni in totale.

Cosa imparerò in «Definire una coorte in base alla prima azione»?

Assegnazione di ogni utente a una coorte in base alla data del suo primo evento. Eserciti SQL 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 SQL Interview Prep?

Non è richiesta alcuna esperienza precedente. SQL 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 1 di 4.

Quanto tempo richiede la lezione «Definire una coorte in base alla prima azione»?

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 SQL Interview Prep?

Sì. Ogni lezione SQL 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. Definire una coorte in base alla prima azione
  2. Creare una matrice di retention
  3. Retention al giorno N e retention progressiva
  4. Query su abbandono e ritorno degli utenti
← Torna a SQL Interview Prep