t_booster_mode_dashboard = {
    '01_truncate_transformation_table': '''
        truncate CANDIVORE.TRANSFORMATION.TRANS_BOOSTER_MODE_DASHBOARD
    ''',
    '02_set_last_update_variable': '''
                set T_BOOSTER_MODE_DASHBOARD_UPDATED_DT_MNG = (
                    SELECT
            date(
                NVL(max(LAST_LOAD_DATE), to_timestamp('2023-08-25'))
            ) as LAST_UPDATED_DT_MNG
        FROM
            candivore.manage.management_table
        WHERE
            1 = 1
            AND table_name = 'CANDIVORE.SEMANTIC_LAYER.T_BOOSTER_MODE_DASHBOARD'
        ORDER BY
            ID DESC
        LIMIT
            1
        );
    ''',
    '03_insert_delta_into_trans_table': '''
      INSERT INTO CANDIVORE.TRANSFORMATION.TRANS_BOOSTER_MODE_DASHBOARD (
         INTERVAL_DATE
        ,ARENA_GROUP
        ,CURRENT_ARENA_INDEX
        ,TROPHY_GROUP
        ,payers_segment
        ,LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE
        ,LTV_GROUP
        ,LO_ENTRY_TROPHY_MODE
        ,LO_ENTRY_TROPHY_GAIN
        ,LO_ENTRY_TROPHY_LOSS
        ,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
        ,stakes_multiplier
        ,LAST_UPDATED_DT
        ,DW_INSERT_DT
        ,ROUNDS
        ,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
        ,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
    )
SELECT
    match_end_date AS INTERVAL_DATE,
    arena_group,
    current_arena_index,
    LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE,
    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,
    ltv_group,
    lo_entry_trophy_mode,
    lo_entry_trophy_gain,
    lo_entry_trophy_loss,
    pvp_mode,
    MATCH_TYPE_ID,
    case
        when PVP_MODE = 'Solo' then 'Solo'
        when pvp_mode = 'Rumble' then 'Rumble'
        when calendar_entry_id like '%onboarding%' then 'Onboarding'
        when calendar_entry_id like '%adventure%' then 'Adventure'
        when match_sub_type = 'boosters-clash-friendly' then 'Booster Clash'
        when match_sub_type = 'score-race' then 'Leaderboard'
        when is_random_mixer = TRUE then 'Random Mixer'
        else MATCH_TYPE
    end 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,
    ifnull(stakes_multiplier,1) as stakes_multiplier,
    match_end_date as last_updated_dt,
    current_timestamp() as dw_insert_dt,
    SUM(ROUNDS_CNT) AS ROUNDS,
    SUM(MATCH_SCORE) AS 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, a.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, a.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, a.user_id, rematch_cnt)
        END
    ) AS OOT_MATCHES,
    COUNT(DISTINCT concat(match_id, a.user_id, rematch_cnt)) AS MATCHES,
    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
    CANDIVORE.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
                    CANDIVORE.PROD.F_LIVEOPS_CALENDAR
                where 1=1
                and derived_tstamp > DATEADD(DAY, -7, $T_BOOSTER_MODE_DASHBOARD_UPDATED_DT_MNG)
            )
        WHERE
            rn = 1
    ) b on a.calendar_entry_id = b.lo_entry_id
left join
        (select user_id,interval_date,payers_segment from candivore.prod.daily_users_from_params where interval_date between DATEADD(DAY, -1, $T_BOOSTER_MODE_DASHBOARD_UPDATED_DT_MNG) and $T_BOOSTER_MODE_DASHBOARD_UPDATED_DT_MNG) K
        on a.user_id = K.user_id and a.match_end_date = K.interval_date
WHERE
    is_bot = FALSE
    AND MATCH_END_DATE > DATEADD(DAY, -3, $T_BOOSTER_MODE_DASHBOARD_UPDATED_DT_MNG)
GROUP BY
    1,
    2,
    3,
    4,
    5,
    6,
    7,
    8,
    9,
    10,
    11,
    12,
    13,
    14,
    15,
    16,
    17,
    18,
    19,
    20,
    21,
    22,
    23,
    24,
    25,
    26,
    27,
    28,
    29,
    30,
    31
    ;
    ''',
    '04_set_merge_error_helper': '''
        ALTER SESSION SET ERROR_ON_NONDETERMINISTIC_MERGE = false;
    ''',
    '05_merge_temp_to_prod': '''
        MERGE INTO CANDIVORE.SEMANTIC_LAYER.DW_T_BOOSTER_MODE_DASHBOARD AS t
        USING CANDIVORE.TRANSFORMATION.TRANS_BOOSTER_MODE_DASHBOARD AS S
            ON  t.INTERVAL_DATE             = s.INTERVAL_DATE
            AND t.ARENA_GROUP               = s.ARENA_GROUP
            AND t.CURRENT_ARENA_INDEX       = s.CURRENT_ARENA_INDEX
            AND t.TROPHY_GROUP              = s.TROPHY_GROUP
            AND t.payers_segment            = s.payers_segment
            AND t.LTV_GROUP                 = s.LTV_GROUP
            AND t.PVP_MODE                  = s.PVP_MODE
            AND t.MATCH_TYPE_ID             = s.MATCH_TYPE_ID
            AND t.MATCH_MODE                = s.MATCH_MODE
            AND (t.MATCH_SUB_TYPE           = s.MATCH_SUB_TYPE OR (s.MATCH_SUB_TYPE IS NULL AND t.MATCH_SUB_TYPE IS NULL ) )
            AND (t.MODIFIER_1               = s.MODIFIER_1 OR (s.MODIFIER_1 IS NULL AND t.MODIFIER_1 IS NULL ) )
            AND (t.MODIFIER_2               = s.MODIFIER_2 OR (s.MODIFIER_2 IS NULL AND t.MODIFIER_2 IS NULL ) )
            AND (t.MATCH_MODIFIER_3         = s.MATCH_MODIFIER_3 OR (s.MATCH_MODIFIER_3 IS NULL AND t.MATCH_MODIFIER_3 IS NULL ) )
            AND (t.RESOURCE_SUB_TYPE        = s.RESOURCE_SUB_TYPE OR (s.RESOURCE_SUB_TYPE IS NULL AND t.RESOURCE_SUB_TYPE IS NULL ) )
            AND (t.RIVAL_RESOURCE_SUB_TYPE  = s.RIVAL_RESOURCE_SUB_TYPE OR (s.RIVAL_RESOURCE_SUB_TYPE IS NULL AND t.RIVAL_RESOURCE_SUB_TYPE IS NULL ) )
            AND (t.IS_PRIVATE               = s.IS_PRIVATE OR (s.IS_PRIVATE IS NULL AND t.IS_PRIVATE IS NULL ) )
            AND t.BOOSTER_NAME              = s.BOOSTER_NAME
            AND t.RIVAL_BOOSTER_NAME        = s.RIVAL_BOOSTER_NAME
            AND t.IS_RIVAL_BOT              = s.IS_RIVAL_BOT
            AND t.APP_VERSION               = s.APP_VERSION
            AND t.IS_MC_MATCH               = s.IS_MC_MATCH
            AND (t.LO_ENTRY_TROPHY_MODE     = s.LO_ENTRY_TROPHY_MODE OR (s.LO_ENTRY_TROPHY_MODE IS NULL AND t.LO_ENTRY_TROPHY_MODE IS NULL ) )
            AND (t.LO_ENTRY_TROPHY_GAIN     = s.LO_ENTRY_TROPHY_GAIN OR (s.LO_ENTRY_TROPHY_GAIN IS NULL AND t.LO_ENTRY_TROPHY_GAIN IS NULL ) )
            AND (t.TURN_INDEX               = s.TURN_INDEX OR (s.TURN_INDEX IS NULL AND t.TURN_INDEX IS NULL ) )
            AND (t.RUMBLE_SIZE              = s.RUMBLE_SIZE OR (s.RUMBLE_SIZE IS NULL AND t.RUMBLE_SIZE IS NULL ) )
            AND (t.SOLO_TYPE                = s.SOLO_TYPE OR (s.SOLO_TYPE IS NULL AND t.SOLO_TYPE IS NULL ) )
            AND (t.stakes_multiplier        = s.stakes_multiplier OR (s.stakes_multiplier IS NULL AND t.stakes_multiplier IS NULL ) )

        WHEN MATCHED THEN UPDATE SET
             t. INTERVAL_DATE                       = s.INTERVAL_DATE
            ,t.ARENA_GROUP                          = s.ARENA_GROUP
            ,t.CURRENT_ARENA_INDEX                  = s.CURRENT_ARENA_INDEX
            ,t.TROPHY_GROUP                         = s.TROPHY_GROUP
            ,t.payers_segment                       = s.payers_segment
            ,t.LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE   = s.LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE
            ,t.LTV_GROUP                            = s.LTV_GROUP
            ,t.LO_ENTRY_TROPHY_MODE                 = s.LO_ENTRY_TROPHY_MODE
            ,t.LO_ENTRY_TROPHY_GAIN                 = s.LO_ENTRY_TROPHY_GAIN
            ,t.LO_ENTRY_TROPHY_LOSS                 = s.LO_ENTRY_TROPHY_LOSS
            ,t.PVP_MODE                             = s.PVP_MODE
            ,t.MATCH_TYPE_ID                        = s.MATCH_TYPE_ID
            ,t.MATCH_TYPE                           = s.MATCH_TYPE
            ,t.MATCH_MODE                           = s.MATCH_MODE
            ,t.MODIFIER_1                           = s.MODIFIER_1
            ,t.MODIFIER_2                           = s.MODIFIER_2
            ,t.MATCH_MODIFIER_3                     = s.MATCH_MODIFIER_3
            ,t.MATCH_SUB_TYPE                       = s.MATCH_SUB_TYPE
            ,t.RESOURCE_SUB_TYPE                    = s.RESOURCE_SUB_TYPE
            ,t.BOOSTER_NAME                         = s.BOOSTER_NAME
            ,t.RIVAL_RESOURCE_SUB_TYPE              = s.RIVAL_RESOURCE_SUB_TYPE
            ,t.RUMBLE_SIZE                          = s.RUMBLE_SIZE
            ,t.RIVAL_BOOSTER_NAME                   = s.RIVAL_BOOSTER_NAME
            ,t.ROUNDS_CNT                           = s.ROUNDS_CNT
            ,t.IS_PRIVATE                           = s.IS_PRIVATE
            ,t.IS_MC_MATCH                          = s.IS_MC_MATCH
            ,t.IS_RIVAL_BOT                         = s.IS_RIVAL_BOT
            ,t.TURN_INDEX                           = s.TURN_INDEX
            ,t.APP_VERSION                          = s.APP_VERSION
            ,t.SOLO_TYPE                            = s.SOLO_TYPE
            ,t.stakes_multiplier                    = s.stakes_multiplier
            ,t.ROUNDS                               = s.ROUNDS
            ,t.MATCH_SCORE                          = s.MATCH_SCORE
            ,t.MATCH_DURATION_MINS                  = s.MATCH_DURATION_MINS
            ,t.BOOSTER_ACTIVATIONS                  = s.BOOSTER_ACTIVATIONS
            ,t.RESYNCS                              = s.RESYNCS
            ,t.RESYNC_MATCHES                       = s.RESYNC_MATCHES
            ,t.ERROR_RESYNCS                        = s.ERROR_RESYNCS
            ,t.ERROR_RESYNC_MATCHES                 = s.ERROR_RESYNC_MATCHES
            ,t.CONCEDED_MATCHES                     = s.CONCEDED_MATCHES
            ,t.CONCEDED_MATCHES_1_10                = s.CONCEDED_MATCHES_1_10
            ,t.CONCEDED_MATCHES_10_60               = s.CONCEDED_MATCHES_10_60
            ,t.CONCEDED_MATCHES_AFTER_60SEC         = s.CONCEDED_MATCHES_AFTER_60SEC
            ,t.OOT                                  = s.OOT
            ,t.OOT_MATCHES                          = s.OOT_MATCHES
            ,t.MATCHES                              = s.MATCHES
            ,t.WINS                                 = s.WINS
            ,t.AVG_MATCH_RANK                       = s.AVG_MATCH_RANK
            ,t.AGIANST_BOT_MATCHES                  = s.AGIANST_BOT_MATCHES
            ,t.TOTAL_SECONDS_PLAYED_IN_SEGMENT      = s.TOTAL_SECONDS_PLAYED_IN_SEGMENT
            ,t.TOTAL_BOOSTER_ACTIVATIONS_IN_SEGMENT = s.TOTAL_BOOSTER_ACTIVATIONS_IN_SEGMENT
            ,t.TOTAL_FOURS                          = s.TOTAL_FOURS
            ,t.TOTAL_FIVES                          = s.TOTAL_FIVES
            ,t.MATCH_MAKING_FOUND_TIME              = s.MATCH_MAKING_FOUND_TIME
            ,t.LAST_UPDATED_DT                      = s.LAST_UPDATED_DT
            ,t.DW_INSERT_DT                         = s.DW_INSERT_DT
        WHEN NOT MATCHED THEN INSERT (
            INTERVAL_DATE
            ,ARENA_GROUP
            ,CURRENT_ARENA_INDEX
            ,TROPHY_GROUP
            ,payers_segment
            ,LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE
            ,LTV_GROUP
            ,LO_ENTRY_TROPHY_MODE
            ,LO_ENTRY_TROPHY_GAIN
            ,LO_ENTRY_TROPHY_LOSS
            ,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
            ,stakes_multiplier
            ,ROUNDS
            ,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
            ,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
            ,LAST_UPDATED_DT
            ,DW_INSERT_DT
        )
        VALUES (
            s.INTERVAL_DATE
            ,s.ARENA_GROUP
            ,s.CURRENT_ARENA_INDEX
            ,s.TROPHY_GROUP
            ,s.payers_segment
            ,s.LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE
            ,s.LTV_GROUP
            ,s.LO_ENTRY_TROPHY_MODE
            ,s.LO_ENTRY_TROPHY_GAIN
            ,s.LO_ENTRY_TROPHY_LOSS
            ,s.PVP_MODE
            ,s.MATCH_TYPE_ID
            ,s.MATCH_TYPE
            ,s.MATCH_MODE
            ,s.MODIFIER_1
            ,s.MODIFIER_2
            ,s.MATCH_MODIFIER_3
            ,s.MATCH_SUB_TYPE
            ,s.RESOURCE_SUB_TYPE
            ,s.BOOSTER_NAME
            ,s.RIVAL_RESOURCE_SUB_TYPE
            ,s.RUMBLE_SIZE
            ,s.RIVAL_BOOSTER_NAME
            ,s.ROUNDS_CNT
            ,s.IS_PRIVATE
            ,s.IS_MC_MATCH
            ,s.IS_RIVAL_BOT
            ,s.TURN_INDEX
            ,s.APP_VERSION
            ,s.SOLO_TYPE
            ,s.stakes_multiplier
            ,s.ROUNDS
            ,s.MATCH_SCORE
            ,s.MATCH_DURATION_MINS
            ,s.BOOSTER_ACTIVATIONS
            ,s.RESYNCS
            ,s.RESYNC_MATCHES
            ,s.ERROR_RESYNCS
            ,s.ERROR_RESYNC_MATCHES
            ,s.CONCEDED_MATCHES
            ,s.CONCEDED_MATCHES_1_10
            ,s.CONCEDED_MATCHES_10_60
            ,s.CONCEDED_MATCHES_AFTER_60SEC
            ,s.OOT
            ,s.OOT_MATCHES
            ,s.MATCHES
            ,s.WINS
            ,s.AVG_MATCH_RANK
            ,s.AGIANST_BOT_MATCHES
            ,s.TOTAL_SECONDS_PLAYED_IN_SEGMENT
            ,s.TOTAL_BOOSTER_ACTIVATIONS_IN_SEGMENT
            ,s.TOTAL_FOURS
            ,s.TOTAL_FIVES
            ,s.MATCH_MAKING_FOUND_TIME
            ,s.LAST_UPDATED_DT
            ,s.DW_INSERT_DT
        )
        ;
    ''',
    '06_update_mng_table': '''
    INSERT INTO CANDIVORE.MANAGE.MANAGEMENT_TABLE(
        TABLE_NAME
        ,DW_INSERT_DATE
        ,LAST_LOAD_DATE
        ,ROWS_LOADED
    )  VALUES (
        'CANDIVORE.SEMANTIC_LAYER.T_BOOSTER_MODE_DASHBOARD'
        ,current_timestamp()
        ,(select max(LAST_UPDATED_DT) from CANDIVORE.TRANSFORMATION.TRANS_BOOSTER_MODE_DASHBOARD)
        ,(select count(*) from CANDIVORE.TRANSFORMATION.TRANS_BOOSTER_MODE_DASHBOARD)
    )
'''
}