Untitled

CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.T_MATCHMAKING_DASHBOARD AS

 

 

SELECT match_end_date, league, match_type,percentile,resource_sub_type,booster_name,mode(min_trophies_in_percentile) min_trophies_in_percentile, mode(max_trophies_in_percentile) max_trophies_in_percentile,round(mode(avg_trophies_in_percentile)) avg_trophies_in_percentile, mode(median_trophies_in_percentile) median_trophies_in_percentile,mode(unique_users_in_percentile) unique_users_in_percentile, round(mode(avg_trophy_gap)) avg_trophy_gap_in_match_type_per_percentile_group, SUM(is_won) wins, COUNT(*) matches, SUM(CASE WHEN rival_resource_sub_type = 'Special Edition' OR rival_resource_sub_type = 'Legendary' THEN 1 ELSE 0 END) matches_agianst_LESE, SUM(match_duration_sec) FROM (

SELECT *

, avg(trophy_gap) OVER (partition BY match_end_date, league, match_type ,percentile) avg_trophy_gap

, MIN(trophies_cnt) OVER(partition BY match_end_date, league, percentile) min_trophies_in_percentile

, MAX(trophies_cnt) OVER(partition BY match_end_date, league, percentile) max_trophies_in_percentile

, median(trophies_cnt) OVER(partition BY match_end_date, league, percentile) median_trophies_in_percentile

, avg(trophies_cnt) OVER(partition BY match_end_date, league, percentile) avg_trophies_in_percentile

, COUNT(DISTINCT user_id) OVER(partition BY match_end_date, league, percentile) unique_users_in_percentile

FROM (

 

 

SELECT * FROM

(SELECT A.*,B.LEAGUE,B.trophies_cnt,true_trophies_cnt,percentile, abs(true_trophies_cnt-rival_trophies_balance) trophy_gap FROM

 

 

(SELECT match_end_date, user_id, match_id,match_type,is_won,rival_trophies_balance, trophies_cnt true_trophies_cnt,CASE

WHEN booster_name = 'FoxyRoxySE' THEN 'Special Edition'

WHEN booster_name = 'MonkeyJoojoo' THEN 'Legendary'

WHEN booster_name = 'BroccoBoogie' THEN 'Legendary'

WHEN booster_name = 'CleoCadabra' THEN 'Legendary'

WHEN booster_name ='BillieBoom' THEN 'Legendary'

WHEN booster_name ='CupidSE' THEN 'Special Edition'

WHEN booster_name ='DoctorColorSE' THEN 'Special Edition'

WHEN booster_name ='ElMagnetoSE' THEN 'Special Edition'

WHEN booster_name ='AllAboardSE' THEN 'Special Edition'

WHEN booster_name ='CupidSE' THEN 'Special Edition'

WHEN booster_name ='AllAboard' THEN 'Diamond 2'

WHEN booster_name ='AutoMatcher' THEN 'Gold'

WHEN booster_name ='BalloonBlast' THEN 'Gold'

WHEN booster_name ='BlazingBoard' THEN 'Gold'

WHEN booster_name ='ColonelMcQuack' THEN 'Diamond 1'

WHEN booster_name ='CrazyClovers' THEN 'Diamond 1'

WHEN booster_name ='CrossAttack' THEN 'Gold'

WHEN booster_name ='Painter' THEN 'Silver'

WHEN booster_name ='UFOBeamSE' THEN 'Special Edition'

WHEN booster_name ='Cupid' THEN 'Diamond 3'

WHEN booster_name ='DoctorColor' THEN 'Diamond 3'

WHEN booster_name ='ElMagneto' THEN 'Diamond 3'

WHEN booster_name ='FoxyRoxy' THEN 'Diamond 2'

WHEN booster_name ='JeevesBot' THEN 'Diamond 2'

WHEN booster_name ='Lightsabers' THEN 'Diamond 1'

WHEN booster_name ='Magic' THEN 'Gold'

WHEN booster_name ='MrAppleberry' THEN 'Diamond 1'

WHEN booster_name ='QueenCobra' THEN 'Diamond 2'

WHEN booster_name ='SpecialDelivery' THEN 'Silver'

WHEN booster_name ='SweepIt' THEN 'Gold'

WHEN booster_name ='UFOBeam' THEN 'Diamond 2'

WHEN booster_name ='WoollyWorkout' THEN 'Diamond 3'

WHEN booster_name ='Infection' THEN 'Silver'

WHEN booster_name ='RubberDucky' THEN 'Bronze'

WHEN booster_name ='SmallBoom' THEN 'Bronze'

WHEN booster_name ='CreateSpecials' THEN 'Silver'

WHEN booster_name ='CheckmateCharles' THEN 'Diamond 3'

ELSE 'bugged?'

END resource_sub_type,CASE

WHEN rival_booster_name = 'FoxyRoxySE' THEN 'Special Edition'

WHEN rival_booster_name = 'MonkeyJoojoo' THEN 'Legendary'

WHEN rival_booster_name = 'BroccoBoogie' THEN 'Legendary'

WHEN rival_booster_name = 'CleoCadabra' THEN 'Legendary'

WHEN rival_booster_name ='BillieBoom' THEN 'Legendary'

WHEN rival_booster_name ='CupidSE' THEN 'Special Edition'

WHEN rival_booster_name ='DoctorColorSE' THEN 'Special Edition'

WHEN rival_booster_name ='ElMagnetoSE' THEN 'Special Edition'

WHEN rival_booster_name ='AllAboardSE' THEN 'Special Edition'

WHEN rival_booster_name ='CupidSE' THEN 'Special Edition'

WHEN rival_booster_name ='AllAboard' THEN 'Diamond 2'

WHEN rival_booster_name ='AutoMatcher' THEN 'Gold'

WHEN rival_booster_name ='BalloonBlast' THEN 'Gold'

WHEN rival_booster_name ='BlazingBoard' THEN 'Gold'

WHEN rival_booster_name ='ColonelMcQuack' THEN 'Diamond 1'

WHEN rival_booster_name ='CrazyClovers' THEN 'Diamond 1'

WHEN rival_booster_name ='CrossAttack' THEN 'Gold'

WHEN rival_booster_name ='Painter' THEN 'Silver'

WHEN rival_booster_name ='UFOBeamSE' THEN 'Special Edition'

WHEN rival_booster_name ='Cupid' THEN 'Diamond 3'

WHEN rival_booster_name ='DoctorColor' THEN 'Diamond 3'

WHEN rival_booster_name ='ElMagneto' THEN 'Diamond 3'

WHEN rival_booster_name ='FoxyRoxy' THEN 'Diamond 2'

WHEN rival_booster_name ='JeevesBot' THEN 'Diamond 2'

WHEN rival_booster_name ='Lightsabers' THEN 'Diamond 1'

WHEN rival_booster_name ='Magic' THEN 'Gold'

WHEN rival_booster_name ='MrAppleberry' THEN 'Diamond 1'

WHEN rival_booster_name ='QueenCobra' THEN 'Diamond 2'

WHEN rival_booster_name ='SpecialDelivery' THEN 'Silver'

WHEN rival_booster_name ='SweepIt' THEN 'Gold'

WHEN rival_booster_name ='UFOBeam' THEN 'Diamond 2'

WHEN rival_booster_name ='WoollyWorkout' THEN 'Diamond 3'

WHEN rival_booster_name ='Infection' THEN 'Silver'

WHEN rival_booster_name ='RubberDucky' THEN 'Bronze'

WHEN rival_booster_name ='SmallBoom' THEN 'Bronze'

WHEN rival_booster_name ='CreateSpecials' THEN 'Silver'

WHEN rival_booster_name ='CheckmateCharles' THEN 'Diamond 3'

ELSE 'bugged?'

END rival_resource_sub_type, booster_name, match_duration_sec FROM candivore.prod.f_user_match WHERE match_end_date > '2022-10-12' AND pvp_mode = 'PvP' AND match_type != 'Challenge' AND (is_private IS NULL OR is_private = FALSE)) A

 

LEFT JOIN

 

(SELECT *, CASE

WHEN rrn/total_daily_matches_in_arena_group_and_match_type <= 0.1 THEN 'p0-p10'

WHEN rrn/total_daily_matches_in_arena_group_and_match_type <= 0.2 THEN 'p10-p20'

WHEN rrn/total_daily_matches_in_arena_group_and_match_type <= 0.3 THEN 'p20-p30'

WHEN rrn/total_daily_matches_in_arena_group_and_match_type <= 0.4 THEN 'p30-p40'

WHEN rrn/total_daily_matches_in_arena_group_and_match_type <= 0.5 THEN 'p40-p50'

WHEN rrn/total_daily_matches_in_arena_group_and_match_type <= 0.6 THEN 'p50-p60'

WHEN rrn/total_daily_matches_in_arena_group_and_match_type <= 0.7 THEN 'p60-p70'

WHEN rrn/total_daily_matches_in_arena_group_and_match_type <= 0.8 THEN 'p70-p80'

WHEN rrn/total_daily_matches_in_arena_group_and_match_type <= 0.9 THEN 'p80-p90'

WHEN rrn/total_daily_matches_in_arena_group_and_match_type <= 0.95 THEN 'p90-p95'

WHEN rrn/total_daily_matches_in_arena_group_and_match_type <= 0.99 THEN 'p95-p99'

WHEN rrn/total_daily_matches_in_arena_group_and_match_type <= 1 THEN 'p99-p100' END percentile

FROM

SELECT *,MAX(rrn) OVER (partition BY interval_date, league) total_daily_matches_in_arena_group_and_match_type

FROM

(SELECT interval_date,user_id,trophies_cnt,CASE

    WHEN login_arena<=13 THEN 'Studios'

    WHEN login_arena<=22 THEN 'Master_League'

    WHEN login_arena>=23 THEN 'Legends_League' END league

,ROW_NUMBER() OVER(partition BY interval_date, league ORDER BY trophies_cnt) rrn

FROM CANDIVORE.PROD.DAILY_USERS_FROM_PARAMS WHERE is_active = 1 AND interval_date > '2022-09-12')

) B

 

ON A.user_id = B.user_id AND A.match_end_date = B.interval_date)

 

 

) GROUP BY 1,2,3,4,5,6