Retention al giorno N e retention progressiva
La differenza tra le definizioni classica, progressiva e con intervallo limitato della retention.
Retention al giorno N e retention progressiva è una lezione SQL Interview Prep gratuita su CoddyKit. Questa è la lezione 3 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é la retention ha più definizioni
Un intervistatore raramente si limiterà a dire «calcoli la retention». La domanda successiva più importante è: quale retention? Gli stessi dati producono numeri molto diversi a seconda della definizione.
Le tre definizioni che deve conoscere sono la retention Day-N (classica), la retention rolling (illimitata) e la retention su finestra delimitata. Capire la differenza e chiedere quale definizione desideri il business è già una parte della competenza che viene valutata.
Retention Day-N (classica)
La retention Day-N chiede: l'utente è stato attivo esattamente nel giorno N dopo la sua prima azione? Day-1, Day-7 e Day-30 sono le metriche canoniche per le app mobile.
La parola chiave è esattamente. Un utente attivo il giorno 6 e il giorno 8, ma non il giorno 7, non è retained al Day-7 secondo la definizione classica. Questa precisione rende il conteggio rigoroso e la curva irregolare.
Calcolare la differenza in giorni
La retention Day-N dipende dal numero di giorni tra l'inizio della coorte e ogni giorno di attività. In Postgres, sottraendo due date si ottiene direttamente un numero intero di giorni.
Negli altri dialetti: DATEDIFF(day, start, d) in SQL Server, DATEDIFF(d, start) in MySQL. Indichi il dialetto: il concetto, cioè un offset in giorni, è identico.
WITH cohort AS (
SELECT user_id, MIN(event_at::date) AS day0
FROM events GROUP BY user_id
),
act AS (
SELECT DISTINCT user_id, event_at::date AS day
FROM events
)
SELECT c.user_id, (a.day - c.day0) AS day_n
FROM cohort c
JOIN act a ON a.user_id = c.user_id;Una query per la retention Day-7
Per ottenere il tasso di retention Day-7, conti gli utenti distinti il cui offset in giorni è uguale a 7 e divida per la dimensione della coorte. Utilizzi l'aggregazione condizionale, così numeratore e denominatore derivano da un'unica scansione.
L'uguaglianza = 7 (non >= 7) è il tratto distintivo della retention classica. Sostituirla con una disuguaglianza cambia silenziosamente la definizione.
WITH dn AS (
SELECT c.user_id, (a.day - c.day0) AS day_n
FROM cohort c JOIN act a ON a.user_id = c.user_id
)
SELECT
COUNT(DISTINCT CASE WHEN day_n = 7 THEN user_id END) AS d7_retained,
COUNT(DISTINCT user_id) AS cohort_size,
ROUND(100.0 * COUNT(DISTINCT CASE WHEN day_n = 7 THEN user_id END)
/ NULLIF(COUNT(DISTINCT user_id), 0), 1) AS d7_pct
FROM dn;Retention rolling (illimitata)
La retention rolling al giorno N pone una domanda più permissiva: l'utente è stato attivo nel giorno N o in un giorno successivo? Al giorno 7 considera retained un utente che è tornato il giorno 7, il giorno 20 o in qualsiasi momento successivo.
Questa definizione produce una curva più uniforme e più alta ed è spesso preferita per misurare la fidelizzazione a lungo termine. Il cambiamento caratteristico consiste nel passare da = N a >= N sull'ultimo giorno di attività.
Retention rolling con il giorno MAX
Il modo più chiaro per calcolare la retention rolling è trovare l'ultimo offset del giorno in cui ogni utente è stato attivo (MAX); un utente è quindi retained secondo la definizione rolling al giorno N se quel valore massimo è >= N.
Dopo il MAX rimane una riga per utente, quindi il conteggio è semplice. Inoltre, diventa evidente che la retention rolling è monotona: se un utente è retained al giorno 30, lo è anche per ogni N più piccolo.
WITH last_day AS (
SELECT c.user_id, MAX(a.day - c.day0) AS max_day_n
FROM cohort c JOIN act a ON a.user_id = c.user_id
GROUP BY c.user_id
)
SELECT
COUNT(*) AS cohort_size,
COUNT(*) FILTER (WHERE max_day_n >= 7) AS rolling_d7,
ROUND(100.0 * COUNT(*) FILTER (WHERE max_day_n >= 7)
/ NULLIF(COUNT(*), 0), 1) AS rolling_d7_pct
FROM last_day;Retention su finestra delimitata
La via di mezzo è la retention su finestra delimitata: l'utente è stato attivo almeno una volta all'interno di una finestra intorno al giorno N, per esempio dal giorno 5 al giorno 9 per una metrica della «settimana 1». Questa definizione considera gli utenti che non sono attivi nel giorno esatto, ma è meno uniforme della retention completamente rolling.
È la definizione più realistica per il business, perché l'utilizzo reale è discontinuo. La query usa un BETWEEN sull'offset in giorni.
SELECT
COUNT(DISTINCT CASE WHEN day_n BETWEEN 5 AND 9
THEN user_id END) AS week1_retained,
COUNT(DISTINCT user_id) AS cohort_size
FROM dn;Tre definizioni, lo stesso utente
Renda concreta la differenza. Un utente inizia al giorno 0, poi è attivo soltanto al giorno 9.
- Day-7 classico: NON retained (non ha avuto attività esattamente il giorno 7).
- Day-7 rolling: retained (il giorno massimo è 9 >= 7).
- Finestra delimitata 5–9, settimana 1: retained (il giorno 9 rientra nella finestra).
Stessi dati, tre risposte. Durante un colloquio, racconti un esempio come questo per dimostrare di comprendere la semantica, non soltanto la sintassi.
Granularità del periodo: giorno, settimana o mese
«Day-N» si generalizza in period-N. Per un prodotto B2B con utilizzo mensile, la retention a livello giornaliero è rumore; raggrupperebbe per mese e porrebbe domande sul mese N. La meccanica è identica: cambia soltanto la granularità del troncamento.
Scelga la granularità in base alla frequenza d'uso naturale del prodotto e lo dichiari. Giornaliera per le app mobile consumer, settimanale o mensile per gli strumenti B2B a utilizzo più lento.
-- weekly grain: offset in whole weeks
SELECT
c.user_id,
FLOOR((a.day - c.day0) / 7) AS week_n
FROM cohort c
JOIN act a ON a.user_id = c.user_id;La trappola della sopravvivenza e della maturità
Una sottile verifica per profili senior: non riporti la retention Day-30 per una coorte che ha soltanto 10 giorni. Questi utenti non hanno ancora avuto la possibilità di essere attivi al giorno 30, quindi il loro valore è artificialmente 0 e non indica una retention realmente bassa.
Per evitarlo, includa nel calcolo Day-N solo le coorti la cui età è >= N. Filtri usando CURRENT_DATE - day0 >= N. Dimenticarsene fa apparire le coorti recenti disastrosamente negative.
WITH cohort AS (
SELECT user_id, MIN(event_at::date) AS day0
FROM events GROUP BY user_id
)
SELECT *
FROM cohort
WHERE (CURRENT_DATE - day0) >= 30; -- mature enough for Day-30Scegliere la definizione ad alta voce
La risposta migliore durante un colloquio non è una query, ma una domanda da rivolgere a sua volta: «Desidera la retention Day-N classica, quella rolling o una finestra delimitata? E qual è il periodo naturale?»
Esponga poi il compromesso: la definizione classica è rigorosa ed è adatta ai punti di attivazione del prodotto in un giorno preciso; la rolling sovrastima il breve termine, ma misura la fidelizzazione nell'intero ciclo di vita; la finestra delimitata è il compromesso realistico. Dimostrare di scegliere consapevolmente la metrica è il vero obiettivo della lezione.
Verifica rapida
La prima azione di un utente avviene al giorno 0; la sua unica attività successiva è al giorno 12. Secondo ciascuna definizione, è retained al Day-7?
Riepilogo: le definizioni di retention
Considerazioni principali sulla retention Day-N e rolling:
- Day-N classica: attività esattamente nel giorno N (offset
= N) — curva rigorosa e irregolare. - Rolling: attività nel giorno N o successivamente (
MAX offset >= N) — più uniforme, monotona e indicativa della fidelizzazione. - Su finestra delimitata: attività all'interno di una finestra (
BETWEEN) — il compromesso realistico. - Generalizzi il giorno a qualsiasi granularità del periodo coerente con la frequenza d'uso del prodotto.
- Riporti la retention Day-N soltanto per le coorti mature (età >= N), per evitare la trappola della sopravvivenza, e chieda sempre quale definizione intenda il business.
Domande Frequenti
La lezione «Retention al giorno N e retention progressiva» è gratuita?
Sì — il testo completo di «Retention al giorno N e retention progressiva» è 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 «Retention al giorno N e retention progressiva»?
La differenza tra le definizioni classica, progressiva e con intervallo limitato della retention. 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 3 di 4.
Quanto tempo richiede la lezione «Retention al giorno N e retention progressiva»?
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
- Definire una coorte in base alla prima azione
- Creare una matrice di retention
- Retention al giorno N e retention progressiva
- Query su abbandono e ritorno degli utenti