Een retentiematrix maken
Tel actieve gebruikers per cohort en periodeverschuiving om een retentietabel te vormen.
Een retentiematrix maken is een gratis Voorbereiding op SQL-sollicitatiegesprekken-les op CoddyKit. Dit is les 2 van 4. Je kunt de volledige les hieronder gratis lezen en daarna in de browser praktisch oefenen met een ingebouwde code-editor en een AI-begeleider die 24/7 beschikbaar is. Deze les maakt deel uit van het leertraject Voorbereiding op SQL-sollicitatiegesprekken. Je voortgang wordt gesynchroniseerd op het web en in de CoddyKit-app. De cursus Voorbereiding op SQL-sollicitatiegesprekken bevat in totaal 4 lessen.
Wat een retentiematrix is
Het vervolg op het definiëren van een cohort is de bekende retentiematrix: rijen zijn cohorten, kolommen zijn periodeverschuivingen (maand 0, 1, 2, ...), en elke cel telt hoeveel gebruikers uit dat cohort op die verschuiving nog actief waren.
Interviewers zijn hier dol op, omdat je cohorttoewijzing, een koppeling met activiteit, een berekening van het periodeverschil en een draaitabel moet combineren. Het is de meest kenmerkende query voor productanalyse.
De twee invoergegevens
Je hebt twee dingen nodig: de cohortperiode van elke gebruiker (uit de vorige les) en een registratie van elke actieve periode per gebruiker. Activiteit komt uit dezelfde gebeurtenissentabel, samengevoegd tot het detailniveau van de periode.
Plan de query dus als volgt: een cohort-CTE, daarna een activiteits-CTE die opsomt in welke maanden elke gebruiker actief was, en koppel ze daarna.
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;Actieve perioden opsommen
De activiteits-CTE beantwoordt de vraag "in welke maanden was elke gebruiker actief?" Kap elke gebeurtenis af op de maand en verwijder duplicaten met DISTINCT of GROUP BY, zodat een gebruiker die in maart veertig keer actief was één rij voor maart oplevert.
Deze lijst per gebruiker en per maand koppel je aan het cohort om retentie over de verschillende verschuivingen heen te meten.
WITH activity AS (
SELECT DISTINCT
user_id,
DATE_TRUNC('month', event_at) AS active_month
FROM events
)
SELECT * FROM activity;De periodeverschuiving berekenen
Het hart van de matrix is het periodenummer: hoeveel maanden na de start van hun cohort viel een bepaalde activiteit? Trek de cohortmaand af van de actieve maand.
In Postgres kun je netjes het aantal volledige maanden tussen beide datums tellen. Een overdraagbare formule vermenigvuldigt het jaarverschil met 12 en telt het maandverschil erbij op; veel databasesystemen bieden hier ook hulpfuncties voor. Verschuiving 0 betekent de eigen startmaand van het cohort.
-- 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;Cohort met activiteit verbinden
Koppel de cohort-CTE aan de activiteits-CTE op user_id. Elke uitvoerrij betekent: deze gebruiker, geboren in cohort X, was actief op offset N. Het aantal unieke gebruikers per (cohort, offset) vormt de matrix in lange vorm.
Omdat elk cohortlid actief is in zijn eigen startmaand, moet offset 0 gelijk zijn aan de cohortgrootte: een ingebouwde plausibiliteitscontrole.
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;De retentietabel in lange vorm
Voeg de berekening van de offset en de aggregatie toe. Je hebt nu een overzichtelijk resultaat in lange vorm: één rij per cohort per offset met het aantal behouden gebruikers. Veel interviewers accepteren dit direct, omdat het omzetten naar een matrix alleen cosmetisch is.
Let erop dat de offset-expressie zowel in SELECT als in GROUP BY staat, omdat die wordt berekend en geen opgeslagen kolom is.
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;Offsets omzetten naar brede kolommen
Om de klassieke matrix te krijgen, zet je offsets om in kolommen met voorwaardelijke aggregatie: een SUM van een CASE voor elke offset. Dit algemeen toepasbare patroon werkt in elk dialect zonder speciale PIVOT-syntaxis.
Elke CASE levert 1 op wanneer period_number van de rij overeenkomt met die kolom, zodat de SUM het aantal behouden gebruikers voor die offset telt.
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;Van aantallen naar retentiepercentages
Interviewers willen meestal percentages, geen ruwe aantallen. Deel het aantal behouden gebruikers op elke offset door de cohortgrootte (offset 0). Zet een waarde om naar een kommagetal of vermenigvuldig met 1.0 om deling met gehele getallen te voorkomen, de meest voorkomende stille fout hier.
Het resultaat is een retentiecurve: 100% in maand 0, die afneemt richting een plateau. Dat plateau is de metriek waar belanghebbenden werkelijk om geven.
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;De valkuil van gehele deling
Een gegarandeerde instinker in een interview: in de meeste databasesystemen is 120 / 500 gelijk aan 0, niet aan 0.24, omdat beide operanden gehele getallen zijn. Retentiepercentages komen daardoor ongemerkt allemaal uit op nul.
Los dit op door één kant numeriek te maken: vermenigvuldig met 100.0, CAST één operand naar NUMERIC of deel door NULLIF(size, 0) om ook een leeg cohort af te vangen. Als je zegt dat NULLIF delen door nul voorkomt, levert dat bonuspunten op.
SELECT
retained_users,
cohort_size,
100.0 * retained_users / NULLIF(cohort_size, 0) AS pct
FROM retention_long;Ontbrekende offsets met nul invullen
Als een cohort op offset 2 geen behouden gebruikers had, levert de JOIN geen rij op en ontstaat er een gat in de matrix. Genereer de volledige matrix van (cohort, offset)-combinaties en koppel de aantallen daaraan met een LEFT JOIN om een expliciete 0 te tonen.
Bouw de matrix door cohorten met een lijst met getallen/offsets te CROSS JOINen en coalesce de ontbrekende aantallen daarna naar nul. Interviewers waarderen het als je dit gat hebt opgemerkt.
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;Driehoekige vorm en recentheidsbias
Nog een punt om te noemen: de matrix is driehoekig. Een cohort dat vorige maand is gestart kan nog geen waarde voor maand 3 hebben, dus latere offsets hebben minder cohorten die bijdragen.
Het gemiddelde van een kolom over cohorten vergelijken is daarom bevooroordeeld ten gunste van oudere cohorten. Zeg dat je de driehoek eerlijk zou tonen of vergelijkingen zou beperken tot offsets die elk cohort heeft bereikt. Dit bewustzijn onderscheidt analisten van mensen die alleen queries schrijven.
Snelle controle
Je retentiequery deelt het aantal behouden gebruikers door de cohortgrootte, maar elk percentage wordt behalve maand 0 als 0 weergegeven. Wat is de waarschijnlijkste oorzaak?
Samenvatting: de retentiematrix
Zo bouw je een retentiematrix in een interview:
- Wijs elke gebruiker een cohortperiode toe en maak daarna een ontdubbelde lijst van de actieve perioden van elke gebruiker.
- Koppel ze en bereken de periode-offset (het aantal maanden tussen cohort en activiteit).
- Agregeer in lange vorm met
COUNT(DISTINCT user_id); zet de resultaten metCASEom naar een matrix als dat nodig is. - Converteer aantallen zorgvuldig naar percentages en voorkom deling met gehele getallen en delen door nul met
100.0enNULLIF. - LEFT JOIN een gegenereerde matrix om nulcellen in te vullen en onthoud dat de matrix driehoekig is.
Leer SQL met een AI-tutor — gratis
Schrijf echte code en voer die uit in je browser, krijg direct hulp van een AI-tutor die 24/7 beschikbaar is en ga verder waar je gebleven bent op het web of in de app.
- Cursussen
- 30
- Lessen
- 120
Veelgestelde vragen
Is de les “Een retentiematrix maken” gratis?
Ja — de volledige tekst van “Een retentiematrix maken” kun je hier gratis op het web lezen. Als je interactief wilt oefenen met een ingebouwde code-editor en een AI-begeleider die 24/7 beschikbaar is, en de rest van de cursus Voorbereiding op SQL-sollicitatiegesprekken wilt ontgrendelen, kun je upgraden naar CoddyKit PRO. De cursus Voorbereiding op SQL-sollicitatiegesprekken bevat in totaal 4 lessen.
Wat leer ik in “Een retentiematrix maken”?
Tel actieve gebruikers per cohort en periodeverschuiving om een retentietabel te vormen. Je oefent met Voorbereiding op SQL-sollicitatiegesprekken door code rechtstreeks in de browser uit te voeren. Een AI-begeleider die 24/7 beschikbaar is beantwoordt je vragen terwijl je de les doorwerkt.
Heb ik ervaring nodig om met Voorbereiding op SQL-sollicitatiegesprekken te beginnen?
Ervaring vooraf is niet nodig. Voorbereiding op SQL-sollicitatiegesprekken op CoddyKit is opgebouwd voor beginners tot gevorderden, zodat je hier of bij het begin kunt starten en in je eigen tempo kunt leren. Dit is les 2 van 4.
Hoe lang duurt de les “Een retentiematrix maken”?
De meeste lessen van CoddyKit duren ongeveer 5–10 minuten. Elke les is kort en interactief, zodat je gestaag vooruitgaat en op het web en in de app precies verdergaat waar je was gebleven.
Kan ik code schrijven en uitvoeren in deze les over Voorbereiding op SQL-sollicitatiegesprekken?
Ja. Elke les over Voorbereiding op SQL-sollicitatiegesprekken bevat een ingebouwde code-editor, zodat je rechtstreeks in je browser echte code kunt schrijven en uitvoeren en direct feedback van AI krijgt — lokale installatie is niet nodig.
Alle lessen in deze cursus
- Een cohort definiëren op basis van de eerste actie
- Een retentiematrix maken
- Retentie op dag N en voortschrijdende retentie
- Query's voor churn en terugkerende gebruikers