紹介の伝播をSQLで追跡する方法「スーパースプレッダー」は誰か

-- referrals（紹介関係テーブル）
-- referral_id    : 紹介ID（主キー）
-- referrer_id    : 紹介した顧客のID
-- referred_id    : 紹介された顧客のID
-- referred_at    : 紹介が成立した日時
-- reward_amount  : 紹介報酬額（円）

-- orders（注文テーブル）
-- order_id, customer_id, order_date, total_amount, status

-- customers（顧客マスタ）
-- customer_id, registered_at, channel, rank

-- STEP1: 顧客別LTVの計算

WITH customer_ltv AS (
    SELECT
        customer_id,
        COUNT(order_id)         AS total_orders,
        SUM(total_amount)       AS lifetime_value,
        MIN(order_date)         AS first_order_date,
        MAX(order_date)         AS last_order_date
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT
    c.customer_id,
    c.registered_at,
    c.channel,
    COALESCE(ltv.total_orders, 0)    AS total_orders,
    COALESCE(ltv.lifetime_value, 0)  AS lifetime_value,
    ltv.first_order_date,
    ltv.last_order_date
FROM customers      c
LEFT JOIN customer_ltv  ltv  ON c.customer_id = ltv.customer_id;

-- STEP2: 再帰CTEで紹介ネットワークを全世代展開する

WITH RECURSIVE referral_tree AS (
    -- ベースケース：紹介した側（referrer）を起点とする
    -- 誰かを紹介したことがある顧客を起点にすべての子孫を展開
    SELECT
        r.referrer_id      AS root_id,        -- ツリーの根（起点）
        r.referred_id      AS node_id,        -- 現在のノード
        r.referrer_id      AS parent_id,      -- 直接の親
        1                  AS generation,     -- 世代（直接紹介=1）
        CAST(r.referrer_id AS VARCHAR)
        || ' > '
        || CAST(r.referred_id AS VARCHAR)    AS referral_path,  -- 経路文字列
        r.referred_at,
        r.reward_amount
    FROM referrals  r

    UNION ALL

    -- 再帰ステップ：子ノードから孫ノードへ
    SELECT
        rt.root_id,
        r.referred_id          AS node_id,
        r.referrer_id          AS parent_id,
        rt.generation + 1      AS generation,
        rt.referral_path
        || ' > '
        || CAST(r.referred_id AS VARCHAR)  AS referral_path,
        r.referred_at,
        r.reward_amount
    FROM referral_tree  rt
    JOIN referrals       r  ON rt.node_id = r.referrer_id
    WHERE rt.generation < 10  -- 深さ上限（無限ループ防止）
    AND rt.referral_path NOT LIKE '%' || CAST(r.referred_id AS VARCHAR) || '%'
    -- 循環参照の防止（自分自身がすでに経路に含まれていないか確認）
)
SELECT
    rt.root_id,
    rt.node_id,
    rt.parent_id,
    rt.generation,
    rt.referral_path,
    rt.referred_at,
    -- 被紹介者のLTV
    COALESCE(ltv.lifetime_value, 0)  AS node_ltv,
    ltv.total_orders,
    rt.reward_amount
FROM referral_tree  rt
LEFT JOIN (
    SELECT customer_id, lifetime_value, total_orders
    FROM orders WHERE status = 'completed'
    GROUP BY customer_id
    -- 実際には STEP1の customer_ltv CTEを参照する
) ltv  ON rt.node_id = ltv.customer_id
ORDER BY rt.root_id, rt.generation, rt.node_id;

-- STEP3: 紹介者ごとのネットワーク価値を集計する（完成版）

WITH RECURSIVE referral_tree AS (
    SELECT
        r.referrer_id  AS root_id,
        r.referred_id  AS node_id,
        r.referrer_id  AS parent_id,
        1              AS generation,
        CAST(r.referrer_id AS VARCHAR) || ' > ' || CAST(r.referred_id AS VARCHAR)  AS path,
        r.reward_amount
    FROM referrals  r

    UNION ALL

    SELECT
        rt.root_id,
        r.referred_id,
        r.referrer_id,
        rt.generation + 1,
        rt.path || ' > ' || CAST(r.referred_id AS VARCHAR),
        r.reward_amount
    FROM referral_tree rt
    JOIN referrals     r   ON rt.node_id = r.referrer_id
    WHERE rt.generation < 10
    AND rt.path NOT LIKE '%' || CAST(r.referred_id AS VARCHAR) || '%'
),
-- 顧客別LTV
customer_ltv AS (
    SELECT
        customer_id,
        SUM(total_amount)  AS lifetime_value,
        COUNT(order_id)    AS total_orders
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
-- 紹介者自身のLTVも加算
referrer_ltv AS (
    SELECT
        r.referrer_id                                AS customer_id,
        COALESCE(ltv.lifetime_value, 0)              AS own_ltv,
        COALESCE(ltv.total_orders, 0)                AS own_orders
    FROM (SELECT DISTINCT referrer_id FROM referrals) r
    LEFT JOIN customer_ltv ltv ON r.referrer_id = ltv.customer_id
),
-- 紹介ツリーのサマリ
tree_summary AS (
    SELECT
        rt.root_id,
        COUNT(DISTINCT rt.node_id)                    AS total_descendants,   -- 子孫の総数
        SUM(COALESCE(ltv.lifetime_value, 0))          AS descendants_ltv,     -- 子孫の合計LTV
        MAX(rt.generation)                            AS max_depth,            -- ツリーの深さ
        SUM(rt.reward_amount)                         AS total_rewards_paid,   -- 支払った報酬総額

        -- 世代別の子孫数
        COUNT(DISTINCT CASE WHEN rt.generation = 1 THEN rt.node_id END)  AS gen1_count,
        COUNT(DISTINCT CASE WHEN rt.generation = 2 THEN rt.node_id END)  AS gen2_count,
        COUNT(DISTINCT CASE WHEN rt.generation = 3 THEN rt.node_id END)  AS gen3_count,

        -- 世代別のLTV
        SUM(CASE WHEN rt.generation = 1 THEN COALESCE(ltv.lifetime_value, 0) ELSE 0 END)  AS gen1_ltv,
        SUM(CASE WHEN rt.generation = 2 THEN COALESCE(ltv.lifetime_value, 0) ELSE 0 END)  AS gen2_ltv,
        SUM(CASE WHEN rt.generation = 3 THEN COALESCE(ltv.lifetime_value, 0) ELSE 0 END)  AS gen3_ltv
    FROM referral_tree  rt
    LEFT JOIN customer_ltv  ltv  ON rt.node_id = ltv.customer_id
    GROUP BY rt.root_id
)
SELECT
    ts.root_id                                        AS customer_id,
    rl.own_ltv                                        AS own_lifetime_value,
    ts.total_descendants,
    ts.descendants_ltv,

    -- ネットワーク価値 = 自分のLTV + 全子孫のLTV
    rl.own_ltv + ts.descendants_ltv                  AS network_value,

    ts.max_depth,
    ts.gen1_count,
    ts.gen2_count,
    ts.gen3_count,
    ts.gen1_ltv,
    ts.gen2_ltv,
    ts.gen3_ltv,
    ts.total_rewards_paid,

    -- ネットワークROI（子孫LTV ÷ 支払った報酬）
    ROUND(ts.descendants_ltv * 1.0
        / NULLIF(ts.total_rewards_paid, 0), 2)        AS network_roi,

    -- スーパースプレッダースコア（ネットワーク価値の全体における相対的な位置）
    ROUND(
        (rl.own_ltv + ts.descendants_ltv) * 100.0
        / SUM(rl.own_ltv + ts.descendants_ltv) OVER ()
    , 2)  AS network_value_share_pct,

    -- ランク付け
    RANK() OVER (ORDER BY rl.own_ltv + ts.descendants_ltv DESC)  AS network_rank

FROM tree_summary   ts
JOIN referrer_ltv   rl  ON ts.root_id = rl.customer_id
ORDER BY network_rank;

-- STEP4: ネットワーク全体の構造分析

WITH RECURSIVE referral_tree AS (
    SELECT referrer_id AS root_id, referred_id AS node_id, 1 AS generation,
           CAST(referrer_id AS VARCHAR) || ' > ' || CAST(referred_id AS VARCHAR) AS path
    FROM referrals
    UNION ALL
    SELECT rt.root_id, r.referred_id, rt.generation + 1,
           rt.path || ' > ' || CAST(r.referred_id AS VARCHAR)
    FROM referral_tree rt JOIN referrals r ON rt.node_id = r.referrer_id
    WHERE rt.generation < 10 AND rt.path NOT LIKE '%' || CAST(r.referred_id AS VARCHAR) || '%'
),
customer_ltv AS (
    SELECT customer_id, SUM(total_amount) AS ltv
    FROM orders WHERE status = 'completed' GROUP BY customer_id
),
network_summary AS (
    SELECT
        root_id,
        COUNT(DISTINCT node_id)                    AS total_descendants,
        SUM(COALESCE(ltv.ltv, 0))                 AS descendants_ltv
    FROM referral_tree rt
    LEFT JOIN customer_ltv ltv ON rt.node_id = ltv.customer_id
    GROUP BY root_id
),
-- ネットワーク価値の分布
value_distribution AS (
    SELECT
        ns.root_id,
        COALESCE(own_ltv.ltv, 0) + ns.descendants_ltv  AS network_value,
        ns.total_descendants
    FROM network_summary  ns
    LEFT JOIN customer_ltv  own_ltv  ON ns.root_id = own_ltv.customer_id
)
SELECT
    -- パレート分析：上位N%がネットワーク価値全体の何%を占めるか
    CASE
        WHEN PERCENT_RANK() OVER (ORDER BY network_value DESC) <= 0.01 THEN '上位1%'
        WHEN PERCENT_RANK() OVER (ORDER BY network_value DESC) <= 0.05 THEN '上位5%'
        WHEN PERCENT_RANK() OVER (ORDER BY network_value DESC) <= 0.10 THEN '上位10%'
        WHEN PERCENT_RANK() OVER (ORDER BY network_value DESC) <= 0.20 THEN '上位20%'
        ELSE                                                                 '下位80%'
    END  AS percentile_group,

    COUNT(root_id)                                                 AS referrer_count,
    SUM(network_value)                                             AS group_network_value,
    ROUND(SUM(network_value) * 100.0 / SUM(SUM(network_value)) OVER (), 1)
        AS value_share_pct,

    ROUND(AVG(total_descendants), 1)                               AS avg_descendants,
    ROUND(AVG(network_value), 0)                                   AS avg_network_value,
    MAX(network_value)                                             AS max_network_value
FROM value_distribution
GROUP BY
    CASE
        WHEN PERCENT_RANK() OVER (ORDER BY network_value DESC) <= 0.01 THEN '上位1%'
        WHEN PERCENT_RANK() OVER (ORDER BY network_value DESC) <= 0.05 THEN '上位5%'
        WHEN PERCENT_RANK() OVER (ORDER BY network_value DESC) <= 0.10 THEN '上位10%'
        WHEN PERCENT_RANK() OVER (ORDER BY network_value DESC) <= 0.20 THEN '上位20%'
        ELSE                                                                 '下位80%'
    END
ORDER BY MAX(PERCENT_RANK() OVER (ORDER BY network_value DESC));

-- STEP5: スーパースプレッダーの早期識別

WITH referral_velocity AS (
    SELECT
        referrer_id                                   AS customer_id,
        COUNT(referred_id)                            AS referral_count,
        DATE_DIFF('day',
            MIN(referred_at),
            MAX(referred_at)
        )  AS referral_span_days,

        -- 最初の紹介を何日以内に行ったか（早期行動指標）
        MIN(DATE_DIFF('day',
            c.registered_at,
            CAST(r.referred_at AS DATE)
        ))  AS days_to_first_referral,

        -- 月あたりの紹介数（紹介速度）
        ROUND(
            COUNT(referred_id) * 30.0
            / NULLIF(DATE_DIFF('day', MIN(CAST(r.referred_at AS DATE)),
                                      CURRENT_DATE), 0)
        , 2)  AS monthly_referral_rate
    FROM referrals        r
    JOIN customers        c   ON r.referrer_id = c.customer_id
    GROUP BY r.referrer_id, c.registered_at
),
with_network AS (
    -- STEP3のnetwork_summaryを参照（実際はCTEとして連結する）
    SELECT
        rv.customer_id,
        rv.referral_count,
        rv.days_to_first_referral,
        rv.monthly_referral_rate,
        COALESCE(ltv.lifetime_value, 0)  AS own_ltv
    FROM referral_velocity  rv
    LEFT JOIN (
        SELECT customer_id, SUM(total_amount) AS lifetime_value
        FROM orders WHERE status = 'completed' GROUP BY customer_id
    ) ltv  ON rv.customer_id = ltv.customer_id
)
SELECT
    customer_id,
    referral_count,
    days_to_first_referral,
    monthly_referral_rate,
    own_ltv,

    -- スーパースプレッダースコア（複合指標）
    ROUND(
        -- 紹介数（重み：50%）
        referral_count * 0.5
        -- 紹介速度（重み：30%）
        + monthly_referral_rate * 0.3
        -- 早期行動（重み：20%。早いほど高スコア）
        + CASE WHEN days_to_first_referral <= 7  THEN 10
               WHEN days_to_first_referral <= 30 THEN 7
               WHEN days_to_first_referral <= 90 THEN 4
               ELSE                                   1 END * 0.2
    , 2)  AS superspreader_score,

    -- アーリーアンバサダーフラグ（登録30日以内に3人以上紹介）
    CASE
        WHEN days_to_first_referral <= 30
        AND referral_count >= 3
        THEN 1 ELSE 0
    END  AS early_ambassador_flag

FROM with_network
ORDER BY superspreader_score DESC;