連続購買をSQLで分析する方法｜何ヶ月買い続けているかを判定するクエリ

2024-01, 2024-02, 2024-03, （空白）, 2024-05, 2024-06, （空白）, 2024-08

Island 1: 2024-01 〜 2024-03（3ヶ月連続）
Island 2: 2024-05 〜 2024-06（2ヶ月連続）
Island 3: 2024-08 〜 2024-08（1ヶ月のみ）

-- orders テーブル
-- order_id     : 注文ID
-- customer_id  : 顧客ID
-- order_date   : 注文日（DATE型）
-- total_amount : 注文金額
-- status       : 'completed' / 'cancelled'

-- STEP1: 顧客×購買月の一覧（月次ユニーク化）

WITH monthly_purchases AS (
    SELECT DISTINCT
        customer_id,
        DATE_TRUNC('month', order_date)  AS purchase_month
    FROM orders
    WHERE status = 'completed'
)
SELECT *
FROM monthly_purchases
ORDER BY customer_id, purchase_month;

-- STEP2: Island グループキーを計算する

WITH monthly_purchases AS (
    SELECT DISTINCT
        customer_id,
        DATE_TRUNC('month', order_date)  AS purchase_month
    FROM orders
    WHERE status = 'completed'
),
with_group_key AS (
    SELECT
        customer_id,
        purchase_month,

        -- 購買月を「月番号」に変換（基準日からの月数）
        DATE_DIFF('month', DATE '2020-01-01', purchase_month)  AS month_num,

        -- 顧客ごとに購買月の連番
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY purchase_month
        )  AS rn,

        -- グループキー = 月番号 - 連番（連続している間は一定）
        DATE_DIFF('month', DATE '2020-01-01', purchase_month)
        - ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY purchase_month
        )  AS group_key
    FROM monthly_purchases
)
SELECT *
FROM with_group_key
ORDER BY customer_id, purchase_month;

-- STEP3: 連続購買区間（Island）ごとのサマリ

WITH monthly_purchases AS (
    SELECT DISTINCT
        customer_id,
        DATE_TRUNC('month', order_date)  AS purchase_month
    FROM orders
    WHERE status = 'completed'
),
with_group_key AS (
    SELECT
        customer_id,
        purchase_month,
        DATE_DIFF('month', DATE '2020-01-01', purchase_month)
        - ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY purchase_month
        )  AS group_key
    FROM monthly_purchases
)
SELECT
    customer_id,
    MIN(purchase_month)  AS streak_start,   -- 連続購買の開始月
    MAX(purchase_month)  AS streak_end,     -- 連続購買の終了月
    COUNT(*)             AS streak_months   -- 連続購買の月数
FROM with_group_key
GROUP BY customer_id, group_key
ORDER BY customer_id, streak_start;

-- STEP4: 現在ストリーク・最長ストリーク（完成版）

WITH monthly_purchases AS (
    SELECT DISTINCT
        customer_id,
        DATE_TRUNC('month', order_date)  AS purchase_month
    FROM orders
    WHERE status = 'completed'
),
with_group_key AS (
    SELECT
        customer_id,
        purchase_month,
        DATE_DIFF('month', DATE '2020-01-01', purchase_month)
        - ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY purchase_month
        )  AS group_key
    FROM monthly_purchases
),
islands AS (
    SELECT
        customer_id,
        MIN(purchase_month)  AS streak_start,
        MAX(purchase_month)  AS streak_end,
        COUNT(*)             AS streak_months
    FROM with_group_key
    GROUP BY customer_id, group_key
),
streak_summary AS (
    SELECT
        customer_id,
        -- 最長ストリーク
        MAX(streak_months)  AS longest_streak,

        -- 現在のストリーク：直前の購買月が「先月」であるIslandのみ
        -- 基準日の前月と streak_end が一致しているものが「現在進行中」
        MAX(
            CASE
                WHEN streak_end = DATE_TRUNC('month', DATE_ADD('month', -1, CURRENT_DATE))
                THEN streak_months
                ELSE 0
            END
        )  AS current_streak,

        -- 現在ストリークの開始月
        MIN(
            CASE
                WHEN streak_end = DATE_TRUNC('month', DATE_ADD('month', -1, CURRENT_DATE))
                THEN streak_start
                ELSE NULL
            END
        )  AS current_streak_start
    FROM islands
    GROUP BY customer_id
)
SELECT
    customer_id,
    current_streak,
    current_streak_start,
    longest_streak,
    -- キャンペーン対象フラグ（例：3ヶ月連続達成）
    CASE WHEN current_streak >= 3 THEN 1 ELSE 0 END  AS campaign_eligible
FROM streak_summary
ORDER BY current_streak DESC, longest_streak DESC;

-- ストリーク長の分布

WITH monthly_purchases AS (
    SELECT DISTINCT
        customer_id,
        DATE_TRUNC('month', order_date)  AS purchase_month
    FROM orders
    WHERE status = 'completed'
),
with_group_key AS (
    SELECT
        customer_id,
        purchase_month,
        DATE_DIFF('month', DATE '2020-01-01', purchase_month)
        - ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY purchase_month
        )  AS group_key
    FROM monthly_purchases
),
islands AS (
    SELECT
        customer_id,
        MAX(purchase_month)  AS streak_end,
        COUNT(*)             AS streak_months
    FROM with_group_key
    GROUP BY customer_id, group_key
),
current_streaks AS (
    SELECT
        customer_id,
        MAX(CASE
            WHEN streak_end = DATE_TRUNC('month', DATE_ADD('month', -1, CURRENT_DATE))
            THEN streak_months ELSE 0
        END)  AS current_streak
    FROM islands
    GROUP BY customer_id
)
SELECT
    current_streak         AS streak_length,
    COUNT(customer_id)     AS customer_count,
    ROUND(
        COUNT(customer_id) * 100.0
        / SUM(COUNT(customer_id)) OVER ()
    , 1)                   AS pct
FROM current_streaks
GROUP BY current_streak
ORDER BY current_streak DESC;