Zapytania dotyczące odpływu i powrotów użytkowników
Identyfikowanie użytkowników, którzy odeszli, oraz tych, którzy wrócili po przerwie.
Zapytania dotyczące odpływu i powrotów użytkowników to bezpłatna lekcja Coding Interview Prep na CoddyKit. To lekcja 4 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej Coding Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs Coding Interview Prep zawiera 4 lekcji w sumie.
Odwrotna strona retencji
Jeśli retencja mierzy, kto pozostał, churn mierzy, kto odszedł, a reaktywacja mierzy, kto wrócił. Rekruterzy zestawiają te pojęcia z retencją, ponieważ pokazują, czy potrafią Państwo rozumować o braku aktywności, co jest trudniejsze niż zliczanie jej wystąpień.
Powtarzająca się pułapka polega na tym, że nie można filtrować wierszy, które nie istnieją. Zapytania dotyczące churnu zasadniczo polegają na znalezieniu przerwy między ostatnią aktywnością użytkownika a dniem dzisiejszym (lub jego następną aktywnością).
Precyzyjne definiowanie churnu
Termin „utracony” nic nie znaczy bez określenia okna. Częsta definicja mówi, że użytkownik jest utracony, jeśli nie wykazywał aktywności przez ostatnie 30 dni. Próg 30 dni bezczynności jest decyzją biznesową, którą należy jednoznacznie ustalić.
W przypadku produktów subskrypcyjnych churn może oznaczać anulowaną lub wygasłą subskrypcję, czyli zmianę statusu, a nie przerwę w aktywności. Przed napisaniem zapytania SQL należy wyjaśnić, który model ma zastosowanie.
Ostatnia aktywność każdego użytkownika
Podstawą churnu opartego na przerwie w aktywności jest najnowsze zdarzenie każdego użytkownika. Pogrupuj dane według użytkownika i wybierz MAX daty zdarzenia.
Ta pojedyncza wartość, porównana z dniem dzisiejszym, informuje, jak długo użytkownik pozostaje nieaktywny. Wszystkie dalsze operacje sprowadzają się do porównania z datą ostatniej aktywności.
SELECT
user_id,
MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id;Zapytanie o utraconych użytkowników
Użytkownik jest utracony, jeśli jego ostatnia aktywność miała miejsce ponad 30 dni temu. Porównaj last_active z CURRENT_DATE - 30. Każdy użytkownik, którego najnowsze zdarzenie przypada przed tym punktem odcięcia, przestał być aktywny.
Zauważ, że właściwa operacja odbywa się po agregacji: najpierw redukujesz dane do jednego wiersza na użytkownika, a dopiero potem sprawdzasz przerwę. Filtrowanie surowych zdarzeń według daty wskazałoby tylko, kto był nieaktywny w danym oknie, a nie kto jest ogólnie utracony.
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';Obliczanie wskaźnika churnu
Wskaźnik churnu to liczba utraconych użytkowników podzielona przez odpowiednią bazę, często przez użytkowników aktywnych na początku okresu. Użyj agregacji warunkowej, aby podczas jednego przebiegu zliczyć użytkowników utraconych i wszystkich użytkowników, a następnie ostrożnie wykonaj dzielenie za pomocą 100.0 i NULLIF.
Podczas rozmowy należy jasno określić mianownik: churn wśród wszystkich użytkowników i churn wśród wcześniej aktywnych użytkowników to różne wskaźniki.
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 okres do okresu z logiką zbiorów
Można też ująć problem inaczej: kto był aktywny w zeszłym miesiącu, ale nie w tym miesiącu? To różnica zbiorów. Utwórz zbiór użytkowników aktywnych w zeszłym miesiącu oraz zbiór użytkowników aktywnych w tym miesiącu, a następnie znajdź elementy pierwszego zbioru, których nie ma w drugim.
Można to wyrazić za pomocą EXCEPT, antyzłączenia LEFT JOIN / IS NULL albo NOT EXISTS. Antyzłączenie jest najbardziej przenośne i właśnie je rekruterzy najczęściej chcą zobaczyć.
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;Forma anti-join
To samo zapytanie o churnie w tym okresie zapisane jako anti-join: dołącz za pomocą LEFT JOIN aktywnych z tego miesiąca do aktywnych z ubiegłego miesiąca, a następnie zachowaj wiersze, w których dopasowanie ma wartość NULL. Są to użytkownicy obecni w ubiegłym miesiącu, ale nieobecni w tym miesiącu — ci, którzy odeszli.
NOT EXISTS jest równie dobrym rozwiązaniem i bezpiecznie obsługuje wartości NULL. Warto wspomnieć, że NOT IN byłoby ryzykowne, gdyby zbiór wewnętrzny mógł zawierać wartości NULL — to klasyczna pułapka.
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;Definiowanie reaktywacji
Reaktywacja (zwana też ponowną aktywacją) dotyczy użytkownika, który utracił aktywność, a następnie znów stał się aktywny. Jej charakterystycznym sygnałem jest luka na osi czasu użytkownika: aktywność, następnie okres ciszy dłuższy niż próg churnu, a potem ponowna aktywność.
Reaktywowany użytkownik w tym miesiącu to osoba, która jest teraz aktywna, w poprzednim okresie była nieaktywna, ale wykazywała aktywność we wcześniejszym okresie. Jest to lustrzane odbicie churnu.
Wykrywanie luk za pomocą LAG
Eleganckim sposobem wykrywania reaktywacji jest funkcja okna LAG: dla każdego okresu aktywności użytkownika sprawdź poprzedni okres, w którym był aktywny. Jeśli przerwa między nimi przekracza próg, bieżący okres oznacza reaktywację.
LAG eliminuje konieczność wykonywania self-join i zapewnia czytelny zapis. Należy podzielić dane według użytkownika, uporządkować je według okresu aktywności i porównać każdy okres z poprzednim.
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';Nowi, reaktywowani i utrzymani użytkownicy
Kompletne zapytanie klasyfikujące aktywność oznacza każdego aktywnego użytkownika w tym okresie jako jednego z trzech typów: nowy (brak wcześniejszej aktywności), utrzymany (aktywny również w poprzednim okresie) lub reaktywowany (wcześniejsza aktywność, ale z przerwą). Wartość prev_month z LAG wyznacza wszystkie trzy klasy.
prev_month IS NULL→ nowyprev_month = active_month - 1→ utrzymany- w przeciwnym razie (wystąpiła luka) → reaktywowany
Przedstawienie takiego podziału jest mocną i kompletną odpowiedzią.
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;Pułapka NULL w NOT IN
Oto ostatnia pułapka. Jeśli zapiszesz churn jako WHERE user_id NOT IN (SELECT user_id FROM this_month), a podzapytanie zwróci choć jedną wartość NULL, cały wynik będzie pusty, ponieważ NOT IN porównane z NULL zwraca UNKNOWN.
Należy użyć NOT EXISTS albo anti-join z LEFT JOIN / IS NULL — oba rozwiązania prawidłowo obsługują wartości NULL. Samodzielne wskazanie tej różnicy jest wiarygodnym sygnałem dużego doświadczenia podczas rozmowy rekrutacyjnej dotyczącej retencji.
-- 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
);Szybkie sprawdzenie
Chcesz znaleźć użytkowników aktywnych w ubiegłym miesiącu, ale nieaktywnych w tym miesiącu. Osoba z zespołu napisała WHERE user_id NOT IN (SELECT user_id FROM this_month), ale zapytanie zwraca zero wierszy, mimo że część użytkowników wyraźnie odeszła. Jaka jest najbezpieczniejsza poprawka?
Podsumowanie: churn i reaktywacja
Najważniejsze informacje o churnie i reaktywacji:
- Churn należy definiować za pomocą progu nieaktywności (np. brak aktywności przez 30 dni) albo zmiany statusu subskrypcji — trzeba jasno określić, które kryterium obowiązuje.
- Należy obliczyć dla każdego użytkownika MAX(last activity), a następnie porównać tę wartość z
CURRENT_DATE - threshold. - Churn liczony okres do okresu jest różnicą zbiorów: należy użyć EXCEPT, NOT EXISTS albo anti-join z LEFT JOIN / IS NULL.
- Reaktywacja oznacza lukę na osi czasu; wykrywa się ją za pomocą
LAG, aby sklasyfikować użytkowników jako nowych, utrzymanych lub reaktywowanych. - Należy unikać
NOT IN, gdy możliwe są wartości NULL — wynik zostanie wtedy po cichu wyzerowany.
Często zadawane pytania
Czy lekcja „Zapytania dotyczące odpływu i powrotów użytkowników” jest bezpłatna?
Tak — pełny tekst „Zapytania dotyczące odpływu i powrotów użytkowników” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu Coding Interview Prep, przejdź na CoddyKit PRO. Kurs Coding Interview Prep zawiera 4 lekcji w sumie.
Co nauczysz się w „Zapytania dotyczące odpływu i powrotów użytkowników”?
Identyfikowanie użytkowników, którzy odeszli, oraz tych, którzy wrócili po przerwie. Ćwiczysz Coding Interview Prep z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.
Czy potrzebuję doświadczenia, aby zacząć Coding Interview Prep?
Nie wymagamy żadnego doświadczenia. Coding Interview Prep w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 4 z 4.
Ile czasu zajmuje lekcja „Zapytania dotyczące odpływu i powrotów użytkowników”?
Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.
Czy mogę pisać i uruchamiać kod w tej lekcji Coding Interview Prep?
Tak. Każda lekcja Coding Interview Prep zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.
Wszystkie lekcje w tym kursie
- Definiowanie kohorty na podstawie pierwszego działania
- Budowanie macierzy retencji
- Retencja dnia N i retencja krocząca
- Zapytania dotyczące odpływu i powrotów użytkowników