SQL Interview Prep · レッスン

今日時点の現在の連続記録

Q: 「今日時点の現在の連続記録」で何を学びますか？

継続中の連続記録を計算し、途切れたらリセットします。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

継続中の連続記録を計算し、途切れたらリセットします。

「今日時点の現在の連続記録」はCoddyKit上の無料SQL Interview Prepレッスンです。これはレッスン4/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。

プロダクトに関する問い

連続日数機能（DuolingoやSnapchatなど）では、過去最長の連続日数ではなく現在の連続日数が必要です。面接での問いは、「ユーザーごとに、今日まで何日連続でアクティブですか？今日活動していなければ0にリセットしてください」です。

ここにはひとひねりあります。連続日数は今日を基準にする必要があります（または猶予ルールのもとでは昨日を基準にします）。すでに学んだギャップとアイランドの基礎を使って組み立てましょう。

アクティブの定義

まず、面接官にルールを確認してください。次の判断によってクエリが変わります。

連続日数には今日の活動が必要ですか。それとも昨日でも許容しますか（猶予期間）。
1日に複数のイベントがある場合、1日にまとめますか。
「1日」を定義するタイムゾーンはどれですか。

ここでは、アクティブな1日につき1行があり、今日または昨日を含む場合に連続日数が継続中であると仮定します。

もう一度アイランドを作る

アンカーを再利用します。ユーザーごとに日付順で番号を付けて差を取ります。連続する各区間は同じアンカーを共有します。現在の連続日数とは、単に最終日が今日または昨日であるアイランドです。

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;

現在の連続日数を求める完全なクエリ

すべての層を組み立てます。番号を付け、アイランドを作り、ユーザーごとに最新のものを選び、最後にrecencyを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を使い、その後date型にキャストします。
UTCのタイムスタンプに単純に::dateを適用すると、夕方のイベントが誤った暦日に入る可能性があります。

この点に触れておくと、面接でよくある追加質問を先回りできます。

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

パフォーマンスとまとめ

頻繁に更新するダッシュボードでは、このクエリをユーザーごとに1回の走査で実行できます。高速性を保つには、次の点に注意してください。

(user_id, login_date)にインデックスを作成します。
必要に応じて入力を最近の日付に限定します。現在の連続日数に、それ自体の長さより古いデータが含まれることはないため、たとえば直近400日を対象にすれば十分です。
非常に頻繁に読み取る場合は、日次データを連続日数テーブルにマテリアライズします。

確認問題

アンカーが「現在」に固定されることを理解しているか確認してください。

まとめ

今日時点の現在の連続日数を計算するには、次のようにします。

login_date - ROW_NUMBER()のアンカーを使ってアイランドを作成します。
streak_endの最大値を使い、ユーザーごとに最新のアイランドを選択します。
streak_endが今日（猶予期間を設ける場合は昨日）である場合だけ、その長さを返します。それ以外は0を返します。
アクティブでないアカウントも含めるにはusersをLEFT JOINし、日付に切り詰める前にタイムゾーンを処理します。

これで、連続区間の検出、最長連続日数、条件を満たすN行連続、継続中の連続日数という、連続日数分析のツールキットをすべて使えるようになりました。

よくある質問