SQL 行動分析 — ウィンドウ関数・再帰CTE・期間分析の応用

WITH purchase_counts AS (
  -- ユーザーごとの購買回数を集計
  SELECT
    user_id,
    COUNT(*) AS purchase_count
  FROM   purchases
  GROUP BY user_id
),
scored AS (
  -- NTILE(4) で4段階スコアリング（1=低頻度, 4=高頻度）
  SELECT
    user_id,
    purchase_count,
    NTILE(4) OVER (
      ORDER BY purchase_count ASC  -- 少ない順 → tile1=低頻度
    ) AS freq_score
  FROM   purchase_counts
)
SELECT
  user_id,
  purchase_count,
  freq_score,
  (freq_score = 4) AS is_high_value  -- スコア4=最高頻度セグメント
FROM   scored
ORDER BY user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE purchase_counts を定義  → グループ化・集計関数を評価
  2. CTE scored を定義           → ウィンドウ関数を評価（行数は保持）
  3. SELECT                      → 列を評価（freq_score, is_high_value）
  4. ORDER BY user_id            → 並び替えて出力
*/

WITH step_lagged AS (
  SELECT
    user_id,
    step,
    stepped_at,
    LAG(stepped_at) OVER (     -- 同一ユーザーの直前ステップ日時を取得
      PARTITION BY user_id
      ORDER BY     stepped_at ASC
    ) AS prev_stepped_at
  FROM   step_events
)
SELECT
  user_id,
  stepped_at,
  prev_stepped_at,
  (stepped_at - prev_stepped_at)       AS days_from_prev,  -- date-date → integer
  (stepped_at - prev_stepped_at) <= 7  AS within_7days     -- 7日以内の真偽値
FROM   step_lagged
WHERE  step = 'signup'                  -- signup 行のみ（page_view行を除外）
ORDER BY user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE step_lagged を定義  → ウィンドウ関数を評価（行数は保持）
  2. WHERE step = 'signup'   → 行を絞り込む
  3. SELECT                  → 列を評価（days_from_prev, within_7days）
  4. ORDER BY user_id        → 並び替えて出力
*/

WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', registered_at)::date AS cohort_month
  FROM users
),
activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_date)::date   AS active_month
  FROM login_events
),
offsets(offset_month) AS (            -- Month 1・2・3 のオフセット値を生成
  SELECT UNNEST(ARRAY[1, 2, 3])
)
SELECT
  c.cohort_month,
  o.offset_month,
  COUNT(DISTINCT c.user_id)  AS cohort_size,
  COUNT(DISTINCT a.user_id)  AS retained,
  ROUND(
    COUNT(DISTINCT a.user_id) * 100.0 /
    NULLIF(COUNT(DISTINCT c.user_id), 0), 1  -- ゼロ除算ガード
  ) AS retention_pct
FROM       cohorts  c
CROSS JOIN offsets  o                  -- 全組み合わせ（2コホート×3オフセット）
LEFT JOIN  activity a
  ON  a.user_id     = c.user_id
  AND a.active_month = c.cohort_month + (o.offset_month || ' month')::interval
GROUP BY c.cohort_month, o.offset_month
ORDER BY c.cohort_month, o.offset_month;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE cohorts を定義       → 値を整形
  2. CTE activity を定義      → 重複を除去
  3. CTE offsets を定義       → 派生テーブルを評価
  4. CROSS JOIN offsets o     → 結合（直積）
  5. LEFT JOIN activity a     → 結合（左表を全行保持）
  6. GROUP BY                 → グループ化
  7. SELECT                   → 集計関数を評価（cohort_size, retained）
  8. ROUND(...)               → 値を整形
  9. ORDER BY                 → 並び替えて出力
*/

WITH RECURSIVE date_series AS (
  SELECT '2024-01-01'::date AS dt  -- アンカー: 集計開始日
  UNION ALL
  SELECT (dt + INTERVAL '1 day')::date
  FROM   date_series
  WHERE  dt < '2024-01-07'              -- 終了条件: 01-07まで
),
daily_dau AS (
  SELECT
    session_date,
    COUNT(DISTINCT user_id) AS dau
  FROM   user_sessions
  GROUP BY session_date
)
SELECT
  ds.dt,
  COALESCE(d.dau, 0) AS dau          -- NULL日は0で補完
FROM      date_series ds
LEFT JOIN daily_dau   d ON d.session_date = ds.dt
ORDER BY  ds.dt;

/*
  実行順序（SQLの論理的な評価順）:
  1. WITH RECURSIVE date_series
  2. CTE daily_dau
  3. FROM date_series ds
  4. COALESCE(d.dau, 0)
  5. ORDER BY ds.dt  → 日付昇順
  */