日付とステータスの変化で島を作る
同じステータスが連続する期間をグループ化します。サブスクリプション状態でよく使われる問題です。
「日付とステータスの変化で島を作る」はCoddyKit上の無料SQL Interview Prepレッスンです。 これはレッスン4/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Interview Prep学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Interview Prepコースには全4レッスンが含まれています。
変化する値で定義されるアイランド
ギャップとアイランドの中で最も実務に近いバリエーションは、同じステータスを共有する連続した行をまとめ、ノイズの多いイベントログを明確な状態期間に整理するものです。典型的な質問は、「サブスクリプションのイベントログから、ユーザーが各ステータスにとどまっていた連続期間ごとに1行を返してください」です。
ここで隣接とは、「値が1異なる」ことではありません。直前の行からステータスが変わっていないことを意味します。ステータスが切り替わった瞬間に新しいアイランドが始まります。この場合は、単純な行番号のテクニックよりも LAG ベースの方法が適しています。
サブスクリプションのサンプル
1人のユーザーについて、日付順に並べた sub_events テーブルを考えます。
- 2026-01-01 active
- 2026-02-01 active
- 2026-03-01 paused
- 2026-04-01 active
- 2026-05-01 active
期待される出力は、3つのステータス期間です。active は1月から2月、paused は3月、active は4月から5月です。2つの active の期間は、paused の期間によって中断されるため、別々のアイランドであることに注意してください。同じステータスでも連続していなければ、異なるアイランドになります。
CREATE TABLE sub_events (
user_id INT, status TEXT, event_date DATE
);
INSERT INTO sub_events VALUES
(1,'active','2026-01-01'),(1,'active','2026-02-01'),
(1,'paused','2026-03-01'),(1,'active','2026-04-01'),
(1,'active','2026-05-01');ステータスが変化した位置にフラグを立てる
LAG を使って、各行のステータスと直前の行のステータスを比較します。異なっている場合（または最初の行で直前の値が NULL の場合）は、新しいアイランドが始まります。変化があれば1、そうでなければ0を出力します。
ユーザー内では日付順を厳密に指定してください。このデータの変化フラグは 1,0,1,1,0 となり、3つの期間の境界を示します。
SELECT
user_id, status, event_date,
CASE
WHEN status = LAG(status)
OVER (PARTITION BY user_id ORDER BY event_date)
THEN 0 ELSE 1
END AS is_change
FROM sub_events;累積和を期間キーにする
これまでと同じように、変化フラグの累積和を取ると、各ステータス期間内で一定のグループキーが得られます。このデータでは 1,1,2,3,3 です。それぞれ異なるキーが、1つの連続期間を表します。
ステータスは1ずつ増える数値ではないため、行番号の差分テクニックはここでは機能しません。隣接を「値が変わっていない」ことと定義する場合は、LAG と累積和の組み合わせが適切です。
WITH flagged AS (
SELECT user_id, status, event_date,
CASE WHEN status = LAG(status)
OVER (PARTITION BY user_id ORDER BY event_date)
THEN 0 ELSE 1 END AS is_change
FROM sub_events
)
SELECT user_id, status, event_date,
SUM(is_change)
OVER (PARTITION BY user_id ORDER BY event_date) AS grp
FROM flagged;ステータス期間にまとめる
user_id、status、累積和のキーで GROUP BY し、各期間の範囲を報告します。期間内では status が一定なので、GROUP BY に status を含めるのは安全です。また、集約関数を使わずに status を選択できます。
結果は正確に3行です。active は01-01から02-01、paused は03-01から03-01、active は04-01から05-01になります。
WITH flagged AS (
SELECT user_id, status, event_date,
CASE WHEN status = LAG(status)
OVER (PARTITION BY user_id ORDER BY event_date)
THEN 0 ELSE 1 END AS chg
FROM sub_events
),
keyed AS (
SELECT user_id, status, event_date,
SUM(chg) OVER (PARTITION BY user_id ORDER BY event_date) AS grp
FROM flagged
)
SELECT user_id, status,
MIN(event_date) AS period_start,
MAX(event_date) AS period_end
FROM keyed
GROUP BY user_id, status, grp
ORDER BY user_id, period_start;イベントから半開区間へ
面接で問われやすい微妙なポイントです。イベントの日付はステータスが開始した時点を示し、期間が実際に終了するのは同じステータスの最後のイベント日ではなく、次のステータスが始まる時点です。正しい期間の終端は、多くの場合、次の期間の開始時点として、半開区間 [start, next_start) で表します。
折りたたんだ期間に対してLEADを適用して次の期間の開始時点を計算し、最後の期間は終了時点なし（NULL または 'current'）のままにします。
WITH periods AS (
-- output of the previous collapse step
SELECT user_id, status, period_start FROM collapsed
)
SELECT user_id, status, period_start,
LEAD(period_start)
OVER (PARTITION BY user_id ORDER BY period_start)
AS period_end_exclusive
FROM periods;連続して繰り返されるステータスの扱い
ログに、active、active、active のように、その間に変化がない冗長な行が含まれている場合はどうでしょうか。繰り返し行の変更フラグは 0 なので、累積和によって自動的に 1 つのアイランドにまとめられます。これが望ましい動作です。連続する同一ステータスは、1 つの期間にまとめられます。
このように繰り返しを自然に重複排除できることは、変更フラグ方式の重要な利点であり、面接官にぜひ伝える価値があります。
時間のギャップで期間を区切る場合
「同じステータス」だけでは不十分な場合もあります。ステータスが同じでも、大きな時間のギャップがあれば期間を区切るべき場合があります。たとえば、1月にactiveになり、6か月間空いてから再びactiveになった場合は、2つの期間として数えることがあります。
変更フラグに2つ目の条件を追加します。ステータスが変わった場合、または前回のイベントからの経過時間がしきい値を超えた場合に、新しいアイランドを開始します。これにより、両方の隣接ルールをすっきり組み合わせられます。
CASE
WHEN status = LAG(status)
OVER (PARTITION BY user_id ORDER BY event_date)
AND event_date - LAG(event_date)
OVER (PARTITION BY user_id ORDER BY event_date) <= 31
THEN 0 ELSE 1
END AS is_change状態の切り替え回数を数える
自然な追加質問として、「このユーザーは何回ステータスを切り替えましたか？」があります。それは変更フラグの合計から最初の1つを引くだけです（最初のフラグは切り替えではなく、初期状態を示すためです）。
言い換えれば、期間の数から 1 を引いた値です。累積和で作ったキーにはすでにこの情報が含まれているため、期間用に構築したのと同じ仕組みで答えが得られます。
WITH flagged AS (
SELECT user_id,
CASE WHEN status = LAG(status)
OVER (PARTITION BY user_id ORDER BY event_date)
THEN 0 ELSE 1 END AS chg
FROM sub_events
)
SELECT user_id, SUM(chg) - 1 AS status_switches
FROM flagged GROUP BY user_id;ここで自己結合より優れている理由
ステータス期間を自己結合で求める場合、各行を隣接する行と組み合わせて変化を検出し、その後で境界をつなぎ合わせる必要があります。これはエラーが起きやすい複数ステップの処理で、3つ以上の期間になると扱いにくくなります。
LAG-フラグ-累積和-GROUP BYのパイプラインなら、結合を使わず、1回の処理で任意の数の期間を扱えます。この違い、つまり線形の単一パスと二次の自己結合を説明できることこそ、シニア向け面接で評価される思考力です。
再利用できるテンプレート
この4句のテンプレートを覚えてください。CASEの隣接判定だけを変更すれば、ステータス・アイランド問題全体を解決できます。
- flag：LAGを使うCASEで新しいアイランドを検出します。
- key：変更フラグの累積SUMを、パーティション分割して順序付けします。
- collapse：パーティション列、ステータス、キーでGROUP BYします。
- interval（任意）：半開区間の期間終端にLEADを使います。
同じ骨格で、連続する整数、日付、ステータスを扱えます。変わるのはCASE条件だけです。
クイックチェック
ステータス・アイランドのグループ化ルールを理解できているか確認します。
まとめ：ステータスと日付のアイランド
これで、ギャップとアイランドの問題の中でも最も応用的なパターンを解けるようになりました。
- 隣接性 = 前の行からステータスが変わっていないこと。変更時は
LAGでフラグを変えます。 - 変更フラグの累積和を取り、期間ごとのグループキーにします。
GROUP BY user_id, status, keyでまとめ、期間の範囲を取得します。- 半開区間の終端には
LEADを使い、大きな時間のギャップで区切るにはフラグを拡張します。 - 同じ内容の行は自動的にまとめられ、切り替え回数も同じフラグから求められます。
- 再利用できる1つのテンプレートで、整数、日付、ステータスを扱えます。変わるのはCASEだけです。
これでギャップとアイランドのコースは完了です。SQL面接でシニアレベルの思考力を示す、信頼性の高い題材です。
よくある質問
「日付とステータスの変化で島を作る」レッスンは無料ですか？
はい。「日付とステータスの変化で島を作る」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Interview Prepコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Interview Prepコースには全4レッスンが含まれています。
「日付とステータスの変化で島を作る」で何を学びますか？
同じステータスが連続する期間をグループ化します。サブスクリプション状態でよく使われる問題です。 ブラウザで直接実行するハンズオンコードでSQL Interview Prepを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
SQL Interview Prepを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのSQL Interview Prepは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン4/4です。
「日付とステータスの変化で島を作る」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このSQL Interview Prepレッスンでコードを書いて実行できますか？
はい。すべてのSQL Interview Prepレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。
このコースのすべてのレッスン
- ギャップと島問題を見分ける
- 行番号の差分トリック
- 系列内のギャップを見つける
- 日付とステータスの変化で島を作る