0Pricing
SQL Interview Prep · Lezione

Creare una matrice di retention

Conteggio degli utenti attivi per coorte e periodo trascorso, per creare una tabella di retention.

Creare una matrice di retention è una lezione SQL 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 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.

Cos'è una matrice di retention

Il seguito della definizione di una coorte è la famosa matrice di retention: le righe sono le coorti, le colonne sono gli offset dei periodi, mese 0, 1, 2, ... e ogni cella conta quanti utenti di quella coorte erano ancora attivi a quell'offset.

Gli intervistatori la apprezzano perché richiede di combinare l'assegnazione della coorte, un join con i dati dell'attività, un calcolo della differenza tra periodi e un pivot. È la query più rappresentativa della product analytics.

I due input

Sono necessari due elementi: il periodo della coorte di ogni utente, ottenuto nella lezione precedente, e un record di ogni periodo di attività per utente. L'attività proviene dalla stessa tabella degli eventi, ridotta alla granularità del periodo.

Strutturi quindi la query nel modo seguente: una CTE per la coorte, poi una CTE per l'attività che elenca i mesi in cui ogni utente è stato attivo, quindi il join tra le due.

WITH user_cohort AS (
  SELECT user_id,
    DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events
  GROUP BY user_id
)
SELECT * FROM user_cohort;

Elencare i periodi di attività

La CTE dell'attività risponde alla domanda «in quali mesi è stato attivo ogni utente?». Tronchi ogni evento al mese e rimuova i duplicati con DISTINCT o GROUP BY, così un utente attivo 40 volte a marzo produce una sola riga per marzo.

Questo elenco per utente e per mese è ciò con cui eseguire il join alla coorte per misurare la permanenza nei diversi offset.

WITH activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT * FROM activity;

Calcolare l'offset del periodo

Il cuore della matrice è il numero del periodo: quanti mesi dopo l'inizio della coorte si è verificata una determinata attività? Sottragga il mese della coorte al mese dell'attività.

In Postgres, un modo pulito consiste nel contare i mesi interi tra le due date. Una formula portabile moltiplica la differenza degli anni per 12 e aggiunge la differenza dei mesi; molti motori offrono anche funzioni di supporto. L'offset 0 indica il mese iniziale della coorte.

-- months between two month-truncated dates (Postgres)
SELECT
  (EXTRACT(YEAR  FROM active_month) - EXTRACT(YEAR  FROM cohort_month)) * 12
+ (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month))
  AS period_number;

Unire la coorte all'attività

Unisca la CTE della coorte alla CTE delle attività usando user_id. Ogni riga risultante indica che questo utente, appartenente alla coorte X, è stato attivo all'offset N. Il conteggio degli utenti distinti per (coorte, offset) costituisce la matrice in formato lungo.

Poiché ogni membro della coorte è attivo nel proprio mese di inizio, l'offset 0 dovrebbe corrispondere alla dimensione della coorte: è un controllo di coerenza integrato.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT c.cohort_month, a.active_month, c.user_id
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id;

La tabella di retention in formato lungo

Aggiunga il calcolo dell'offset ed esegua l'aggregazione. Ora dispone di un risultato ordinato in formato lungo: una riga per ogni coorte e ogni offset, con il conteggio degli utenti retained. Molti intervistatori accettano direttamente questo formato, perché il pivot è solo una questione estetica.

Noti che l'espressione dell'offset compare sia in SELECT sia in GROUP BY, perché viene calcolata e non è una colonna memorizzata.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT
  c.cohort_month,
  (EXTRACT(YEAR FROM a.active_month)-EXTRACT(YEAR FROM c.cohort_month))*12
  +(EXTRACT(MONTH FROM a.active_month)-EXTRACT(MONTH FROM c.cohort_month)) AS period_number,
  COUNT(DISTINCT c.user_id) AS retained_users
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, period_number
ORDER BY c.cohort_month, period_number;

Trasformare gli offset in colonne

Per ottenere la griglia classica, trasformi gli offset in colonne con l'aggregazione condizionale: una SUM di una CASE per ogni offset. Questo schema portabile funziona in qualsiasi dialetto senza una sintassi PIVOT specifica.

Ogni CASE restituisce 1 quando il period_number della riga corrisponde a quella colonna, quindi la SUM conta gli utenti retained per quell'offset.

SELECT
  cohort_month,
  COUNT(DISTINCT CASE WHEN period_number = 0 THEN user_id END) AS m0,
  COUNT(DISTINCT CASE WHEN period_number = 1 THEN user_id END) AS m1,
  COUNT(DISTINCT CASE WHEN period_number = 2 THEN user_id END) AS m2,
  COUNT(DISTINCT CASE WHEN period_number = 3 THEN user_id END) AS m3
FROM retention_long
GROUP BY cohort_month
ORDER BY cohort_month;

Dai conteggi ai tassi di retention

Di solito gli intervistatori vogliono le percentuali, non i conteggi grezzi. Divida gli utenti retained di ogni offset per la dimensione della coorte (offset 0). Esegua il cast a float oppure moltiplichi per 1.0 per evitare la divisione intera, il bug silenzioso più comune in questo caso.

Il risultato è una curva di retention: 100% al mese 0, in diminuzione verso un plateau. È proprio questo plateau a interessare gli stakeholder.

SELECT
  cohort_month,
  period_number,
  retained_users,
  ROUND(
    100.0 * retained_users
    / MAX(retained_users) OVER (PARTITION BY cohort_month),
    1
  ) AS retention_pct
FROM retention_long
ORDER BY cohort_month, period_number;

La trappola della divisione intera

Un'insidia quasi certa nei colloqui: nella maggior parte dei motori 120 / 500 equivale a 0, non a 0.24, perché entrambi gli operandi sono interi. Le percentuali di retention risultano quindi silenziosamente tutte uguali a zero.

Risolva il problema rendendo numerico uno dei due lati: moltiplichi per 100.0, esegua il CAST di un operando a NUMERIC oppure divida per NULLIF(size, 0) per gestire anche una coorte vuota. Dire che «NULLIF impedisce la divisione per zero» le farà guadagnare punti extra.

SELECT
  retained_users,
  cohort_size,
  100.0 * retained_users / NULLIF(cohort_size, 0) AS pct
FROM retention_long;

Riempire con zero gli offset mancanti

Se una coorte non ha utenti retained all'offset 2, il JOIN non produce alcuna riga e nella matrice rimane un vuoto. Per visualizzare uno 0 esplicito, generi la griglia completa delle combinazioni (coorte, offset) ed esegua un LEFT JOIN dei conteggi.

Costruisca la griglia con un CROSS JOIN tra le coorti e un elenco di numeri/offset, quindi usi coalesce per sostituire con zero i conteggi mancanti. Gli intervistatori apprezzano quando si nota questa lacuna.

WITH offsets AS (SELECT generate_series(0, 6) AS period_number),
cohorts AS (SELECT DISTINCT cohort_month FROM retention_long)
SELECT
  c.cohort_month, o.period_number,
  COALESCE(r.retained_users, 0) AS retained_users
FROM cohorts c
CROSS JOIN offsets o
LEFT JOIN retention_long r
  ON r.cohort_month = c.cohort_month
 AND r.period_number = o.period_number
ORDER BY c.cohort_month, o.period_number;

Forma triangolare e bias di recenza

Un altro punto da discutere: la matrice è triangolare. Una coorte iniziata il mese scorso non può avere ancora un valore per il mese 3, quindi agli offset più avanzati contribuiscono meno coorti.

Di conseguenza, confrontare la media di una colonna tra le coorti introduce un bias a favore delle coorti più vecchie. Indichi che mostrerebbe il triangolo così com'è oppure limiterebbe i confronti agli offset raggiunti da tutte le coorti. Questa consapevolezza distingue gli analisti da chi sa solo scrivere query.

Verifica rapida

La query di retention divide gli utenti retained per la dimensione della coorte, ma ogni percentuale viene visualizzata come 0, tranne quella del mese 0. Qual è la causa più probabile?

Riepilogo: la matrice di retention

Per costruire una matrice di retention durante un colloquio:

  • Assegni a ogni utente un periodo di coorte, quindi elenchi i suoi periodi di attività senza duplicati.
  • Li unisca e calcoli l'offset del periodo (i mesi tra la coorte e l'attività).
  • Esegua l'aggregazione in formato lungo con COUNT(DISTINCT user_id); usi CASE per il pivot se è necessaria una griglia.
  • Trasformi con attenzione i conteggi in tassi, evitando la divisione intera e la divisione per zero con 100.0 e NULLIF.
  • Esegua un LEFT JOIN con una griglia generata per riempire le celle a zero e ricordi che la matrice è triangolare.

Domande Frequenti

La lezione «Creare una matrice di retention» è gratuita?

Sì — il testo completo di «Creare una matrice di retention» è 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 «Creare una matrice di retention»?

Conteggio degli utenti attivi per coorte e periodo trascorso, per creare una tabella di 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 2 di 4.

Quanto tempo richiede la lezione «Creare una matrice di retention»?

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