-- journeyテーブルの主要カラム構成
-- （ステージ0・ステージ1・ゴール・ドロップアウトの例）

SELECT
  cdp_customer_id,

  -- ジャーニー全体
  intime_journey,          -- ジャーニーにエントリーした時刻
  outtime_journey,         -- ジャーニーからドロップアウトした時刻

  -- ゴール
  intime_goal,             -- ゴール基準を満たした時刻

  -- ステージ0
  intime_stage_0,          -- ステージ0にエントリーした時刻
  outtime_stage_0,         -- ステージ0から出た時刻
  intime_stage_0_milestone,-- ステージ0のマイルストーンを満たした時刻
  intime_stage_0_exit_0,   -- ステージ0の終了基準0を満たした時刻

  -- ステージ0の各ステップ（ハッシュ値がステップIDになる）
  intime_stage_0_24d42ab6_activation,   -- Activation Stepを通過した時刻
  outtime_stage_0_24d42ab6_activation,  -- 同上（通過なのでintimeと同値）
  intime_stage_0_c48862a1_wait,         -- Wait Stepに入った時刻
  outtime_stage_0_c48862a1_wait,        -- Wait Stepを出た時刻（Wait日数後）

  -- ステージ1
  intime_stage_1,
  outtime_stage_1,
  intime_stage_1_milestone

FROM journey_13  -- journey_${journey_id}

-- 現在各プロファイルがどのステージ・ステップにいるかを確認する

SELECT
  cdp_customer_id,
  CASE
    WHEN intime_goal IS NOT NULL
    THEN 'GOAL'
    WHEN outtime_journey IS NOT NULL
    THEN 'DROPOUT'
    WHEN intime_stage_1 IS NOT NULL AND outtime_stage_1 IS NULL
    THEN 'ステージ1（進行中）'
    WHEN intime_stage_0 IS NOT NULL AND outtime_stage_0 IS NULL
    THEN 'ステージ0（進行中）'
    ELSE '未エントリー'
  END AS current_position,

  -- ステージ0のActivation Stepを通過済みかどうか
  CASE
    WHEN intime_stage_0_24d42ab6_activation IS NOT NULL
    THEN 'アクティベーション済み'
    ELSE '未アクティベーション'
  END AS activation_status

FROM journey_13
ORDER BY intime_journey DESC

-- Activation Stepを通過したプロファイルの日別集計
-- （アクティベーションが実行されたプロファイルの実数確認）

SELECT
  TD_TIME_FORMAT(
    intime_stage_0_24d42ab6_activation,
    'yyyy-MM-dd', 'JST'
  )                                    AS activation_date,
  COUNT(DISTINCT cdp_customer_id)      AS activated_profiles
FROM journey_13
WHERE intime_stage_0_24d42ab6_activation IS NOT NULL
GROUP BY 1
ORDER BY 1 DESC

-- ジャーニー全体のファネル分析

WITH journey_summary AS (
  SELECT
    COUNT(DISTINCT cdp_customer_id)                    AS total_entered,
    COUNT(DISTINCT CASE
      WHEN intime_stage_1 IS NOT NULL
      THEN cdp_customer_id END)                        AS reached_stage1,
    COUNT(DISTINCT CASE
      WHEN intime_goal IS NOT NULL
      THEN cdp_customer_id END)                        AS reached_goal,
    COUNT(DISTINCT CASE
      WHEN outtime_journey IS NOT NULL AND intime_goal IS NULL
      THEN cdp_customer_id END)                        AS dropped_out
  FROM journey_13
  WHERE intime_journey IS NOT NULL
)
SELECT
  total_entered,
  reached_stage1,
  ROUND(100.0 * reached_stage1 / total_entered, 1)    AS stage1_rate_pct,
  reached_goal,
  ROUND(100.0 * reached_goal / total_entered, 1)      AS goal_rate_pct,
  dropped_out,
  ROUND(100.0 * dropped_out / total_entered, 1)       AS dropout_rate_pct
FROM journey_summary

-- Activation Stepを本日通過したプロファイルがいるか確認
SELECT COUNT(*) AS count_today
FROM journey_13
WHERE intime_stage_0_24d42ab6_activation =
  TD_DATE_TRUNC('day', TD_SCHEDULED_TIME(), 'JST')

-- ステージ0にいてマイルストーンを満たしているが
-- ステージ1に移動していないプロファイルを確認
SELECT
  cdp_customer_id,
  intime_stage_0,
  intime_stage_0_milestone,
  intime_stage_1
FROM journey_13
WHERE
  intime_stage_0 IS NOT NULL
  AND outtime_stage_0 IS NULL          -- まだステージ0にいる
  AND intime_stage_0_milestone IS NOT NULL  -- マイルストーンを満たしている

-- Wait Stepの実際の滞在日数を確認
SELECT
  cdp_customer_id,
  intime_stage_0_c48862a1_wait,
  outtime_stage_0_c48862a1_wait,
  -- Wait Stepの実際の滞在日数を計算
  DATE_DIFF(
    'day',
    intime_stage_0_c48862a1_wait,
    outtime_stage_0_c48862a1_wait
  ) AS actual_wait_days
FROM journey_13
WHERE
  intime_stage_0_c48862a1_wait IS NOT NULL
  AND outtime_stage_0_c48862a1_wait IS NOT NULL
ORDER BY actual_wait_days DESC

-- ジャーニーIDの特定
-- URL例：https://console-next.treasuredata.com/app/ps/257941/e/13/j/da
--   ペアレントセグメントID：257941 → テーブルプレフィックス：cdp_audience_257941
--   ジャーニーID：13 → テーブル名：journey_13

-- ただしjourney_13テーブルは cdp_audience_257941 データベースに存在する

SELECT *
FROM cdp_audience_257941.journey_13
WHERE intime_journey IS NOT NULL
LIMIT 100