Budowanie wieloetapowego lejka
Zliczanie użytkowników docierających do każdego uporządkowanego etapu i obliczanie współczynników konwersji między etapami.
Budowanie wieloetapowego lejka 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.
Co naprawdę sprawdza pytanie o lejek
Gdy osoba rekrutująca mówi „zbuduj lejek rejestracji”, sprawdza, czy potrafisz policzyć unikatowych użytkowników, którzy docierają do każdego kolejnego kroku, oraz wyrazić spadek między tymi krokami.
Lejek może mieć takie etapy jak visit -> signup -> activate -> purchase. Wynikiem jest zazwyczaj jeden wiersz na krok, zawierający liczbę użytkowników i współczynnik konwersji.
- Należy zliczać użytkowników, a nie zdarzenia (użytkownik, który wywoła zdarzenie dwa razy, nadal liczy się jako jeden użytkownik).
- Kroki są uporządkowane; dotarcie do kroku 3 oznacza przejście przez kroki 1 i 2.
Tabela zdarzeń, którą otrzymasz
W niemal każdym zadaniu dotyczącym lejka otrzymasz jedną tabelę events w postaci długiej. Wyobraź sobie następującą strukturę:
user_id— użytkownik, który wykonał działanieevent_name— nazwa zdarzenia, np. 'visit', 'signup', 'purchase'event_time— znacznik czasu
Jeden wiersz odpowiada jednemu działaniu. Twoim zadaniem jest przekształcenie tych danych w zliczenia dla kolejnych kroków. Przed napisaniem SQL zawsze potwierdź z osobą rekrutującą dokładne nazwy zdarzeń.
CREATE TABLE events (
user_id INT,
event_name VARCHAR(50),
event_time TIMESTAMP
);Zliczanie użytkowników na jednym kroku
Na początek rozważ prosty przypadek: ilu unikatowych użytkowników dotarło do jednego kroku? Użyj COUNT(DISTINCT user_id) z filtrem nałożonym na nazwę zdarzenia.
To podstawowy element każdego lejka. Jeśli potrafisz przejrzyście zliczyć jeden krok, potrafisz zliczyć je wszystkie.
SELECT COUNT(DISTINCT user_id) AS users_who_signed_up
FROM events
WHERE event_name = 'signup';Agregacja warunkowa dla wszystkich kroków
Przejrzysta odpowiedź na rozmowie rekrutacyjnej zlicza każdy krok podczas jednego przebiegu, używając agregacji warunkowej: CASE wewnątrz COUNT(DISTINCT ...).
Dla każdego kroku należy zliczyć unikatowych użytkowników, których zdarzenie odpowiada temu krokowi. Jedno skanowanie danych daje jeden wiersz z podsumowaniem wszystkich kroków.
SELECT
COUNT(DISTINCT CASE WHEN event_name = 'visit' THEN user_id END) AS step1_visit,
COUNT(DISTINCT CASE WHEN event_name = 'signup' THEN user_id END) AS step2_signup,
COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS step3_purchase
FROM events;Ukryty błąd: kroki nie są uporządkowane
Właśnie pokazane zapytanie zawiera pułapkę, którą osoby rekrutujące uwielbiają. Zlicza każdego, kto wykonał 'purchase', nawet jeśli w danych nigdy nie odwiedził strony ani się nie zarejestrował.
Prawdziwy lejek wymaga, aby każdy kolejny krok był podzbiorem wcześniejszego kroku. Niezależne zliczanie zdarzeń może sprawić, że krok 3 będzie większy od kroku 2, co w lejku jest logicznie niemożliwe.
Rozwiązanie polega na powiązaniu kroków każdego użytkownika, zwykle przez wcześniejsze sprowadzenie danych do jednego wiersza na użytkownika.
Jeden wiersz na użytkownika z flagami
Sprawdzony wzorzec polega na sprowadzeniu dziennika zdarzeń do jednego wiersza na użytkownika oraz dodaniu flagi logicznej (w postaci 0/1) określającej, czy użytkownik kiedykolwiek wykonał dany krok. MAX(CASE ...) przekształca długi dziennik w szerokie podsumowanie dla każdego użytkownika.
WITH user_steps AS (
SELECT
user_id,
MAX(CASE WHEN event_name = 'visit' THEN 1 ELSE 0 END) AS did_visit,
MAX(CASE WHEN event_name = 'signup' THEN 1 ELSE 0 END) AS did_signup,
MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
FROM events
GROUP BY user_id
)
SELECT * FROM user_steps;Wymuszanie kolejności kroków
Teraz należy wymusić regułę lejka: użytkownik liczy się dla kroku N tylko wtedy, gdy wykonał również każdy wcześniejszy krok. Dotarcie do 'purchase' ma znaczenie tylko wtedy, gdy użytkownik odwiedził stronę i się zarejestrował.
Należy sumować flagi wraz z połączonymi warunkami wstępnymi, aby każdy krok był rzeczywistym podzbiorem poprzedniego.
WITH user_steps AS (
SELECT
user_id,
MAX(CASE WHEN event_name = 'visit' THEN 1 ELSE 0 END) AS did_visit,
MAX(CASE WHEN event_name = 'signup' THEN 1 ELSE 0 END) AS did_signup,
MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase
FROM events
GROUP BY user_id
)
SELECT
SUM(did_visit) AS step1_visit,
SUM(CASE WHEN did_visit = 1 AND did_signup = 1 THEN 1 ELSE 0 END) AS step2_signup,
SUM(CASE WHEN did_visit = 1 AND did_signup = 1 AND did_purchase = 1 THEN 1 ELSE 0 END) AS step3_purchase
FROM user_steps;Przekształcanie zliczeń w uporządkowany wynik długi
Osoby rekrutujące często wolą jeden wiersz na krok zamiast jednego szerokiego wiersza. Przekształć szerokie podsumowanie do postaci długiej za pomocą niewielkiego UNION ALL, dodając numer kroku, aby zachować kolejność.
W kolejnym kroku znacznie ułatwi to obliczanie współczynników konwersji i tworzenie wykresów.
WITH funnel AS (
SELECT 1 AS step_no, 'visit' AS step_name, 1000 AS users UNION ALL
SELECT 2, 'signup', 420 UNION ALL
SELECT 3, 'purchase', 95
)
SELECT step_no, step_name, users
FROM funnel
ORDER BY step_no;Współczynnik konwersji między kolejnymi krokami
Znaczenie mają dwa współczynniki i osoba rekrutująca zapyta, który z nich masz na myśli:
- Konwersja kroku: liczba użytkowników na tym kroku podzielona przez liczbę użytkowników na poprzednim kroku.
- Konwersja ogółem: liczba użytkowników na tym kroku podzielona przez liczbę użytkowników na początku lejka.
Użyj LAG, aby pobrać liczbę użytkowników z poprzedniego kroku i obliczyć konwersję między krokami. Należy rzutować wynik na typ dziesiętny, aby uniknąć dzielenia całkowitego.
WITH funnel AS (
SELECT 1 AS step_no, 'visit' AS step_name, 1000 AS users UNION ALL
SELECT 2, 'signup', 420 UNION ALL
SELECT 3, 'purchase', 95
)
SELECT
step_name,
users,
ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_conv_pct
FROM funnel
ORDER BY step_no;Konwersja ogółem liczona od początku lejka
W przypadku konwersji ogółem podziel liczbę użytkowników na każdym kroku przez liczbę użytkowników na pierwszym kroku. FIRST_VALUE zastosowane do uporządkowanego lejka przypisuje tę początkową wartość każdemu wierszowi.
Zawsze należy wspomnieć osobie rekrutującej, że zabezpieczono się przed dzieleniem całkowitym przez pomnożenie przez 100.0.
WITH funnel AS (
SELECT 1 AS step_no, 'visit' AS step_name, 1000 AS users UNION ALL
SELECT 2, 'signup', 420 UNION ALL
SELECT 3, 'purchase', 95
)
SELECT
step_name,
users,
ROUND(100.0 * users / FIRST_VALUE(users) OVER (ORDER BY step_no), 1) AS overall_pct
FROM funnel
ORDER BY step_no;Połączenie całego lejka
Oto kompletne rozwiązanie, którego oczekują osoby rekrutujące: sprowadź dane do flag na poziomie użytkownika, wymuś kolejność, przekształć wynik do postaci długiej, a następnie oblicz oba współczynniki. Omawiaj rozwiązanie na głos, wyjaśniając przeznaczenie każdego CTE.
Ta struktura dobrze się skaluje: aby dodać krok, wystarczy dodać jedną flagę i jeden wiersz UNION ALL.
WITH user_steps AS (
SELECT user_id,
MAX(CASE WHEN event_name = 'visit' THEN 1 ELSE 0 END) AS s1,
MAX(CASE WHEN event_name = 'signup' THEN 1 ELSE 0 END) AS s2,
MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS s3
FROM events GROUP BY user_id
),
totals AS (
SELECT 1 AS step_no, 'visit' AS step_name, SUM(s1) AS users FROM user_steps UNION ALL
SELECT 2, 'signup', SUM(CASE WHEN s1=1 AND s2=1 THEN 1 ELSE 0 END) FROM user_steps UNION ALL
SELECT 3, 'purchase', SUM(CASE WHEN s1=1 AND s2=1 AND s3=1 THEN 1 ELSE 0 END) FROM user_steps
)
SELECT step_name, users,
ROUND(100.0 * users / LAG(users) OVER (ORDER BY step_no), 1) AS step_pct
FROM totals ORDER BY step_no;Szybkie sprawdzenie
Osoba rekrutująca zauważa, że Twój lejek pokazuje 95 użytkowników na etapie 'purchase', ale tylko 80 na etapie 'signup'. Jaka jest najbardziej prawdopodobna przyczyna?
Podsumowanie: lejki wieloetapowe
Potrafisz już budować lejek w sposób oczekiwany podczas rozmów rekrutacyjnych:
- Zliczaj unikatowych użytkowników dla każdego uporządkowanego kroku, nigdy surowe zdarzenia.
- Sprowadzaj dziennik zdarzeń do jednego wiersza na użytkownika, używając flag
MAX(CASE ...). - Wymuszaj kolejność, aby każdy krok był podzbiorem poprzedniego.
- Obliczaj konwersję między kolejnymi krokami (LAG) i ogółem (FIRST_VALUE), zabezpieczając się przed dzieleniem całkowitym.
Następny temat: upewnienie się, że te kroki rzeczywiście nastąpiły we właściwej kolejności i w określonym przedziale czasu.
Często zadawane pytania
Czy lekcja „Budowanie wieloetapowego lejka” jest bezpłatna?
Tak — pełny tekst „Budowanie wieloetapowego lejka” 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 „Budowanie wieloetapowego lejka”?
Zliczanie użytkowników docierających do każdego uporządkowanego etapu i obliczanie współczynników konwersji między etapami. Ć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 „Budowanie wieloetapowego lejka”?
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
- Budowanie wieloetapowego lejka
- Uporządkowane zdarzenia i okna czasowe
- Przypisywanie do testu A/B i metryki
- Wzrost, istotność i zabezpieczenia w SQL