Untitled

YuvalGai

Sep 17th, 2024

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

text 71.55 KB | None | 0 0

raw download clone embed print report

t_modes_control = {
'01_truncate_transformation_table': '''
truncate MATCH_MASTERS.TRANSFORMATION.TRANS_MODES_CONTROL
''',
'02_set_from_update_variable': '''
set FROM_T_MODES_CONTROL_UPDATED_DT_MNG = (
SELECT
date(
NVL(max(LAST_LOAD_DATE), to_timestamp('2024-08-23'))
) as LAST_UPDATED_DT_MNG
FROM
MATCH_MASTERS.manage.management_table
WHERE
1 = 1
AND table_name = 'CANDIVORE.SEMANTIC_LAYER.T_MODES_CONTROL'
ORDER BY
ID DESC
LIMIT
1
);
''',
'03_set_to_update_variable': '''
set TO_T_MODES_CONTROL_UPDATED_DT_MNG = (
SELECT
dateadd(day,-1,current_date())
);
''',
'04_insert_delta_into_trans_table': '''
INSERT INTO MATCH_MASTERS.TRANSFORMATION.TRANS_MODES_CONTROL (
segment,
user_segment,
payers_segment,
interval_date,
lo_entry_id,
match_type,
LTV_group,
trophy_group,
arena_group,
match_type_id,
match_mode,
modifier_1,
modifier_2,
match_modifier_3,
is_random_mixer,
rounds_cnt,
event_start_date,
min_time,
max_time,
unique_users_with_LESE_boosters,
rumble_size,
tournament_size,
lo_event_id,
lo_event_min_trophies,
lo_event_max_trophies,
calendar_order,
hours,
lo_entry_tickets_required,
lo_visible_on_homescreen,
lo_entry_min_booster_rarity,
lo_entry_max_booster_rarity,
lo_event_solo_moves_per_turn,
elimination_round,
lo_event_turn_duration_sec,
lo_entry_is_private,
LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE,
lo_event_wins_limit,
lo_is_friendly,
lo_event_max_lives,
lo_event_badge,
lo_entry_trophy_mode,
lo_entry_trophy_gain,
lo_entry_trophy_loss,
unique_users_in_event_and_segment,
legendary_boosters_spent,
se_boosters_spent,
DIAMOND1_BOOSTERS_SPENT,
DIAMOND2_BOOSTERS_SPENT,
DIAMOND3_BOOSTERS_SPENT,
GOLD_BOOSTERS_SPENT,
SILVER_BOOSTERS_SPENT,
BRONZE_BOOSTERS_SPENT,
LEGENDARY_BOOSTERS_EARNED,
SE_BOOSTERS_EARNED,
DIAMOND1_BOOSTERS_EARNED,
DIAMOND2_BOOSTERS_EARNED,
DIAMOND3_BOOSTERS_EARNED,
GOLD_BOOSTERS_EARNED,
SILVER_BOOSTERS_EARNED,
BRONZE_BOOSTERS_EARNED,
SHIELDS_EARNED,
SHIELDS_SPENT,
PERKS_EARNED,
PERKS_SPENT,
TICKETS_EARNED,
TICKETS_SPENT,
TROPHY_EARNED,
TROPHY_SPENT,
COINS_EARNED,
COINS_SPENT,
STICKERSEASONAL_EARNED,
STICKERSILVER_EARNED,
STICKERWHITE_EARNED,
SPIN_EARNED,
SUPERSPIN_EARNED,
STICKERADVENTURE_EARNED,
STICKERDIAMOND_EARNED,
STICKERGOLD_EARNED,
STICKERTOKEN_EARNED,
stickerseasonal_earned_duplicate,
stickersilver_earned_duplicate,
stickerwhite_earned_duplicate,
stickeradventure_earned_duplicate,
stickerdiamond_earned_duplicate,
stickergold_earned_duplicate,
SUPERSPIN_EARNED_VALUE,
STICKERADVENTURE_EARNED_VALUE,
STICKERDIAMOND_EARNED_VALUE,
STICKERGOLD_EARNED_VALUE,
STICKERTOKEN_EARNED_VALUE,
LEGENDARY_BOOSTERS_SPENT_VALUE,
SE_BOOSTERS_SPENT_VALUE,
DIAMOND1_BOOSTERS_SPENT_VALUE,
DIAMOND2_BOOSTERS_SPENT_VALUE,
DIAMOND3_BOOSTERS_SPENT_VALUE,
GOLD_BOOSTERS_SPENT_VALUE,
SILVER_BOOSTERS_SPENT_VALUE,
BRONZE_BOOSTERS_SPENT_VALUE,
LEGENDARY_BOOSTERS_EARNED_VALUE,
SE_BOOSTERS_EARNED_VALUE,
DIAMOND1_BOOSTERS_EARNED_VALUE,
DIAMOND2_BOOSTERS_EARNED_VALUE,
DIAMOND3_BOOSTERS_EARNED_VALUE,
GOLD_BOOSTERS_EARNED_VALUE,
SILVER_BOOSTERS_EARNED_VALUE,
BRONZE_BOOSTERS_EARNED_VALUE,
SHIELDS_EARNED_VALUE,
SHIELDS_SPENT_VALUE,
PERKS_EARNED_VALUE,
PERKS_SPENT_VALUE,
TICKETS_EARNED_VALUE,
TICKETS_SPENT_VALUE,
TROPHY_EARNED_VALUE,
TROPHY_SPENT_VALUE,
STICKERSEASONAL_EARNED_VALUE,
STICKERSILVER_EARNED_VALUE,
STICKERWHITE_EARNED_VALUE,
SPIN_EARNED_VALUE,
mini_game_tokens_earned_value,
mini_game_tokens_earned,
total_matches,
total_seconds_played_in_segment,
total_booster_activations_in_segment,
total_match_score_in_segment,
games_with_legendary_boosters,
games_with_SE_boosters,
diamond_losses,
diamond3_losses,
legendary_losses,
SE_losses,
total_losses,
total_wins,
legendary_wins,
SE_wins,
total_coin_value,
total_match_ids,
total_bots,
total_on_fire,
conceded_matches,
total_perk_activations,
Match_making_found_time,
total_matches_for_tournament_calc,
TWO_X_MATCHES,
THREE_X_MATCHES,
FOUR_X_MATCHES,
FIVE_X_MATCHES,
LE_points_earned,
LE_value_earned,
LAST_UPDATED_DT,
DW_INSERT_DT
)
select
-- group by fields:
segment,
segment2 as user_segment,
payers_segment,
interval_date,
lo_entry_id,
match_type,
LTV_group,
trophy_group,
arena_group,
-- dimensions
mode(match_type_id) match_type_id,
mode(match_mode) match_mode,
mode(modifier_1) modifier_1,
mode(modifier_2) modifier_2,
mode(match_modifier_3) match_modifier_3,
mode(is_random_mixer) is_random_mixer,
mode(rounds_cnt) rounds_cnt,
mode(event_start_date) event_start_date,
mode(min_time) min_time,
mode(max_time) max_time,
mode(unique_users_with_LESE_boosters) unique_users_with_LESE_boosters,
mode(rumble_size) rumble_size,
mode(tournament_size) tournament_size,
mode(lo_event_id) lo_event_id,
mode(lo_event_min_trophies) lo_event_min_trophies,
mode(lo_event_max_trophies) lo_event_max_trophies,
mode(calendar_order) calendar_order,
mode(hours) hours,
mode(lo_entry_tickets_required) lo_entry_tickets_required,
mode(lo_visible_on_homescreen) lo_visible_on_homescreen,
mode(lo_entry_min_booster_rarity) lo_entry_min_booster_rarity,
mode(lo_entry_max_booster_rarity) lo_entry_max_booster_rarity,
mode(lo_event_solo_moves_per_turn) lo_event_solo_moves_per_turn,
mode(elimination_round) elimination_round,
mode(lo_event_turn_duration_sec) lo_event_turn_duration_sec,
mode(lo_entry_is_private) lo_entry_is_private,
mode(LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE) LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE,
mode(lo_event_wins_limit) lo_event_wins_limit,
mode(lo_is_friendly) lo_is_friendly,
mode(lo_event_max_lives) lo_event_max_lives,
mode(lo_event_badge) lo_event_badge,
mode(lo_entry_trophy_mode) lo_entry_trophy_mode,
mode(lo_entry_trophy_gain) lo_entry_trophy_gain,
mode(lo_entry_trophy_loss) lo_entry_trophy_loss,
mode(unique_users_in_event_and_segment) unique_users_in_event_and_segment,
-- measure fields
sum(legendary_boosters_spent) legendary_boosters_spent,
sum(se_boosters_spent) se_boosters_spent,
sum(DIAMOND1_BOOSTERS_SPENT) DIAMOND1_BOOSTERS_SPENT,
sum(DIAMOND2_BOOSTERS_SPENT) DIAMOND2_BOOSTERS_SPENT,
sum(DIAMOND3_BOOSTERS_SPENT) DIAMOND3_BOOSTERS_SPENT,
sum(GOLD_BOOSTERS_SPENT) GOLD_BOOSTERS_SPENT,
sum(SILVER_BOOSTERS_SPENT) SILVER_BOOSTERS_SPENT,
sum(BRONZE_BOOSTERS_SPENT) BRONZE_BOOSTERS_SPENT,
sum(LEGENDARY_BOOSTERS_EARNED) LEGENDARY_BOOSTERS_EARNED,
sum(SE_BOOSTERS_EARNED) SE_BOOSTERS_EARNED,
sum(DIAMOND1_BOOSTERS_EARNED) DIAMOND1_BOOSTERS_EARNED,
sum(DIAMOND2_BOOSTERS_EARNED) DIAMOND2_BOOSTERS_EARNED,
sum(DIAMOND3_BOOSTERS_EARNED) DIAMOND3_BOOSTERS_EARNED,
sum(GOLD_BOOSTERS_EARNED) GOLD_BOOSTERS_EARNED,
sum(SILVER_BOOSTERS_EARNED) SILVER_BOOSTERS_EARNED,
sum(BRONZE_BOOSTERS_EARNED) BRONZE_BOOSTERS_EARNED,
sum(SHIELDS_EARNED) SHIELDS_EARNED,
sum(SHIELDS_SPENT) SHIELDS_SPENT,
sum(PERKS_EARNED) PERKS_EARNED,
sum(PERKS_SPENT) PERKS_SPENT,
sum(TICKETS_EARNED) TICKETS_EARNED,
sum(TICKETS_SPENT) TICKETS_SPENT,
sum(TROPHY_EARNED) TROPHY_EARNED,
sum(TROPHY_SPENT) TROPHY_SPENT,
sum(COINS_EARNED) COINS_EARNED,
sum(COINS_SPENT) COINS_SPENT,
sum(STICKERSEASONAL_EARNED) STICKERSEASONAL_EARNED,
sum(STICKERSILVER_EARNED) STICKERSILVER_EARNED,
sum(STICKERWHITE_EARNED) STICKERWHITE_EARNED,
sum(SPIN_EARNED) SPIN_EARNED,
sum(SUPERSPIN_EARNED) SUPERSPIN_EARNED,
sum(STICKERADVENTURE_EARNED) STICKERADVENTURE_EARNED,
sum(STICKERDIAMOND_EARNED) STICKERDIAMOND_EARNED,
sum(STICKERGOLD_EARNED) STICKERGOLD_EARNED,
sum(STICKERTOKEN_EARNED) STICKERTOKEN_EARNED,
sum(stickerseasonal_earned_duplicate) stickerseasonal_earned_duplicate,
sum(stickersilver_earned_duplicate) stickersilver_earned_duplicate,
sum(stickerwhite_earned_duplicate) stickerwhite_earned_duplicate,
sum(stickeradventure_earned_duplicate) stickeradventure_earned_duplicate,
sum(stickerdiamond_earned_duplicate) stickerdiamond_earned_duplicate,
sum(stickergold_earned_duplicate) stickergold_earned_duplicate,
sum(SUPERSPIN_EARNED_VALUE) SUPERSPIN_EARNED_VALUE,
sum(STICKERADVENTURE_EARNED_VALUE) STICKERADVENTURE_EARNED_VALUE,
sum(STICKERDIAMOND_EARNED_VALUE) STICKERDIAMOND_EARNED_VALUE,
sum(STICKERGOLD_EARNED_VALUE) STICKERGOLD_EARNED_VALUE,
sum(STICKERTOKEN_EARNED_VALUE) STICKERTOKEN_EARNED_VALUE,
sum(LEGENDARY_BOOSTERS_SPENT_VALUE) LEGENDARY_BOOSTERS_SPENT_VALUE,
sum(SE_BOOSTERS_SPENT_VALUE) SE_BOOSTERS_SPENT_VALUE,
sum(DIAMOND1_BOOSTERS_SPENT_VALUE) DIAMOND1_BOOSTERS_SPENT_VALUE,
sum(DIAMOND2_BOOSTERS_SPENT_VALUE) DIAMOND2_BOOSTERS_SPENT_VALUE,
sum(DIAMOND3_BOOSTERS_SPENT_VALUE) DIAMOND3_BOOSTERS_SPENT_VALUE,
sum(GOLD_BOOSTERS_SPENT_VALUE) GOLD_BOOSTERS_SPENT_VALUE,
sum(SILVER_BOOSTERS_SPENT_VALUE) SILVER_BOOSTERS_SPENT_VALUE,
sum(BRONZE_BOOSTERS_SPENT_VALUE) BRONZE_BOOSTERS_SPENT_VALUE,
sum(LEGENDARY_BOOSTERS_EARNED_VALUE) LEGENDARY_BOOSTERS_EARNED_VALUE,
sum(SE_BOOSTERS_EARNED_VALUE) SE_BOOSTERS_EARNED_VALUE,
sum(DIAMOND1_BOOSTERS_EARNED_VALUE) DIAMOND1_BOOSTERS_EARNED_VALUE,
sum(DIAMOND2_BOOSTERS_EARNED_VALUE) DIAMOND2_BOOSTERS_EARNED_VALUE,
sum(DIAMOND3_BOOSTERS_EARNED_VALUE) DIAMOND3_BOOSTERS_EARNED_VALUE,
sum(GOLD_BOOSTERS_EARNED_VALUE) GOLD_BOOSTERS_EARNED_VALUE,
sum(SILVER_BOOSTERS_EARNED_VALUE) SILVER_BOOSTERS_EARNED_VALUE,
sum(BRONZE_BOOSTERS_EARNED_VALUE) BRONZE_BOOSTERS_EARNED_VALUE,
sum(SHIELDS_EARNED_VALUE) SHIELDS_EARNED_VALUE,
sum(SHIELDS_SPENT_VALUE) SHIELDS_SPENT_VALUE,
sum(PERKS_EARNED_VALUE) PERKS_EARNED_VALUE,
sum(PERKS_SPENT_VALUE) PERKS_SPENT_VALUE,
sum(TICKETS_EARNED_VALUE) TICKETS_EARNED_VALUE,
sum(TICKETS_SPENT_VALUE) TICKETS_SPENT_VALUE,
sum(TROPHY_EARNED_VALUE) TROPHY_EARNED_VALUE,
sum(TROPHY_SPENT_VALUE) TROPHY_SPENT_VALUE,
sum(STICKERSEASONAL_EARNED_VALUE) STICKERSEASONAL_EARNED_VALUE,
sum(STICKERSILVER_EARNED_VALUE) STICKERSILVER_EARNED_VALUE,
sum(STICKERWHITE_EARNED_VALUE) STICKERWHITE_EARNED_VALUE,
sum(SPIN_EARNED_VALUE) SPIN_EARNED_VALUE,
sum(mini_game_tokens_earned_value) mini_game_tokens_earned_value,
sum(mini_game_tokens_earned) mini_game_tokens_earned,
sum(total_matches) total_matches,
sum(total_seconds_played_in_segment) total_seconds_played_in_segment,
sum(total_booster_activations_in_segment) total_booster_activations_in_segment,
sum(total_match_score_in_segment) total_match_score_in_segment,
sum(games_with_legendary_boosters) games_with_legendary_boosters,
sum(games_with_SE_boosters) games_with_SE_boosters,
sum(diamond_losses) diamond_losses,
sum(diamond3_losses) diamond3_losses,
sum(legendary_losses) legendary_losses,
sum(SE_losses) SE_losses,
sum(total_losses) total_losses,
sum(total_wins) total_wins,
sum(legendary_wins) legendary_wins,
sum(SE_wins) SE_wins,
sum(total_coin_value) total_coin_value,
sum(total_match_ids) total_match_ids,
sum(total_bots) total_bots,
sum(total_on_fire) total_on_fire,
sum(conceded_matches) conceded_matches,
sum(total_perk_activations) total_perk_activations,
sum(Match_making_found_time) Match_making_found_time,
sum(total_matches_for_tournament_calc) total_matches_for_tournament_calc,
sum(TWO_X_MATCHES) TWO_X_MATCHES,
sum(THREE_X_MATCHES) THREE_X_MATCHES,
sum(FOUR_X_MATCHES) FOUR_X_MATCHES,
sum(FIVE_X_MATCHES) FIVE_X_MATCHES,
sum(LE_points_earned) LE_points_earned,
sum(LE_value_earned) LE_value_earned,
to_timestamp(INTERVAL_DATE) as LAST_UPDATED_DT,
current_timestamp() as DW_INSERT_DT
from (
select
COALESCE(Transactions.interval_date,Matches.interval_date) interval_date,
COALESCE(Transactions.user_id,Matches.user_id) user_id,
COALESCE(Transactions.lo_entry_id,Matches.lo_entry_id) lo_entry_id,
COALESCE(Transactions.match_type,Matches.match_type,'NA') match_type,
Transactions.legendary_boosters_spent,
Transactions.se_boosters_spent,
nvl(Transactions.segment2,'NA') segment2,
Transactions.DIAMOND1_BOOSTERS_SPENT,
Transactions.DIAMOND2_BOOSTERS_SPENT,
Transactions.DIAMOND3_BOOSTERS_SPENT,
Transactions.GOLD_BOOSTERS_SPENT,
Transactions.SILVER_BOOSTERS_SPENT,
Transactions.BRONZE_BOOSTERS_SPENT,
Transactions.LEGENDARY_BOOSTERS_EARNED,
Transactions.SE_BOOSTERS_EARNED,
Transactions.DIAMOND1_BOOSTERS_EARNED,
Transactions.DIAMOND2_BOOSTERS_EARNED,
Transactions.DIAMOND3_BOOSTERS_EARNED,
Transactions.GOLD_BOOSTERS_EARNED,
Transactions.SILVER_BOOSTERS_EARNED,
Transactions.BRONZE_BOOSTERS_EARNED,
Transactions.SHIELDS_EARNED,
Transactions.SHIELDS_SPENT,
Transactions.PERKS_EARNED,
Transactions.PERKS_SPENT,
Transactions.TICKETS_EARNED,
Transactions.TICKETS_SPENT,
Transactions.TROPHY_EARNED,
Transactions.TROPHY_SPENT,
Transactions.COINS_EARNED,
Transactions.COINS_SPENT,
Transactions.STICKERSEASONAL_EARNED,
Transactions.STICKERSILVER_EARNED,
Transactions.STICKERWHITE_EARNED,
Transactions.SPIN_EARNED,
Transactions.SUPERSPIN_EARNED,
Transactions.STICKERADVENTURE_EARNED,
Transactions.STICKERDIAMOND_EARNED,
Transactions.STICKERGOLD_EARNED,
Transactions.STICKERTOKEN_EARNED,
Transactions.stickerseasonal_earned_duplicate,
Transactions.stickersilver_earned_duplicate,
Transactions.stickerwhite_earned_duplicate,
Transactions.stickeradventure_earned_duplicate,
Transactions.stickerdiamond_earned_duplicate,
Transactions.stickergold_earned_duplicate,
Transactions.SUPERSPIN_EARNED_VALUE,
Transactions.STICKERADVENTURE_EARNED_VALUE,
Transactions.STICKERDIAMOND_EARNED_VALUE,
Transactions.STICKERGOLD_EARNED_VALUE,
Transactions.STICKERTOKEN_EARNED_VALUE,
Transactions.LEGENDARY_BOOSTERS_SPENT_VALUE,
Transactions.SE_BOOSTERS_SPENT_VALUE,
Transactions.DIAMOND1_BOOSTERS_SPENT_VALUE,
Transactions.DIAMOND2_BOOSTERS_SPENT_VALUE,
Transactions.DIAMOND3_BOOSTERS_SPENT_VALUE,
Transactions.GOLD_BOOSTERS_SPENT_VALUE,
Transactions.SILVER_BOOSTERS_SPENT_VALUE,
Transactions.BRONZE_BOOSTERS_SPENT_VALUE,
Transactions.LEGENDARY_BOOSTERS_EARNED_VALUE,
Transactions.SE_BOOSTERS_EARNED_VALUE,
Transactions.DIAMOND1_BOOSTERS_EARNED_VALUE,
Transactions.DIAMOND2_BOOSTERS_EARNED_VALUE,
Transactions.DIAMOND3_BOOSTERS_EARNED_VALUE,
Transactions.GOLD_BOOSTERS_EARNED_VALUE,
Transactions.SILVER_BOOSTERS_EARNED_VALUE,
Transactions.BRONZE_BOOSTERS_EARNED_VALUE,
Transactions.SHIELDS_EARNED_VALUE,
Transactions.SHIELDS_SPENT_VALUE,
Transactions.PERKS_EARNED_VALUE,
Transactions.PERKS_SPENT_VALUE,
Transactions.TICKETS_EARNED_VALUE,
Transactions.TICKETS_SPENT_VALUE,
Transactions.TROPHY_EARNED_VALUE,
Transactions.TROPHY_SPENT_VALUE,
Transactions.STICKERSEASONAL_EARNED_VALUE,
Transactions.STICKERSILVER_EARNED_VALUE,
Transactions.STICKERWHITE_EARNED_VALUE,
Transactions.SPIN_EARNED_VALUE,
Transactions.mini_game_tokens_earned_value,
Transactions.mini_game_tokens_earned,
Matches.match_type_id,
Matches.match_mode,
Matches.modifier_1,
Matches.modifier_2,
Matches.match_modifier_3,
Matches.is_random_mixer,
Matches.rounds_cnt,
Matches.total_matches,
Matches.event_start_date,
Matches.total_seconds_played_in_segment,
Matches.total_booster_activations_in_segment,
Matches.total_match_score_in_segment,
Matches.games_with_legendary_boosters,
Matches.games_with_SE_boosters,
Matches.diamond_losses,
Matches.diamond3_losses,
Matches.legendary_losses,
Matches.SE_losses,
Matches.total_losses,
Matches.total_wins,
Matches.legendary_wins,
Matches.SE_wins,
Matches.min_time,
Matches.max_time,
Matches.unique_users_with_LESE_boosters,
Matches.total_coin_value,
Matches.total_match_ids,
Matches.rumble_size,
Matches.tournament_size,
Matches.total_bots,
Matches.total_on_fire,
Matches.conceded_matches,
Matches.total_perk_activations,
Matches.Match_making_found_time,
Matches.total_matches_for_tournament_calc,
Matches.TWO_X_MATCHES,
Matches.THREE_X_MATCHES,
Matches.FOUR_X_MATCHES,
Matches.FIVE_X_MATCHES,
Matches.LTV_group,
Matches.trophy_group,
Matches.arena_group,
Event_Dim.lo_event_id,
Event_Dim.lo_event_min_trophies,
Event_Dim.lo_event_max_trophies,
Event_Dim.calendar_order,
Event_Dim.hours,
Event_Dim.lo_entry_tickets_required,
Event_Dim.lo_visible_on_homescreen,
Event_Dim.lo_entry_min_booster_rarity,
Event_Dim.lo_entry_max_booster_rarity,
Event_Dim.lo_event_solo_moves_per_turn,
Event_Dim.lo_event_turn_amt elimination_round,
Event_Dim.lo_event_turn_duration_sec,
Event_Dim.lo_entry_is_private,
Event_Dim.LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE,
Event_Dim.lo_event_wins_limit,
Event_Dim.lo_is_friendly,
Event_Dim.lo_event_max_lives,
Event_Dim.lo_event_badge,
Event_Dim.lo_entry_trophy_mode,
Event_Dim.lo_entry_trophy_gain,
Event_Dim.lo_entry_trophy_loss,
Lo_points.points_earned LE_points_earned,
Lo_points.value_earned LE_value_earned,
nvl(Lo_points.segment,'NA') segment,
nvl(Daily_Segments.payers_segment,'NA') payers_segment,
count(distinct Transactions.user_id) over(partition by Transactions.lo_entry_id,Matches.LTV_group,Matches.trophy_group,Matches.arena_group,segment,payers_segment,segment2) unique_users_in_event_and_segment
from
(SELECT
DATE(derived_tstamp) interval_date,
user_id,
ifnull(CASE WHEN lo_entry_id = '' THEN NULL ELSE lo_entry_id END, 'NA') as lo_entry_id,
match_type,
case when lo_segment in ('Default','Change') then lo_segment else null end segment2,
SUM(
CASE
WHEN resource_sub_type = 'Legendary'
AND is_received_resource = 0 THEN resource_cnt
END
) AS legendary_boosters_spent,
SUM(
CASE
WHEN resource_sub_type = 'Special Edition'
AND is_received_resource = 0 THEN resource_cnt
END
) AS se_boosters_spent,
SUM(
CASE
WHEN resource_sub_type = 'Diamond 1'
AND is_received_resource = 0 THEN resource_cnt
END
) AS diamond1_boosters_spent,
SUM(
CASE
WHEN resource_sub_type = 'Diamond 2'
AND is_received_resource = 0 THEN resource_cnt
END
) AS diamond2_boosters_spent,
SUM(
CASE
WHEN resource_sub_type = 'Diamond 3'
AND is_received_resource = 0 THEN resource_cnt
END
) AS diamond3_boosters_spent,
SUM(
CASE
WHEN resource_sub_type = 'Gold'
and resource_type = 'Booster'
AND is_received_resource = 0 THEN resource_cnt
END
) AS gold_boosters_spent,
SUM(
CASE
WHEN resource_sub_type = 'Silver'
and resource_type = 'Booster'
AND is_received_resource = 0 THEN resource_cnt
END
) AS silver_boosters_spent,
SUM(
CASE
WHEN resource_sub_type = 'Bronze'
AND is_received_resource = 0 THEN resource_cnt
END
) AS bronze_boosters_spent,
SUM(
CASE
WHEN resource_sub_type = 'Legendary'
AND is_received_resource = resource_cnt THEN 1
END
) AS legendary_boosters_earned,
SUM(
CASE
WHEN resource_sub_type = 'Special Edition'
AND is_received_resource = resource_cnt THEN 1
END
) AS se_boosters_earned,
SUM(
CASE
WHEN resource_sub_type = 'Diamond 1'
AND is_received_resource = 1 THEN resource_cnt
END
) AS diamond1_boosters_earned,
SUM(
CASE
WHEN resource_sub_type = 'Diamond 2'
AND is_received_resource = 1 THEN resource_cnt
END
) AS diamond2_boosters_earned,
SUM(
CASE
WHEN resource_sub_type = 'Diamond 3'
AND is_received_resource = 1 THEN resource_cnt
END
) AS diamond3_boosters_earned,
SUM(
CASE
WHEN resource_sub_type = 'Gold'
and resource_type = 'Booster'
AND is_received_resource = 1 THEN resource_cnt
END
) AS gold_boosters_earned,
SUM(
CASE
WHEN resource_sub_type = 'Silver'
AND is_received_resource = 1 THEN resource_cnt
END
) AS silver_boosters_earned,
SUM(
CASE
WHEN resource_sub_type = 'Bronze'
AND is_received_resource = 1 THEN resource_cnt
END
) AS bronze_boosters_earned,
SUM(
CASE
WHEN resource_type = 'Shield'
AND is_received_resource = 1 THEN resource_cnt
END
) AS shields_earned,
SUM(
CASE
WHEN resource_type = 'Shield'
AND is_received_resource = 0 THEN resource_cnt
END
) AS shields_spent,
SUM(
CASE
WHEN resource_type = 'Perk'
AND is_received_resource = 1 THEN resource_cnt
END
) AS perks_earned,
SUM(
CASE
WHEN resource_type = 'Perk'
AND is_received_resource = 0 THEN resource_cnt
END
) AS perks_spent,
SUM(
CASE
WHEN resource_type = 'Ticket'
AND is_received_resource = 1 THEN resource_cnt
END
) AS tickets_earned,
SUM(
CASE
WHEN resource_type = 'Ticket'
AND is_received_resource = 0 THEN resource_cnt
END
) AS tickets_spent,
SUM(
CASE
WHEN resource_type = 'Trophy'
AND is_received_resource = 1 THEN resource_cnt
END
) AS trophy_earned,
SUM(
CASE
WHEN resource_type = 'Trophy'
AND is_received_resource = 0 THEN resource_cnt
END
) AS trophy_spent,
SUM(
CASE
WHEN resource_id = 'Coin'
AND is_received_resource = 1 THEN resource_cnt
END
) AS coins_earned,
SUM(
CASE
WHEN resource_id = 'Coin'
AND is_received_resource = 0 THEN resource_cnt
END
) AS coins_spent,
sum(
case
when resource_id = 'StickerSeasonal'
and is_received_resource = 1
and resource_item_status = 'unique' then resource_cnt
else 0
end
) as StickerSeasonal_earned,
sum(
case
when resource_id = 'StickerSilver'
and is_received_resource = 1
and resource_item_status = 'unique' then resource_cnt
else 0
end
) as StickerSilver_earned,
sum(
case
when resource_id = 'StickerWhite'
and is_received_resource = 1
and resource_item_status = 'unique' then resource_cnt
else 0
end
) as StickerWhite_earned,
sum(
case
when resource_id = 'StickerAdventure'
and is_received_resource = 1
and resource_item_status = 'unique' then resource_cnt
end
) as StickerAdventure_earned,
sum(
case
when resource_id = 'StickerDiamond'
and is_received_resource = 1
and resource_item_status = 'unique' then resource_cnt
end
) as StickerDiamond_earned,
sum(
case
when resource_id = 'StickerGold'
and is_received_resource = 1
and resource_item_status = 'unique' then resource_cnt
end
) as StickerGold_earned,
sum(
case
when resource_id = 'StickerSeasonal'
and is_received_resource = 1
and resource_item_status = 'duplicate' then resource_cnt
else 0
end
) as StickerSeasonal_earned_duplicate,
sum(
case
when resource_id = 'StickerSilver'
and is_received_resource = 1
and resource_item_status = 'duplicate' then resource_cnt
else 0
end
) as StickerSilver_earned_duplicate,
sum(
case
when resource_id = 'StickerWhite'
and is_received_resource = 1
and resource_item_status = 'duplicate' then resource_cnt
else 0
end
) as StickerWhite_earned_duplicate,
sum(
case
when resource_id = 'StickerAdventure'
and is_received_resource = 1
and resource_item_status = 'duplicate' then resource_cnt
end
) as StickerAdventure_earned_duplicate,
sum(
case
when resource_id = 'StickerDiamond'
and is_received_resource = 1
and resource_item_status = 'duplicate' then resource_cnt
end
) as StickerDiamond_earned_duplicate,
sum(
case
when resource_id = 'StickerGold'
and is_received_resource = 1
and resource_item_status = 'duplicate' then resource_cnt
end
) as StickerGold_earned_duplicate,
sum(
case
when resource_id = 'Spin'
and is_received_resource = 1 then resource_cnt
else 0
end
) as Spin_earned,
sum(
case
when resource_id = 'SuperSpin'
and is_received_resource = 1 then resource_cnt
end
) as SuperSpin_earned,
sum(
case
when resource_id = 'StickerToken'
and is_received_resource = 1 then resource_cnt
end
) as StickerToken_earned,
sum(
case
when resource_id = 'SuperSpin'
and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
end
) as SuperSpin_earned_value,
sum(
case
when resource_id = 'StickerAdventure'
and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
end
) as StickerAdventure_earned_value,
sum(
case
when resource_id = 'StickerDiamond'
and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
end
) as StickerDiamond_earned_value,
sum(
case
when resource_id = 'StickerGold'
and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
end
) as StickerGold_earned_value,
sum(
case
when resource_id = 'StickerToken'
and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
end
) as StickerToken_earned_value,
SUM(
CASE
WHEN resource_sub_type = 'Legendary'
AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
END
) AS legendary_boosters_spent_value,
SUM(
CASE
WHEN resource_sub_type = 'Special Edition'
AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
END
) AS se_boosters_spent_value,
SUM(
CASE
WHEN resource_sub_type = 'Diamond 1'
AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
END
) AS diamond1_boosters_spent_value,
SUM(
CASE
WHEN resource_sub_type = 'Diamond 2'
AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
END
) AS diamond2_boosters_spent_value,
SUM(
CASE
WHEN resource_sub_type = 'Diamond 3'
AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
END
) AS diamond3_boosters_spent_value,
SUM(
CASE
WHEN resource_sub_type = 'Gold'
and resource_type = 'Booster'
AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
END
) AS gold_boosters_spent_value,
SUM(
CASE
WHEN resource_sub_type = 'Silver'
AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
END
) AS silver_boosters_spent_value,
SUM(
CASE
WHEN resource_sub_type = 'Bronze'
AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
END
) AS bronze_boosters_spent_value,
SUM(
CASE
WHEN resource_sub_type = 'Legendary'
AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
END
) AS legendary_boosters_earned_value,
SUM(
CASE
WHEN resource_sub_type = 'Special Edition'
AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
END
) AS se_boosters_earned_value,
SUM(
CASE
WHEN resource_sub_type = 'Diamond 1'
AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
END
) AS diamond1_boosters_earned_value,
SUM(
CASE
WHEN resource_sub_type = 'Diamond 2'
AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
END
) AS diamond2_boosters_earned_value,
SUM(
CASE
WHEN resource_sub_type = 'Diamond 3'
AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
END
) AS diamond3_boosters_earned_value,
SUM(
CASE
WHEN resource_sub_type = 'Gold'
and resource_type = 'Booster'
AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
END
) AS gold_boosters_earned_value,
SUM(
CASE
WHEN resource_sub_type = 'Silver'
AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
END
) AS silver_boosters_earned_value,
SUM(
CASE
WHEN resource_sub_type = 'Bronze'
AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
END
) AS bronze_boosters_earned_value,
SUM(
CASE
WHEN resource_type = 'Shield'
AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
END
) AS shields_earned_value,
SUM(
CASE
WHEN resource_type = 'Shield'
AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
END
) AS shields_spent_value,
SUM(
CASE
WHEN resource_type = 'Perk' and resource_sub_type = 'Paid'
AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
END
) AS perks_earned_value,
SUM(
CASE
WHEN resource_type = 'Perk' and resource_sub_type = 'Paid'
AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
END
) AS perks_spent_value,
SUM(
CASE
WHEN resource_type = 'Ticket'
AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
END
) AS tickets_earned_value,
SUM(
CASE
WHEN resource_type = 'Ticket'
AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
END
) AS tickets_spent_value,
SUM(
CASE
WHEN resource_type = 'Trophy'
AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
END
) AS trophy_earned_value,
SUM(
CASE
WHEN resource_type = 'Trophy'
AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
END
) AS trophy_spent_value,
sum(
case
when resource_id = 'StickerSeasonal'
and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
else 0
end
) as StickerSeasonal_earned_value,
sum(
case
when resource_id = 'StickerSilver'
and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
else 0
end
) as StickerSilver_earned_value,
sum(
case
when resource_id = 'StickerWhite'
and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
else 0
end
) as StickerWhite_earned_value,
sum(
case
when resource_id = 'Spin'
and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
else 0
end
) as Spin_earned_value
sum(
case
when resource_id = 'mini_game_tokens'
and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
else 0
end
) as mini_game_tokens_earned_value
sum(
case
when resource_id = 'mini_game_tokens'
and is_received_resource = 1 then resource_cnt
else 0
end
) as mini_game_tokens_earned
FROM MATCH_MASTERS.PROD.F_USER_RESOURCE_TRANSACTION
WHERE
(match_type_id > 0 OR transaction_source = 'live_event')
and (
transaction_source in('live_event','match_end','disconnection_refund','match_start')
OR transaction_source LIKE 'tournament%'
)
AND DATE(derived_tstamp) between DATEADD(day, -6, $FROM_T_MODES_CONTROL_UPDATED_DT_MNG) and $TO_T_MODES_CONTROL_UPDATED_DT_MNG
GROUP BY ALL -- (5)
) Transactions
full outer JOIN
(SELECT
date(derived_tstamp) interval_date
,user_id
,ifnull(calendar_entry_id, 'NA') lo_entry_id
,match_type
,CASE
WHEN mode(trophies_cnt) < 800 THEN '800'
WHEN mode(trophies_cnt) < 3800 THEN 'Studios'
WHEN mode(trophies_cnt) < 30000 THEN 'Master_League'
WHEN mode(trophies_cnt) >= 30000 THEN 'Legends_League'
END arena_group
,CASE
WHEN mode(trophies_cnt) < 1500 THEN '0-1500'
WHEN mode(trophies_cnt) < 3800 THEN '1500-3800'
WHEN mode(trophies_cnt) < 8000 THEN '3800-8000'
WHEN mode(trophies_cnt) < 17000 THEN '8000-17000'
WHEN mode(trophies_cnt) < 30000 THEN '17000-30000'
ELSE '30000+'
END AS trophy_group,
mode(LTV_group) LTV_group,
mode(unique_users) unique_users,
mode(unique_users_with_LESE_boosters) unique_users_with_LESE_boosters,
min(event_start_date) event_start_date,
mode(match_type_id) match_type_id,
mode(tournament_size) tournament_size,
min(min_time) min_time,
min(max_time) max_time,
mode(rumble_size) rumble_size,
mode(rounds_cnt) rounds_cnt,
mode(is_random_mixer) is_random_mixer,
mode(modifier_1) modifier_1,
mode(modifier_2) modifier_2,
mode(ifnull(match_modifier_3, '')) match_modifier_3,
CASE
WHEN mode(is_random_mixer) = TRUE
OR mode(match_type_id) = 2 THEN 'random'
ELSE mode(match_mode)
END match_mode,
--, max_max_prize_eligibility
--, mode(unique_users_per_solo_prize) unique_users_per_solo_prize
sum(match_making_found_time) as match_making_found_time,
sum(
case
when current_tournament_stage = 0
or current_tournament_stage is null then 1
end
) as TOTAL_MATCHES_FOR_TOURNAMENT_CALC,
COUNT(*) AS total_matches,
SUM(
CASE
WHEN is_conceded = 0 THEN 1
ELSE 0
END
) unconceded_matches,
COUNT(DISTINCT match_id) AS total_match_ids,
SUM(MATCH_DURATION_SEC) AS total_seconds_played_in_segment,
SUM(
CASE
WHEN is_conceded = 0 THEN MATCH_DURATION_SEC
ELSE 0
END
) AS total_seconds_played_in_segment_unconceded,
SUM(ability_activated_cnt) AS total_booster_activations_in_segment,
SUM(
CASE
WHEN is_conceded = 0 THEN ability_activated_cnt
ELSE 0
END
) AS total_booster_activations_in_segment_unconceded,
SUM(match_score) AS total_match_score_in_segment,
SUM(
CASE
WHEN is_conceded = 0 THEN match_score
ELSE 0
END
) AS total_match_score_in_segment_unconceded,
SUM(
CASE
WHEN resource_sub_type = 'Legendary' THEN 1
END
) AS games_with_legendary_boosters,
SUM(
CASE
WHEN resource_type = 'Booster'
AND resource_sub_type = 'Special Edition' THEN 1
END
) AS games_with_SE_boosters,
SUM(
CASE
WHEN booster_tier = 'Diamond'
AND is_won = 0 THEN 1
END
) AS diamond_losses,
SUM(
CASE
WHEN resource_sub_type = 'Diamond 3'
AND is_won = 0 THEN 1
END
) AS diamond3_losses,
SUM(
CASE
WHEN resource_sub_type = 'Legendary'
AND is_won = 0 THEN 1
END
) AS legendary_losses,
SUM(
CASE
WHEN resource_sub_type = 'Special Edition'
AND is_won = 0 THEN 1
END
) AS SE_losses,
SUM(
CASE
WHEN is_won = 0 THEN 1
END
) AS total_losses,
SUM(is_won) AS total_wins,
SUM(
CASE
WHEN resource_sub_type = 'Legendary'
THEN is_won
END
) legendary_wins,
SUM(
CASE
WHEN resource_type = 'Booster'
AND resource_sub_type = 'Special Edition'
THEN is_won
END
) SE_wins,
SUM(
CASE
WHEN resource_type = 'Booster'
and (
current_tournament_stage = 0
or current_tournament_stage is null
) THEN Booster_value
END
) AS total_coin_value,
SUM(is_on_fire_game) total_on_fire,
SUM(
CASE
WHEN match_type = 'Rumble' THEN (
(rumble_size - unique_users_in_match) / unique_users_in_match
)
ELSE is_rival_bot
END
) total_bots,
SUM(board_ability1_uses + board_ability2_uses) AS total_perk_activations,
SUM(
CASE
WHEN is_conceded = 1
AND is_won = 0 THEN 1
ELSE 0
END
) conceded_matches,
SUM(
case
when stakes_multiplier = 2 then 1
end
) TWO_X_MATCHES,
SUM(
case
when stakes_multiplier = 3 then 1
end
) THREE_X_MATCHES,
SUM(
case
when stakes_multiplier = 4 then 1
end
) FOUR_X_MATCHES,
SUM(
case
when stakes_multiplier = 5 then 1
end
) FIVE_X_MATCHES
FROM
(
SELECT
*,
MIN(derived_tstamp) OVER(partition BY calendar_entry_id) min_time,
MAX(derived_tstamp) OVER(partition BY calendar_entry_id) max_time,
COUNT(DISTINCT user_id) OVER(partition BY calendar_entry_id) unique_users --, COUNT(DISTINCT user_id) OVER(partition BY calendar_entry_id,max_max_prize_eligibility) unique_users_per_solo_prize
,
COUNT(DISTINCT user_id) OVER(partition BY match_id) unique_users_in_match,
MIN(DATE(derived_tstamp)) OVER(partition BY calendar_entry_id) event_start_date,
MIN(date_trunc('hour', derived_tstamp)) OVER(partition BY calendar_entry_id) event_start_ts,
COUNT(
DISTINCT CASE
WHEN resource_sub_type = 'Legendary'
OR resource_sub_type = 'Special Edition' THEN user_id
END
) OVER(partition BY calendar_entry_id) unique_users_with_LESE_boosters
FROM
(
SELECT
*,
MAX(max_prize_eligibility) OVER(partition BY calendar_entry_id, user_id) max_max_prize_eligibility
FROM
MATCH_MASTERS.PROD.F_USER_MATCH WHERE DATE(derived_tstamp) between DATEADD(day, -7, $FROM_T_MODES_CONTROL_UPDATED_DT_MNG) and $TO_T_MODES_CONTROL_UPDATED_DT_MNG
)
)
GROUP BY ALL ) Matches
ON Transactions.interval_date = Matches.interval_date and Transactions.user_id = Matches.user_id and nvl(Transactions.lo_entry_id,Transactions.match_type) = nvl(Matches.lo_entry_id,Matches.match_type)
LEFT JOIN
(SELECT
A.LO_ENTRY_ID,
B.lo_event_id,
A.interval_date,
A.lo_entry_name,
A.calendar_order,
A.lo_visible_on_homescreen,
nvl(min0,min1) lo_event_min_trophies,
nvl(max0,max1) lo_event_max_trophies,
hours,
lo_entry_tickets_required,
lo_entry_min_booster_rarity,
lo_entry_max_booster_rarity,
lo_event_solo_moves_per_turn,
lo_event_badge,
lo_event_turn_amt,
lo_event_turn_duration_sec,
lo_entry_is_private,
lo_event_wins_limit,
LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE,
lo_is_friendly,
lo_entry_trophy_mode,
lo_entry_trophy_gain,
lo_entry_trophy_loss,
B.lo_event_max_lives
FROM
(
(
SELECT
nvl(LO_ENTRY_ID,lo_original_entry_id) LO_ENTRY_ID,
DATE(derived_tstamp) interval_date,
LO_ENTRY_NAME,
calendar_order,
lo_visible_on_homescreen,
lo_entry_min_trophies min0,
lo_entry_max_trophies max0,
round(
(lo_entry_end_ts - lo_entry_start_ts) / 60 / 60,
0
) hours,
lo_entry_tickets_required,
lo_entry_min_booster_rarity,
lo_entry_max_booster_rarity,
lo_entry_is_private,
lo_entry_trophy_mode,
lo_entry_trophy_gain,
lo_entry_trophy_loss,
LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE
FROM
(
SELECT
*
,ifnull(LO_ENTRY_UPDATE_TS,LO_ENTRY_TS) lo_entry_ts2
FROM
MATCH_MASTERS.PROD.F_LIVEOPS_CALENDAR
qualify ROW_NUMBER() OVER (
partition BY lo_entry_id
ORDER BY
lo_entry_ts2 DESC
) = 1
)
) A
LEFT JOIN (
SELECT
DATE(derived_tstamp) interval_date,
lo_event_id,
lo_event_min_trophies min1,
lo_event_max_trophies max1,
lo_event_solo_moves_per_turn,
lo_event_turn_amt,
lo_event_turn_duration_sec,
lo_event_wins_limit,
lo_is_friendly,
lo_event_badge,
lo_event_max_lives
FROM
MATCH_MASTERS.PROD.DIM_EVENT_CONFIG
qualify ROW_NUMBER() OVER (
partition BY LO_EVENT_ID
ORDER BY
derived_tstamp DESC
) = 1
) B ON A.LO_ENTRY_NAME = B.LO_EVENT_ID
) QUALIFY ROW_NUMBER() OVER (
PARTITION by A.LO_ENTRY_ID
ORDER BY
A.interval_date desc
) = 1
) Event_Dim
ON coalesce(Transactions.lo_entry_id,Matches.lo_entry_id) = Event_Dim.lo_entry_id
LEFT JOIN
( SELECT
source_calendar_entry_id,
DATE(derived_tstamp) AS interval_date,
user_id,
nvl(mode(segment),'No-Segment') segment,
SUM(points_earned) points_earned,
SUM(points_earned * event_point_value) value_earned
FROM
MATCH_MASTERS.PROD.F_LIVE_EVENT_PROGRESSION
WHERE
action = 'points_earned'
AND DATE(derived_tstamp) between DATEADD(day, -7, $FROM_T_MODES_CONTROL_UPDATED_DT_MNG) and $TO_T_MODES_CONTROL_UPDATED_DT_MNG
GROUP BY all ) Lo_points
ON COALESCE(Transactions.interval_date,Matches.interval_date) = Lo_points.interval_date and COALESCE(Transactions.user_id,Matches.user_id) = Lo_points.user_id and coalesce(Transactions.lo_entry_id,Matches.lo_entry_id) = Lo_points.source_calendar_entry_id
LEFT JOIN
(select interval_date,user_id,payers_segment from MATCH_MASTERS.prod.daily_users_from_params where interval_date between DATEADD(day, -7, $FROM_T_MODES_CONTROL_UPDATED_DT_MNG) and $TO_T_MODES_CONTROL_UPDATED_DT_MNG
) Daily_Segments
ON COALESCE(Transactions.user_id,Matches.user_id) = Daily_Segments.user_id and COALESCE(Transactions.interval_date,Matches.interval_date) = Daily_Segments.interval_date
)
WHERE 1 = 1
AND interval_date is not null
AND interval_date between DATEADD(day, -3, $FROM_T_MODES_CONTROL_UPDATED_DT_MNG) and $TO_T_MODES_CONTROL_UPDATED_DT_MNG
GROUP BY ALL;
''',
'05_set_merge_error_helper': '''
ALTER SESSION SET ERROR_ON_NONDETERMINISTIC_MERGE = false;
''',
'06_merge_temp_to_prod': '''
MERGE INTO MATCH_MASTERS.SEMANTIC_LAYER.T_MODES_CONTROL AS t
USING MATCH_MASTERS.TRANSFORMATION.TRANS_MODES_CONTROL AS S
ON s.INTERVAL_DATE = t.INTERVAL_DATE
AND (s.ARENA_GROUP = t.ARENA_GROUP OR (s.ARENA_GROUP IS NULL AND t.ARENA_GROUP IS NULL ) )
AND (s.TROPHY_GROUP = t.TROPHY_GROUP OR (s.TROPHY_GROUP IS NULL AND t.TROPHY_GROUP IS NULL) )
AND (s.LTV_GROUP = t.LTV_GROUP OR (s.LTV_GROUP IS NULL AND t.LTV_GROUP IS NULL) )
AND (s.payers_segment = t.payers_segment OR (s.payers_segment IS NULL AND t.payers_segment IS NULL) )
AND (s.LO_ENTRY_ID = t.LO_ENTRY_ID OR (s.LO_ENTRY_ID IS NULL AND t.LO_ENTRY_ID IS NULL) )
AND (s.MATCH_TYPE = t.MATCH_TYPE OR (s.MATCH_TYPE IS NULL AND t.MATCH_TYPE IS NULL) )
AND (s.SEGMENT = t.SEGMENT OR (s.SEGMENT IS NULL AND t.SEGMENT IS NULL) )
AND (s.user_segment = t.user_segment OR (s.user_segment IS NULL AND t.user_segment IS NULL) )
AND t.interval_date > dateadd(week,-1,$FROM_T_MODES_CONTROL_UPDATED_DT_MNG)
WHEN MATCHED THEN UPDATE SET
t.segment = s.segment,
t.user_segment = s.user_segment,
t.payers_segment = s.payers_segment,
t.interval_date = s.interval_date,
t.lo_entry_id = s.lo_entry_id,
t.match_type = s.match_type,
t.LTV_group = s.LTV_group,
t.trophy_group = s.trophy_group,
t.arena_group = s.arena_group,
t.match_type_id = s.match_type_id,
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.is_random_mixer = s.is_random_mixer,
t.rounds_cnt = s.rounds_cnt,
t.event_start_date = s.event_start_date,
t.min_time = s.min_time,
t.max_time = s.max_time,
t.unique_users_with_LESE_boosters = s.unique_users_with_LESE_boosters,
t.rumble_size = s.rumble_size,
t.tournament_size = s.tournament_size,
t.lo_event_id = s.lo_event_id,
t.lo_event_min_trophies = s.lo_event_min_trophies,
t.lo_event_max_trophies = s.lo_event_max_trophies,
t.calendar_order = s.calendar_order,
t.hours = s.hours,
t.lo_entry_tickets_required = s.lo_entry_tickets_required,
t.lo_visible_on_homescreen = s.lo_visible_on_homescreen,
t.lo_entry_min_booster_rarity = s.lo_entry_min_booster_rarity,
t.lo_entry_max_booster_rarity = s.lo_entry_max_booster_rarity,
t.lo_event_solo_moves_per_turn = s.lo_event_solo_moves_per_turn,
t.elimination_round = s.elimination_round,
t.lo_event_turn_duration_sec = s.lo_event_turn_duration_sec,
t.lo_entry_is_private = s.lo_entry_is_private,
t.LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE = s.LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE,
t.lo_event_wins_limit = s.lo_event_wins_limit,
t.lo_is_friendly = s.lo_is_friendly,
t.lo_event_max_lives = s.lo_event_max_lives,
t.lo_event_badge = s.lo_event_badge,
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.unique_users_in_event_and_segment = s.unique_users_in_event_and_segment,
t.legendary_boosters_spent = s.legendary_boosters_spent,
t.se_boosters_spent = s.se_boosters_spent,
t.DIAMOND1_BOOSTERS_SPENT = s.DIAMOND1_BOOSTERS_SPENT,
t.DIAMOND2_BOOSTERS_SPENT = s.DIAMOND2_BOOSTERS_SPENT,
t.DIAMOND3_BOOSTERS_SPENT = s.DIAMOND3_BOOSTERS_SPENT,
t.GOLD_BOOSTERS_SPENT = s.GOLD_BOOSTERS_SPENT,
t.SILVER_BOOSTERS_SPENT = s.SILVER_BOOSTERS_SPENT,
t.BRONZE_BOOSTERS_SPENT = s.BRONZE_BOOSTERS_SPENT,
t.LEGENDARY_BOOSTERS_EARNED = s.LEGENDARY_BOOSTERS_EARNED,
t.SE_BOOSTERS_EARNED = s.SE_BOOSTERS_EARNED,
t.DIAMOND1_BOOSTERS_EARNED = s.DIAMOND1_BOOSTERS_EARNED,
t.DIAMOND2_BOOSTERS_EARNED = s.DIAMOND2_BOOSTERS_EARNED,
t.DIAMOND3_BOOSTERS_EARNED = s.DIAMOND3_BOOSTERS_EARNED,
t.GOLD_BOOSTERS_EARNED = s.GOLD_BOOSTERS_EARNED,
t.SILVER_BOOSTERS_EARNED = s.SILVER_BOOSTERS_EARNED,
t.BRONZE_BOOSTERS_EARNED = s.BRONZE_BOOSTERS_EARNED,
t.SHIELDS_EARNED = s.SHIELDS_EARNED,
t.SHIELDS_SPENT = s.SHIELDS_SPENT,
t.PERKS_EARNED = s.PERKS_EARNED,
t.PERKS_SPENT = s.PERKS_SPENT,
t.TICKETS_EARNED = s.TICKETS_EARNED,
t.TICKETS_SPENT = s.TICKETS_SPENT,
t.TROPHY_EARNED = s.TROPHY_EARNED,
t.TROPHY_SPENT = s.TROPHY_SPENT,
t.COINS_EARNED = s.COINS_EARNED,
t.COINS_SPENT = s.COINS_SPENT,
t.STICKERSEASONAL_EARNED = s.STICKERSEASONAL_EARNED,
t.STICKERSILVER_EARNED = s.STICKERSILVER_EARNED,
t.STICKERWHITE_EARNED = s.STICKERWHITE_EARNED,
t.SPIN_EARNED = s.SPIN_EARNED,
t.SUPERSPIN_EARNED = s.SUPERSPIN_EARNED,
t.STICKERADVENTURE_EARNED = s.STICKERADVENTURE_EARNED,
t.STICKERDIAMOND_EARNED = s.STICKERDIAMOND_EARNED,
t.STICKERGOLD_EARNED = s.STICKERGOLD_EARNED,
t.STICKERTOKEN_EARNED = s.STICKERTOKEN_EARNED,
t.stickerseasonal_earned_duplicate = s.stickerseasonal_earned_duplicate,
t.stickersilver_earned_duplicate = s.stickersilver_earned_duplicate,
t.stickerwhite_earned_duplicate = s.stickerwhite_earned_duplicate,
t.stickeradventure_earned_duplicate = s.stickeradventure_earned_duplicate,
t.stickerdiamond_earned_duplicate = s.stickerdiamond_earned_duplicate,
t.stickergold_earned_duplicate = s.stickergold_earned_duplicate,
t.SUPERSPIN_EARNED_VALUE = s.SUPERSPIN_EARNED_VALUE,
t.STICKERADVENTURE_EARNED_VALUE = s.STICKERADVENTURE_EARNED_VALUE,
t.STICKERDIAMOND_EARNED_VALUE = s.STICKERDIAMOND_EARNED_VALUE,
t.STICKERGOLD_EARNED_VALUE = s.STICKERGOLD_EARNED_VALUE,
t.STICKERTOKEN_EARNED_VALUE = s.STICKERTOKEN_EARNED_VALUE,
t.LEGENDARY_BOOSTERS_SPENT_VALUE = s.LEGENDARY_BOOSTERS_SPENT_VALUE,
t.SE_BOOSTERS_SPENT_VALUE = s.SE_BOOSTERS_SPENT_VALUE,
t.DIAMOND1_BOOSTERS_SPENT_VALUE = s.DIAMOND1_BOOSTERS_SPENT_VALUE,
t.DIAMOND2_BOOSTERS_SPENT_VALUE = s.DIAMOND2_BOOSTERS_SPENT_VALUE,
t.DIAMOND3_BOOSTERS_SPENT_VALUE = s.DIAMOND3_BOOSTERS_SPENT_VALUE,
t.GOLD_BOOSTERS_SPENT_VALUE = s.GOLD_BOOSTERS_SPENT_VALUE,
t.SILVER_BOOSTERS_SPENT_VALUE = s.SILVER_BOOSTERS_SPENT_VALUE,
t.BRONZE_BOOSTERS_SPENT_VALUE = s.BRONZE_BOOSTERS_SPENT_VALUE,
t.LEGENDARY_BOOSTERS_EARNED_VALUE = s.LEGENDARY_BOOSTERS_EARNED_VALUE,
t.SE_BOOSTERS_EARNED_VALUE = s.SE_BOOSTERS_EARNED_VALUE,
t.DIAMOND1_BOOSTERS_EARNED_VALUE = s.DIAMOND1_BOOSTERS_EARNED_VALUE,
t.DIAMOND2_BOOSTERS_EARNED_VALUE = s.DIAMOND2_BOOSTERS_EARNED_VALUE,
t.DIAMOND3_BOOSTERS_EARNED_VALUE = s.DIAMOND3_BOOSTERS_EARNED_VALUE,
t.GOLD_BOOSTERS_EARNED_VALUE = s.GOLD_BOOSTERS_EARNED_VALUE,
t.SILVER_BOOSTERS_EARNED_VALUE = s.SILVER_BOOSTERS_EARNED_VALUE,
t.BRONZE_BOOSTERS_EARNED_VALUE = s.BRONZE_BOOSTERS_EARNED_VALUE,
t.SHIELDS_EARNED_VALUE = s.SHIELDS_EARNED_VALUE,
t.SHIELDS_SPENT_VALUE = s.SHIELDS_SPENT_VALUE,
t.PERKS_EARNED_VALUE = s.PERKS_EARNED_VALUE,
t.PERKS_SPENT_VALUE = s.PERKS_SPENT_VALUE,
t.TICKETS_EARNED_VALUE = s.TICKETS_EARNED_VALUE,
t.TICKETS_SPENT_VALUE = s.TICKETS_SPENT_VALUE,
t.TROPHY_EARNED_VALUE = s.TROPHY_EARNED_VALUE,
t.TROPHY_SPENT_VALUE = s.TROPHY_SPENT_VALUE,
t.STICKERSEASONAL_EARNED_VALUE = s.STICKERSEASONAL_EARNED_VALUE,
t.STICKERSILVER_EARNED_VALUE = s.STICKERSILVER_EARNED_VALUE,
t.STICKERWHITE_EARNED_VALUE = s.STICKERWHITE_EARNED_VALUE,
t.SPIN_EARNED_VALUE = s.SPIN_EARNED_VALUE,
t.mini_game_tokens_earned_value = s.mini_game_tokens_earned_value,
t.mini_game_tokens_earned = s.mini_game_tokens_earned,
t.total_matches = s.total_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_match_score_in_segment = s.total_match_score_in_segment,
t.games_with_legendary_boosters = s.games_with_legendary_boosters,
t.games_with_SE_boosters = s.games_with_SE_boosters,
t.diamond_losses = s.diamond_losses,
t.diamond3_losses = s.diamond3_losses,
t.legendary_losses = s.legendary_losses,
t.SE_losses = s.SE_losses,
t.total_losses = s.total_losses,
t.total_wins = s.total_wins,
t.legendary_wins = s.legendary_wins,
t.SE_wins = s.SE_wins,
t.total_coin_value = s.total_coin_value,
t.total_match_ids = s.total_match_ids,
t.total_bots = s.total_bots,
t.total_on_fire = s.total_on_fire,
t.conceded_matches = s.conceded_matches,
t.total_perk_activations = s.total_perk_activations,
t.Match_making_found_time = s.Match_making_found_time,
t.total_matches_for_tournament_calc = s.total_matches_for_tournament_calc,
t.TWO_X_MATCHES = s.TWO_X_MATCHES,
t.THREE_X_MATCHES = s.THREE_X_MATCHES,
t.FOUR_X_MATCHES = s.FOUR_X_MATCHES,
t.FIVE_X_MATCHES = s.FIVE_X_MATCHES,
t.LE_points_earned = s.LE_points_earned,
t.LE_value_earned = s.LE_value_earned,
t.LAST_UPDATED_DT = s.LAST_UPDATED_DT,
t.DW_INSERT_DT = s.DW_INSERT_DT
WHEN NOT MATCHED THEN INSERT (
segment,
user_segment,
payers_segment,
interval_date,
lo_entry_id,
match_type,
LTV_group,
trophy_group,
arena_group,
match_type_id,
match_mode,
modifier_1,
modifier_2,
match_modifier_3,
is_random_mixer,
rounds_cnt,
event_start_date,
min_time,
max_time,
unique_users_with_LESE_boosters,
rumble_size,
tournament_size,
lo_event_id,
lo_event_min_trophies,
lo_event_max_trophies,
calendar_order,
hours,
lo_entry_tickets_required,
lo_visible_on_homescreen,
lo_entry_min_booster_rarity,
lo_entry_max_booster_rarity,
lo_event_solo_moves_per_turn,
elimination_round,
lo_event_turn_duration_sec,
lo_entry_is_private,
LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE,
lo_event_wins_limit,
lo_is_friendly,
lo_event_max_lives,
lo_event_badge,
lo_entry_trophy_mode,
lo_entry_trophy_gain,
lo_entry_trophy_loss,
unique_users_in_event_and_segment,
legendary_boosters_spent,
se_boosters_spent,
DIAMOND1_BOOSTERS_SPENT,
DIAMOND2_BOOSTERS_SPENT,
DIAMOND3_BOOSTERS_SPENT,
GOLD_BOOSTERS_SPENT,
SILVER_BOOSTERS_SPENT,
BRONZE_BOOSTERS_SPENT,
LEGENDARY_BOOSTERS_EARNED,
SE_BOOSTERS_EARNED,
DIAMOND1_BOOSTERS_EARNED,
DIAMOND2_BOOSTERS_EARNED,
DIAMOND3_BOOSTERS_EARNED,
GOLD_BOOSTERS_EARNED,
SILVER_BOOSTERS_EARNED,
BRONZE_BOOSTERS_EARNED,
SHIELDS_EARNED,
SHIELDS_SPENT,
PERKS_EARNED,
PERKS_SPENT,
TICKETS_EARNED,
TICKETS_SPENT,
TROPHY_EARNED,
TROPHY_SPENT,
COINS_EARNED,
COINS_SPENT,
STICKERSEASONAL_EARNED,
STICKERSILVER_EARNED,
STICKERWHITE_EARNED,
SPIN_EARNED,
SUPERSPIN_EARNED,
STICKERADVENTURE_EARNED,
STICKERDIAMOND_EARNED,
STICKERGOLD_EARNED,
STICKERTOKEN_EARNED,
stickerseasonal_earned_duplicate,
stickersilver_earned_duplicate,
stickerwhite_earned_duplicate,
stickeradventure_earned_duplicate,
stickerdiamond_earned_duplicate,
stickergold_earned_duplicate,
SUPERSPIN_EARNED_VALUE,
STICKERADVENTURE_EARNED_VALUE,
STICKERDIAMOND_EARNED_VALUE,
STICKERGOLD_EARNED_VALUE,
STICKERTOKEN_EARNED_VALUE,
LEGENDARY_BOOSTERS_SPENT_VALUE,
SE_BOOSTERS_SPENT_VALUE,
DIAMOND1_BOOSTERS_SPENT_VALUE,
DIAMOND2_BOOSTERS_SPENT_VALUE,
DIAMOND3_BOOSTERS_SPENT_VALUE,
GOLD_BOOSTERS_SPENT_VALUE,
SILVER_BOOSTERS_SPENT_VALUE,
BRONZE_BOOSTERS_SPENT_VALUE,
LEGENDARY_BOOSTERS_EARNED_VALUE,
SE_BOOSTERS_EARNED_VALUE,
DIAMOND1_BOOSTERS_EARNED_VALUE,
DIAMOND2_BOOSTERS_EARNED_VALUE,
DIAMOND3_BOOSTERS_EARNED_VALUE,
GOLD_BOOSTERS_EARNED_VALUE,
SILVER_BOOSTERS_EARNED_VALUE,
BRONZE_BOOSTERS_EARNED_VALUE,
SHIELDS_EARNED_VALUE,
SHIELDS_SPENT_VALUE,
PERKS_EARNED_VALUE,
PERKS_SPENT_VALUE,
TICKETS_EARNED_VALUE,
TICKETS_SPENT_VALUE,
TROPHY_EARNED_VALUE,
TROPHY_SPENT_VALUE,
STICKERSEASONAL_EARNED_VALUE,
STICKERSILVER_EARNED_VALUE,
STICKERWHITE_EARNED_VALUE,
SPIN_EARNED_VALUE,
mini_game_tokens_earned_value,
mini_game_tokens_earned,
total_matches,
total_seconds_played_in_segment,
total_booster_activations_in_segment,
total_match_score_in_segment,
games_with_legendary_boosters,
games_with_SE_boosters,
diamond_losses,
diamond3_losses,
legendary_losses,
SE_losses,
total_losses,
total_wins,
legendary_wins,
SE_wins,
total_coin_value,
total_match_ids,
total_bots,
total_on_fire,
conceded_matches,
total_perk_activations,
Match_making_found_time,
total_matches_for_tournament_calc,
TWO_X_MATCHES,
THREE_X_MATCHES,
FOUR_X_MATCHES,
FIVE_X_MATCHES,
LE_points_earned,
LE_value_earned,
LAST_UPDATED_DT,
DW_INSERT_DT
)
VALUES (
s.segment,
s.user_segment,
s.payers_segment,
s.interval_date,
s.lo_entry_id,
s.match_type,
s.LTV_group,
s.trophy_group,
s.arena_group,
s.match_type_id,
s.match_mode,
s.modifier_1,
s.modifier_2,
s.match_modifier_3,
s.is_random_mixer,
s.rounds_cnt,
s.event_start_date,
s.min_time,
s.max_time,
s.unique_users_with_LESE_boosters,
s.rumble_size,
s.tournament_size,
s.lo_event_id,
s.lo_event_min_trophies,
s.lo_event_max_trophies,
s.calendar_order,
s.hours,
s.lo_entry_tickets_required,
s.lo_visible_on_homescreen,
s.lo_entry_min_booster_rarity,
s.lo_entry_max_booster_rarity,
s.lo_event_solo_moves_per_turn,
s.elimination_round,
s.lo_event_turn_duration_sec,
s.lo_entry_is_private,
s.LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE,
s.lo_event_wins_limit,
s.lo_is_friendly,
s.lo_event_max_lives,
s.lo_event_badge,
s.lo_entry_trophy_mode,
s.lo_entry_trophy_gain,
s.lo_entry_trophy_loss,
s.unique_users_in_event_and_segment,
s.legendary_boosters_spent,
s.se_boosters_spent,
s.DIAMOND1_BOOSTERS_SPENT,
s.DIAMOND2_BOOSTERS_SPENT,
s.DIAMOND3_BOOSTERS_SPENT,
s.GOLD_BOOSTERS_SPENT,
s.SILVER_BOOSTERS_SPENT,
s.BRONZE_BOOSTERS_SPENT,
s.LEGENDARY_BOOSTERS_EARNED,
s.SE_BOOSTERS_EARNED,
s.DIAMOND1_BOOSTERS_EARNED,
s.DIAMOND2_BOOSTERS_EARNED,
s.DIAMOND3_BOOSTERS_EARNED,
s.GOLD_BOOSTERS_EARNED,
s.SILVER_BOOSTERS_EARNED,
s.BRONZE_BOOSTERS_EARNED,
s.SHIELDS_EARNED,
s.SHIELDS_SPENT,
s.PERKS_EARNED,
s.PERKS_SPENT,
s.TICKETS_EARNED,
s.TICKETS_SPENT,
s.TROPHY_EARNED,
s.TROPHY_SPENT,
s.COINS_EARNED,
s.COINS_SPENT,
s.STICKERSEASONAL_EARNED,
s.STICKERSILVER_EARNED,
s.STICKERWHITE_EARNED,
s.SPIN_EARNED,
s.SUPERSPIN_EARNED,
s.STICKERADVENTURE_EARNED,
s.STICKERDIAMOND_EARNED,
s.STICKERGOLD_EARNED,
s.STICKERTOKEN_EARNED,
s.stickerseasonal_earned_duplicate,
s.stickersilver_earned_duplicate,
s.stickerwhite_earned_duplicate,
s.stickeradventure_earned_duplicate,
s.stickerdiamond_earned_duplicate,
s.stickergold_earned_duplicate,
s.SUPERSPIN_EARNED_VALUE,
s.STICKERADVENTURE_EARNED_VALUE,
s.STICKERDIAMOND_EARNED_VALUE,
s.STICKERGOLD_EARNED_VALUE,
s.STICKERTOKEN_EARNED_VALUE,
s.LEGENDARY_BOOSTERS_SPENT_VALUE,
s.SE_BOOSTERS_SPENT_VALUE,
s.DIAMOND1_BOOSTERS_SPENT_VALUE,
s.DIAMOND2_BOOSTERS_SPENT_VALUE,
s.DIAMOND3_BOOSTERS_SPENT_VALUE,
s.GOLD_BOOSTERS_SPENT_VALUE,
s.SILVER_BOOSTERS_SPENT_VALUE,
s.BRONZE_BOOSTERS_SPENT_VALUE,
s.LEGENDARY_BOOSTERS_EARNED_VALUE,
s.SE_BOOSTERS_EARNED_VALUE,
s.DIAMOND1_BOOSTERS_EARNED_VALUE,
s.DIAMOND2_BOOSTERS_EARNED_VALUE,
s.DIAMOND3_BOOSTERS_EARNED_VALUE,
s.GOLD_BOOSTERS_EARNED_VALUE,
s.SILVER_BOOSTERS_EARNED_VALUE,
s.BRONZE_BOOSTERS_EARNED_VALUE,
s.SHIELDS_EARNED_VALUE,
s.SHIELDS_SPENT_VALUE,
s.PERKS_EARNED_VALUE,
s.PERKS_SPENT_VALUE,
s.TICKETS_EARNED_VALUE,
s.TICKETS_SPENT_VALUE,
s.TROPHY_EARNED_VALUE,
s.TROPHY_SPENT_VALUE,
s.STICKERSEASONAL_EARNED_VALUE,
s.STICKERSILVER_EARNED_VALUE,
s.STICKERWHITE_EARNED_VALUE,
s.SPIN_EARNED_VALUE,
s.mini_game_tokens_earned_value,
s.mini_game_tokens_earned,
s.total_matches,
s.total_seconds_played_in_segment,
s.total_booster_activations_in_segment,
s.total_match_score_in_segment,
s.games_with_legendary_boosters,
s.games_with_SE_boosters,
s.diamond_losses,
s.diamond3_losses,
s.legendary_losses,
s.SE_losses,
s.total_losses,
s.total_wins,
s.legendary_wins,
s.SE_wins,
s.total_coin_value,
s.total_match_ids,
s.total_bots,
s.total_on_fire,
s.conceded_matches,
s.total_perk_activations,
s.Match_making_found_time,
s.total_matches_for_tournament_calc,
s.TWO_X_MATCHES,
s.THREE_X_MATCHES,
s.FOUR_X_MATCHES,
s.FIVE_X_MATCHES,
s.LE_points_earned,
s.LE_value_earned,
s.LAST_UPDATED_DT,
s.DW_INSERT_DT
)
;
''',
'07_update_mng_table': '''
INSERT INTO MATCH_MASTERS.MANAGE.MANAGEMENT_TABLE(
TABLE_NAME
,DW_INSERT_DATE
,LAST_LOAD_DATE
,ROWS_LOADED
) VALUES (
'CANDIVORE.SEMANTIC_LAYER.T_MODES_CONTROL'
,current_timestamp()
,(select max(LAST_UPDATED_DT) from MATCH_MASTERS.TRANSFORMATION.TRANS_MODES_CONTROL)
,(select count(*) from MATCH_MASTERS.TRANSFORMATION.TRANS_MODES_CONTROL)
)
'''
}

Add Comment

Please, Sign In to add comment