0Pricing
SQL Interview Prep · Lekcja

Definiowanie kohorty na podstawie pierwszego działania

Przypisywanie każdemu użytkownikowi kohorty na podstawie daty jego pierwszego zdarzenia.

Definiowanie kohorty na podstawie pierwszego działania to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 1 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 kohorty pojawiają się na rozmowach kwalifikacyjnych

Gdy osoba prowadząca rozmowę kwalifikacyjną z zakresu analityki produktu mówi „zbuduj kohortę”, sprawdza, czy potrafi Pan/Pani przypisać każdego użytkownika do grupy na podstawie tego, kiedy po raz pierwszy wykonał określone działanie, a następnie śledzić tę grupę w czasie.

Kohorta to zbiór użytkowników, których zdarzenie początkowe przypada na ten sam okres — zwykle jest to pierwszy zakup, rejestracja albo logowanie. Kohorty są przydatne, ponieważ pozwalają porównywać użytkowników na równych zasadach: każdego użytkownika z kohorty styczniowej mierzy się od jego własnego początku w styczniu.

Pierwszym ćwiczonym elementem, któremu poświęcona jest ta lekcja, jest niezawodne wyznaczanie daty pierwszego działania każdego użytkownika.

Tabela źródłowa

Niemal każde pytanie dotyczące kohort zaczyna się od tabeli zdarzeń: jednego wiersza dla każdego działania użytkownika wraz ze znacznikiem czasu. Wyobraźmy sobie tabelę events:

  • user_id — kto wykonał działanie
  • event_type — co zrobił
  • event_at — kiedy, jako znacznik czasu

Podczas rozmowy kwalifikacyjnej należy głośno doprecyzować poziom szczegółowości: „Czy każdy wiersz odpowiada jednemu zdarzeniu i czy użytkownik może występować wiele razy?”. Odpowiedź niemal zawsze brzmi „tak”, dlatego potrzebna jest agregacja sprowadzająca dane do pierwszego działania każdego użytkownika.

CREATE TABLE events (
  user_id    INT,
  event_type VARCHAR(50),
  event_at   TIMESTAMP
);

Pierwsze działanie = MIN znacznika czasu

Podstawowa operacja jest prosta: grupowanie według user_id i pobranie wartości MIN(event_at). Ta wartość minimalna oznacza pierwsze działanie użytkownika, czyli moment, który przypisuje go do kohorty.

To odpowiedź, którą osoba prowadząca rozmowę kwalifikacyjną chce usłyszeć w pierwszej kolejności, zanim pojawią się bardziej rozbudowane funkcje okna. Zwykłe GROUP BY jest poprawne, czytelne i szybkie.

SELECT
  user_id,
  MIN(event_at) AS first_action_at
FROM events
GROUP BY user_id;

Filtrowanie do zdarzenia definiującego

Często kohortę definiuje konkretne działanie, a nie dowolne zdarzenie. „Kohortowanie użytkowników według ich pierwszego zakupu” oznacza, że przed pobraniem wartości minimalnej trzeba przefiltrować wiersze zakupu.

Filtr należy umieścić w WHERE, aby funkcja MIN uwzględniała tylko kwalifikujące się wiersze. Częstą pułapką podczas rozmowy kwalifikacyjnej jest pobranie MIN ze wszystkich zdarzeń, a następnie filtrowanie, co przypisałoby nieprawidłową datę rozpoczęcia każdej osobie, która przeglądała ofertę przed zakupem.

SELECT
  user_id,
  MIN(event_at) AS first_purchase_at
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Przypisywanie do okresu kohorty

Kohorta jest zwykle okresem, a nie dokładnym znacznikiem czasu: na przykład „kohorta 2024-03” albo „tydzień rozpoczynający się 2024-03-04”. Datę pierwszego działania należy obciąć do początku danego okresu.

W Postgresie należy użyć DATE_TRUNC('month', ...). W MySQL można użyć DATE_FORMAT(d, '%Y-%m-01'), a w SQL Server DATETRUNC(month, d) lub obliczyć pierwszy dzień miesiąca. Podczas rozmowy kwalifikacyjnej warto określić używany dialekt, aby wybór składni był świadomy.

SELECT
  user_id,
  DATE_TRUNC('month', MIN(event_at)) AS cohort_month
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Umieszczenie logiki w CTE

Przypisanie kohorty każdemu użytkownikowi jest elementem, który będzie wielokrotnie wykorzystywany w zapytaniach dotyczących retencji, dlatego należy umieścić je w jasno nazwanym CTE. Dzięki temu kolejne kroki są czytelniejsze, a osoba prowadząca rozmowę widzi, że myśli Pan/Pani o rozwiązaniu jako o zestawie elementów, które można łączyć.

Od tego momentu każde kolejne zapytanie może dołączyć tabelę user_cohort, aby ustalić, do której grupy należy użytkownik.

WITH user_cohort AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events
  WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT * FROM user_cohort;

Rozmiar kohorty: liczenie członków

Pierwszą kontrolą poprawności, której oczekuje osoba prowadząca rozmowę, jest rozmiar kohorty: liczba użytkowników należących do każdej kohorty. Należy pogrupować CTE z przypisaniami według cohort_month i policzyć unikalnych użytkowników.

Dla bezpieczeństwa należy użyć COUNT(DISTINCT user_id), nawet jeśli CTE zawiera już jeden wiersz na użytkownika; pokazuje to, że uwzględnia Pan/Pani poziom szczegółowości danych. Ta liczba będzie później mianownikiem dla każdego procentu retencji.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT
  cohort_month,
  COUNT(DISTINCT user_id) AS cohort_size
FROM user_cohort
GROUP BY cohort_month
ORDER BY cohort_month;

Alternatywa z funkcją okna

Czasami osoba prowadząca rozmowę prosi o etykietę kohorty przypisaną do każdego wiersza zdarzenia, a nie o tabelę zredukowaną do jednego wiersza na użytkownika. W takiej sytuacji przydaje się funkcja okna: MIN(event_at) OVER (PARTITION BY user_id) oblicza pierwsze działanie bez usuwania wierszy.

Jest to przydatne, gdy w jednym przebiegu potrzebne są zarówno szczegółowe zdarzenia, jak i etykieta kohorty — to punkt wyjścia do zliczania retencji.

SELECT
  user_id,
  event_at,
  DATE_TRUNC('month',
    MIN(event_at) OVER (PARTITION BY user_id)
  ) AS cohort_month
FROM events
WHERE event_type = 'purchase';

Pułapka remisów i duplikatów

Co się stanie, jeśli użytkownik ma dwa zdarzenia z dokładnie takim samym najwcześniejszym znacznikiem czasu? MIN obsługuje to bez problemu: zwraca jedną wartość minimalną niezależnie od liczby powiązanych wierszy, więc przypisanie do kohorty nadal pozostaje jedno na użytkownika.

Inaczej jest w przypadku podejścia z użyciem ROW_NUMBER() ... ORDER BY event_at, w którym remisy są rozstrzygane arbitralnie. Aby uzyskać stabilny wynik, należy dodać deterministyczny element rozstrzygający, taki jak event_id. Samodzielne zwrócenie uwagi na ten kompromis pokazuje dojrzałość techniczną.

SELECT user_id, event_at,
  ROW_NUMBER() OVER (
    PARTITION BY user_id
    ORDER BY event_at, event_id
  ) AS rn
FROM events
WHERE event_type = 'purchase';

Strefy czasowe i granica dnia

Subtelne pytanie podczas rozmowy kwalifikacyjnej: zakup o 23:30 w Nowym Jorku przypada następnego dnia w UTC. Jeśli kohorty są grupowane według dni kalendarzowych, to strefa czasowa decyduje, do której kohorty trafi użytkownik.

Bezpieczna odpowiedź brzmi: znaczniki czasu należy przechowywać w UTC, a następnie przed obcięciem do dnia konwertować je do strefy czasowej używanej przez firmę. Trzeba jednoznacznie wskazać, która strefa definiuje „dzień” dla danej miary, ponieważ ta jedna decyzja może przenieść tysiące użytkowników między kohortami.

SELECT
  user_id,
  DATE_TRUNC('day',
    MIN(event_at AT TIME ZONE 'America/New_York')
  ) AS cohort_day
FROM events
WHERE event_type = 'purchase'
GROUP BY user_id;

Wykluczanie użytkowników sprzed zakresu

Rzeczywiste analizy ograniczają kohortę do zakresu dat, na przykład „kohorty, które rozpoczęły się w pierwszym kwartale”. Należy filtrować zagregowaną datę pierwszego działania, czyli użyć klauzuli HAVING albo zewnętrznego filtra na CTE, a nie WHERE na surowych zdarzeniach.

Filtrowanie surowych zdarzeń według daty błędnie pozwoliłoby użytkownikowi, który dokonał pierwszego zakupu w grudniu, ale również wykonał działanie w pierwszym kwartale, trafić do kohorty z pierwszego kwartału. Warunek należy zawsze oprzeć na obliczonym pierwszym działaniu.

WITH user_cohort AS (
  SELECT user_id, MIN(event_at) AS first_at
  FROM events WHERE event_type = 'purchase'
  GROUP BY user_id
)
SELECT user_id, DATE_TRUNC('month', first_at) AS cohort_month
FROM user_cohort
WHERE first_at >= DATE '2024-01-01'
  AND first_at <  DATE '2024-04-01';

Szybkie sprawdzenie

Osoba prowadząca rozmowę pyta: „Przypisz każdego użytkownika do kohorty według miesiąca pierwszego zakupu. Użytkownicy mogą przeglądać ofertę przed zakupem”. Które podejście jest poprawne?

Podsumowanie: definiowanie kohorty

Najważniejsze wnioski dotyczące definiowania kohorty podczas rozmowy kwalifikacyjnej:

  • Kohorta grupuje użytkowników według ich pierwszego kwalifikującego się działania.
  • Należy obliczyć je za pomocą MIN(event_at) po przefiltrowaniu zdarzenia definiującego w WHERE.
  • Działanie należy przypisać do okresu za pomocą DATE_TRUNC lub odpowiednika właściwego dla danego dialektu.
  • Przypisanie należy umieścić w CTE, aby można je było ponownie wykorzystać; COUNT(DISTINCT user_id) zwraca rozmiar kohorty.
  • Należy uważać na granicę dnia wyznaczaną przez strefę czasową i ograniczać zakresy dat na podstawie obliczonego pierwszego działania, nigdy na podstawie surowych zdarzeń.

Po opanowaniu tego zagadnienia macierz retencji z następnej lekcji sprowadza się do wykonania złączenia.

Często zadawane pytania

Czy lekcja „Definiowanie kohorty na podstawie pierwszego działania” jest bezpłatna?

Tak — pełny tekst „Definiowanie kohorty na podstawie pierwszego działania” 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 „Definiowanie kohorty na podstawie pierwszego działania”?

Przypisywanie każdemu użytkownikowi kohorty na podstawie daty jego pierwszego zdarzenia. Ć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 1 z 4.

Ile czasu zajmuje lekcja „Definiowanie kohorty na podstawie pierwszego działania”?

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

  1. Definiowanie kohorty na podstawie pierwszego działania
  2. Budowanie macierzy retencji
  3. Retencja dnia N i retencja krocząca
  4. Zapytania dotyczące odpływu i powrotów użytkowników
← Powrót do SQL Interview Prep