Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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(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,
- 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,
- CASE
- WHEN match_type_id = 4 OR transaction_source LIKE 'tournament%' THEN 'tournament'
- WHEN lo_event_id LIKE '%mutation%' THEN 'solo_mutation'
- WHEN lo_event_id LIKE 'Solo%' OR lo_event_id LIKE 'solo%' THEN 'solo'
- WHEN lo_event_type_config = 'Rumble' THEN 'rumble'
- WHEN lo_event_type_config = 'AdventureMode' THEN 'adventure'
- WHEN lo_event_type_config LIKE 'Leaderboard%' THEN 'leaderboard'
- WHEN pvp_mode = 'Showdown' then 'Showdown'
- WHEN lo_event_type_config LIKE 'BoostersClash' THEN 'boosters_clash'
- WHEN match_type_id = 5 THEN 'daily'
- WHEN match_type_id = 2 THEN 'classic'
- ELSE 'other'
- END AS 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
- 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) >= current_date - 91
- GROUP BY ALL -- (5)
- ) Transactions
- full outer JOIN
- (SELECT
- date(derived_tstamp) interval_date
- ,user_id
- ,ifnull(calendar_entry_id, 'NA') lo_entry_id
- ,CASE
- WHEN lo_event_id LIKE '%mutation%' THEN 'solo_mutation'
- WHEN lo_event_type = 'Rumble' THEN 'rumble'
- WHEN lo_event_type = 'AdventureMode' THEN 'adventure'
- WHEN lo_event_type = 'Onboarding' THEN 'onboarding'
- WHEN lo_event_id LIKE 'Solo%' OR lo_event_id LIKE 'solo%' THEN 'solo'
- WHEN pvp_mode = 'Showdown' then 'Showdown'
- WHEN lo_event_type LIKE 'Leaderboard%' THEN 'leaderboard'
- WHEN lo_event_type LIKE 'BoostersClash' THEN 'boosters_clash'
- WHEN match_type_id = 4 THEN 'tournament'
- WHEN match_type_id = 5 THEN 'daily'
- WHEN match_type_id = 2 THEN 'classic'
- ELSE match_type
- END AS 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) >= current_date - 91
- )
- )
- GROUP BY ALL ) Matches
- ON Transactions.interval_date = Matches.interval_date and Transactions.user_id = Matches.user_id and Transactions.lo_entry_id = Matches.lo_entry_id and Transactions.match_type = 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) >= current_date - 91
- 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 >= current_date - 91
- ) 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 >= current_date - 91
- GROUP BY ALL
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement