SQL イベントモデリング — アトリビューション分析の応用

QUESTION 6

セッション化 — LAG で前イベント時刻を取得し、30分超ギャップで条件付き累積SUM によりセッションIDを採番

LAGSUM OVERセッション化Window関数

前提知識 ▼

イベントモデリングで最頻出の前処理がセッション化（Sessionization）です。同一ユーザーの連続するイベントを、一定の非アクティブ時間（例: 30分）で区切ってセッションに束ねます。Window関数 LAG で直前イベント時刻を取得し、ギャップ判定の0/1フラグを累積SUM することでセッションIDが採番できます。

-- LAG(): 同一PARTITION内で1行前の値を取得
LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time)

-- 累積SUM でセッションIDを採番（境界フラグの累積 = セッション番号）
SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time)

セッション化の3段階パターン：(1) LAG で前時刻を取得 → (2) ギャップ判定で 0/1 フラグ化 → (3) 累積SUM でセッションIDに変換。この3段CTE は実務イベント分析のもっとも基本的なワークホースであり、後段のセッション長・ページ遷移分析・直帰率算出すべての出発点になります。

問題 ▼

user_events テーブルから、同一ユーザーの直前イベントから30分超のギャップがあれば新しいセッションとして区切り、ユーザーごとにセッション数・総イベント数・1セッションあたり平均イベント数を集計してください。出力列は user_id, session_count, total_events, avg_events_per_session（小数第1位）、user_id 昇順で返してください。

使用テーブル

► user_events（9行）

user_id	event_time
1	2024-01-10 10:00
1	2024-01-10 10:05
1	2024-01-10 10:20
1	2024-01-10 12:00
1	2024-01-10 12:10
2	2024-01-10 09:00
2	2024-01-10 09:15
2	2024-01-10 11:30
3	2024-01-11 14:00

期待出力 ▼

user_id	session_count	total_events	avg_events_per_session
1	2	5	2.5
2	2	3	1.5
3	1	1	1.0

模範解答コード ▼

WITH gapped AS (                                              -- ① LAG で直前イベント時刻を取得
  SELECT
    user_id,
    event_time,
    LAG(event_time) OVER (PARTITION BY user_id
                          ORDER BY event_time) AS prev_time
  FROM   user_events
),
flagged AS (                                              -- ② 30分超ギャップを新セッション(1)、それ以外を継続(0)
  SELECT
    user_id, event_time, prev_time,
    CASE
      WHEN prev_time IS NULL THEN 1                          -- 最初のイベントは新セッション
      WHEN event_time - prev_time > INTERVAL '30 minutes' THEN 1
      ELSE 0
    END AS is_new_session
  FROM   gapped
),
sessionized AS (                                          -- ③ 累積SUM でセッションIDを採番
  SELECT
    user_id, event_time,
    SUM(is_new_session) OVER (PARTITION BY user_id
                              ORDER BY event_time) AS session_id
  FROM   flagged
)
SELECT
  user_id,
  COUNT(DISTINCT session_id)                              AS session_count,
  COUNT(*)                                                  AS total_events,
  ROUND(COUNT(*)::numeric / COUNT(DISTINCT session_id), 1) AS avg_events_per_session
FROM   sessionized
GROUP BY user_id
ORDER BY user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE gapped
  2. CTE flagged
  3. CTE sessionized
  4. 外側クエリ
  */

解説（テーブル変化・ポイント） ▼

WITH gapped AS (
  SELECT user_id, event_time,
    LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time
  FROM   user_events
),
flagged AS (
  SELECT user_id, event_time, prev_time,
    CASE WHEN prev_time IS NULL THEN 1
         WHEN event_time - prev_time > INTERVAL '30 minutes' THEN 1 ELSE 0 END AS is_new_session
  FROM gapped
),
sessionized AS (
  SELECT user_id, event_time,
    SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY event_time) AS session_id
  FROM flagged
)
SELECT user_id,
  COUNT(DISTINCT session_id) AS session_count,
  COUNT(*) AS total_events,
  ROUND(COUNT(*)::numeric / COUNT(DISTINCT session_id),1) AS avg_events_per_session
FROM   sessionized
GROUP BY user_id
ORDER BY user_id;

LEGEND ▼

データ取得・読込対象

① FROM user_events（9行） ▼

FROM user_events9行のイベントログを読み込みます。3ユーザーぶんの時系列イベントです。同一ユーザー内で時系列順にギャップを評価していくのがセッション化の出発点です。

1 / 5

user_id	event_time
1	2024-01-10 10:00
1	2024-01-10 10:05
1	2024-01-10 10:20
1	2024-01-10 12:00
1	2024-01-10 12:10
2	2024-01-10 09:00
2	2024-01-10 09:15
2	2024-01-10 11:30
3	2024-01-11 14:00

9行読込（3ユーザー × 時系列イベント）

学習ポイント

LAG + 累積SUM はセッション化の黄金パターン：LAG で前時刻を取得 → CASE でギャップ判定の 0/1 フラグ → SUM OVER で累積する3段階は、Postgres・BigQuery・Snowflake すべてで同じ構文で動作します。この同じパターンが「離脱前最終操作の検出」「連続イベント数のカウント」「画面遷移パスの抽出」など多数の派生分析に応用できます。

PARTITION BY と ORDER BY の役割：PARTITION BY user_id は「ユーザー単位で独立してWindow関数を評価する」指定で、user1 の LAG が user2 のデータを混入させないことを保証します。ORDER BY event_time は「同じユーザー内では時系列順に並べる」指定で、これがないと LAG の値が予測不能になります。Window関数は PARTITION + ORDER の正しさが結果の正しさそのものです。

INTERVAL を使った時刻演算：PostgreSQL では timestamp - timestamp = interval となり、INTERVAL リテラルとの比較も自然です。BigQuery では TIMESTAMP_DIFF(t1, t2, MINUTE) > 30 形式に置き換える必要があるなど、方言の差はあるものの「ギャップ判定」の概念は普遍です。

アンチパターン

PARTITION BY を省略する：LAG(event_time) OVER (ORDER BY event_time) と書くと、全行が1つのパーティションになり「user2 の1行目の prev_time が user1 の最終行になる」誤動作が発生します。ユーザーを跨いだセッション化は意味をなさず、PARTITION BY user_id は必須です。

累積SUM の代わりに DENSE_RANK を使う：DENSE_RANK() OVER(PARTITION BY user_id, is_new_session ORDER BY event_time) でセッションIDを作ろうとする実装が散見されますが、これでは「is_new_session=0 の行が全て同じグループ」になり破綻します。累積SUM は単調増加するため、フラグの累積=ID として安全に使えます。

実務コラム：セッション化が拓く分析の世界

セッション化ができれば、次はセッション単位の指標が無数に算出できます。セッション長（最終イベント時刻 − 最初のイベント時刻）、セッション内ページビュー数、直帰セッション（1イベントのみのセッション）の割合、コンバージョン到達セッション数など。「ユーザー単位のCV率」と「セッション単位のCV率」では数値の意味も改善施策の方向性も大きく異なるため、両軸での集計はプロダクト分析の基本です。今回のクエリは Google Analytics のセッション定義（デフォルト30分非アクティビティ）を SQL で完全に再現するロジックでもあります。

QUESTION 7

アトリビューション — FIRST_VALUE + LAG で初回流入チャネルと購入直前タッチを抽出する

FIRST_VALUELAGアトリビューションマーケ分析

前提知識 ▼

マーケティング分析の中核がアトリビューション（Attribution）です。あるコンバージョン（購入）が「どのチャネルの貢献によるものか」を割り当てる分析で、「初回タッチ」「ラストタッチ」「マルチタッチ」などのモデルがあります。Window関数で各ユーザーの行動履歴に「いつでも参照できる初回値」「直前の値」をぶら下げるのが基本テクニックです。

-- FIRST_VALUE: PARTITION 内の先頭行の値を全行に伝搬
FIRST_VALUE(channel) OVER (PARTITION BY user_id ORDER BY event_time)

-- LAG: 直前1行の値を取得（購入直前タッチに利用）
LAG(channel) OVER (PARTITION BY user_id ORDER BY event_time)

FIRST_VALUE のフレーム既定値に注意：PostgreSQL の Window関数で ORDER BY を指定すると、フレームの既定は RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW です。FIRST_VALUE は「フレームの先頭」の値を返すため、ORDER BY 指定があればパーティションの先頭値を全行に伝搬する挙動になり、初回タッチ抽出にそのまま使えます。

問題 ▼

user_events テーブルから、各購入(purchase)イベントに対して「そのユーザーの初回タッチチャネル(first_touch)」と「購入直前のチャネル(last_touch)」を抽出してください。出力列は user_id, first_touch, last_touch, purchase_time、user_id 昇順で返してください。購入のないユーザーは出力に含めません。

使用テーブル

► user_events（9行）

user_id	event_time	event_type	channel
1	2024-01-10 10:00	view	google_ads
1	2024-01-10 11:00	view	organic
1	2024-01-10 12:00	click	facebook_ads
1	2024-01-10 13:00	purchase	direct
2	2024-01-11 09:00	view	organic
2	2024-01-11 10:00	purchase	email
3	2024-01-11 11:00	view	google_ads
3	2024-01-11 12:00	click	google_ads
4	2024-01-12 09:00	view	twitter_ads

期待出力 ▼

user_id	first_touch	last_touch	purchase_time
1	google_ads	facebook_ads	2024-01-10 13:00
2	organic	organic	2024-01-11 10:00

模範解答コード ▼

WITH attributed AS (                                  -- 全イベントに初回/直前チャネルを付与
  SELECT
    user_id,
    event_time,
    event_type,
    channel,
    FIRST_VALUE(channel) OVER (
      PARTITION BY user_id ORDER BY event_time
    )                                                       AS first_touch,
    LAG(channel) OVER (
      PARTITION BY user_id ORDER BY event_time
    )                                                       AS last_touch
  FROM   user_events
)
SELECT
  user_id,
  first_touch,
  last_touch,
  event_time AS purchase_time
FROM   attributed
WHERE  event_type = 'purchase'                              -- 購入行のみ抽出
ORDER BY user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE attributed
  2. 外側クエリ
  */

解説（テーブル変化・ポイント） ▼

WITH attributed AS (
  SELECT user_id, event_time, event_type, channel,
    FIRST_VALUE(channel) OVER (
      PARTITION BY user_id ORDER BY event_time
    ) AS first_touch,
    LAG(channel) OVER (
      PARTITION BY user_id ORDER BY event_time
    ) AS last_touch
  FROM   user_events
)
SELECT user_id, first_touch, last_touch,
  event_time AS purchase_time
FROM   attributed
WHERE  event_type = 'purchase'
ORDER BY user_id;

LEGEND ▼

データ取得・読込対象

① FROM user_events（9行） ▼

FROM user_events9行のイベントログ（4ユーザー × view/click/purchase × 各種チャネル）を読み込みます。user3・user4 は購入未到達ですが、Window関数は全行に対して評価されます。

1 / 6

user_id	event_time	event_type	channel
1	10:00	view	google_ads
1	11:00	view	organic
1	12:00	click	facebook_ads
1	13:00	purchase	direct
2	09:00	view	organic
2	10:00	purchase	email
3	11:00	view	google_ads
3	12:00	click	google_ads
4	09:00	view	twitter_ads

9行読込（4ユーザー × 複数チャネル）

学習ポイント

FIRST_VALUE と LAG の使い分け：FIRST_VALUE は「パーティションの先頭値（不変）」を全行に伝搬します。LAG は「直前行の値（相対参照）」を返します。初回タッチは前者、ラストタッチ（購入直前）は後者がそれぞれぴったり対応します。両方を同じCTEで取得し、WHERE で購入行に絞れば1パスで完成します。

Window関数を WHERE と別CTEに分ける理由：SQL の論理評価順は FROM → WHERE → GROUP BY → SELECT で、Window関数は SELECT 段階で評価されます。WHERE で先に purchase 行に絞ると、LAG の参照対象が view/click ではなく次の purchase になってしまうため、Window関数を計算する CTE と WHERE を別段階に分けるのが鉄則です。

ラストタッチ = LAG(channel) で十分か：厳密には「同一セッション内のラストタッチ」を見るべきケースもあります。長期に渡るリードのアトリビューションでは、30日前の初回タッチを「初回」とすべきか、直近セッションの初回タッチとすべきかはビジネス定義によります。FIRST_VALUE のフレームを RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW に絞れば「直近7日間の初回タッチ」も表現可能です。

アンチパターン

WHERE で先に絞ってから Window 関数を計算する：WHERE event_type='purchase' を CTE 内に書くと、view・click 行が消えるため LAG の対象がなくなり、last_touch が常に NULL（直前購入のない最初の購入では）または「2回目の購入の直前=1回目の購入」になります。Window関数の評価は WHERE の後であることを忘れてはいけません。

FIRST_VALUE にフレーム指定なしで安心する：FIRST_VALUE(channel) OVER (PARTITION BY user_id) のように ORDER BY を省略すると、フレームの並びが不定になり「先頭行」がランダム化されます。FIRST_VALUE には必ず ORDER BY を併記してください。LAST_VALUE はさらに罠が深く、デフォルトのフレーム範囲が CURRENT ROW までのため ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING の明示が必要です。

実務コラム：マルチタッチアトリビューションへの拡張

FIRST_VALUE/LAG による初回・直前タッチアトリビューションはシングルタッチモデルと呼ばれ、シンプルですが「中間タッチが評価されない」という弱点があります。実務ではより高度なマルチタッチアトリビューション（線形・時間減衰・U字型など）が用いられます。例えば「全タッチに等しい貢献を割り当てる線形モデル」は、CTE で全タッチ数 N を求めて貢献度 = 1/N と計算するだけで実装可能です。Window関数の COUNT(*) OVER(PARTITION BY user_id) を組み合わせれば、各購入に至るまでの全タッチへの貢献度配分が SQL のみで完結します。セッション化と組み合わせれば「セッション単位での U字型アトリビューション」など実務レベルの分析パイプラインも構築できます。

QUESTION 8

ファネルステップ間時間 — LAG + EXTRACT(EPOCH) で平均コンバージョン所要時間を計算

LAGEXTRACT EPOCHファネル分析所要時間

前提知識 ▼

基礎編で「ファネル到達数」「順序検証」を扱いましたが、実務では「ステップ間の所要時間」もコンバージョンの質を測る重要指標です。view → click の平均時間が長すぎる場合は決断材料の不足、click → purchase が長い場合は決済フローのフリクションが疑われます。

-- 直前イベントとの時間差を秒単位で取得
EXTRACT(EPOCH FROM (event_time - prev_event_time))

-- 60で割って分単位、ROUND で小数第1位
ROUND(AVG(...) / 60, 1)

EXTRACT(EPOCH FROM interval) — interval を秒に変換：timestamp - timestamp は interval 型を返し、EPOCH を抽出すると秒単位の数値になります。MySQL の TIMESTAMPDIFF(SECOND, t1, t2)、BigQuery の TIMESTAMP_DIFF(t2, t1, SECOND) と同じ役割を果たします。秒からの単位変換は /60 → 分、/3600 → 時間 です。

問題 ▼

user_events テーブルから、各ユーザーの連続イベント（view → click、click → purchase）の所要時間を計算し、遷移種別ごとの件数と平均分数を出力してください。出力列は transition, event_count, avg_minutes（小数第1位）、view → click、click → purchase の順で返してください。

使用テーブル

► user_events（8行）

user_id	event_type	event_time
1	view	2024-01-10 10:00
1	click	2024-01-10 10:10
1	purchase	2024-01-10 11:00
2	view	2024-01-11 09:00
2	click	2024-01-11 09:30
3	view	2024-01-11 14:00
3	click	2024-01-11 14:05
3	purchase	2024-01-11 14:35

期待出力 ▼

transition	event_count	avg_minutes
view -> click	3	15.0
click -> purchase	2	40.0

模範解答コード ▼

WITH ordered_events AS (                              -- 直前イベントの種別と時刻を Window 関数で取得
  SELECT
    user_id,
    event_type,
    event_time,
    LAG(event_type) OVER (
      PARTITION BY user_id ORDER BY event_time
    )                                                       AS prev_event_type,
    LAG(event_time) OVER (
      PARTITION BY user_id ORDER BY event_time
    )                                                       AS prev_event_time
  FROM   user_events
)
SELECT
  prev_event_type || ' -> ' || event_type AS transition,
  COUNT(*)                                                   AS event_count,
  ROUND(
    AVG(EXTRACT(EPOCH FROM (event_time - prev_event_time))) / 60
  , 1)                                                       AS avg_minutes
FROM   ordered_events
WHERE  (prev_event_type = 'view'     AND event_type = 'click')
    OR (prev_event_type = 'click'    AND event_type = 'purchase')
GROUP BY prev_event_type, event_type
ORDER BY CASE prev_event_type WHEN 'view' THEN 1 ELSE 2 END;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE ordered_events
  2. 外側クエリ
  */

解説（テーブル変化・ポイント） ▼

WITH ordered_events AS (
  SELECT user_id, event_type, event_time,
    LAG(event_type) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event_type,
    LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event_time
  FROM   user_events
)
SELECT prev_event_type || ' -> ' || event_type AS transition,
  COUNT(*) AS event_count,
  ROUND(AVG(EXTRACT(EPOCH FROM (event_time - prev_event_time))) / 60, 1) AS avg_minutes
FROM   ordered_events
WHERE (prev_event_type = 'view' AND event_type = 'click')
   OR (prev_event_type = 'click' AND event_type = 'purchase')
GROUP BY prev_event_type, event_type
ORDER BY CASE prev_event_type WHEN 'view' THEN 1 ELSE 2 END;

LEGEND ▼

データ取得・読込対象

① FROM user_events（8行） ▼

FROM user_events3ユーザーのファネル系イベントを読み込みます。各ユーザーがどのステップに何分かけて遷移したかを計算するのが目的です。

1 / 5

user_id	event_type	event_time
1	view	10:00
1	click	10:10
1	purchase	11:00
2	view	09:00
2	click	09:30
3	view	14:00
3	click	14:05
3	purchase	14:35

8行読込（3ユーザー × ファネルイベント）

学習ポイント

EXTRACT(EPOCH FROM interval) で秒単位の数値化：PostgreSQL の timestamp - timestamp は interval 型を返しますが、AVG や ROUND と組み合わせるには数値化が必要です。EXTRACT(EPOCH FROM ...) は interval を秒数の DOUBLE PRECISION に変換し、AVG・SUM などの集計関数で素直に使えるようになります。

「平均」だけでなく分布を見るべき場面：平均は外れ値に弱く、極端に長い所要時間（数日経って戻ってきたユーザーなど）に引きずられます。実務では PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ...) で中央値、PERCENTILE_CONT(0.95) で 95パーセンタイルを見るのが王道です。平均と中央値の乖離が大きい場合は分布の歪みを疑うべきサインです。

遷移文字列の生成と並び順制御：prev_event_type || ' -> ' || event_type で見やすい遷移ラベルを生成しています。アルファベット順だと "click -> purchase" が "view -> click" より前に来るため、ORDER BY に CASE を挟んでファネル順を再現するのが実務的なテクニックです。

アンチパターン

interval をそのまま AVG に渡す：PostgreSQL では AVG(t2 - t1) も interval を返すため動作しますが、ROUND との合成や別の単位（分・時間）への変換が面倒になります。EXTRACT(EPOCH FROM ...) で秒に変換しておくと、ロジックがどのRDBMSでも一貫します。

セッションを跨いだ遷移時間を含めてしまう：同じユーザーが30日前に view し、今日になって click した場合、LAG は素直に30日のギャップを返します。意味のある所要時間とは言えません。実務では 「同一セッション内の遷移のみ」「24時間以内の遷移のみ」といった上限フィルタが必要です。セッション化結果と組み合わせて WHERE session_id = prev_session_id を追加するのが王道です。

実務コラム：所要時間メトリクスは UX 改善の羅針盤

view → click の所要時間が長い場合、ユーザーは「商品詳細を熟考している」可能性と「決定打を見つけられず躊躇している」可能性の両方があります。これを判別するにはセグメント別の比較が有効で、最終的に購入したユーザーと離脱したユーザーで所要時間の分布を比較します。「購入者の中央値=2分、離脱者の中央値=15分」のようなパターンが見えれば、「迷うユーザーは離脱する」というインサイトに直結します。さらに click → purchase が長い場合は決済UIや配送オプションの問題が示唆され、改善優先度の判断材料になります。Q6（セッション化）・Q7（アトリビューション）と組み合わせれば「初回タッチチャネル別 × ステップ別の所要時間分布」といった多次元分析も可能です。

QUESTION 9

再帰CTE — 日付ディメンションを生成し LEFT JOIN で DAU 時系列のゼロ埋めを行う

WITH RECURSIVELEFT JOIN時系列分析再帰CTE

前提知識 ▼

DAU（Daily Active Users）の時系列を可視化するときの最大の落とし穴がイベントのない日が結果から消えてしまう問題です。素直に GROUP BY date するとアクティビティがゼロの日は存在しない行になり、グラフが連続せず誤った印象を与えます。これを防ぐのが再帰CTE による日付ディメンションの生成です。

WITH RECURSIVE date_series(d) AS (
  SELECT MIN(event_time::date) FROM user_events    -- 起点（アンカー）
  UNION ALL
  SELECT d + 1                                  -- 再帰: 累積結果の各行から+1日した行を生成
  FROM   date_series
  WHERE  d < (SELECT MAX(event_time::date) FROM user_events)
)

再帰CTE は「アンカー」「再帰部」「終了条件」の3点セット：(1) アンカー = 最初に作られる行、(2) 再帰部 = 累積結果に毎回追加される行の生成ロジック、(3) 終了条件 = WHERE 句に書く再帰停止条件。再帰部はそれまでの累積結果（自分自身）を参照でき、終了条件が偽になるまで「次の行」を生成し続けます。今回は「最後のイベント日に到達したら停止」が終了条件です。

問題 ▼

user_events テーブルから、最初のイベント日から最後のイベント日まで全ての日付を出力し、各日付の DAU（アクティブユーザー数）を計算してください。イベントのない日は DAU=0 として出力します。出力列は event_date, dau、event_date 昇順で返してください。

使用テーブル

► user_events（5行）

user_id	event_time
1	2024-01-10
2	2024-01-10
1	2024-01-12
3	2024-01-12
1	2024-01-14

期待出力 ▼

event_date	dau
2024-01-10	2
2024-01-11	0
2024-01-12	2
2024-01-13	0
2024-01-14	1

模範解答コード ▼

WITH RECURSIVE date_series(d) AS (                  -- ① 再帰CTE で日付の連続値を生成
  SELECT MIN(event_time::date)                              -- アンカー: 最初の日付
  FROM   user_events
  UNION ALL
  SELECT d + 1                                              -- 再帰部: 直前の累積結果から+1日
  FROM   date_series
  WHERE  d < (SELECT MAX(event_time::date) FROM user_events)  -- 終了条件: 最終日まで
)
SELECT
  ds.d                                  AS event_date,
  COUNT(DISTINCT e.user_id)             AS dau          -- COUNT(DISTINCT) は NULL を無視 → 0 になる
FROM       date_series ds
LEFT JOIN  user_events e
  ON ds.d = e.event_time::date                            -- 日付一致でJOIN
GROUP BY   ds.d
ORDER BY   ds.d;

/*
  実行順序（SQLの論理的な評価順）:
  1. WITH RECURSIVE date_series  → 日付を1日ずつ再帰生成
  2. LEFT JOIN user_events       → 各日にイベントを結合（無い日はNULL）
  3. GROUP BY ds.d               → 日ごとに COUNT(DISTINCT user_id) を集計
  */

解説（テーブル変化・ポイント） ▼

WITH RECURSIVE date_series(d) AS (
  SELECT MIN(event_time::date)
  FROM   user_events
  UNION ALL
  SELECT d + 1
  FROM   date_series
  WHERE  d < (SELECT MAX(event_time::date) FROM user_events)
)
SELECT ds.d AS event_date,
  COUNT(DISTINCT e.user_id) AS dau
FROM       date_series ds
LEFT JOIN  user_events e
  ON ds.d = e.event_time::date
GROUP BY ds.d
ORDER BY ds.d;

LEGEND ▼

① アンカー: SELECT MIN(event_time::date) → 累積結果に1行追加 ▼

date_series の初期1行が生まれるMIN(event_time::date) によって取得された最初の日付 (2024-01-10) が累積結果の起点（アンカー）として挿入されます。これが再帰反復の出発点です。

1 / 6

反復	累積結果(date_series)	今回追加された行
アンカー	2024-01-10	2024-01-10 ←初期行

累積結果: 1行（アンカー実行後）

学習ポイント

再帰CTE の3要素：(1) アンカー: 最初の行を生成（SELECT MIN(...)）、(2) 再帰部: 直前ステップで追加された行から次行を生成（SELECT d+1 FROM date_series）、(3) 終了条件: WHERE 句が偽になるまで反復継続。3要素のどれが欠けても再帰CTE は機能しないため、必ず全てを意識して書きます。

日付ディメンション × LEFT JOIN がゼロ埋めの定石：連続日付を持つ「ディメンション表」を左に置き、事実表（イベント）を右に LEFT JOIN すると、イベントゼロの日も行が消えずに残ります。COUNT(DISTINCT) は NULL を無視するため、自然に 0 が出力されます。これは BI ツール（Tableau・Looker）に渡す前の「データ整形の鉄則」です。

PostgreSQL では generate_series がもっと簡単：PostgreSQL なら SELECT generate_series(min_date, max_date, '1 day'::interval)::date で日付列を1行で生成可能です。再帰CTE は「BigQuery・MySQL など generate_series がないDBで日付列を作る」「真のグラフ走査（祖先・子孫の探索）」など本来の用途で力を発揮します。日付ディメンション生成は標準SQL（再帰CTE）と方言API（generate_series）の両方を知っておくのが理想です。

アンチパターン

終了条件を忘れる（無限ループ）：再帰部に WHERE 句を書かないと、d がいつまでも +1 され続け無限ループになります。PostgreSQL では recursion depth exceeds 100 などのエラーで停止しますが、本番DBで実行する前にローカルでテストすべきです。

UNION ALL ではなく UNION を使う：UNION は重複を排除するため、累積結果が大きくなるほど比較コストが O(N²) になります。再帰CTE では絶対に UNION ALL を使うべきです（再帰の各反復で生成される行はユニーク前提）。INNER JOIN や CTE で重複が混ざる場合は、再帰部の SELECT 内で DISTINCT を使うか、ロジック自体を見直してください。

日付ディメンションなしで GROUP BY → イベントゼロの日が消える：SELECT event_time::date, COUNT(DISTINCT user_id) FROM user_events GROUP BY event_time::date だけで集計すると、1/11 と 1/13 の行が出力されません。グラフ化したときに線が不自然に切れたり、平均DAUの分母が誤ったりします。時系列の連続性は日付ディメンション × LEFT JOIN で必ず担保してください。

実務コラム：再帰CTE の真価は階層・グラフ探索

日付ディメンション生成は再帰CTE の「入門」用途ですが、本領は階層構造の走査にあります。組織図（社員→上司の繰り返しで CEO まで辿る）、カテゴリツリー（商品カテゴリの祖先・子孫展開）、SNSのフォロー関係（友達の友達まで N 段階展開）など、「行のリンクを辿って広がっていく」探索処理は再帰CTE でしか自然に書けません。さらに CYCLE 句（無限ループ検出）や SEARCH 句（探索順序の制御）を組み合わせれば、本格的なグラフ分析も SQL で完結します。今回学んだ「アンカー + 再帰 + 終了条件」のメンタルモデルは、より複雑な再帰問題でもそのまま使えます。

QUESTION 10

連続ログイン日数 — Gaps and Islands パターンで日付 − ROW_NUMBER 差分でグループ化し最長ストリークを算出

ROW_NUMBERGaps & Islandsストリーク分析継続率

前提知識 ▼

「連続ログイン日数」「連続購入日数」のように連続した塊（ストリーク）を検出する問題は、Gaps and Islands という古典的なパターンで解きます。各行に ROW_NUMBER() を採番し、日付 − 行番号 という不思議な差分を計算すると、連続した日付の塊では同じ値、ギャップが入ると別の値になるという性質を利用します。

-- 日付 - row_number(日) で「島」を識別する魔法のキー
login_date - ROW_NUMBER() OVER (
  PARTITION BY user_id ORDER BY login_date
) * INTERVAL '1 day' AS streak_group

なぜ日付 - row_number で島が見えるのか：連続日付では「日付」も「row_number」も1ずつ増えるため、引き算した値は変化しません（島の中で一定）。ギャップが入ると日付だけ飛び、row_number は飛ばないため差分が増えます。同じ差分値を持つ行が同じ島 = 連続塊として識別できます。

問題 ▼

user_logins テーブルから、各ユーザーの最長連続ログイン日数（longest_streak）を計算してください。出力列は user_id, longest_streak、user_id 昇順で返してください。

使用テーブル

► user_logins（10行）

user_id	login_date
1	2024-01-01
1	2024-01-02
1	2024-01-03
1	2024-01-05
1	2024-01-06
2	2024-01-10
3	2024-01-15
3	2024-01-16
3	2024-01-17
3	2024-01-18

期待出力 ▼

user_id	longest_streak
1	3
2	1
3	4

模範解答コード ▼

WITH numbered AS (                                       -- ① ユーザーごとに ROW_NUMBER を採番
  SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    )                                                       AS rn
  FROM   user_logins
),
grouped AS (                                          -- ② 日付 - rn日 で「島」のキー（streak_group）
  SELECT
    user_id,
    login_date,
    login_date - rn * INTERVAL '1 day'                       AS streak_group
  FROM   numbered
),
streaks AS (                                          -- ③ 島ごとに長さを集計
  SELECT
    user_id,
    streak_group,
    COUNT(*)                                                AS streak_length
  FROM   grouped
  GROUP BY user_id, streak_group
)
SELECT
  user_id,
  MAX(streak_length)                                       AS longest_streak
FROM   streaks
GROUP BY user_id
ORDER BY user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE numbered
  2. CTE grouped
  3. CTE streaks
  4. 外側クエリ
  */

解説（テーブル変化・ポイント） ▼

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM   user_logins
),
grouped AS (
  SELECT user_id, login_date,
    login_date - rn * INTERVAL '1 day' AS streak_group
  FROM numbered
),
streaks AS (
  SELECT user_id, streak_group,
    COUNT(*) AS streak_length
  FROM grouped
  GROUP BY user_id, streak_group
)
SELECT user_id,
  MAX(streak_length) AS longest_streak
FROM   streaks
GROUP BY user_id
ORDER BY user_id;

LEGEND ▼

データ取得・読込対象

① FROM user_logins（10行） ▼

FROM user_logins3ユーザーのログイン履歴を読み込みます。user1 は連続日と非連続日が混在しており、最長ストリーク検出の典型ケースです。

1 / 5

user_id	login_date
1	2024-01-01
1	2024-01-02
1	2024-01-03
1	2024-01-05
1	2024-01-06
2	2024-01-10
3	2024-01-15
3	2024-01-16
3	2024-01-17
3	2024-01-18

10行読込（3ユーザー × ログイン履歴）

学習ポイント

「日付 − ROW_NUMBER」は Gaps and Islands の聖杯：連続日付では両者とも1ずつ増えるため引き算は変化せず、ギャップで日付だけ飛ぶため値が変わるという数学的性質を利用します。この発想は「連続購入日」「連続勝利」「連続出席」など全ての「連続」検出問題に応用でき、Window関数の中でも最も汎用性の高いパターンの一つです。

RANK() / DENSE_RANK() ではダメな理由：同じユーザーが同じ日に複数ログインしている場合、ORDER BY が一意でなくなり、RANK だと飛び番、DENSE_RANK だと同一値が複数行になり「日付 − rn」の差分が同じ島内でも変動してしまいます。ROW_NUMBER は「同順位がない強制連番」のため、Gaps and Islands では ROW_NUMBER 一択です。同日に複数ログインがある場合は事前に SELECT DISTINCT user_id, login_date しておくのが安全です。

3段CTE で意図を分離する：numbered（連番）→ grouped（島キー）→ streaks（島の長さ）→ 最終集計（最大）と段階的に CTE を分割すると、各ステップの意味が明確になりデバッグ・チューニング・拡張が容易になります。「最長ではなく現時点でアクティブなストリーク」を求めたいなら、最後の WHERE を streak_group = (今日 - streak_length日) のように変更するだけで派生クエリが書けます。

アンチパターン

LAG で前日との差を取って累積和でグループ番号を作る亜流：SUM(CASE WHEN login_date - LAG(login_date) > 1 THEN 1 ELSE 0 END) OVER(...) という解法もありますが、CTE 段が増えてコードが冗長になります。「日付 - row_number」のほうが1行で島キーが生まれるため可読性で勝ります。両方知った上で適切な方を選びましょう。

同日複数ログインを考慮しない：本問では user_logins は1日1行前提ですが、実イベントテーブル（user_events）には1日に複数件あり得ます。そのまま ROW_NUMBER を採番すると、同一日に複数の rn が振られ「日付 - rn」が島内で揃いません。必ず SELECT DISTINCT user_id, event_time::date AS login_date FROM user_events で事前にユニーク化してから Gaps and Islands に渡してください。

実務コラム：ストリーク分析が示す「習慣化」

SNS・ゲーム・学習アプリで「7日連続ログイン」「30日連続使用」といったメトリクスが重視される理由は、ストリークが「習慣化に成功したユーザー」の代理指標として優秀だからです。Duolingo の連続記録（Streak）バッジ、Snapchat の Snapstreak は、ユーザーをアプリに再訪させる強力な仕組みとして知られています。今回の SQL を活用すれば、「最長ストリーク7日以上のユーザーは1年後の継続率が3倍」のような相関分析が可能になり、施策設計の根拠データになります。さらにセッション化、アトリビューション、日付ディメンションと組み合わせれば、「初回流入チャネル別 × 週次の最大ストリーク分布」のような多次元分析が SQL のみで構築でき、本問題集5問の応用編が実務分析パイプラインの完成形に直結します。