select
    INTERVAL_DATE
    ,LTV_GROUP
    ,LEAGUE
    ,TROPHY_GROUP
    ,payers_segment
    ,SENIORITY_BIN
    ,TRANSACTION_SOURCE
    ,RESOURCE_ID
    ,COIN_PRICE
    ,RESOURCE_CNT
    ,OCCASIONS_BOUGHT
    ,COINS_SPENT
    ,COINS_PER_ITEM
    ,INTERVAL_DATE as LAST_UPDATED_DT
    ,current_timestamp() as DW_INSERT_DT
from
(
    (
        (
            select
                date(derived_tstamp) interval_date,
                ltv_group_static ltv_group,
                league,
                trophy_group,
                payers_segment,
                seniority_bin,
                'boxes' transaction_source,
                to_varchar(prize_container [0] [1]) resource_id,
                coin_price,case
                    when prize_container [0] [1] like '%Big%' then mode(resource_cnt) * 6
                    when prize_container [0] [1] like '%Small%' then mode(resource_cnt) * 3
                end resource_cnt,case
                    when prize_container [0] [1] like '%Big%' then round(count(*) / 6)
                    when prize_container [0] [1] like '%Small%' then round(count(*) / 3)
                end occasions_bought,case
                    when prize_container [0] [1] like '%Big%' then round(sum(coin_price) / 6)
                    when prize_container [0] [1] like '%Small%' then round(sum(coin_price) / 3)
                end coins_spent,
                sum(coin_price) / count(*) coins_per_item
            from
                (
                    select
                        A.*,
                        B.ltv_group ltv_group_static,
                        B.league,
                        B.trophy_group,
                        B.payers_segment,
                        B.seniority_bin
                    from
                        (
                            (
                                select
                                    *,
                                    date(derived_tstamp) interval_date
                                from
                                    MATCH_MASTERS.PROD.F_USER_RESOURCE_TRANSACTION
                                where 1=1
                                    and date(derived_tstamp) >= current_date - 61
                                    and is_coin_paid = 1
                                    and is_received_resource = TRUE
                                    and is_chest = 1
                            ) A
                            left join (
                                select
                                    user_id,
                                    interval_date,
                                    ltv_group,CASE
                                        WHEN trophies_cnt < 3800 THEN 'Studios'
                                        WHEN trophies_cnt < 30000 THEN 'Master_League'
                                        WHEN trophies_cnt >= 30000 THEN 'Legends_League'
                                    END as league,
                                    CASE
                                        WHEN trophies_cnt < 1500 THEN '0-1500'
                                        WHEN trophies_cnt < 3800 THEN '1500-3800'
                                        WHEN trophies_cnt < 8000 THEN '3800-8000'
                                        WHEN trophies_cnt < 17000 THEN '8000-17000'
                                        WHEN trophies_cnt < 30000 THEN '17000-30000'
                                        ELSE '30000+'
                                    END AS trophy_group,
                                    payers_segment,
                                    seniority_bin
                                from
                                    MATCH_MASTERS.prod.daily_users_from_params
                                where 1=1
                                    and interval_date >= current_date - 61
                            ) B on A.user_id = B.user_id
                            and A.interval_date = B.interval_date
                        )
                )
            where
                is_chest = 1
                and is_coin_paid = 1
                and date(derived_tstamp) >= current_date - 61
            group by
                1,
                2,
                3,
                4,
                5,
                6,
                7,
                8,
                9
        )
        union all
            (
                SELECT
                    interval_date,
                    ltv_group_static ltv_group,
                    league,
                    trophy_group,
                    payers_segment,
                    seniority_bin,
                    transaction_source,
                    resource_id,
                    coin_price,
                    resource_cnt,
                    count(*) occasions_bought,
                    sum(coin_price) coins_spent,
                    sum(coin_price) / count(*) coins_per_item
                from
                    (
                        select
                            A.*,
                            B.ltv_group ltv_group_static,
                            B.league,
                            B.payers_segment,
                            B.trophy_group,
                            B.seniority_bin
                        from
                            (
                                (
                                    select
                                        *,
                                        date(derived_tstamp) interval_date
                                    from
                                        MATCH_MASTERS.PROD.F_USER_RESOURCE_TRANSACTION
                                    where
                                        date(derived_tstamp) >= current_date - 61
                                        and is_coin_paid = 1
                                        and is_received_resource = TRUE
                                        and is_chest = 0
                                        and transaction_source != 'daily_deals'
                                ) A
                                left join (
                                    select
                                        user_id,
                                        interval_date,
                                        ltv_group,CASE
                                            WHEN trophies_cnt < 3800 THEN 'Studios'
                                            WHEN trophies_cnt < 30000 THEN 'Master_League'
                                            WHEN trophies_cnt >= 30000 THEN 'Legends_League'
                                        END as league,
                                        CASE
                                            WHEN trophies_cnt < 1500 THEN '0-1500'
                                            WHEN trophies_cnt < 3800 THEN '1500-3800'
                                            WHEN trophies_cnt < 8000 THEN '3800-8000'
                                            WHEN trophies_cnt < 17000 THEN '8000-17000'
                                            WHEN trophies_cnt < 30000 THEN '17000-30000'
                                            ELSE '30000+'
                                        END AS trophy_group,
                                        payers_segment,
                                        seniority_bin
                                    from
                                        MATCH_MASTERS.prod.daily_users_from_params
                                    where 1=1
                                        and interval_date >= current_date - 61
                                ) B on A.user_id = B.user_id
                                and A.interval_date = B.interval_date
                            )
                    )
                group by
                    1,
                    2,
                    3,
                    4,
                    5,
                    6,
                    7,
                    8,
                    9,
                    10
                order by
                    1 desc,
                    2,
                    3
            )
        union all
            (
                SELECT
                    interval_date,
                    ltv_group_static ltv_group,
                    league,
                    trophy_group,
                    payers_segment,
                    seniority_bin,
                    transaction_source,
                    resource_id,
                    resource_cnt coin_price,
                    1 resource_cnt,
                    count(*) occasions_bought,
                    sum(resource_cnt) coins_spent,
                    resource_cnt coins_per_item
                from
                    (
                        select
                            A.*,
                            B.ltv_group ltv_group_static,
                            B.league,
                            B.payers_segment,
                            B.trophy_group,
                            B.seniority_bin
                        from
                            (
                                (
                                    select
                                        *,
                                        date(derived_tstamp) interval_date
                                    from
                                        MATCH_MASTERS.PROD.F_USER_RESOURCE_TRANSACTION
                                    where 1=1
                                        and date(derived_tstamp) >= current_date - 61
                                        and resource_id = 'Coin'
                                        and is_received_resource = FALSE
                                        and transaction_source in ('tournament', 'match_end', 'create_team')
                                ) A
                                left join (
                                    select
                                        user_id,
                                        interval_date,
                                        ltv_group,CASE
                                            WHEN trophies_cnt < 3800 THEN 'Studios'
                                            WHEN trophies_cnt < 30000 THEN 'Master_League'
                                            WHEN trophies_cnt >= 30000 THEN 'Legends_League'
                                        END as league,
                                        CASE
                                            WHEN trophies_cnt < 1500 THEN '0-1500'
                                            WHEN trophies_cnt < 3800 THEN '1500-3800'
                                            WHEN trophies_cnt < 8000 THEN '3800-8000'
                                            WHEN trophies_cnt < 17000 THEN '8000-17000'
                                            WHEN trophies_cnt < 30000 THEN '17000-30000'
                                            ELSE '30000+'
                                        END AS trophy_group,
                                        payers_segment,
                                        seniority_bin
                                    from
                                        MATCH_MASTERS.prod.daily_users_from_params
                                    where
                                        interval_date >= current_date - 61
                                ) B on A.user_id = B.user_id
                                and A.interval_date = B.interval_date
                            )
                    )
                group by
                    1,
                    2,
                    3,
                    4,
                    5,
                    6,
                    7,
                    8,
                    9,
                    10
                order by
                    1 desc,
                    2,
                    3
            )
    )
union all
    (
        SELECT
            interval_date,
            ltv_group_static ltv_group,
            league,
            trophy_group,
            payers_segment,
            seniority_bin,
            transaction_source,
            offer_id resource_id,
            coin_price,
            1 resource_cnt,
            count(*) occasions_bought,
            sum(coin_price) coins_spent,
            sum(coin_price) / count(*) coins_per_item
        from
            (
                select
                    A.*,
                    B.ltv_group ltv_group_static,
                    B.league,
                    B.payers_segment,
                    B.trophy_group,
                    B.seniority_bin
                from
                    (
                        (
                            select
                                *,
                                date(derived_tstamp) interval_date
                            from
                                MATCH_MASTERS.PROD.F_USER_RESOURCE_TRANSACTION
                            where 1=1
                                and date(derived_tstamp) >= current_date - 61
                                and resource_id = 'Coin'
                                and is_received_resource = FALSE
                                and transaction_source = 'daily_deals'
                        ) A
                        left join (
                            select
                                user_id,
                                interval_date,
                                ltv_group,CASE
                                    WHEN trophies_cnt < 3800 THEN 'Studios'
                                    WHEN trophies_cnt < 30000 THEN 'Master_League'
                                    WHEN trophies_cnt >= 30000 THEN 'Legends_League'
                                END as league,
                                CASE
                                    WHEN trophies_cnt < 1500 THEN '0-1500'
                                    WHEN trophies_cnt < 3800 THEN '1500-3800'
                                    WHEN trophies_cnt < 8000 THEN '3800-8000'
                                    WHEN trophies_cnt < 17000 THEN '8000-17000'
                                    WHEN trophies_cnt < 30000 THEN '17000-30000'
                                    ELSE '30000+'
                                END AS trophy_group,
                                payers_segment,
                                seniority_bin
                            from
                                MATCH_MASTERS.prod.daily_users_from_params
                            where 1=1
                                and interval_date >= current_date - 61
                        ) B on A.user_id = B.user_id
                        and A.interval_date = B.interval_date
                    )
            )
        group by
            1,
            2,
            3,
            4,
            5,
            6,
            7,
            8,
            9,
            10
        order by
            1 desc,
            2,
            3
    )
)
where 1=1
and interval_date >= current_date - 61