WITH user_range AS (
    SELECT 
        user_id,
        DATE(create_time) AS create_date,
        DATE(last_sync_time) AS last_sync_date
    FROM user_info
),
user_hero AS (
    SELECT 
        user_id, 
        ARRAY_AGG(DISTINCT hero_name) AS hero_list 
    FROM hero_step 
    GROUP BY user_id
),
date_series AS (
    SELECT 
        ur.user_id,
        ur.create_date,
        ur.last_sync_date,
        date_id
    FROM user_range ur
    CROSS JOIN UNNEST(
        SEQUENCE(ur.create_date, ur.last_sync_date, INTERVAL '1' DAY)
    ) AS t(date_id)
),
base AS (
    SELECT 
        ds.user_id,
        ds.date_id,
        hero_name,
        ds.create_date,
        ds.last_sync_date
    FROM date_series ds
    JOIN user_hero uh ON ds.user_id = uh.user_id
    CROSS JOIN UNNEST(uh.hero_list) AS t(hero_name)
)
SELECT 
    b.user_id,
    b.hero_name,
    b.date_id,
    hs.hero_step,
    hs.hero_power_rank,
    COALESCE(pt.total_pay_cnt, 0) AS pay_cnt,
    COALESCE(pbh.pay_hero_name_cnt, 0) AS pay_hero_name_cnt,
    COALESCE(pt.total_pay_amt, 0) AS pay_amt,
    COALESCE(pbh.pay_hero_name_amt, 0) AS pay_hero_name_amt,
    b.create_date,
    b.last_sync_date,
    DATE_DIFF('day', b.create_date, b.date_id) + 1 AS lifedays,
    fpd.first_pay_hero_name_date
FROM base b
LEFT JOIN hero_step hs 
    ON b.user_id = hs.user_id 
    AND b.hero_name = hs.hero_name 
    AND b.date_id = hs.dt
LEFT JOIN (
    SELECT 
        user_id,
        DATE(pay_date) AS pay_date,
        COUNT(*) AS total_pay_cnt,
        SUM(consume_amt) AS total_pay_amt
    FROM pay_detail
    GROUP BY 1, 2
) pt ON b.user_id = pt.user_id AND b.date_id = pt.pay_date
LEFT JOIN (
    SELECT 
        user_id,
        DATE(pay_date) AS pay_date,
        hero_name,
        COUNT(*) AS pay_hero_name_cnt,
        SUM(consume_amt) AS pay_hero_name_amt
    FROM pay_detail
    GROUP BY 1, 2, 3
) pbh ON b.user_id = pbh.user_id AND b.hero_name = pbh.hero_name AND b.date_id = pbh.pay_date
LEFT JOIN (
    SELECT 
        user_id,
        hero_name,
        MIN(DATE(pay_date)) AS first_pay_hero_name_date
    FROM pay_detail
    GROUP BY 1, 2
) fpd ON b.user_id = fpd.user_id AND b.hero_name = fpd.hero_name