Query's voor churn en terugkerende gebruikers
Identificeer gebruikers die zijn vertrokken en gebruikers die na een onderbreking terugkeerden.
Query's voor churn en terugkerende gebruikers is een gratis Voorbereiding op programmeerinterviews-les op CoddyKit. Dit is les 4 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 programmeerinterviews. Je voortgang wordt gesynchroniseerd op het web en in de CoddyKit-app. De cursus Voorbereiding op programmeerinterviews bevat in totaal 4 lessen.
De keerzijde van retentie
Als retentie meet wie bleef, meet uitval wie vertrok en meet heractivatie wie terugkwam. Interviewers combineren deze metriek met retentie omdat ze laten zien of je kunt redeneren over de afwezigheid van activiteit, wat moeilijker is dan aanwezigheid tellen.
De terugkerende truc: je kunt niet filteren op rijen die niet bestaan. Queries voor uitval gaan in essentie over het vinden van het hiaat tussen de laatste activiteit van een gebruiker en nu, of tussen diens laatste en volgende activiteit.
Afhaken precies definiëren
"Afgehaakt" heeft zonder venster geen betekenis. Een veelgebruikte definitie: een gebruiker is afgehaakt als die de afgelopen 30 dagen geen activiteit heeft gehad. De drempel van 30 dagen inactiviteit is de bedrijfskeuze die je precies moet vastleggen.
Voor abonnementsproducten kan afhaken in plaats daarvan een opgezegd of verlopen abonnement betekenen: een statuswijziging in plaats van een periode zonder activiteit. Verduidelijk welk model van toepassing is voordat je SQL schrijft.
Laatste activiteit per gebruiker
De basis van uitval op basis van een activiteitsgat is de meest recente gebeurtenis van elke gebruiker. Groepeer per gebruiker en neem de MAX van de datum van de gebeurtenis.
Deze ene waarde, vergeleken met vandaag, laat zien hoelang de gebruiker stil is geweest. Alles daarna is een vergelijking met de datum waarop de gebruiker voor het laatst is gezien.
SELECT
user_id,
MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id;De query voor afgehaakte gebruikers
Een gebruiker is afgehaakt als de laatste activiteit meer dan 30 dagen geleden was. Vergelijk last_active met CURRENT_DATE - 30. Iedereen van wie de meest recente gebeurtenis vóór die grensdatum ligt, is stilgevallen.
Het werk gebeurt pas na aggregatie: reduceer tot één rij per gebruiker en test daarna het hiaat. Ruwe gebeurtenissen op datum filteren zou alleen aangeven wie in een venster inactief was, niet wie in het algemeen is afgehaakt.
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';Het uitvalpercentage berekenen
Het uitvalpercentage is het aantal afgehaakte gebruikers gedeeld door de relevante basisgroep, vaak gebruikers die aan het begin van de periode actief waren. Gebruik voorwaardelijke aggregatie om afgehaakte gebruikers en het totaal in één doorgang te tellen en deel vervolgens zorgvuldig met 100.0 en NULLIF.
Wees in het interview expliciet over de noemer: uitval onder alle gebruikers ooit en uitval onder eerder actieve gebruikers zijn verschillende metriek.
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;Uitval van periode tot periode met verzamelingenlogica
Een andere formulering: wie was vorige maand actief, maar deze maand niet? Dit is een verschil van verzamelingen. Stel de verzameling actieve gebruikers van vorige maand en die van deze maand samen en zoek vervolgens de elementen van de eerste verzameling die niet in de tweede zitten.
Je kunt dit uitdrukken met EXCEPT, een LEFT JOIN / IS NULL-antijoin of NOT EXISTS. De antijoin is het meest overdraagbaar en is wat interviewers het vaakst willen zien.
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;De vorm met een anti-join
Dezelfde query voor churn in deze periode, maar dan als anti-join: koppel de actieve gebruikers van deze maand met een LEFT JOIN aan die van vorige maand en houd vervolgens de rijen met een NULL-treffer over. Dit zijn gebruikers die vorige maand wel aanwezig waren, maar deze maand niet: de afgehaakte gebruikers.
NOT EXISTS is een even goed antwoord en gaat veilig om met NULL-waarden. Vermeld dat NOT IN riskant kan zijn als de interne verzameling NULL-waarden kan bevatten: een klassieke valkuil.
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;Heractivatie definiëren
Heractivatie (ook wel reactivatie) betekent dat een gebruiker eerst is afgehaakt en daarna weer actief wordt. Het kenmerkende patroon is een gat in de tijdlijn: eerst actief, daarna een periode zonder activiteit die langer duurt dan de churndrempel, en vervolgens weer actief.
Een gebruiker die deze maand opnieuw actief is, is dus nu actief, was in de vorige periode inactief, maar had in een eerdere periode wel activiteit. Het is het spiegelbeeld van churn.
Gaten detecteren met LAG
De elegante manier om heractivatie te vinden is de LAG-vensterfunctie: bekijk voor elke activiteitsperiode van elke gebruiker de vorige actieve periode. Als het gat ertussen groter is dan de drempel, is deze periode een heractivatie.
Met LAG vermijd je een self-join en blijft de query overzichtelijk. Deel de gegevens op per gebruiker, sorteer op de actieve periode en vergelijk elke periode met de vorige.
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';Nieuw, hergeactiveerd of behouden
Een volledige query voor activiteitsclassificatie labelt elke gebruiker die deze periode actief is als een van de volgende: nieuw (geen eerdere activiteit), behouden (ook in de vorige periode actief) of hergeactiveerd (wel eerdere activiteit, maar met een gat). De prev_month uit LAG bepaalt alle drie de categorieën.
prev_month IS NULL→ nieuwprev_month = active_month - 1→ behouden- anders (een gat) → hergeactiveerd
Met deze uitsplitsing geef je een sterk en volledig antwoord.
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;De NULL-valkuil van NOT IN
Een laatste valkuil. Als je churn schrijft als WHERE user_id NOT IN (SELECT user_id FROM this_month) en die subquery zelfs maar één NULL oplevert, wordt het hele resultaat leeg. Dat komt doordat NOT IN bij een vergelijking met NULL de waarde UNKNOWN oplevert.
Gebruik liever NOT EXISTS of een anti-join met LEFT JOIN / IS NULL; die werken ook bij NULL-waarden correct. Dit verschil uit eigen beweging benoemen is een betrouwbaar teken van senioriteit tijdens sollicitatiegesprekken over retentie.
-- 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
);Korte controle
Je wilt de gebruikers vinden die vorige maand actief waren, maar deze maand niet. Een teamgenoot schreef WHERE user_id NOT IN (SELECT user_id FROM this_month), maar de query levert nul rijen op terwijl duidelijk is dat sommige gebruikers zijn afgehaakt. Wat is de veiligste oplossing?
Samenvatting: churn en heractivatie
De belangrijkste punten over churn en heractivatie:
- Definieer churn aan de hand van een drempel voor inactiviteit (bijvoorbeeld geen activiteit gedurende 30 dagen) of een wijziging van de abonnementsstatus: geef aan welke van de twee je gebruikt.
- Bereken voor elke gebruiker de MAX(last activity) en vergelijk die vervolgens met
CURRENT_DATE - threshold. - Churn van periode tot periode is een verschil tussen verzamelingen: gebruik EXCEPT, NOT EXISTS of een anti-join met LEFT JOIN / IS NULL.
- Heractivatie is een gat in de tijdlijn; detecteer dit met
LAGom gebruikers te classificeren als nieuw, behouden of hergeactiveerd. - Vermijd
NOT INals NULL-waarden mogelijk zijn: de uitkomst wordt dan stilletjes leeg.
Leer Voorbereiding op programmeerinterviews 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
- 90
- Lessen
- 360
Veelgestelde vragen
Is de les “Query's voor churn en terugkerende gebruikers” gratis?
Ja — de volledige tekst van “Query's voor churn en terugkerende gebruikers” 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 programmeerinterviews wilt ontgrendelen, kun je upgraden naar CoddyKit PRO. De cursus Voorbereiding op programmeerinterviews bevat in totaal 4 lessen.
Wat leer ik in “Query's voor churn en terugkerende gebruikers”?
Identificeer gebruikers die zijn vertrokken en gebruikers die na een onderbreking terugkeerden. Je oefent met Voorbereiding op programmeerinterviews 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 programmeerinterviews te beginnen?
Ervaring vooraf is niet nodig. Voorbereiding op programmeerinterviews 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 4 van 4.
Hoe lang duurt de les “Query's voor churn en terugkerende gebruikers”?
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 programmeerinterviews?
Ja. Elke les over Voorbereiding op programmeerinterviews 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