SQL Interview Prep · Lekcja

Bieżąca aktywna passa na dziś

Obliczanie trwającej passy i resetowanie jej po przerwie.

Bieżąca aktywna passa na dziś to bezpłatna lekcja SQL 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 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 produktowe

Funkcje serii (na przykład w Duolingo lub Snapchat) wymagają bieżącej serii, a nie historycznej najdłuższej serii. Pytanie rekrutacyjne brzmi: „Dla każdego użytkownika określ, przez ile kolejnych dni kończących się dzisiaj był aktywny. Ustaw wartość 0, jeśli nie był aktywny dzisiaj.”

Wprowadza to dodatkowy warunek: seria musi być zakotwiczona na dzisiaj (lub na wczoraj, jeśli obowiązuje okres tolerancji). Zbudujmy ją na znanej już podstawie metody luk i wysp.

Definiowanie aktywności

Najpierw należy ustalić z rekruterem zasady — te decyzje zmieniają zapytanie:

Czy seria wymaga aktywności dzisiaj, czy dopuszczalne jest wczoraj (okres tolerancji)?
Czy wiele zdarzeń w ciągu jednego dnia jest sprowadzanych do jednego dnia?
Jaka strefa czasowa definiuje „dzień”?

Założymy jeden wiersz na aktywny dzień oraz to, że seria jest bieżąca, jeśli obejmuje dzisiaj lub wczoraj.

Ponowne budowanie wysp

Należy ponownie użyć kotwicy: ponumerować dni każdego użytkownika według daty i odjąć numer. Każdy kolejny ciąg będzie miał tę samą kotwicę. Bieżąca seria to po prostu wyspa, której ostatnim dniem jest dzisiaj lub wczoraj.

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;

Identyfikowanie najnowszej wyspy

Najnowsza wyspa każdego użytkownika to ta z największą wartością streak_end. Sprawdzamy, czy jej zakończenie jest wystarczająco niedawne, aby uznać serię za trwającą.

CURRENT_DATE oznacza dzisiaj. CURRENT_DATE - 1 oznacza wczoraj. Jeśli streak_end jest równe którejkolwiek z tych wartości, seria jest aktywna.

WITH /* ...numbered, islands... */
latest AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY streak_end DESC
    ) AS rn2
  FROM islands
)
SELECT * FROM latest WHERE rn2 = 1;

Zastosowanie testu aktualności

Teraz należy sprawdzić, czy najnowsza wyspa jest aktywna. Jeśli jej data końcowa przypada na dzisiaj lub wczoraj, bieżąca seria jest równa jej długości; w przeciwnym razie użytkownik przerwał serię, a bieżąca wartość wynosi 0.

SELECT user_id,
  CASE
    WHEN streak_end >= CURRENT_DATE - 1
    THEN streak_len
    ELSE 0
  END AS current_streak
FROM latest
WHERE rn2 = 1;

Pełne zapytanie dla bieżącej serii

Należy połączyć wszystkie warstwy: ponumerować wiersze, zbudować wyspy, wybrać najnowszą wyspę dla każdego użytkownika, a następnie zastosować CASE sprawdzający aktualność. To kompletna odpowiedź.

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,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
latest AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY streak_end DESC
    ) AS rn2
  FROM islands
)
SELECT user_id,
  CASE WHEN streak_end >= CURRENT_DATE - 1
       THEN streak_len ELSE 0 END AS current_streak
FROM latest
WHERE rn2 = 1
ORDER BY user_id;

Rygorystyczne wymaganie aktywności tylko dzisiaj

Jeśli reguła biznesowa jest rygorystyczna — seria jest aktywna tylko wtedy, gdy użytkownik był aktywny dzisiaj — należy zmienić porównanie tak, aby wymagało streak_end = CURRENT_DATE.

Wersja z okresem tolerancji (>= CURRENT_DATE - 1) jest łagodniejsza i często stosowana w rzeczywistych aplikacjach, w których dzień jeszcze się nie skończył. Zawsze należy potwierdzić, której wersji oczekuje rekruter.

CASE WHEN streak_end = CURRENT_DATE
     THEN streak_len ELSE 0 END AS current_streak

Alternatywa: przejście wstecz

Inny model myślowy, który preferują niektórzy rekruterzy, polega na obliczeniu odległości każdego dnia od dzisiaj. Dany dzień należy do bieżącej serii tylko wtedy, gdy obecny jest każdy dzień od niego aż do dzisiaj. Równoważnie można porównać login_date z CURRENT_DATE - (offset from the latest).

Metoda wysp jest zazwyczaj prostsza, ale znajomość alternatywy pokazuje szersze zrozumienie tematu. Kluczowa idea pozostaje taka sama: nieprzerwany ciąg zakotwiczony na dzisiaj.

Uwzględnianie nieaktywnych użytkowników

Użytkownicy nieobecni w logins nie mają żadnej wyspy i znikają z wyniku. Jeśli dla każdego użytkownika należy zwrócić serię (najprawdopodobniej o długości 0), trzeba użyć LEFT JOIN z listą użytkowników i zastosować COALESCE, aby zamienić wynik na 0.

SELECT u.user_id,
  COALESCE(s.current_streak, 0) AS current_streak
FROM users u
LEFT JOIN current_streaks s ON s.user_id = u.user_id;

Pułapki związane ze strefami czasowymi

Jeśli aktywność jest przechowywana jako znaczniki czasu, określenie „dzisiaj” zależy od strefy czasowej. Przed obcięciem do daty należy dokonać konwersji.

Należy przechowywać i porównywać dane w UTC albo najpierw przeliczyć je na lokalną strefę użytkownika.
W Postgresie należy użyć event_ts AT TIME ZONE 'UTC' AT TIME ZONE user_tz, a następnie rzutować wynik na datę.
Proste użycie ::date na znaczniku czasu w UTC może przypisać zdarzenie wieczorne do niewłaściwego dnia kalendarzowego.

Wspomnienie o tym pozwala uprzedzić klasyczne pytanie uzupełniające.

SELECT user_id,
  (event_ts AT TIME ZONE 'UTC'
             AT TIME ZONE 'America/New_York')::date AS local_day
FROM events;

Wydajność i podsumowanie

W przypadku często odświeżanych pulpitów to zapytanie wykonuje się dla każdego użytkownika w jednym przejściu. Aby zachować wysoką szybkość działania:

Należy utworzyć indeks (user_id, login_date).
Opcjonalnie można ograniczyć dane wejściowe do ostatnich dat — bieżąca seria nie może obejmować danych starszych niż jej własna długość, więc kroczące okno obejmujące na przykład ostatnie 400 dni w zupełności wystarczy.
Jeśli dane są odczytywane bardzo często, należy materializować dane dzienne w tabeli serii.

Szybki test

Należy potwierdzić, że rozumie się zakotwiczenie względem „teraz”.

Podsumowanie

Aby obliczyć bieżącą aktywną serię na dziś:

Należy zbudować wyspy za pomocą kotwicy login_date - ROW_NUMBER().
Należy wybrać najnowszą wyspę każdego użytkownika, używając maksymalnej wartości streak_end.
Należy zwrócić jej długość tylko wtedy, gdy streak_end przypada na dzisiaj (lub na wczoraj w przypadku okresu tolerancji), a w przeciwnym razie zwrócić 0.
Należy użyć LEFT JOIN z tabelą users dla nieaktywnych kont oraz uwzględnić strefy czasowe przed obcięciem wartości do daty.

Masz już pełen zestaw narzędzi do analizy serii: wykrywanie ciągów, najdłuższą serię, warunki dla N kolejnych wierszy oraz bieżącą serię.

Często zadawane pytania

Czy lekcja „Bieżąca aktywna passa na dziś” jest bezpłatna?

Tak — pełny tekst „Bieżąca aktywna passa na dziś” 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 „Bieżąca aktywna passa na dziś”?

Obliczanie trwającej passy i resetowanie jej po przerwie. Ć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 4 z 4.

Ile czasu zajmuje lekcja „Bieżąca aktywna passa na dziś”?

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