Forberedelse til kodeinterviews · Lektion

Opbygning af en fastholdelsesmatrix

Optælling af aktive brugere efter kohorte og periodeforskydning for at danne en fastholdelsestabel.

Lektion 2 af 413 trin

Opbygning af en fastholdelsesmatrix er en gratis Forberedelse til kodeinterviews-lektion på CoddyKit. Dette er lektion 2 af 4. Du kan læse hele lektionen gratis nedenfor — og derefter øve dig praktisk i browseren med en indbygget kodeeditor og en AI-vejleder, der er tilgængelig døgnet rundt. Den er en del af læringsforløbet i Forberedelse til kodeinterviews, og dine fremskridt synkroniseres på tværs af nettet og CoddyKit-appen. Forberedelse til kodeinterviews-kurset indeholder 4 lektioner i alt.

Hvad en fastholdelsesmatrix er

Opfølgningen på at definere en kohorte er den berømte fastholdelsesmatrix: rækkerne er kohorter, kolonnerne er periodeforskydninger (måned 0, 1, 2, ...), og hver celle tæller, hvor mange fra kohorten der stadig var aktive efter den pågældende forskydning.

Interviewere elsker denne opgave, fordi den tvinger dig til at kombinere kohortetildeling, en sammenføjning tilbage til aktivitet, en beregning af periodeforskellen og en pivottabel. Det er den mest repræsentative forespørgsel inden for produktanalyse.

De to input

Du skal bruge to ting: hver brugers kohorteperiode fra den forrige lektion og en registrering af hver aktiv periode pr. bruger. Aktiviteten kommer fra den samme hændelsestabel, aggregeret til periodens granularitet.

Planlæg derfor forespørgslen sådan: først en kohorte-CTE, derefter en aktivitets-CTE, der viser hvilke måneder hver bruger var aktiv i, og til sidst en sammenføjning af dem.

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;

Vis aktive perioder

Aktivitets-CTE'en besvarer spørgsmålet "i hvilke måneder var hver bruger aktiv?" Afkort hver hændelse til måneden, og fjern dubletter med DISTINCT eller GROUP BY, så en bruger, der var aktiv 40 gange i marts, giver én række for marts.

Denne liste pr. bruger og pr. måned er det, du forbinder med kohorten for at måle overlevelsen på tværs af periodeforskydninger.

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

Beregn periodeforskydningen

Matrixens kerne er periodenummeret: Hvor mange måneder efter kohortens start fandt en given aktivitet sted? Træk kohortemåneden fra den aktive måned.

I Postgres er en enkel metode at tælle hele måneder mellem de to datoer. En portabel formel ganger forskellen i år med 12 og lægger forskellen i måneder til; mange databasemotorer har også hjælpefunktioner. Forskydning 0 betyder kohortens egen startmåned.

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

Sammenkædning af kohorte og aktivitet

Sammenkæd kohorte-CTE'en med aktivitets-CTE'en via user_id. Hver resultat-række siger: Denne bruger, der tilhører kohorte X, var aktiv ved forskydning N. At tælle unikke brugere pr. (kohorte, forskydning) giver matricen i langformat.

Fordi hvert kohortemedlem er aktivt i sin egen startmåned, bør forskydning 0 være lig med kohortens størrelse – en indbygget plausibilitetskontrol.

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;

Fastholdelsestabellen i langformat

Tilføj beregningen af forskydningen, og aggregér. Nu har du et overskueligt resultat i langformat: én række pr. kohorte pr. forskydning med et antal fastholdte brugere. Mange interviewere accepterer dette direkte, fordi pivotering kun er kosmetisk.

Bemærk, at udtrykket for forskydningen optræder i både SELECT og GROUP BY, fordi det beregnes og ikke er en gemt kolonne.

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;

Pivotér til brede kolonner

For at få det klassiske gitter skal du pivotere forskydninger til kolonner med betinget aggregering: en SUM af en CASE for hver forskydning. Dette portable mønster virker i alle dialekter uden særlig PIVOT-syntaks.

Hver CASE returnerer 1, når rækkens period_number matcher den pågældende kolonne, så SUM tæller fastholdte brugere for den forskydning.

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;

Fra antal til fastholdelsesrater

Interviewere vil som regel have procenter, ikke rå antal. Divider antallet af fastholdte brugere ved hver forskydning med kohortens størrelse (forskydning 0). Konvertér til et flydende tal, eller gang med 1.0, så du undgår heltalsdivision, som er den mest almindelige skjulte fejl her.

Resultatet er en fastholdelseskurve: 100 % i måned 0, som falder mod et stabilt niveau. Det stabile niveau er det mål, interessenterne faktisk går op i.

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;

Fælden med heltalsdivision

En sikker interviewfælde: I de fleste databasemotorer er 120 / 500 lig med 0, ikke 0.24, fordi begge operander er heltal. Fastholdelsesprocenterne bliver derfor i stilhed til lutter nuller.

Ret det ved at gøre den ene side numerisk: gang med 100.0, CAST en operand til NUMERIC, eller divider med NULLIF(size, 0) for også at beskytte mod en tom kohorte. Hvis du siger »og NULLIF forhindrer division med nul«, får du bonuspoint.

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

Udfyld manglende forskydninger med nul

Hvis en kohorte havde nul fastholdte brugere ved forskydning 2, skaber JOIN'en ingen række, så der opstår et hul i matricen. For at vise et eksplicit 0 skal du generere hele gitteret af kombinationer af (kohorte, forskydning) og lave et LEFT JOIN med tællingerne.

Byg gitteret ved at lave et CROSS JOIN af kohorter med en liste over tal eller forskydninger, og brug derefter coalesce til at sætte manglende antal til nul. Interviewere sætter pris på, at du opdager hullet.

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;

Trekantet form og skævhed mod nyere data

Endnu en pointe, du kan tage op: matricen er trekantet. En kohorte, der startede sidste måned, kan endnu ikke have en værdi for måned 3, så senere forskydninger har færre kohorter, der bidrager.

Et gennemsnit af en kolonne på tværs af kohorter er derfor skævt til fordel for ældre kohorter. Nævn, at du enten ville vise trekanten ærligt eller begrænse sammenligninger til de forskydninger, som alle kohorter har nået. Denne bevidsthed adskiller analytikere fra folk, der kun skriver forespørgsler.

Hurtig kontrol

Din forespørgsel efter fastholdelse dividerer fastholdte brugere med kohortens størrelse, men alle procenter vises som 0 bortset fra måned 0. Hvad er den mest sandsynlige årsag?

Besvar den i den interaktive lektion

Opsummering: Fastholdelsesmatricen

Sådan bygger du en fastholdelsesmatrix i et interview:

Tildel hver bruger en kohorteperiode, og opstil derefter hver brugers aktive perioder uden dubletter.
Sammenkæd dem, og beregn periodeforskydningen (månederne mellem kohorten og aktiviteten).
Aggregér til langformat med COUNT(DISTINCT user_id); pivotér via CASE, hvis der kræves et gitter.
Konvertér antal til rater med omtanke, undgå heltalsdivision, og beskyt mod division med nul ved hjælp af 100.0 og NULLIF.
Lav et LEFT JOIN med et genereret gitter for at udfylde celler med nul, og husk, at matricen er trekantet.

Gratis at komme i gang

Lær Forberedelse til kodeinterviews med en AI-underviser — gratis

Skriv og kør rigtig kode i din browser, få øjeblikkelig hjælp fra en AI-underviser døgnet rundt, og fortsæt, hvor du slap, på web eller i appen.

Kurser: 90
Lektioner: 360

Start gratis Se hele kurset

Ofte stillede spørgsmål

Er lektionen “Opbygning af en fastholdelsesmatrix” gratis?

Ja — hele teksten til “Opbygning af en fastholdelsesmatrix” kan læses gratis her på nettet. Hvis du vil øve dig interaktivt med en indbygget kodeeditor og en AI-vejleder døgnet rundt og få adgang til resten af Forberedelse til kodeinterviews-kurset, skal du opgradere til CoddyKit PRO. Forberedelse til kodeinterviews-kurset indeholder 4 lektioner i alt.

Hvad lærer jeg i “Opbygning af en fastholdelsesmatrix”?

Optælling af aktive brugere efter kohorte og periodeforskydning for at danne en fastholdelsestabel. Du øver dig i Forberedelse til kodeinterviews med praktisk kode, som du kører direkte i browseren, og en AI-vejleder døgnet rundt besvarer dine spørgsmål, mens du arbejder dig gennem lektionen.

Skal jeg have erfaring for at begynde på Forberedelse til kodeinterviews?

Der kræves ingen tidligere erfaring. Forberedelse til kodeinterviews på CoddyKit er tilrettelagt for både begyndere og øvede, så du kan starte her eller fra begyndelsen og lære i dit eget tempo. Dette er lektion 2 af 4.

Hvor lang tid tager lektionen “Opbygning af en fastholdelsesmatrix”?

De fleste CoddyKit-lektioner tager cirka 5–10 minutter. Hver lektion er kort og interaktiv, så du gør løbende fremskridt og kan fortsætte, hvor du slap – på både web og app.

Kan jeg skrive og køre kode i denne Forberedelse til kodeinterviews-lektion?

Ja. Alle Forberedelse til kodeinterviews-lektioner har en indbygget kodeeditor, så du kan skrive og køre rigtig kode direkte i din browser og få øjeblikkelig feedback fra AI – uden lokal opsætning.

Alle lektioner i dette kursus

Definition af en kohorte ud fra den første handling
Opbygning af en fastholdelsesmatrix
Dag-N- og rullende fastholdelse
Forespørgsler om kundefrafald og tilbagevenden

← Tilbage til Forberedelse til kodeinterviews