SQL Interview Prep · 강의

오늘 기준 현재 활성 연속 기록

진행 중인 연속 기록을 계산하고 중단되면 초기화합니다.

오늘 기준 현재 활성 연속 기록은(는) CoddyKit의 무료 SQL Interview Prep 강의입니다. 이것은 4개 중 4번째 강의입니다. 아래에서 전체 강의를 무료로 읽을 수 있으며, 내장 코드 에디터와 24/7 AI 튜터와 함께 브라우저에서 직접 실습할 수 있습니다. 이 강의는 SQL Interview Prep 학습 경로의 일부이며, 진행 상황이 웹과 CoddyKit 앱에 동기화됩니다. SQL Interview Prep 강의에는 총 4개의 강의가 포함되어 있습니다.

제품 관점의 질문

듀오링고나 스냅챗 같은 연속 기록 기능에는 과거의 최장 기록이 아니라 현재 연속 기록이 필요합니다. 면접 질문은 다음과 같습니다. "각 사용자에 대해 오늘까지 며칠 연속으로 활동했습니까? 오늘 활동을 빠뜨렸다면 0으로 초기화하십시오."

여기에는 한 가지 변형이 추가됩니다. 연속 기록은 오늘을 기준으로 해야 합니다(또는 유예 규칙에 따라 어제일 수 있습니다). 이미 알고 있는 간격과 구간 기반 위에서 이를 만들어 보겠습니다.

활성 상태 정의

먼저 면접관과 규칙을 명확히 하십시오 — 이러한 결정에 따라 쿼리가 달라집니다.

연속 기록에 오늘의 활동이 반드시 필요합니까, 아니면 어제도 허용됩니까(유예 기간)?
하루에 여러 이벤트가 있으면 하루에 한 행으로 합칩니까?
"하루"를 정의하는 시간대는 무엇입니까?

여기서는 활성 날짜마다 한 행이 있고, 오늘 OR 어제의 날짜를 포함하면 연속 기록이 현재 유효하다고 가정하겠습니다.

구간 다시 만들기

앵커를 재사용합니다. 각 사용자의 날짜에 날짜순으로 번호를 매기고 차이를 계산합니다. 모든 연속 구간은 같은 앵커를 공유합니다. 현재 연속 기록은 마지막 날이 오늘 또는 어제인 구간일 뿐입니다.

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;

최근성 검사 적용

이제 최신 구간이 활성 상태인지 결정합니다. 종료 날짜가 오늘 또는 어제라면 현재 연속 기록은 해당 구간의 길이이고, 그렇지 않으면 사용자의 연속 기록이 끊긴 것이므로 현재 값은 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;

현재 연속 기록 전체 쿼리

모든 계층을 조합합니다. 번호를 매기고, 구간을 만들고, 사용자별 최신 구간을 선택한 다음, 최근성 CASE를 적용합니다. 이것이 완성된 답변입니다.

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;

오늘만 인정하는 엄격한 규칙

비즈니스 규칙이 엄격하여 사용자가 오늘 활동한 경우에만 연속 기록이 유효하다면 streak_end = CURRENT_DATE가 되도록 비교를 변경합니다.

유예 기간 버전(>= CURRENT_DATE - 1)은 더 친화적이며 실제 앱에서 흔히 사용됩니다. 아직 하루가 끝나지 않았을 수 있기 때문입니다. 면접관이 어느 규칙을 원하는지 항상 확인하십시오.

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

대안: 뒤로 거슬러 올라가기

일부 면접관이 선호하는 다른 사고방식은 각 날짜에서 오늘까지의 간격을 계산하는 것입니다. 어떤 날짜가 현재 연속 기록의 일부가 되려면 그 날짜부터 오늘까지 모든 날짜가 존재해야 합니다. 다시 말해 login_date를 CURRENT_DATE - (offset from the latest)와 비교합니다.

구간 방식이 보통 더 깔끔하지만, 대안을 알고 있으면 깊이 있는 이해를 보여 줄 수 있습니다. 핵심 아이디어는 같습니다. 오늘을 앵커로 하는 끊기지 않은 연쇄입니다.

비활성 사용자 포함

logins에 없는 사용자는 구간이 없으므로 사라집니다. 모든 사용자의 연속 기록을 보고해야 한다면(대개 0일 것입니다) 사용자 목록에 LEFT JOIN하고 결과에 COALESCE를 적용하여 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;

시간대의 함정

활동이 타임스탬프로 저장되어 있다면 "오늘"은 시간대에 따라 달라집니다. 날짜로 절단하기 전에 변환하십시오.

UTC로 저장하고 비교하거나, 먼저 사용자의 현지 시간대로 변환합니다.
Postgres에서는 event_ts AT TIME ZONE 'UTC' AT TIME ZONE user_tz를 사용한 다음 날짜로 형변환합니다.
UTC 타임스탬프에 단순히 ::date를 적용하면 저녁 이벤트가 잘못된 달력 날짜에 배치될 수 있습니다.

이 내용을 언급하면 흔한 함정에 관한 후속 질문을 미리 막을 수 있습니다.

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

성능과 마무리

자주 새로 고치는 대시보드에서는 이 쿼리가 사용자별로 한 번의 순회로 실행됩니다. 빠르게 유지하려면 다음을 따르십시오.

(user_id, login_date)에 인덱스를 만듭니다.
선택적으로 입력을 최근 날짜로 제한합니다 — 현재 연속 기록에는 그 길이보다 오래된 데이터가 포함될 수 없으므로, 예를 들어 최근 400일의 이동 범위면 충분합니다.
매우 자주 읽는다면 일별 데이터를 연속 기록 테이블에 구체화합니다.

빠른 확인

현재 시점을 기준으로 앵커를 설정하는 방법을 이해했는지 확인하십시오.

복습

오늘 기준 현재 활성 연속 기록을 계산하려면 다음과 같이 합니다.

login_date - ROW_NUMBER() 앵커로 구간을 만듭니다.
각 사용자에서 streak_end가 가장 큰 최신 구간을 선택합니다.
streak_end가 오늘(또는 유예 기간이라면 어제)인 경우에만 해당 구간의 길이를 반환하고, 그렇지 않으면 0을 반환합니다.
비활성 계정을 포함하려면 사용자 목록에 LEFT JOIN하고, 날짜로 절단하기 전에 시간대를 처리합니다.

이제 구간 탐지, 최장 연속 기록, N개 연속 조건, 현재 유효한 연속 기록을 아우르는 전체 연속 기록 분석 도구를 갖추었습니다.

자주 묻는 질문