--以下２つのEntry ASINそれぞれに集計した後にUNION ALLで合算する
--entryasin1
--entryasin2
-- allasinlist というテーブルを別途作成しアップロードしておいて販売してる全てのasinをリスト化しておく。自社で販売しているasinに限定してCustomer Lifetime Valueを計算する

WITH 
-- Initial ASIN 1
initial_purchases_1 AS (
    SELECT
        user_id,
        MIN(CONVERT_TIME_ZONE_FROM_UTC(event_date_utc, 'Asia/Tokyo')) AS first_purchase_date
    FROM
        TABLE(EXTEND_TIME_WINDOW('conversions_all', 'P60D', 'P1D')) -- Extend time window for initial purchases
    WHERE
        new_to_brand = TRUE
        AND tracked_asin = 'entryasin1'
        AND CONVERT_TIME_ZONE_FROM_UTC(event_date_utc, 'Asia/Tokyo') BETWEEN '2024-01-01' AND '2024-02-29'
    GROUP BY
        user_id
),
repeat_purchases_1 AS (
    SELECT
        r.user_id,
        r.tracked_asin AS repeat_asin,
        'entryasin1' AS initial_asin, -- Initial ASIN 1
        SUM(r.total_product_sales) AS total_repeat_revenue,
        SUM(r.total_units_sold) AS total_repeat_units
    FROM
        TABLE(EXTEND_TIME_WINDOW('conversions_all', 'P300D', 'P1D')) r -- Extend time window for repeat purchases
    JOIN
        initial_purchases_1 i ON r.user_id = i.user_id
    JOIN
        allasinlist p ON r.tracked_asin = p.asin -- Filter repeat purchases by ASINs in external table
    WHERE
        r.new_to_brand = FALSE
        AND CONVERT_TIME_ZONE_FROM_UTC(r.event_date_utc, 'Asia/Tokyo') >= i.first_purchase_date
        AND SECONDS_BETWEEN(i.first_purchase_date, CONVERT_TIME_ZONE_FROM_UTC(r.event_date_utc, 'Asia/Tokyo')) <= 231 * 24 * 3600
        AND r.total_product_sales > 0
    GROUP BY
        r.user_id, r.tracked_asin
),

-- Initial ASIN 2
initial_purchases_2 AS (
    SELECT
        user_id,
        MIN(CONVERT_TIME_ZONE_FROM_UTC(event_date_utc, 'Asia/Tokyo')) AS first_purchase_date
    FROM
        TABLE(EXTEND_TIME_WINDOW('conversions_all', 'P60D', 'P1D')) -- Extend time window for initial purchases
    WHERE
        new_to_brand = TRUE
        AND tracked_asin = 'entryasin2'
        AND CONVERT_TIME_ZONE_FROM_UTC(event_date_utc, 'Asia/Tokyo') BETWEEN '2024-01-01' AND '2024-02-29'
    GROUP BY
        user_id
),
repeat_purchases_2 AS (
    SELECT
        r.user_id,
        r.tracked_asin AS repeat_asin,
        'entryasin2' AS initial_asin, -- Initial ASIN 2
        SUM(r.total_product_sales) AS total_repeat_revenue,
        SUM(r.total_units_sold) AS total_repeat_units
    FROM
        TABLE(EXTEND_TIME_WINDOW('conversions_all', 'P300D', 'P1D')) r -- Extend time window for repeat purchases
    JOIN
        initial_purchases_2 i ON r.user_id = i.user_id
    JOIN
        allasinlist p ON r.tracked_asin = p.asin -- Filter repeat purchases by ASINs in external table
    WHERE
        r.new_to_brand = FALSE
        AND CONVERT_TIME_ZONE_FROM_UTC(r.event_date_utc, 'Asia/Tokyo') >= i.first_purchase_date
        AND SECONDS_BETWEEN(i.first_purchase_date, CONVERT_TIME_ZONE_FROM_UTC(r.event_date_utc, 'Asia/Tokyo')) <= 231 * 24 * 3600
        AND r.total_product_sales > 0
    GROUP BY
        r.user_id, r.tracked_asin
)

-- Union results for each ASIN
SELECT initial_asin, repeat_asin, SUM(total_repeat_revenue) AS total_repeat_revenue, SUM(total_repeat_units) AS total_repeat_units
FROM repeat_purchases_1
GROUP BY initial_asin, repeat_asin

UNION ALL

SELECT initial_asin, repeat_asin, SUM(total_repeat_revenue) AS total_repeat_revenue, SUM(total_repeat_units) AS total_repeat_units
FROM repeat_purchases_2
GROUP BY initial_asin, repeat_asin;