SELECT match_end_date, league, match_type, pvp_mode, tr

SELECT

match_end_date,

league,

match_type,

pvp_mode,

trophies_percentile,

elo_percentile,

is_rival_bot,

resource_sub_type,

rival_resource_sub_type,

booster_name,

match_end_date as LAST_UPDATED_DT,

current_timestamp() as DW_INSERT_DT,

mode(min_trophies_in_trophies_percentile) min_trophies_in_trophies_percentile,

mode(max_trophies_in_trophies_percentile) max_trophies_in_trophies_percentile,

round(mode(avg_trophies_in_trophies_percentile)) avg_trophies_in_trophies_percentile,

mode(median_trophies_in_trophies_percentile) median_trophies_in_trophies_percentile,

mode(unique_users_in_trophies_percentile) unique_users_in_trophies_percentile,

round(mode(avg_trophy_gap)) avg_trophy_gap_in_match_type_per_trophy_percentile_group,

mode(min_elo_in_elo_percentile) min_elo_in_elo_percentile,

mode(max_elo_in_elo_percentile) max_elo_in_elo_percentile,

round(mode(avg_elo_in_elo_percentile)) avg_elo_in_elo_percentile,

mode(median_elo_in_elo_percentile) median_elo_in_elo_percentile,

mode(unique_users_in_elo_percentile) unique_users_in_elo_percentile,

round(mode(avg_elo_gap)) avg_elop_in_match_type_per_elo_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

END

) matches_agianst_LESE,

SUM(match_duration_sec) match_duration_sec

FROM

(

SELECT

*,

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

MIN(trophies_cnt) OVER(partition BY match_end_date, league, trophies_percentile) min_trophies_in_trophies_percentile,

MAX(trophies_cnt) OVER(partition BY match_end_date, league, trophies_percentile) max_trophies_in_trophies_percentile,

median(trophies_cnt) OVER(partition BY match_end_date, league, trophies_percentile) median_trophies_in_trophies_percentile,

avg(trophies_cnt) OVER(partition BY match_end_date, league, trophies_percentile) avg_trophies_in_trophies_percentile,

COUNT(DISTINCT user_id) OVER(partition BY match_end_date, league, trophies_percentile) unique_users_in_trophies_percentile,

avg(elo_gap) OVER (partition BY match_end_date, league, match_type, elo_percentile) avg_elo_gap,

MIN(previous_elo_balance) OVER(partition BY match_end_date, league, elo_percentile) min_elo_in_elo_percentile,

MAX(previous_elo_balance) OVER(partition BY match_end_date, league, elo_percentile) max_elo_in_elo_percentile,

median(previous_elo_balance) OVER(partition BY match_end_date, league, elo_percentile) median_elo_in_elo_percentile,

avg(previous_elo_balance) OVER(partition BY match_end_date, league, elo_percentile) avg_elo_in_elo_percentile,

COUNT(DISTINCT user_id) OVER(partition BY match_end_date, league, elo_percentile) unique_users_in_elo_percentile

FROM

(

SELECT

A.*,

B.trophies_percentile,

B.elo_percentile,

abs(trophies_cnt - rival_trophies_balance) trophy_gap,

abs(previous_elo_balance - previous_rival_elo_balance) elo_gap

FROM

(

SELECT

match_end_date,

user_id,

pvp_mode,

match_type,

is_rival_bot,

is_won,

rival_trophies_balance,

trophies_cnt,

resource_sub_type,

rival_resource_sub_type,

booster_name,

match_duration_sec,

previous_elo_balance,

rival_elo-(elo-previous_elo_balance) previous_rival_elo_balance,

case

when previous_trophies_balance < 3800 then 'Studios'

when previous_trophies_balance < 30000 then 'MR'

when previous_trophies_balance >= 30000 then 'LL'

else 'Check data'

End league

FROM

MATCH_MASTERS.prod.f_user_match

WHERE 1=1

AND match_end_date >= current_date - 91

AND pvp_mode in ('PvP','Showdown')

AND (is_private IS NULL OR is_private = FALSE)

and is_rival_bot = 0

) A

LEFT JOIN (

SELECT

user_id

, interval_date

, trophies_percentile

, elo_percentile

FROM MATCH_MASTERS.PROD.DAILY_USERS_FROM_PARAMS

WHERE

is_active = 1

and interval_date >= current_date - 91

) B ON A.user_id = B.user_id

AND A.match_end_date = B.interval_date

)

where 1=1

and MATCH_END_DATE >= current_date - 91

GROUP BY ALL

Untitled