0Pricing
SQL Interview Prep · Lekcja

Wykrywanie kolejnych dni kalendarzowych

Używanie arytmetyki dat i numerów wierszy do znajdowania nieprzerwanych ciągów dni

Wykrywanie kolejnych dni kalendarzowych 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.

Kontekst zadania rekrutacyjnego

Rekruterzy lubią pytania o serie, ponieważ pokazują, czy naprawdę rozumieją Państwo funkcje okna i arytmetykę dat. Typowe polecenie brzmi: „Mając tabelę z datami logowania użytkowników, znajdź każdą nieprzerwaną serię kolejnych dni kalendarzowych.”

Pierwszym odruchem może być samozłączenie porównujące każdy wiersz z następnym, ale na dużych tabelach rozwiązanie to szybko staje się nieefektywne i trudno je przejrzyście zapisać. Profesjonalna odpowiedź wykorzystuje technikę luk i wysp. W tej lekcji nauczą się Państwo przejrzyście wykrywać kolejne dni za pomocą numerów wierszy i odejmowania dat.

Przykładowe dane

W całej lekcji korzystamy z tabeli logins, zawierającej jeden wiersz dla każdego użytkownika i każdego dnia, w którym był aktywny. Zakładamy, że duplikaty zostały już usunięte (jedno logowanie na dzień kalendarzowy).

  • user_id — identyfikator użytkownika, który się zalogował
  • login_date — wartość typu DATE

Dla użytkownika 1 daty to 1, 2 i 3 stycznia, następnie przerwa, a potem 6 i 7 stycznia. Oczekujemy dwóch serii: jednej trwającej 3 dni i drugiej trwającej 2 dni.

SELECT * FROM logins ORDER BY user_id, login_date;
-- user_id | login_date
--    1    | 2024-01-01
--    1    | 2024-01-02
--    1    | 2024-01-03
--    1    | 2024-01-06
--    1    | 2024-01-07

Najważniejsza obserwacja

Oto sztuczka, która pozwala rozwiązać każdy problem z kolejnymi dniami. Jeśli uporządkują Państwo wiersze według daty i przypiszą każdemu z nich kolejny numer wiersza, to w dowolnej serii kolejnych dni różnica między datą a numerem wiersza pozostaje stała.

Dlaczego? Zarówno data, jak i numer wiersza zwiększają się dokładnie o 1 przy każdym kolejnym dniu, więc ich różnica się nie zmienia. Gdy pojawia się przerwa, data przeskakuje, ale numer wiersza nie — stała zostaje przerwana i rozpoczyna się nowa grupa.

Analiza różnicy

Prześledźmy to ręcznie dla użytkownika 1. ROW_NUMBER zwraca wartości 1, 2, 3, 4, 5. Odejmijmy numer wiersza (jako liczbę dni) od daty i obserwujmy wynik.

  • 1 stycznia − 1 = 31 grudnia
  • 2 stycznia − 2 = 31 grudnia
  • 3 stycznia − 3 = 31 grudnia
  • 6 stycznia − 4 = 2 stycznia
  • 7 stycznia − 5 = 2 stycznia

Pierwsze trzy wiersze mają wspólną wartość 31 grudnia, a dwa ostatnie — 2 stycznia. Ta wspólna wartość kotwicy jest naszym kluczem grupowania.

Dodawanie ROW_NUMBER

Pierwszym konkretnym krokiem jest przypisanie numeru wiersza, z podziałem na użytkowników, aby serie nigdy nie przekraczały granic użytkownika, oraz z uporządkowaniem według daty.

PARTITION BY user_id rozpoczyna licznik od nowa dla każdego użytkownika, a ORDER BY login_date gwarantuje, że kolejność odpowiada kalendarzowi.

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

Obliczanie kotwicy grupy

Teraz odejmijmy rn dni od login_date. W PostgreSQL można bezpośrednio odejmować od daty całkowitą liczbę dni. Wynikiem jest stała kotwica identyfikująca każdą wyspę.

Zwróć uwagę, że nie można odwołać się do aliasu rn w tym samym SELECT, w którym jest on definiowany — dlatego poprzednie zapytanie trzeba najpierw umieścić w CTE lub podzapytaniu.

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,
  login_date - rn AS grp
FROM numbered;

Grupowanie wysp

Mając kotwicę, wiemy, że każda kolejna seria ma tę samą wartość grp. Wykonaj GROUP BY dla user_id i grp, a następnie użyj agregacji, aby uzyskać początek, koniec i długość każdej serii.

  • MIN(login_date) — pierwszy dzień serii
  • MAX(login_date) — ostatni dzień serii
  • COUNT(*) — liczba dni w serii
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,
  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
ORDER BY user_id, streak_start;

Różnice między dialektami

Składnia arytmetyki dat różni się w zależności od dialektu. Warto wspomnieć o tym podczas rozmowy, aby pokazać szeroką znajomość tematu.

  • PostgreSQL: login_date - rn (data pomniejszona o całkowitą liczbę dni)
  • MySQL: DATE_SUB(login_date, INTERVAL rn DAY)
  • SQL Server: DATEADD(day, -rn, login_date)

Logika jest identyczna; zmieniają się tylko nazwy funkcji. Przenośny model mentalny brzmi: „Przesuń każdą datę wstecz o jej pozycję, aby cała seria złożyła się do jednej stałej wartości”.

-- SQL Server version of the anchor
DATEADD(day, -1 * rn, login_date) AS grp

Dlaczego nie samozłączenie?

Rekruter może zapytać, dlaczego uniknęli Państwo samozłączenia, takiego jak l1.login_date = l2.login_date + 1. Warto podać następujące powody:

  • Samozłączenie sprawdza tylko sąsiedztwo, a nie całą serię — do złożenia pełnych serii nadal potrzebne jest grupowanie.
  • Może powodować zwielokrotnienie wierszy i bez dobrych indeksów ma złożoność O(n²).
  • Metoda z numerami wierszy wykonuje jedno uporządkowane przejście i znacznie lepiej się skaluje.

Funkcje okna są współczesną i oczekiwaną odpowiedzią na tego rodzaju problemy.

Zabezpieczenie przed duplikatami

Cała technika zakłada jeden wiersz dla każdego użytkownika i dnia. Jeśli źródło zawiera wiele logowań tego samego dnia, dwa wiersze z tą samą datą otrzymają różne numery wierszy, co zniekształci kotwicę.

Należy temu zapobiec, deduplikując dane najpierw — rzutując znaczniki czasu na daty i używając DISTINCT albo stosując DENSE_RANK dla daty zamiast ROW_NUMBER, aby równe daty otrzymywały ten sam numer.

WITH days AS (
  SELECT DISTINCT user_id, login_ts::date AS login_date
  FROM raw_logins
)
SELECT * FROM days;

Pełne rozwiązanie

Połączenie wszystkich elementów daje przejrzystą, gotową na rozmowę kwalifikacyjną odpowiedź, która wyświetla każdą serię kolejnych dni wraz z jej początkiem, końcem i długością.

Ten sam schemat — deduplikacja, numerowanie, odejmowanie, grupowanie — rozwiązuje niemal każde zadane Państwu pytanie dotyczące kolejności.

WITH days AS (
  SELECT DISTINCT user_id, login_ts::date AS login_date
  FROM raw_logins
),
numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM days
)
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
ORDER BY user_id, streak_start;

Szybkie sprawdzenie

Sprawdź, czy rozumiesz najważniejszą sztuczkę.

Podsumowanie

Poznali Państwo podstawowy wzorzec dla kolejnych dni:

  • Deduplikacja do jednego wiersza na użytkownika i dzień.
  • ROW_NUMBER uporządkowany według daty i podzielony na partycje według użytkownika.
  • Odejmowanie numeru wiersza od daty w celu uzyskania stałej kotwicy dla każdej serii.
  • GROUP BY kotwicy i agregacja w celu uzyskania początku, końca i długości.

Ten szkielet problemów luk i wysp skaluje się w jednym przejściu i jest lepszy od samozłączeń. Następnie wykorzystają go Państwo do obliczenia najdłuższej serii dla każdego użytkownika.

Często zadawane pytania

Czy lekcja „Wykrywanie kolejnych dni kalendarzowych” jest bezpłatna?

Tak — pełny tekst „Wykrywanie kolejnych dni kalendarzowych” 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 „Wykrywanie kolejnych dni kalendarzowych”?

Używanie arytmetyki dat i numerów wierszy do znajdowania nieprzerwanych ciągów dni Ć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 „Wykrywanie kolejnych dni kalendarzowych”?

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. Wykrywanie kolejnych dni kalendarzowych
  2. Najdłuższa passa na użytkownika
  3. N kolejnych wierszy spełniających warunek
  4. Bieżąca aktywna passa na dziś
← Powrót do SQL Interview Prep