0Pricing
SQL Interview Prep · Lezione

Query su abbandono e ritorno degli utenti

Identificazione degli utenti che hanno abbandonato il servizio e di quelli tornati dopo un intervallo.

Query su abbandono e ritorno degli utenti è una lezione SQL Interview Prep gratuita su CoddyKit. Questa è la lezione 4 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.

L'altro lato della retention

Se la retention misura chi è rimasto, il churn misura chi ha abbandonato e la resurrection misura chi è tornato. Gli intervistatori li affiancano alla retention perché mostrano se sa ragionare sull'assenza di attività, che è più difficile del semplice conteggio delle presenze.

Il punto ricorrente è questo: non può filtrare righe che non esistono. Le query sul churn riguardano fondamentalmente l'individuazione dell'intervallo tra l'ultima attività di un utente e il momento attuale (o la sua attività successiva).

Definire il churn con precisione

«Churned» non significa nulla senza una finestra temporale. Una definizione comune è: un utente è churned se non ha avuto alcuna attività negli ultimi 30 giorni. La soglia di inattività di 30 giorni è una scelta di business che deve essere definita con precisione.

Per i prodotti in abbonamento, il churn può invece indicare un abbonamento annullato o scaduto, cioè un cambio di stato anziché un intervallo senza attività. Chiarisca quale modello si applica prima di scrivere SQL.

L'ultima attività per utente

La base del churn calcolato sull'intervallo di inattività è l'evento più recente di ogni utente. Raggruppi per utente e calcoli il MAX della data dell'evento.

Confrontato con la data odierna, questo singolo valore indica da quanto tempo l'utente è inattivo. Tutto ciò che segue consiste nel confrontare i dati con questa data dell'ultima attività.

SELECT
  user_id,
  MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id;

La query degli utenti in churn

Un utente è in churn se la sua ultima attività risale a più di 30 giorni fa. Confronti last_active con CURRENT_DATE - 30. Chiunque abbia un evento più recente precedente a questa soglia è diventato inattivo.

Noti che l'elaborazione avviene dopo l'aggregazione: riduce i dati a una riga per utente e poi verifica l'intervallo. Filtrare gli eventi grezzi per data direbbe soltanto chi è stato inattivo in una finestra, non chi è complessivamente in churn.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events
  GROUP BY user_id
)
SELECT user_id, last_active
FROM last_seen
WHERE last_active < CURRENT_DATE - INTERVAL '30 days';

Calcolare il tasso di churn

Il tasso di churn è dato dagli utenti in churn divisi per la base di riferimento, spesso gli utenti attivi all'inizio del periodo. Utilizzi l'aggregazione condizionale per contare in un'unica scansione gli utenti in churn e il totale, quindi divida con attenzione usando 100.0 e NULLIF.

Durante il colloquio espliciti il denominatore: il churn calcolato su tutti gli utenti storici e quello calcolato sugli utenti precedentemente attivi sono metriche diverse.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days'
  ) AS churned,
  COUNT(*) AS total_users,
  ROUND(100.0 * COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days')
    / NULLIF(COUNT(*), 0), 1) AS churn_pct
FROM last_seen;

Churn tra periodi con la logica degli insiemi

Un'altra formulazione è: chi era attivo il mese scorso ma non questo mese? Si tratta di una differenza tra insiemi. Costruisca l'insieme degli utenti attivi il mese scorso e quello degli utenti attivi questo mese, quindi individui i membri del primo insieme che non appartengono al secondo.

Può esprimerla con EXCEPT, con un anti-join LEFT JOIN / IS NULL oppure con NOT EXISTS. L'anti-join è l'approccio più portabile e quello che gli intervistatori vogliono vedere più spesso.

WITH last_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-04-01' AND event_at < DATE '2024-05-01'
),
this_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-05-01' AND event_at < DATE '2024-06-01'
)
SELECT user_id FROM last_month
EXCEPT
SELECT user_id FROM this_month;

La forma dell'anti-join

La stessa query per il churn di questo periodo usando un anti-join: esegua un LEFT JOIN degli utenti attivi questo mese su quelli attivi il mese scorso, quindi mantenga le righe in cui la corrispondenza è NULL. Sono gli utenti presenti il mese scorso ma assenti questo mese: quelli in churn.

NOT EXISTS è una risposta altrettanto valida e gestisce i NULL in modo sicuro. Precisi che NOT IN sarebbe rischioso se l'insieme interno potesse contenere dei NULL: un classico errore insidioso.

SELECT lm.user_id
FROM last_month lm
LEFT JOIN this_month tm ON tm.user_id = lm.user_id
WHERE tm.user_id IS NULL;

Definizione della riattivazione

La riattivazione (detta anche reactivation) riguarda un utente che era in churn e poi è tornato attivo. Il segnale distintivo è un intervallo nella sua cronologia: attivo, poi un periodo di inattività più lungo della soglia di churn, quindi di nuovo attivo.

Quindi, un utente riattivato questo mese è attivo ora, era inattivo nel periodo precedente, ma aveva svolto attività in un periodo ancora precedente. È l'immagine speculare del churn.

Rilevare gli intervalli con LAG

Il modo più elegante per individuare la riattivazione è la funzione finestra LAG: per ogni periodo di attività di ciascun utente, esamini il periodo attivo precedente. Se l'intervallo tra i due supera la soglia, il periodo corrente indica una riattivazione.

LAG evita un self-join e rende la query leggibile. Partizioni per utente, ordini per periodo di attività e confronti ogni periodo con il suo predecessore.

WITH monthly AS (
  SELECT DISTINCT user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
),
gaps AS (
  SELECT user_id, active_month,
    LAG(active_month) OVER (
      PARTITION BY user_id ORDER BY active_month
    ) AS prev_month
  FROM monthly
)
SELECT user_id, active_month AS resurrected_month
FROM gaps
WHERE prev_month IS NOT NULL
  AND active_month > prev_month + INTERVAL '1 month';

Nuovi, mantenuti o riattivati

Una query completa per classificare l'attività assegna a ogni utente attivo in questo periodo una delle seguenti categorie: nuovo (nessuna attività precedente), mantenuto (attivo anche nel periodo precedente) oppure riattivato (attività precedente, ma con un intervallo). Il valore prev_month ottenuto da LAG determina tutte e tre le categorie.

  • prev_month IS NULL → nuovo
  • prev_month = active_month - 1 → mantenuto
  • altrimenti (un intervallo) → riattivato

Produrre questa suddivisione è una risposta completa e di grande efficacia.

SELECT user_id, active_month,
  CASE
    WHEN prev_month IS NULL THEN 'new'
    WHEN active_month = prev_month + INTERVAL '1 month' THEN 'retained'
    ELSE 'resurrected'
  END AS user_state
FROM gaps;

La trappola di NOT IN con NULL

Un'ultima insidia. Se scrive il churn come WHERE user_id NOT IN (SELECT user_id FROM this_month) e quella sottoquery restituisce anche un solo NULL, l'intero risultato sarà vuoto, perché NOT IN restituisce UNKNOWN quando confrontato con NULL.

Preferisca NOT EXISTS oppure un anti-join con LEFT JOIN / IS NULL, che gestiscono correttamente i NULL. Segnalare spontaneamente questa differenza è un chiaro segnale di esperienza nelle interviste sulla retention.

-- safe anti-join instead of NOT IN
SELECT lm.user_id
FROM last_month lm
WHERE NOT EXISTS (
  SELECT 1 FROM this_month tm
  WHERE tm.user_id = lm.user_id
);

Verifica rapida

Si vogliono individuare gli utenti attivi il mese scorso ma non questo mese. Un collega ha scritto WHERE user_id NOT IN (SELECT user_id FROM this_month), ma la query restituisce zero righe anche se alcuni utenti sono chiaramente entrati in churn. Qual è la correzione più sicura?

Riepilogo: churn e riattivazione

Elementi essenziali di churn e riattivazione:

  • Definisca il churn in base a una soglia di inattività (ad es., nessuna attività per 30 giorni) oppure a un cambiamento dello stato dell'abbonamento: chiarisca quale dei due criteri sta usando.
  • Calcoli il MAX(last activity) di ogni utente, quindi lo confronti con CURRENT_DATE - threshold.
  • Il churn tra un periodo e l'altro è una differenza tra insiemi: usi EXCEPT, NOT EXISTS oppure un anti-join con LEFT JOIN / IS NULL.
  • La riattivazione è un intervallo nella cronologia; la rilevi con LAG per classificare gli utenti come nuovi, mantenuti o riattivati.
  • Eviti NOT IN quando sono possibili valori NULL: svuota silenziosamente il risultato.

Domande Frequenti

La lezione «Query su abbandono e ritorno degli utenti» è gratuita?

Sì — il testo completo di «Query su abbandono e ritorno degli utenti» è 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 «Query su abbandono e ritorno degli utenti»?

Identificazione degli utenti che hanno abbandonato il servizio e di quelli tornati dopo un intervallo. 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 4 di 4.

Quanto tempo richiede la lezione «Query su abbandono e ritorno degli utenti»?

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