Forberedelse til kodeintervjuer · leksjon

Spørringer for frafall og tilbakekomst

Identifisere brukere som har sluttet, og de som kom tilbake etter et opphold.

Leksjon 4 av 413 trinn

Spørringer for frafall og tilbakekomst er en gratis leksjon i Forberedelse til kodeintervjuer på CoddyKit. Dette er leksjon 4 av 4. Du kan lese hele leksjonen gratis nedenfor – og deretter øve praktisk i nettleseren med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt. Den er en del av læringsløpet i Forberedelse til kodeintervjuer, og fremdriften din synkroniseres mellom nettet og CoddyKit-appen. Kurset i Forberedelse til kodeintervjuer inneholder totalt 4 leksjoner.

Den andre siden av retention

Hvis retention måler hvem som ble, måler churn hvem som forsvant, og resurrection hvem som kom tilbake. Intervjuere setter disse opp mot retention fordi de viser om du kan resonnere om fravær av aktivitet, noe som er vanskeligere enn å telle aktivitet.

Det tilbakevendende poenget er at du ikke kan filtrere på rader som ikke finnes. Churn-spørringer handler i bunn og grunn om å finne gapet mellom brukerens siste aktivitet og nå, eller mellom brukerens siste og neste aktivitet.

Definer churn presist

«Churned» gir ingen mening uten et tidsvindu. En vanlig definisjon er at en bruker er churned hvis vedkommende ikke har hatt aktivitet de siste 30 dagene. Terskelen på 30 dager uten aktivitet er et forretningsvalg du må avklare.

For abonnementsprodukter kan churn i stedet bety et avsluttet eller utløpt abonnement, altså en statusendring snarere enn et aktivitetsgap. Avklar hvilken modell som gjelder, før du skriver SQL.

Siste aktivitet per bruker

Grunnlaget for churn basert på aktivitetsgap er hver brukers siste hendelse. Gruppér per bruker og ta MAX av hendelsesdatoen.

Denne ene verdien, sammenlignet med dagens dato, forteller deg hvor lenge brukeren har vært inaktiv. Alt som skjer videre, er en sammenligning med denne datoen for siste aktivitet.

SELECT
  user_id,
  MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id;

Spørringen for churnede brukere

En bruker er churned hvis den siste aktiviteten fant sted for mer enn 30 dager siden. Sammenlign last_active med CURRENT_DATE - 30. Alle hvis nyeste hendelse er eldre enn denne grensen, har blitt inaktive.

Legg merke til at arbeidet skjer etter aggregeringen: Først reduserer du dataene til én rad per bruker, og deretter tester du gapet. Hvis du filtrerer råhendelser på dato, finner du bare hvem som var inaktive i et tidsvindu, ikke hvem som generelt har churnet.

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

Beregn churn-rate

Churn-rate er antallet brukere som har churnet, delt på det relevante grunnlaget, ofte brukere som var aktive ved periodens start. Bruk betinget aggregering til å telle churnede og totalt antall i én gjennomgang, og divider deretter forsiktig med 100.0 og NULLIF.

Vær tydelig på nevneren i intervjuet: churn blant alle brukere noensinne og churn blant tidligere aktive brukere er ulike måltall.

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;

Churn fra periode til periode med mengdelogikk

En annen innfallsvinkel er: hvem var aktive forrige måned, men ikke denne måneden? Dette er en mengdedifferanse. Bygg mengden av aktive brukere forrige måned og mengden av aktive brukere denne måneden, og finn deretter medlemmene i den første mengden som ikke finnes i den andre.

Du kan uttrykke dette med EXCEPT, en LEFT JOIN / IS NULL anti-join eller NOT EXISTS. Anti-join er den mest portable løsningen og den intervjuere oftest vil se.

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;

Anti-join-varianten

Den samme spørringen for frafall i denne perioden som en anti-join: LEFT JOIN denne månedens aktive brukere på forrige måneds, og behold deretter radene der treffet er NULL. Dette er brukere som var til stede forrige måned, men ikke denne måneden – brukere som har falt fra.

NOT EXISTS er et like godt svar og håndterer NULL-verdier på en trygg måte. Nevn at NOT IN kan være risikabelt hvis det indre resultatsettet kan inneholde NULL-verdier, en klassisk fallgruve.

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;

Definere reaktivering

Reaktivering (også kalt gjenaktivering) er når en bruker som har falt fra, blir aktiv igjen. Kjennetegnet er et hull i tidslinjen: aktiv, deretter en periode uten aktivitet som er lengre enn terskelen for frafall, og så aktiv igjen.

En reaktivert bruker denne måneden er altså en bruker som er aktiv nå, var inaktiv i forrige periode, men hadde aktivitet i en tidligere periode. Det er motsatsen til frafall.

Oppdage hull med LAG

Den elegante måten å finne reaktivering på er LAG-vindusfunksjonen: for hver aktivitetsperiode for hver bruker ser du på den forrige aktive perioden. Hvis avstanden mellom dem overstiger terskelen, er denne perioden en reaktivering.

LAG gjør en self-join overflødig og gir en ryddig spørring. Partisjoner etter bruker, sorter etter den aktive perioden, og sammenlign hver periode med den foregående.

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

Nye vs reaktiverte vs beholdte

En komplett spørring for aktivitetsklassifisering merker hver aktive bruker i denne perioden som én av følgende: ny (ingen tidligere aktivitet), beholdt (også aktiv forrige periode), eller reaktivert (tidligere aktivitet, men et hull). prev_month fra LAG styrer alle tre.

prev_month IS NULL → ny
prev_month = active_month - 1 → beholdt
ellers (et hull) → reaktivert

Å produsere denne oppdelingen er et sterkt og komplett svar.

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;

NULL-fellen i NOT IN

En siste fallgruve. Hvis du skriver frafall som WHERE user_id NOT IN (SELECT user_id FROM this_month) og underspørringen returnerer selv én NULL, blir hele resultatet tomt, fordi NOT IN evalueres som UNKNOWN mot NULL.

Foretrekk NOT EXISTS eller en LEFT JOIN / IS NULL anti-join, som håndterer NULL-verdier korrekt. Å påpeke denne forskjellen uten at du blir bedt om det, er et tydelig signal på senioritet i intervjuer om brukerretensjon.

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

Kort kontroll

Du ønsker brukere som var aktive forrige måned, men ikke denne måneden. En kollega skrev WHERE user_id NOT IN (SELECT user_id FROM this_month), og den returnerer ingen rader selv om noen tydelig har falt fra. Hva er den tryggeste løsningen?

Besvar den i den interaktive leksjonen

Oppsummering: frafall og reaktivering

Det viktigste om frafall og reaktivering:

Definer frafall ut fra en terskel for inaktivitet (for eksempel ingen aktivitet på 30 dager) eller en endring i abonnementsstatus – avklar hvilken.
Beregn hver brukers MAX(last activity), og sammenlign deretter med CURRENT_DATE - threshold.
Frafall fra periode til periode er en mengdedifferanse: bruk EXCEPT, NOT EXISTS eller en LEFT JOIN / IS NULL anti-join.
Reaktivering er et hull i tidslinjen. Oppdag det med LAG for å klassifisere brukere som nye / beholdte / reaktiverte.
Unngå NOT IN når NULL-verdier er mulig – det tømmer resultatet uten å si fra.

Gratis å komme i gang

Lær deg Forberedelse til kodeintervjuer med en AI-veileder – gratis

Skriv og kjør ekte kode i nettleseren, få umiddelbar hjelp fra en AI-veileder som er tilgjengelig døgnet rundt, og fortsett der du slapp – på nettet eller i appen.

Kurs: 90
Leksjoner: 360

Start læringen gratis Se hele kurset

Ofte stilte spørsmål

Er leksjonen «Spørringer for frafall og tilbakekomst» gratis?

Ja – hele teksten i «Spørringer for frafall og tilbakekomst» er gratis å lese her på nettet. For å øve interaktivt med en innebygd kodeeditor og en AI-veileder som er tilgjengelig døgnet rundt, og for å låse opp resten av Forberedelse til kodeintervjuer-kurset, kan du oppgradere til CoddyKit PRO. Kurset i Forberedelse til kodeintervjuer inneholder totalt 4 leksjoner.

Hva lærer jeg i «Spørringer for frafall og tilbakekomst»?

Identifisere brukere som har sluttet, og de som kom tilbake etter et opphold. Du øver på Forberedelse til kodeintervjuer med praktisk kode som du kjører direkte i nettleseren, mens en AI-veileder som er tilgjengelig døgnet rundt, svarer på spørsmålene dine mens du jobber deg gjennom leksjonen.

Trenger jeg erfaring for å begynne med Forberedelse til kodeintervjuer?

Ingen tidligere erfaring er nødvendig. Forberedelse til kodeintervjuer på CoddyKit er lagt opp for både nybegynnere og viderekomne, så De kan begynne her eller helt fra start og lære i Deres eget tempo. Dette er leksjon 4 av 4.

Hvor lang tid tar leksjonen «Spørringer for frafall og tilbakekomst»?

De fleste CoddyKit-leksjoner tar omtrent 5–10 minutter. Hver leksjon er kort og interaktiv, slik at De gjør jevne fremskritt og kan fortsette akkurat der De slapp – både på nettet og i appen.

Kan jeg skrive og kjøre kode i denne Forberedelse til kodeintervjuer-leksjonen?

Ja. Alle Forberedelse til kodeintervjuer-leksjoner har en innebygd kodeeditor, slik at De kan skrive og kjøre ekte kode direkte i nettleseren og få umiddelbar tilbakemelding fra AI – uten lokal konfigurering.

Alle leksjonene i dette kurset

Definere en kohort ut fra første handling
Bygge en retensjonsmatrise
Retensjon på dag N og rullerende retensjon
Spørringer for frafall og tilbakekomst

← Tilbake til Forberedelse til kodeintervjuer