select
    INTERVAL_DATE,
    ARENA_GROUP,
    CURRENT_ARENA_INDEX,
    TROPHY_GROUP,
    TROPHY_GROUP_2,
    ARENA,
    ARENA_TIERS,
    ARENA_RANKS,
    LTV_GROUP,
    IS_PAYER,
    LO_ENTRY_TROPHY_MODE,
    LO_ENTRY_TROPHY_GAIN,
    LO_ENTRY_TROPHY_LOSS,
    LO_ENTRY_MODIFIER_TYPE1,
    PVP_MODE,
    MATCH_TYPE_ID,
    MATCH_TYPE,
    MATCH_MODE,
    MODIFIER_1,
    MODIFIER_2,
    MATCH_MODIFIER_3,
    MATCH_SUB_TYPE,
    RESOURCE_SUB_TYPE,
    BOOSTER_NAME,
    RIVAL_RESOURCE_SUB_TYPE,
    RUMBLE_SIZE,
    RIVAL_BOOSTER_NAME,
    ROUNDS_CNT,
    IS_PRIVATE,
    IS_MC_MATCH,
    IS_RIVAL_BOT,
    TURN_INDEX,
    APP_VERSION,
    SOLO_TYPE,
    MUTATION_OPTIONS,
    MUTATIONS_PICK,
    ROUNDS,
    MATCH_SCORE,
    RIVAL_MATCH_SCORE,
    MATCH_DURATION_MINS,
    BOOSTER_ACTIVATIONS,
    RESYNCS,
    RESYNC_MATCHES,
    ERROR_RESYNCS,
    ERROR_RESYNC_MATCHES,
    CONCEDED_MATCHES,
    CONCEDED_MATCHES_1_10,
    CONCEDED_MATCHES_10_60,
    CONCEDED_MATCHES_AFTER_60SEC,
    OOT,
    OOT_MATCHES,
    MATCHES,
    USERS,
    WINS,
    AVG_MATCH_RANK,
    AGIANST_BOT_MATCHES,
    TOTAL_SECONDS_PLAYED_IN_SEGMENT,
    TOTAL_BOOSTER_ACTIVATIONS_IN_SEGMENT,
    TOTAL_FOURS,
    TOTAL_FIVES,
    MATCH_MAKING_FOUND_TIME,
    PICK_NM,
    PICKS,
    MATCH_ID,
    USER_ID,
    to_timestamp(INTERVAL_DATE) as LAST_UPDATED_DT,
    current_timestamp() as DW_INSERT_DT
from
    (
        select
            INTERVAL_DATE,
            ARENA_GROUP,
            CURRENT_ARENA_INDEX,
            TROPHY_GROUP,
            TROPHY_GROUP_2,
            ARENA,
            ARENA_TIERS,
            ARENA_RANKS,
            LTV_GROUP,
            IS_PAYER,
            LO_ENTRY_TROPHY_MODE,
            LO_ENTRY_TROPHY_GAIN,
            LO_ENTRY_TROPHY_LOSS,
            LO_ENTRY_MODIFIER_TYPE1,
            PVP_MODE,
            MATCH_TYPE_ID,
            MATCH_TYPE,
            MATCH_MODE,
            MODIFIER_1,
            MODIFIER_2,
            MATCH_MODIFIER_3,
            MATCH_SUB_TYPE,
            RESOURCE_SUB_TYPE,
            BOOSTER_NAME,
            RIVAL_RESOURCE_SUB_TYPE,
            RUMBLE_SIZE,
            RIVAL_BOOSTER_NAME,
            ROUNDS_CNT,
            IS_PRIVATE,
            IS_MC_MATCH,
            IS_RIVAL_BOT,
            TURN_INDEX,
            APP_VERSION,
            SOLO_TYPE,
            MUTATION_OPTIONS,
            MUTATIONS_PICK,
            ROUNDS,
            MATCH_SCORE,
            RIVAL_MATCH_SCORE,
            MATCH_DURATION_MINS,
            BOOSTER_ACTIVATIONS,
            RESYNCS,
            RESYNC_MATCHES,
            ERROR_RESYNCS,
            ERROR_RESYNC_MATCHES,
            CONCEDED_MATCHES,
            CONCEDED_MATCHES_1_10,
            CONCEDED_MATCHES_10_60,
            CONCEDED_MATCHES_AFTER_60SEC,
            OOT,
            OOT_MATCHES,
            MATCHES,
            USERS,
            WINS,
            AVG_MATCH_RANK,
            AGIANST_BOT_MATCHES,
            TOTAL_SECONDS_PLAYED_IN_SEGMENT,
            TOTAL_BOOSTER_ACTIVATIONS_IN_SEGMENT,
            TOTAL_FOURS,
            TOTAL_FIVES,
            MATCH_MAKING_FOUND_TIME,
            PICK_NM,
            PICKS,
            MATCH_ID,
            USER_ID,
            to_timestamp(INTERVAL_DATE) as LAST_UPDATED_DT,
            current_timestamp() as DW_INSERT_DT
        from
            (
                SELECT
                    match_end_date AS INTERVAL_DATE,
                    match_id,
                    user_id,
                    arena_group,
                    current_arena_index,
                    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,
                    CASE
                        WHEN trophies_cnt < 125 THEN '0-125'
                        WHEN trophies_cnt < 1400 THEN '125-1400'
                        WHEN trophies_cnt < 2000 THEN '1400-2000'
                        WHEN trophies_cnt < 3800 THEN '2000-3800'
                        WHEN trophies_cnt < 4500 THEN '3800-4500'
                        WHEN trophies_cnt < 5500 THEN '4500-5500'
                        WHEN trophies_cnt < 7000 THEN '5500-7000'
                        WHEN trophies_cnt < 10000 THEN '7000-10000'
                        WHEN trophies_cnt < 13000 THEN '10000-13000'
                        WHEN trophies_cnt < 16000 THEN '13000-16000'
                        WHEN trophies_cnt < 20000 THEN '16000-20000'
                        WHEN trophies_cnt < 30000 THEN '20000-30000'
                        WHEN trophies_cnt >= 30000 THEN '30000+'
                    END AS trophy_group_2,
                    case
                        when trophies_cnt < 50 then '0_spotlight_Stars'
                        when trophies_cnt < 300 then '1_Jungle_Jam'
                        when trophies_cnt < 500 then '2_Dustville_Duel'
                        when trophies_cnt < 800 then '3_FireWorks_Festival'
                        when trophies_cnt < 1100 then '4_Crystal_cave'
                        when trophies_cnt < 1400 then '5_Laser_Matches'
                        when trophies_cnt < 1700 then '6_Funcky_Tiles'
                        when trophies_cnt < 2000 then '7_Plunder_Pirates'
                        when trophies_cnt < 2300 then '8_Sugar_Rush'
                        when trophies_cnt < 2600 then '9_Cloud_City'
                        when trophies_cnt < 2900 then '10_Vegas_Views'
                        when trophies_cnt < 3200 then '11_Bombs_Away'
                        when trophies_cnt < 3500 then '12_Tribal_Trouble'
                        when trophies_cnt < 3800 then '13_Color_Crystals'
                        when trophies_cnt < 4500 then '14_Challenger_1'
                        when trophies_cnt < 5500 then '15_Challenger_2'
                        when trophies_cnt < 7000 then '16_Challenger_3'
                        when trophies_cnt < 10000 then '17_Master_1'
                        when trophies_cnt < 13000 then '18_Master_2'
                        when trophies_cnt < 16000 then '19_Master_3'
                        when trophies_cnt < 20000 then '20_Grand_Master_1'
                        when trophies_cnt < 25000 then '21_Grand_Master_2'
                        when trophies_cnt < 30000 then '22_Grand_Master_3'
                        when trophies_cnt < 32000 then '23_Legend_1'
                        when trophies_cnt < 34000 then '24_Legend_2'
                        when trophies_cnt < 36000 then '25_Legend_3'
                        when trophies_cnt < 38000 then '26_Legend_4'
                        when trophies_cnt < 40000 then '27_Legend_5'
                        when trophies_cnt < 43000 then '28_Supreme_Legend_1'
                        when trophies_cnt < 46000 then '29_Supreme_Legend_2'
                        when trophies_cnt < 50000 then '30_Supreme_Legend_3'
                        when trophies_cnt < 55000 then '31_Supreme_Legend_4'
                        when trophies_cnt < 60000 then '32_Supreme_Legend_5'
                        when trophies_cnt < 70000 then '33_Ultimate_Legend_1'
                        when trophies_cnt < 85000 then '34_Ultimate_Legend_2'
                        when trophies_cnt < 105000 then '35_Ultimate_Legend_3'
                        when trophies_cnt < 130000 then '36_Ultimate_Legend_4'
                        else '37_Ultimate_Legend_5'
                    end as arena,
                    case
                        when trophies_cnt < 3800 then '00_Studio'
                        when trophies_cnt < 7000 then '01_Challenger'
                        when trophies_cnt < 16000 then '02_Master'
                        when trophies_cnt < 30000 then '03_Grand_Master'
                        when trophies_cnt < 40000 then '04_Legends'
                        when trophies_cnt < 43000 then '05_Supreme_Legend'
                        else '06_Ultimate_Legend'
                    end as arena_tiers,
                    case
                        when trophies_cnt < 3800 then '00_Studio'
                        when trophies_cnt < 30000 then '01_Masters'
                        else '03_Legends_league'
                    end as arena_ranks,
                    ltv_group,
                    is_payer,
                    lo_entry_trophy_mode,
                    lo_entry_trophy_gain,
                    lo_entry_trophy_loss,
                    lo_entry_modifier_type1,
                    pvp_mode,
                    MATCH_TYPE_ID,
                    CASE
                        WHEN is_random_mixer = TRUE THEN 'Daily_Random_Mixer'
                        ELSE match_type
                    END AS MATCH_TYPE,
                    MATCH_MODE,
                    MODIFIER_1,
                    MODIFIER_2,
                    match_modifier_3,
                    concat(
                        MATCH_SUB_TYPE,
                        '-',
                        MATCH_MODE,
                        '+',
                        MODIFIER_1,
                        '-',
                        MODIFIER_2,
                        '-',
                        match_modifier_3
                    ) AS match_sub_type,
                    RESOURCE_SUB_TYPE,
                    BOOSTER_NAME,
                    RIVAL_RESOURCE_SUB_TYPE,
                    RUMBLE_SIZE,
                    CASE
                        WHEN PVP_MODE NOT LIKE 'PvP' THEN 'No Rival(Solo/Rumble)'
                        ELSE RIVAL_BOOSTER_NAME
                    END RIVAL_BOOSTER_NAME,
                    rounds_cnt,
                    is_private,
                    is_mc_match,
                    is_rival_bot,
                    turn_index,
                    app_version,
                    CASE
                        when LO_EVENT_ID like '%-mc-%' then 'MC'
                        when LO_EVENT_ID like '%cosmic%' then 'cosmic'
                        when LO_EVENT_ID like '%rally%' then 'rally'
                        when LO_EVENT_ID like '%heist%' then 'heist'
                    end Solo_Type,
                    mutation_options,
                    mutations_pick,
                    to_varchar(mutations_pick [0]) pick0,
                    to_varchar(mutations_pick [1]) pick1,
                    to_varchar(mutations_pick [2]) pick2,
                    to_varchar(mutations_pick [3]) pick3,
                    to_varchar(mutations_pick [4]) pick4,
                    to_varchar(mutations_pick [5]) pick5,
                    to_varchar(mutations_pick [6]) pick6,
                    to_varchar(mutations_pick [7]) pick7,
                    to_varchar(mutations_pick [8]) pick8,
                    to_varchar(mutations_pick [9]) pick9,
                    SUM(ROUNDS_CNT) AS ROUNDS,
                    SUM(MATCH_SCORE) AS MATCH_SCORE,
                    SUM(rival_MATCH_SCORE) AS rival_MATCH_SCORE,
                    SUM(MATCH_DURATION_SEC) / 60 AS MATCH_DURATION_MINS,
                    SUM(ABILITY_ACTIVATED_CNT) AS BOOSTER_ACTIVATIONS,
                    SUM(ifnull(CS_RESYNCS, 0)) AS RESYNCS,
                    COUNT(
                        DISTINCT CASE
                            WHEN CS_RESYNCS > 0 THEN concat(match_id, user_id, rematch_cnt)
                        END
                    ) AS RESYNC_MATCHES,
                    SUM(ifnull(ERROR_RESYNCS, 0)) AS ERROR_RESYNCS,
                    COUNT(
                        DISTINCT CASE
                            WHEN ERROR_RESYNCS > 0 THEN concat(match_id, user_id, rematch_cnt)
                        END
                    ) AS ERROR_RESYNC_MATCHES,
                    SUM(
                        CASE
                            WHEN is_conceded = 1
                            AND is_won = 0
                            AND match_duration_sec = 0 THEN 1
                        END
                    ) AS CONCEDED_MATCHES,
                    SUM(
                        CASE
                            WHEN is_conceded = 1
                            AND is_won = 0
                            AND match_duration_sec BETWEEN 1
                            AND 10 THEN 1
                        END
                    ) AS CONCEDED_MATCHES_1_10,
                    SUM(
                        CASE
                            WHEN is_conceded = 1
                            AND is_won = 0
                            AND match_duration_sec BETWEEN 10
                            AND 60 THEN 1
                        END
                    ) AS CONCEDED_MATCHES_10_60,
                    SUM(
                        CASE
                            WHEN is_conceded = 1
                            AND is_won = 0
                            AND match_duration_sec > 60 THEN 1
                        END
                    ) AS CONCEDED_MATCHES_AFTER_60sec,
                    SUM(ifnull(rival_played_out_of_time, 0)) AS OOT,
                    COUNT(
                        DISTINCT CASE
                            WHEN rival_played_out_of_time > 0 THEN concat(match_id, user_id, rematch_cnt)
                        END
                    ) AS OOT_MATCHES,
                    COUNT(DISTINCT concat(match_id, user_id, rematch_cnt)) AS MATCHES,
                    count(distinct user_id) users,
                    SUM(is_won) AS wins,
                    AVG(MATCH_RANK) AS AVG_MATCH_RANK,
                    sum(is_rival_bot) AS agianst_bot_MATCHES,
                    sum(MATCH_DURATION_SEC) as total_seconds_played_in_segment,
                    sum(ability_activated_cnt) as total_booster_activations_in_segment,
                    sum(four_matched_cnt) as total_fours,
                    sum(five_matched_cnt) as total_fives,
                    sum(match_making_found_time) as match_making_found_time
                FROM
                    MATCH_MASTERS.PROD.F_USER_MATCH a
                    left join (
                        SELECT
                            *
                        FROM
                            (
                                SELECT
                                    *,CASE
                                        WHEN lo_entry_ts IS NULL THEN lo_entry_update_ts
                                        ELSE lo_entry_ts
                                    END lo_entry_ts2,
                                    ROW_NUMBER() OVER (
                                        partition BY lo_entry_id
                                        ORDER BY
                                            lo_entry_ts2 DESC
                                    ) rn
                                FROM
                                    MATCH_MASTERS.PROD.F_LIVEOPS_CALENDAR
                            )
                        WHERE
                            1 = 1
                            AND rn = 1
                            AND date(DERIVED_TSTAMP) >= current_date -61
                    ) b on a.calendar_entry_id = b.lo_entry_id
                WHERE
                    is_bot = FALSE
                    AND MATCH_END_DATE >= current_date -61
                    AND match_mode = 'mutation-lab-rumble-mode'
                GROUP BY
                    ALL
            ) UNPIVOT(
                picks FOR pick_nm IN (
                    pick0,
                    pick1,
                    pick2,
                    pick3,
                    pick4,
                    pick5,
                    pick6,
                    pick7,
                    pick8,
                    pick9
                )
            ))