CREATE OR REPLACE TABLE candivore.semantic_layer.T_MODES_CONTROL AS
  SELECT A.*
, G.match_type2
, G.match_type_id
, G.match_mode
, G.modifier_1
, G.modifier_2
, G.is_random_mixer
, G.rounds_cnt
, G.total_matches
, G.unique_users_from_match_end
, G.event_start_date
, G.event_start_ts
, G.total_seconds_played_in_segment
, G.total_booster_activations_in_segment
, G.total_match_score_in_segment
, G.games_with_legendary_boosters
, G.games_with_SE_boosters
, G.diamond_losses
, G.diamond3_losses
, G.legendary_losses
, G.SE_losses
, G.total_losses
, G.total_wins
, G.legendary_wins
, G.SE_wins
, G.min_time
, G.max_time
, G.unique_users
, G.unique_users_per_solo_prize
, G.unique_users_with_LESE_boosters
, G.total_coin_value
, G.total_match_ids
, G.rumble_size
, G.tournament_size
, G.total_bots
, G.total_on_fire
, G.conceded_matches
, G.total_perk_activations
, G.max_max_prize_eligibility
, D.points_earned LE_points_earned
, D.value_earned LE_value_earned
, C.lo_event_id
, C.lo_event_min_trophies
, C.lo_event_max_trophies
, C.calendar_order
, C.hours
, C.lo_entry_tickets_required
, C.lo_visible_on_homescreen
, C.lo_entry_min_booster_rarity
, C.lo_entry_max_booster_rarity
, C.lo_event_solo_moves_per_turn
, C.lo_event_turn_amt elimination_round
, C.lo_event_turn_duration_sec
, C.lo_entry_is_private
FROM (SELECT
DATE(derived_tstamp) interval_date
, CASE
    WHEN trophies_cnt < 800 THEN '800'
    WHEN CURRENT_ARENA_INDEX<=13 THEN 'Studios'
    WHEN CURRENT_ARENA_INDEX<=22 THEN 'Master_League'
    WHEN CURRENT_ARENA_INDEX>=23 THEN 'Legends_League'
 END arena_group
, CASE
    WHEN trophies_cnt < 125 THEN '0-125'
    WHEN trophies_cnt < 1400 THEN '125-1400'
    WHEN trophies_cnt < 2000 THEN '1400-2000'
    WHEN trophies_cnt < 3800 THEN '2000-3800'
    WHEN trophies_cnt < 7000 THEN '3800-7000'
    WHEN trophies_cnt < 16000 THEN '7000-16000'
    WHEN trophies_cnt < 30000 THEN '16000-30000'
    WHEN trophies_cnt >= 30000 THEN '30000+'
  END AS trophy_group
, LTV_group
, lo_entry_id
, lo_event_type
, lo_event_config_id
, CASE
    WHEN lo_event_type = 'Solo' THEN 'solo'
    WHEN lo_event_type = 'Rumble' THEN 'rumble'
    WHEN lo_event_type = 'AdventureMode' THEN 'adventure'
    WHEN lo_event_type LIKE 'Leaderboard%' THEN 'leaderboard'
    WHEN lo_event_type LIKE 'BoostersClash' THEN 'boosters_clash'
    WHEN match_type_id = 4 OR transaction_source LIKE 'tournament%' THEN 'tournament'
    WHEN match_type_id = 5 THEN 'daily'
    WHEN match_type_id = 2 THEN 'classic'
    ELSE 'other'
END AS match_type
, COUNT(DISTINCT match_id) transaction_matches
, COUNT(DISTINCT user_id) AS unique_users_from_transaction
, 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 is_received_resource = 0 THEN resource_cnt END) AS gold_boosters_spent
, SUM(CASE WHEN resource_sub_type = 'Silver' 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 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_sub_type = 'Legendary' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value 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 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 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 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 END) AS diamond3_boosters_spent_value
, SUM(CASE WHEN resource_sub_type = 'Gold' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS gold_boosters_spent_value
, SUM(CASE WHEN resource_sub_type = 'Silver' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS silver_boosters_spent_value
, SUM(CASE WHEN resource_sub_type = 'Bronze' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS bronze_boosters_spent_value
, SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value 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 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 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 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 END) AS diamond3_boosters_earned_value
, SUM(CASE WHEN resource_sub_type = 'Gold' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS gold_boosters_earned_value
, SUM(CASE WHEN resource_sub_type = 'Silver' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS silver_boosters_earned_value
, SUM(CASE WHEN resource_sub_type = 'Bronze' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS bronze_boosters_earned_value
, SUM(CASE WHEN resource_type = 'Shield' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS shields_earned_value
, SUM(CASE WHEN resource_type = 'Shield' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS shields_spent_value
, SUM(CASE WHEN resource_type = 'Perk' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS perks_earned_value
, SUM(CASE WHEN resource_type = 'Perk' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS perks_spent_value
, SUM(CASE WHEN resource_type = 'Ticket' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS tickets_earned_value
, SUM(CASE WHEN resource_type = 'Ticket' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS tickets_spent_value
, SUM(CASE WHEN resource_type = 'Trophy' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS trophy_earned_value
, SUM(CASE WHEN resource_type = 'Trophy' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS trophy_spent_value
FROM (SELECT temp1.*, temp2.resource_coin_value, temp1.resource_cnt*temp2.resource_coin_value AS total_coins_value FROM (SELECT * FROM CANDIVORE.PROD.F_USER_RESOURCE_TRANSACTION WHERE (transaction_source LIKE 'live_event' OR transaction_source LIKE 'match_end' OR transaction_source LIKE 'tournament%' OR transaction_source LIKE 'disconnection_refund') AND DATE(derived_tstamp) > DATEADD(DAY,-121,GETDATE())) temp1 LEFT JOIN
(SELECT resource_name, resource_version, resource_sub_type, resource_coin_value  FROM (SELECT * FROM
(SELECT *, ROW_NUMBER() OVER (partition BY resource_name, resource_version ORDER BY db_create_date DESC, resource_id DESC) AS resource_rank
  FROM candivore.prod.DIM_RESOURCE_VERSION)
  WHERE resource_rank = 1)) temp2
ON temp1.resource_id = temp2.resource_name AND temp1.resource_version = temp2.resource_version AND temp1.resource_sub_type = temp2.resource_sub_type)
GROUP BY 1,2,3,4,5,6,7,8) A

LEFT JOIN
(SELECT
match_end_date interval_date
,CASE
    WHEN trophies_cnt < 800 THEN '800'
    WHEN CURRENT_ARENA_INDEX<=13 THEN 'Studios'
    WHEN CURRENT_ARENA_INDEX<=22 THEN 'Master_League'
    WHEN CURRENT_ARENA_INDEX>=23 THEN 'Legends_League'
 END arena_group
, CASE
    WHEN trophies_cnt < 125 THEN '0-125'
    WHEN trophies_cnt < 1400 THEN '125-1400'
    WHEN trophies_cnt < 2000 THEN '1400-2000'
    WHEN trophies_cnt < 3800 THEN '2000-3800'
    WHEN trophies_cnt < 7000 THEN '3800-7000'
    WHEN trophies_cnt < 16000 THEN '7000-16000'
    WHEN trophies_cnt < 30000 THEN '16000-30000'
    WHEN trophies_cnt >= 30000 THEN '30000+'
  END AS trophy_group
, LTV_group
, calendar_entry_id lo_entry_id
, unique_users
, unique_users_with_LESE_boosters
, event_start_date
, event_start_ts
, CASE
    WHEN lo_event_type = 'Solo' THEN 'solo'
    WHEN lo_event_type = 'Rumble' THEN 'rumble'
    WHEN lo_event_type = 'AdventureMode' THEN 'adventure'
    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_type2
, match_type_id
, CASE WHEN is_random_mixer = TRUE THEN 'random' ELSE match_mode END match_mode
, is_random_mixer
, rounds_cnt
, tournament_size
, modifier_1
, modifier_2
, min_time
, max_time
, rumble_size
, max_max_prize_eligibility
, mode(unique_users_per_solo_prize) unique_users_per_solo_prize
, 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
, COUNT(DISTINCT user_id) AS unique_users_from_match_end
, 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(CASE WHEN is_won = 1 THEN 1 END) AS total_wins
, SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_won = 1 THEN 1 END) legendary_wins
, SUM(CASE WHEN resource_type = 'Booster' AND resource_sub_type = 'Special Edition' AND is_won = 1 THEN 1 END) SE_wins
, SUM(CASE WHEN resource_type = 'Booster' THEN resource_coin_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
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 CANDIVORE.PROD.F_USER_MATCH LEFT JOIN
(SELECT resource_id, resource_coin_value  FROM CANDIVORE.PROD.DIM_RESOURCE_VERSION) temp2
ON CANDIVORE.PROD.F_USER_MATCH.resource_id = temp2.resource_id WHERE is_bot = 0 AND DATE(CANDIVORE.PROD.F_USER_MATCH.derived_tstamp) > DATEADD(DAY,-121,GETDATE())))
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21) G
ON A.interval_date = G.interval_date AND A.arena_group = G.arena_group AND A.trophy_group = G.trophy_group AND A.LTV_group = G.LTV_group AND A.lo_entry_id = G.lo_entry_id AND A.match_type = G.match_type2

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,
CASE WHEN min0 IS NULL THEN min1 ELSE min0 END lo_event_min_trophies,
CASE WHEN max0 IS NULL THEN max1 ELSE max0 END 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_turn_amt, lo_event_turn_duration_sec, lo_entry_is_private
FROM
((SELECT CASE WHEN lo_original_entry_id IS NULL THEN LO_ENTRY_ID ELSE LO_original_ENTRY_ID END 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 FROM (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 rn = 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  FROM CANDIVORE.PROD.DIM_EVENT_CONFIG) B
ON A.LO_ENTRY_NAME = B.LO_EVENT_ID))
C
ON A.lo_entry_id = C.lo_entry_id

FULL OUTER JOIN
(SELECT table1.source_calendar_entry_id, table1.interval_date interval_date, table2.arena_group, table2.trophy_group, table2.LTV_group, SUM(table1.points_earned) points_earned, SUM(table1.points_earned*table1.event_point_value) value_earned FROM  (SELECT *, DATE(derived_tstamp) AS interval_date FROM CANDIVORE.PROD.F_LIVE_EVENT_PROGRESSION WHERE action = 'points_earned' AND event_slot = 'Main') AS table1 LEFT JOIN (SELECT
user_id
, interval_date
, CASE
    WHEN trophies_cnt < 800 THEN '800'
    WHEN CURRENT_ARENA_INDEX<=13 THEN 'Studios'
    WHEN CURRENT_ARENA_INDEX<=22 THEN 'Master_League'
    WHEN CURRENT_ARENA_INDEX>=23 THEN 'Legends_League'
 END ARENA_GROUP
, CASE
    WHEN trophies_cnt < 125 THEN '0-125'
    WHEN trophies_cnt < 1400 THEN '125-1400'
    WHEN trophies_cnt < 2000 THEN '1400-2000'
    WHEN trophies_cnt < 3800 THEN '2000-3800'
    WHEN trophies_cnt < 7000 THEN '3800-7000'
    WHEN trophies_cnt < 16000 THEN '7000-16000'
    WHEN trophies_cnt < 30000 THEN '16000-30000'
    WHEN trophies_cnt >= 30000 THEN '30000+'
  END AS trophy_group

, CASE WHEN total_iap_amt = 0 THEN '0'
           WHEN total_iap_amt <= 10 THEN 'Low'
           WHEN total_iap_amt <= 100 THEN 'Med'
           WHEN total_iap_amt <= 299 THEN 'High'
           WHEN total_iap_amt <= 999 THEN 'Very High'
           WHEN total_iap_amt >= 1000 THEN 'VIP'
           ELSE NULL
END AS LTV_group
 FROM CANDIVORE.PROD.USER_DAILY_PARAMS) table2 ON table1.user_id = table2.user_id AND table1.interval_date = table2.interval_date  GROUP BY 1,2,3,4,5) D
ON A.lo_entry_id = D.source_calendar_entry_id AND A.interval_date = D.interval_date AND A.LTV_group = D.LTV_group AND A.trophy_group = D.trophy_group AND A.ARENA_GROUP = D.ARENA_GROUP