0Pricing
SQL Interview Prep · レッスン

ユーザーごとの最長連続記録

各グループ内で連続する期間の最大長を計算します。

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

問題

連続する日を検出した後によく出される追加質問です。「ユーザーごとに、連続してアクティブだった最長の記録はどれですか？」プロダクトチームやグロースチームは、エンゲージメントを測るためにこの指標を頻繁に利用します。

各区間を特定する方法はすでに分かっています。ここで新たに必要なのは、ユーザーごとに最長区間の日数を見つけ、さらにその最長記録の日付も返すことです。このレッスンでは、ギャップとアイランドの骨格をそのまま発展させます。

アイランドの作り方を振り返る

前のレッスンでは、各連続区間のグループ化にlogin_date - ROW_NUMBER()をアイランドの基準値として使いました。ユーザーごとに複数のアイランドが存在する場合があるため、まずアイランドごとに1行を作り、その後ユーザーごとに1行へ集約します。

この2層構成を覚えておいてください。最初にアイランドを作り、次にアイランドを集約します。

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;

アイランドごとに1行

各アイランドを、長さと日付範囲を持つ1行の要約にまとめます。ユーザーと基準値でグループ化し、必要な指標を計算します。

このCTEにはislandsという名前を付け、次の層から読みやすくします。

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;

シンプルな回答：長さのMAX

面接官が長さだけを求めているなら、最後の手順は1行で書けます。アイランドをユーザーごとにグループ化し、長さの最大値を取ります。

開始日や終了日が必要ない場合には、これが最も簡潔な回答です。

-- ...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;

日付も返す

面接官から「その連続記録がいつ発生したかも示してください」と追加されることがあります。単純なMAXだけでは、どのアイランドが最長だったかは分かりません。ユーザーごとにアイランドを順位付けし、順位1のものを残す必要があります。

長さの降順でROW_NUMBERを使って順位付けすると、各ユーザーの最長記録に順位1が付きます。同率の場合に備えて、決定的に順位を決められるタイブレーカーも追加します。

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

順位付けして絞り込む

順位付けをCTEで囲み、その後でrnk = 1に絞り込みます。ウィンドウ関数はWHEREで直接絞り込めないため、この追加の層が必須です。

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とROW_NUMBER

ユーザーに同じ最長日数の連続記録が2つあり、面接官が両方の結果を求めている場合はどうでしょうか。ROW_NUMBERをRANKに置き換え、rnk = 1を残します。

  • ROW_NUMBER — ユーザーごとに必ず1つの勝者を返します（タイブレーカーを追加しない限り、同率時の選択は任意です）。
  • RANK — 同率の最長記録はすべて順位1になり、すべて残ります。

どちらの動作が必要かを確認してください。エッジケースへの注意深さを示せます。

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

実例

ユーザー7が1月1日〜4日にログインし、次に1月10日〜11日、その後1月20日〜23日にログインしたとします。アイランドは長さ4、2、4の3つです。最長の長さは4で、同率になっています。

  • ROW_NUMBERとタイブレーカーstreak_startを使う場合：1月1日〜4日の区間だけを返します。
  • RANKを使う場合：1月1日〜4日と1月20日〜23日の両方を返します。

これを口頭で説明すれば、同率のケースを検討したことを示せます。

ログインしていないユーザーの扱い

面接官から「一度もログインしていないユーザーはどうしますか？」と聞かれることがあります。そのようなユーザーはloginsに行が存在しないため、結果から消えてしまいます。連続日数を0として表示する必要がある場合は、完全なusersテーブルをLEFT JOINし、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;

パフォーマンスに関する注意点

このパターンでは、データを順序付きで1回走査した後、グループ化します。高速性を保つには、次の点に注意してください。

  • ウィンドウのORDER BYでのソートを避けられるよう、(user_id, login_date)にインデックスを作成します。
  • ソースに1日あたり複数のイベントがある場合は、早い段階で重複を排除します。
  • ORDER BYでlogin_dateを関数でラップするのは避けます。インデックスが使えなくなる可能性があります。

非常に大きなテーブルでは、この方法はどのような自己結合アプローチよりも大幅に高速です。

面接での完全な回答

各ユーザーの最長連続日数とその日付を返す、完全で洗練されたクエリを示します。ホワイトボードに書くべきバージョンです。

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;

確認問題

要件に合う適切なツールを選んでください。

まとめ

ユーザーごとの最長連続日数を計算するには、次のようにします。

  • login_date - ROW_NUMBER()のアンカーを使ってアイランドを作成します。
  • 各アイランドを、長さと日付範囲に集約します。
  • 長さだけが必要な場合は、ユーザーごとにグループ化したMAX(streak_len)を使います。
  • 日付も必要な場合は、ユーザーごとにアイランドを順位付けし、ランク1だけを残します。同率を含めるにはRANKを、勝者を1つに絞るにはROW_NUMBERを使います。
  • ユーザーごとの連続日数が0のケースも表示するには、usersをLEFT JOINします。

次は、条件を満たすN行連続の検出です。

よくある質問

「ユーザーごとの最長連続記録」レッスンは無料ですか？

はい。「ユーザーごとの最長連続記録」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。

「ユーザーごとの最長連続記録」で何を学びますか？

各グループ内で連続する期間の最大長を計算します。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Interview Prepを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン2/4です。

「ユーザーごとの最長連続記録」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Interview Prepレッスンでコードを書いて実行できますか？

はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

  1. 連続する暦日を検出する
  2. ユーザーごとの最長連続記録
  3. 条件を満たすN行の連続
  4. 今日時点の現在の連続記録
← SQL Interview Prepに戻る