SQL Interview Prep · Lekcja

Retencja dnia N i retencja krocząca

Różnica między klasycznymi, kroczącymi i ograniczonymi definicjami retencji.

Retencja dnia N i retencja krocząca to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 3 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 SQL Interview Prep, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Dlaczego retencja ma wiele definicji

Rekruter rzadko powie po prostu: „oblicz retencję”. Trafne pytanie uzupełniające brzmi: którą retencję? Te same dane dają bardzo różne wyniki w zależności od przyjętej definicji.

Trzy definicje, które należy znać, to: retencja Day-N (klasyczna), retencja krocząca (bez ograniczeń) oraz retencja w ograniczonym oknie. Umiejętność rozróżnienia ich i zapytania, której potrzebuje firma, jest właśnie sprawdzaną umiejętnością.

Retencja Day-N (klasyczna)

Retencja Day-N odpowiada na pytanie: czy użytkownik był aktywny dokładnie N dni po swoim pierwszym działaniu? Day-1, Day-7 i Day-30 to standardowe wskaźniki aplikacji mobilnych.

Kluczowe słowo to dokładnie. Użytkownik aktywny w dniach 6 i 8, ale nie w dniu 7, nie jest objęty retencją Day-7 w klasycznym ujęciu. Ta precyzja sprawia, że zliczenie jest restrykcyjne, a krzywa poszarpana.

Obliczanie różnicy dni

Retencja Day-N opiera się na liczbie dni między początkiem kohorty a każdym dniem aktywności. W Postgres odejmowanie od siebie dwóch dat zwraca bezpośrednio całkowitą liczbę dni.

W innych dialektach: DATEDIFF(day, start, d) w SQL Server oraz DATEDIFF(d, start) w MySQL. Należy wskazać dialekt; pojęcie, czyli przesunięcie w dniach, pozostaje identyczne.

WITH cohort AS (
  SELECT user_id, MIN(event_at::date) AS day0
  FROM events GROUP BY user_id
),
act AS (
  SELECT DISTINCT user_id, event_at::date AS day
  FROM events
)
SELECT c.user_id, (a.day - c.day0) AS day_n
FROM cohort c
JOIN act a ON a.user_id = c.user_id;

Zapytanie o retencję Day-7

Aby uzyskać wskaźnik retencji Day-7, zlicz unikalnych użytkowników, których przesunięcie w dniach wynosi 7, i podziel tę liczbę przez rozmiar kohorty. Użyj agregacji warunkowej, aby zarówno licznik, jak i mianownik uzyskać podczas jednego skanowania.

Równość = 7, a nie >= 7, jest charakterystyczna dla retencji klasycznej. Zastąpienie jej nierównością po cichu zmienia definicję.

WITH dn AS (
  SELECT c.user_id, (a.day - c.day0) AS day_n
  FROM cohort c JOIN act a ON a.user_id = c.user_id
)
SELECT
  COUNT(DISTINCT CASE WHEN day_n = 7 THEN user_id END) AS d7_retained,
  COUNT(DISTINCT user_id)                               AS cohort_size,
  ROUND(100.0 * COUNT(DISTINCT CASE WHEN day_n = 7 THEN user_id END)
        / NULLIF(COUNT(DISTINCT user_id), 0), 1)         AS d7_pct
FROM dn;

Retencja krocząca (bez ograniczeń)

Retencja krocząca przy dniu N zadaje mniej restrykcyjne pytanie: czy użytkownik był aktywny w dniu N lub w dowolnym późniejszym dniu? Użytkownik jest uznawany za objętego retencją Day-7, jeśli wrócił w dniu 7, dniu 20 lub kiedykolwiek później.

Daje to gładszą i wyższą krzywą, dlatego często preferuje się tę definicję do pomiaru długoterminowego przywiązania użytkowników. Charakterystyczna zmiana polega na zastąpieniu = N przez >= N dla maksymalnego dnia aktywności.

Retencja krocząca na podstawie ostatniego dnia aktywności

Przejrzysty sposób obliczania retencji kroczącej polega na znalezieniu dla każdego użytkownika ostatniego przesunięcia dnia aktywności (MAX). Użytkownik jest wtedy objęty retencją kroczącą w dniu N, jeśli ta maksymalna wartość jest >= N.

Po użyciu MAX każdy użytkownik odpowiada jednemu wierszowi, więc zliczanie jest proste. Dzięki temu widać również, że retencja krocząca jest monotoniczna: jeśli użytkownik jest objęty retencją w dniu 30, jest nią objęty także dla każdego mniejszego N.

WITH last_day AS (
  SELECT c.user_id, MAX(a.day - c.day0) AS max_day_n
  FROM cohort c JOIN act a ON a.user_id = c.user_id
  GROUP BY c.user_id
)
SELECT
  COUNT(*)                                          AS cohort_size,
  COUNT(*) FILTER (WHERE max_day_n >= 7)            AS rolling_d7,
  ROUND(100.0 * COUNT(*) FILTER (WHERE max_day_n >= 7)
        / NULLIF(COUNT(*), 0), 1)                    AS rolling_d7_pct
FROM last_day;

Retencja w ograniczonym oknie

Rozwiązaniem pośrednim jest retencja ograniczona: użytkownik był aktywny co najmniej raz w oknie wokół dnia N, na przykład w dniach 5–9 dla wskaźnika „tydzień 1”. Toleruje to brak aktywności dokładnie w danym dniu, ale wygładza wyniki w mniejszym stopniu niż retencja w pełni krocząca.

To najbardziej realistyczna biznesowo definicja, ponieważ rzeczywiste korzystanie z produktu jest nieregularne. Zapytanie używa operatora BETWEEN dla przesunięcia w dniach.

SELECT
  COUNT(DISTINCT CASE WHEN day_n BETWEEN 5 AND 9
                      THEN user_id END) AS week1_retained,
  COUNT(DISTINCT user_id)               AS cohort_size
FROM dn;

Trzy definicje, ten sam użytkownik

Prześledźmy konkretny przykład. Użytkownik rozpoczyna aktywność w dniu 0, a następnie jest aktywny wyłącznie w dniu 9.

Klasyczna retencja Day-7: brak retencji (brak aktywności dokładnie w dniu 7).
Retencja krocząca Day-7: użytkownik jest objęty retencją (maksymalne przesunięcie 9 >= 7).
Retencja w ograniczonym oknie 5–9, tydzień 1: użytkownik jest objęty retencją (dzień 9 mieści się w oknie).

Te same dane, trzy odpowiedzi. Podczas rozmowy rekrutacyjnej warto omówić jeden przykład w ten sposób, aby pokazać zrozumienie semantyki, a nie tylko składni.

Ziarnistość okresu: dzień, tydzień czy miesiąc

„Day-N” można uogólnić do period-N. W przypadku produktu B2B używanego co miesiąc retencja na poziomie dni byłaby szumem; należałoby grupować dane według miesięcy i analizować miesiąc N. Mechanizm pozostaje taki sam, zmienia się tylko ziarnistość obcinania dat.

Należy dobrać ziarnistość do naturalnego rytmu korzystania z produktu i wyraźnie to zaznaczyć. Dzienna pasuje do konsumenckich aplikacji mobilnych, a tygodniowa lub miesięczna do wolniej używanych narzędzi B2B.

-- weekly grain: offset in whole weeks
SELECT
  c.user_id,
  FLOOR((a.day - c.day0) / 7) AS week_n
FROM cohort c
JOIN act a ON a.user_id = c.user_id;

Pułapka przeżywalności i dojrzałości

To subtelne pytanie dla starszych kandydatów: nie należy raportować retencji Day-30 dla kohorty, która ma dopiero 10 dni. Taka kohorta nie miała jeszcze możliwości być aktywna 30. dnia, więc jej wartość wynosi sztucznie 0, a nie oznacza rzeczywiście niskiej retencji.

Należy uwzględniać tylko kohorty, których wiek jest >= N, gdy raportowana jest retencja Day-N. Użyj filtra CURRENT_DATE - day0 >= N. Pominięcie tego warunku sprawia, że najnowsze kohorty wyglądają katastrofalnie źle.

WITH cohort AS (
  SELECT user_id, MIN(event_at::date) AS day0
  FROM events GROUP BY user_id
)
SELECT *
FROM cohort
WHERE (CURRENT_DATE - day0) >= 30;  -- mature enough for Day-30

Jawny wybór definicji

Najlepsza odpowiedź podczas rozmowy rekrutacyjnej nie jest zapytaniem, lecz pytaniem zwrotnym: „Czy potrzebują Państwo klasycznej retencji Day-N, retencji kroczącej czy ograniczonego okna? I jaki jest naturalny okres?”

Następnie należy wyjaśnić kompromis: klasyczna retencja jest restrykcyjna i dobrze nadaje się do dokładnych interakcji z produktem; krocząca zawyża retencję krótkoterminową, ale mierzy długoterminowe przywiązanie; ograniczona jest realistycznym kompromisem. Chodzi przede wszystkim o pokazanie, że wybór metryki jest świadomy.

Szybkie sprawdzenie

Pierwsze działanie użytkownika przypada na dzień 0, a jedyna kolejna aktywność na dzień 12. Czy według każdej definicji użytkownik jest objęty retencją Day-7?

Podsumowanie: definicje retencji

Najważniejsze informacje o retencji Day-N i retencji kroczącej:

Klasyczna retencja Day-N: aktywność dokładnie w dniu N (przesunięcie = N) — restrykcyjna, z poszarpaną krzywą.
Retencja krocząca: aktywność w dniu N lub później (MAX offset >= N) — gładsza, monotoniczna, mierzy przywiązanie użytkowników.
Retencja ograniczona: aktywność w określonym oknie (BETWEEN) — realistyczny kompromis.
Dzień można uogólnić do dowolnej ziarnistości okresu odpowiadającej rytmowi korzystania z produktu.
Retencję Day-N należy raportować wyłącznie dla dojrzałych kohort (wiek >= N), aby uniknąć pułapki przeżywalności, i zawsze należy zapytać, którą definicję ma na myśli firma.

Często zadawane pytania

Czy lekcja „Retencja dnia N i retencja krocząca” jest bezpłatna?

Tak — pełny tekst „Retencja dnia N i retencja krocząca” 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 SQL Interview Prep, przejdź na CoddyKit PRO. Kurs SQL Interview Prep zawiera 4 lekcji w sumie.

Co nauczysz się w „Retencja dnia N i retencja krocząca”?

Różnica między klasycznymi, kroczącymi i ograniczonymi definicjami retencji. Ćwiczysz SQL 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ąć SQL Interview Prep?

Nie wymagamy żadnego doświadczenia. SQL 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 3 z 4.

Ile czasu zajmuje lekcja „Retencja dnia N i retencja krocząca”?

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 SQL Interview Prep?

Tak. Każda lekcja SQL 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

← Powrót do SQL Interview Prep