SQL 行動分析 — アトリビューション・RFM分析の応用

WITH lagged AS (
  -- 各ユーザーの前回PV時刻を LAG で取得
  SELECT
    user_id,
    viewed_at,
    LAG(viewed_at) OVER (
      PARTITION BY user_id
      ORDER BY     viewed_at
    ) AS prev_viewed_at
  FROM   page_views
),
flagged AS (
  -- 前回PVとの差分（分）と、新セッション開始フラグを算出
  SELECT
    user_id,
    viewed_at,
    EXTRACT(EPOCH FROM (viewed_at - prev_viewed_at)) / 60 AS gap_minutes,
    CASE WHEN prev_viewed_at IS NULL
              OR EXTRACT(EPOCH FROM (viewed_at - prev_viewed_at)) / 60 > 30
         THEN 1 ELSE 0
    END AS new_session_flag   -- 初回行 or 30分超 → 1
  FROM   lagged
)
SELECT
  user_id,
  viewed_at,
  gap_minutes,
  SUM(new_session_flag) OVER (
    PARTITION BY user_id
    ORDER BY     viewed_at
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS session_id   -- フラグの累積和 = セッション通し番号
FROM   flagged
ORDER BY user_id, viewed_at;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE lagged:
     FROM page_views               → 行を読み込む
     LAG(viewed_at) OVER (...)     → ウィンドウ関数を評価（行数は保持）
  2. CTE flagged:
     FROM lagged                   → 行を読み込む
     EXTRACT(EPOCH ...) / CASE     → 値を整形
  3. FROM flagged
     SUM(...) OVER (...) AS session_id → ウィンドウ関数を評価（行数は保持）
  4. ORDER BY user_id, viewed_at   → 並び替えて出力
*/

WITH numbered AS (
  -- ユーザーごとに購入日昇順で連番
  SELECT
    user_id, order_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY order_date
    ) AS rn
  FROM   daily_orders
),
grouped AS (
  -- island キー：order_date - (rn-1)::integer で連続日が同一値に
  SELECT
    user_id, order_date,
    order_date - (rn - 1)::integer AS grp
  FROM   numbered
),
streaks AS (
  -- 各 island を集約：開始・終了・日数
  SELECT
    user_id, grp,
    MIN(order_date) AS streak_start,
    MAX(order_date) AS streak_end,
    COUNT(*)         AS streak_days
  FROM   grouped
  GROUP BY user_id, grp
),
ranked AS (
  -- 最長ストリークを rnk=1 に、最新購入日を窓関数で取得
  SELECT
    user_id, streak_start, streak_end, streak_days,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY     streak_days DESC, streak_end DESC
    ) AS rnk,
    MAX(streak_end) OVER (PARTITION BY user_id) AS user_latest_date
  FROM   streaks
)
SELECT
  user_id,
  streak_days AS max_streak_days,
  streak_start,
  streak_end,
  CASE WHEN streak_end = user_latest_date
       THEN 'TRUE' ELSE 'FALSE'
  END AS is_active   -- 最長島の終了日 = 最新購入日 → 継続中
FROM   ranked
WHERE  rnk = 1
ORDER BY user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE numbered
  2. CTE grouped
  3. CTE streaks
  4. CTE ranked
  5. SELECT ... WHERE rnk = 1  → 行を絞り込む・列を評価
  6. ORDER BY user_id          → 並び替えて出力
  */

WITH RECURSIVE invite_tree AS (
  -- アンカー：招待者なし（ルートユーザー）から depth=0 で開始
  SELECT
    user_id,
    name,
    0 AS depth
  FROM   users
  WHERE  invited_by IS NULL
  UNION ALL
  -- 再帰：tree の現在行が招待した子ユーザーを次の階層として追加
  SELECT
    u.user_id,
    u.name,
    t.depth + 1                -- 1段下げる
  FROM   invite_tree t
  JOIN   users u ON u.invited_by = t.user_id
),
invite_counts AS (
  -- 各ユーザーが直接招待した人数を集計
  SELECT
    invited_by AS inviter_id,
    COUNT(*) AS direct_invitees
  FROM   users
  WHERE  invited_by IS NOT NULL
  GROUP BY invited_by
)
SELECT
  t.user_id,
  t.name,
  t.depth,
  COALESCE(c.direct_invitees, 0) AS direct_invitees   -- 招待ゼロは NULL → 0
FROM   invite_tree t
LEFT JOIN invite_counts c ON c.inviter_id = t.user_id
ORDER BY t.depth, t.user_id;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE invite_tree（再帰）:
     アンカー部を評価（起点）
     再帰1回目（子を展開）
     再帰2回目（子を展開）
     再帰3回目（子を展開）
     追加行なしで停止
  2. CTE invite_counts:
     FROM users / WHERE      → 行を読み込む・絞り込む
     GROUP BY invited_by     → グループ化
     COUNT                   → 集計関数を評価
  3. FROM invite_tree t
     LEFT JOIN invite_counts c → 結合（左表を全行保持）
     COALESCE(...)           → 値を整形
  4. ORDER BY t.depth, t.user_id → 並び替えて出力
*/

WITH user_summary AS (
  -- ユーザーごとに最終購入日と合計購入額を集計
  SELECT
    user_id,
    MAX(purchase_date) AS last_purchase_date,
    SUM(amount)         AS total_amount
  FROM   customer_purchases
  GROUP BY user_id
),
scored AS (
  -- 2軸で NTILE(3) を適用：Recency は降順、Monetary は昇順
  SELECT
    user_id,
    last_purchase_date,
    total_amount,
    NTILE(3) OVER (ORDER BY last_purchase_date DESC) AS r_rank,
    NTILE(3) OVER (ORDER BY total_amount ASC)        AS m_score
  FROM   user_summary
),
final_scores AS (
  -- r_rank（小=最近）を r_score（大=最近）に反転して直感的なスコアに統一
  SELECT
    user_id,
    last_purchase_date,
    total_amount,
    4 - r_rank AS r_score,   -- 1→3, 2→2, 3→1（最近 = 高スコア）
    m_score
  FROM   scored
)
SELECT
  user_id,
  last_purchase_date,
  total_amount,
  r_score,
  m_score,
  CASE
    WHEN r_score = 3 AND m_score = 3 THEN 'VIP'
    WHEN r_score = 3 AND m_score = 2 THEN '優良見込'
    WHEN r_score = 3 AND m_score = 1 THEN '新規/育成'
    WHEN r_score = 2 AND m_score = 3 THEN '離脱注意'
    WHEN r_score = 2 AND m_score = 2 THEN '標準'
    WHEN r_score = 2 AND m_score = 1 THEN '一般'
    WHEN r_score = 1 AND m_score = 3 THEN '失注リスク'
    ELSE '休眠'          -- r=1 かつ m∈{1,2} は休眠
  END AS segment
FROM   final_scores
ORDER BY r_score DESC, m_score DESC;

/*
  実行順序（SQLの論理的な評価順）:
  1. CTE user_summary  → CTE を定義（Recency・Monetary を集計）
  2. CTE scored        → NTILE でランク付け
  3. CTE final_scores  → スコアを反転・整形
  4. SELECT            → CASE でセグメント判定
  5. ORDER BY          → 並び替えて出力
  */