Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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)
- )
- '''
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement