SQL Interview Prep · Lekcja

Najdłuższa passa na użytkownika

Obliczanie maksymalnej długości kolejnych wystąpień w każdej grupie.

Najdłuższa passa na użytkownika to bezpłatna lekcja SQL Interview Prep na CoddyKit. To lekcja 2 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.

Pytanie

Częste pytanie uzupełniające po wykrywaniu kolejnych dni brzmi: „Jaka jest dla każdego użytkownika jego najdłuższa seria kolejnych aktywnych dni?” Zespoły produktowe i zespoły growth nieustannie zadają to pytanie, aby mierzyć zaangażowanie.

Wiedzą już Państwo, jak identyfikować poszczególne serie. Nowym krokiem jest znalezienie maksymalnej długości serii dla każdego użytkownika oraz często zwrócenie także dat tej najlepszej serii. Ta lekcja bezpośrednio rozwija szkielet problemów luk i wysp.

Przypomnienie o budowaniu wysp

Z poprzedniej lekcji pamiętają Państwo, że grupowanie poszczególnych serii wykorzystuje login_date - ROW_NUMBER() jako kotwicę wyspy. Każdy użytkownik może mieć kilka wysp; najpierw obliczymy jeden wiersz dla każdej wyspy, a następnie zredukujemy wynik do jednego wiersza na użytkownika.

Warto zapamiętać ten dwuetapowy plan: najpierw budujemy wyspy, a następnie agregujemy wyspy.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
)
SELECT user_id, login_date - rn AS grp
FROM numbered;

Jeden wiersz na wyspę

Połącz każdy wyspę w jeden wiersz podsumowania zawierający jej długość i zakres dat. Grupuj według użytkownika i kotwicy, a następnie oblicz metryki.

Nazwiemy ten CTE islands, aby kolejna warstwa mogła przejrzyście odczytywać z niego dane.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
)
SELECT * FROM islands;

Prosta odpowiedź: długość MAX

Jeśli rekruter chce znać tylko długość, końcowy krok jest jednowierszowy: pogrupuj wyspy według użytkownika i wybierz maksymalną długość.

To najprostsza odpowiedź, gdy daty początku i końca nie są wymagane.

-- ...numbered and islands CTEs as before...
SELECT
  user_id,
  MAX(streak_len) AS longest_streak
FROM islands
GROUP BY user_id
ORDER BY user_id;

Zwracanie także dat

Często rekruter dodaje: „i pokaż, kiedy ta seria wystąpiła”. Zwykłe MAX nie wskaże, która wyspa wygrała. Trzeba uszeregować wyspy w obrębie każdego użytkownika i zachować rangę 1.

Użyj ROW_NUMBER, porządkując według długości malejąco, aby najlepsza seria każdego użytkownika otrzymała rangę 1. Dodaj kryterium rozstrzygające, aby remisy były rozwiązywane deterministycznie.

ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC, streak_start ASC
) AS rnk

Szeregowanie i filtrowanie

Umieść szeregowanie w CTE, a następnie odfiltruj wiersze za pomocą rnk = 1. Nie można bezpośrednio filtrować funkcji okna w WHERE, dlatego dodatkowa warstwa jest konieczna.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1;

RANK a ROW_NUMBER przy remisach

Co zrobić, jeśli użytkownik ma dwie serie o tej samej maksymalnej długości, a rekruter chce zwrócić obie? Zamień ROW_NUMBER na RANK i zachowaj rnk = 1.

ROW_NUMBER — dokładnie jeden zwycięzca dla każdego użytkownika (przy remisie wybór jest arbitralny, chyba że dodasz kryterium rozstrzygające).
RANK — wszystkie najdłuższe serie objęte remisem otrzymują rangę 1 i wszystkie zostają zachowane.

Warto doprecyzować, którego zachowania oczekuje rekruter — pokazuje to dbałość o przypadki brzegowe.

RANK() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC
) AS rnk  -- keep all rnk = 1

Przykład z rozwiązaniem

Załóżmy, że użytkownik 7 logował się od 1 do 4 stycznia, następnie 10 i 11 stycznia, a potem od 20 do 23 stycznia. Otrzymujemy trzy wyspy o długościach 4, 2 i 4. Największa długość wynosi 4 i występuje remis.

Przy użyciu ROW_NUMBER oraz kryterium rozstrzygającego streak_start: zwrócona zostanie tylko seria od 1 do 4 stycznia.
Przy użyciu RANK: zwrócone zostaną obie serie — od 1 do 4 stycznia oraz od 20 do 23 stycznia.

Głośne wyjaśnienie tego zachowania pokazuje, że uwzględnili Państwo duplikaty.

Obsługa użytkowników bez logowań

Rekruter może zapytać: „A co z użytkownikami, którzy nigdy się nie zalogowali?” Tacy użytkownicy nie mają żadnych wierszy w logins, więc znikają z wyniku. Jeśli muszą pojawić się z serią o długości 0, należy użyć LEFT JOIN z pełną tabelą users i COALESCE.

SELECT u.user_id,
  COALESCE(MAX(i.streak_len), 0) AS longest_streak
FROM users u
LEFT JOIN islands i ON i.user_id = u.user_id
GROUP BY u.user_id;

Uwagi dotyczące wydajności

Ten wzorzec wykonuje pojedyncze uporządkowane przejście po danych oraz grupowanie. Aby zachować wysoką wydajność:

Należy zapewnić indeks na (user_id, login_date), aby ORDER BY w funkcji okna nie wymagało sortowania.
Jeśli źródło zawiera wiele zdarzeń dziennie, należy przeprowadzić deduplikację na wczesnym etapie.
Należy unikać opakowywania login_date w funkcje w klauzuli ORDER BY, ponieważ może to uniemożliwić użycie indeksu.

W przypadku bardzo dużych tabel rozwiązanie to bez problemu przewyższa wydajnością każde podejście oparte na self-join.

Pełna odpowiedź rekrutacyjna

Oto kompletne, dopracowane zapytanie zwracające najdłuższą serię każdego użytkownika wraz z jej datami — wersja do zapisania na tablicy.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1
ORDER BY user_id;

Szybki test

Należy wybrać właściwe narzędzie do danego wymagania.

Podsumowanie

Aby obliczyć najdłuższą serię dla każdego użytkownika:

Należy zbudować wyspy za pomocą kotwicy login_date - ROW_NUMBER().
Należy sprowadzić każdą wyspę do długości oraz zakresu dat.
Jeśli potrzebna jest tylko długość, należy użyć MAX(streak_len), grupując według użytkownika.
Jeśli potrzebne są również daty, należy uszeregować wyspy dla każdego użytkownika i zachować rangę 1 — użyć RANK, aby uwzględnić remisy, lub ROW_NUMBER, aby wybrać jednego zwycięzcę.
Należy użyć LEFT JOIN z tabelą users, aby uwzględnić użytkowników z serią o długości 0.

Dalej: wykrywanie N kolejnych wierszy spełniających warunek.

Często zadawane pytania

Czy lekcja „Najdłuższa passa na użytkownika” jest bezpłatna?

Tak — pełny tekst „Najdłuższa passa na użytkownika” 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 „Najdłuższa passa na użytkownika”?

Obliczanie maksymalnej długości kolejnych wystąpień w każdej grupie. Ć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 2 z 4.

Ile czasu zajmuje lekcja „Najdłuższa passa na użytkownika”?

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

Wykrywanie kolejnych dni kalendarzowych
Najdłuższa passa na użytkownika
N kolejnych wierszy spełniających warunek
Bieżąca aktywna passa na dziś

← Powrót do SQL Interview Prep