Untitled

 

SELECT

DISTINCT match_end_date AS INTERVAL_DATE,

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 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 < 4500 THEN '3800-4500'

WHEN trophies_cnt < 5500 THEN '4500-5500'

WHEN trophies_cnt < 7000 THEN '5500-7000'

WHEN trophies_cnt < 10000 THEN '7000-10000'

WHEN trophies_cnt < 13000 THEN '10000-13000'

WHEN trophies_cnt < 16000 THEN '13000-16000'

WHEN trophies_cnt < 20000 THEN '16000-20000'

WHEN trophies_cnt < 30000 THEN '20000-30000'

WHEN trophies_cnt >= 30000 THEN '30000+'

END AS trophy_group_2,

pvp_mode,

MATCH_TYPE_ID,

CASE WHEN is_random_mixer = TRUE and pvp_mode = 'PvP' THEN 'Daily_Random_Mixer' ELSE match_type END AS MATCH_TYPE,

MATCH_MODE,

MODIFIER_1,

MODIFIER_2,

match_modifier_3,

is_conceded,

is_technical,

concat(

MATCH_SUB_TYPE,

'-',

MATCH_MODE,

'+',

MODIFIER_1,

'-',

MODIFIER_2,

'-',

match_modifier_3

) AS match_sub_type,

RESOURCE_SUB_TYPE,

BOOSTER_NAME,

RIVAL_RESOURCE_SUB_TYPE,

RUMBLE_SIZE,

CASE WHEN PVP_MODE NOT LIKE 'PvP' THEN 'No Rival(Solo/Rumble)' ELSE RIVAL_BOOSTER_NAME END RIVAL_BOOSTER_NAME,

rounds_cnt,

is_rival_bot,

turn_index,

current_arena_index,

SUM(ROUNDS_CNT) AS ROUNDS,

SUM(MATCH_SCORE) AS MATCH_SCORE,

SUM(MATCH_DURATION_SEC) / 60 AS MATCH_DURATION_MINS,

SUM(ABILITY_ACTIVATED_CNT) AS BOOSTER_ACTIVATIONS,

SUM(ifnull(CS_RESYNCS, 0)) AS RESYNCS,

COUNT(

DISTINCT CASE WHEN CS_RESYNCS > 0 THEN concat(match_id, user_id, rematch_cnt) END

) AS RESYNC_MATCHES,

SUM(ifnull(ERROR_RESYNCS, 0)) AS ERROR_RESYNCS,

COUNT(

DISTINCT CASE WHEN ERROR_RESYNCS > 0 THEN concat(match_id, user_id, rematch_cnt) END

) AS ERROR_RESYNC_MATCHES,

SUM(

CASE WHEN is_conceded = 1

AND is_won = 0

AND match_duration_sec = 0 THEN 1 END

) AS CONCEDED_MATCHES,

SUM(

CASE WHEN is_conceded = 1

AND is_won = 0

AND match_duration_sec BETWEEN 1

AND 10 THEN 1 END

) AS CONCEDED_MATCHES_1_10,

SUM(

CASE WHEN is_conceded = 1

AND is_won = 0

AND match_duration_sec BETWEEN 10

AND 60 THEN 1 END

) AS CONCEDED_MATCHES_10_60,

SUM(

CASE WHEN is_conceded = 1

AND is_won = 0

AND match_duration_sec > 60 THEN 1 END

) AS CONCEDED_MATCHES_AFTER_60sec,

SUM(ifnull(rival_played_out_of_time, 0)) AS OOT,

COUNT(

DISTINCT CASE WHEN rival_played_out_of_time > 0 THEN concat(match_id, user_id, rematch_cnt) END

) AS OOT_MATCHES,

COUNT(DISTINCT concat(match_id, user_id, rematch_cnt)) AS MATCHES,

sum(is_rival_bot) AS agianst_bot_MATCHES,

sum(MATCH_DURATION_SEC) as total_seconds_played_in_segment,

sum(ability_activated_cnt) as total_booster_activations_in_segment,

sum(four_matched_cnt) as total_fours,

sum(five_matched_cnt) as total_fives,

SUM(is_won) AS wins,

sum(match_making_found_time) as match_making_found_time,

AVG(MATCH_RANK) AS AVG_MATCH_RANK,

min(round(trophies_cnt/100)*100) min_trophies_in_segment,

max(round(trophies_cnt/100)*100) max_trophies_in_segment

FROM

CANDIVORE.PROD.F_USER_MATCH

WHERE

is_bot = FALSE

AND MATCH_END_DATE > DATEADD(DAY, -15, GETDATE())

GROUP BY

1,

2,

3,

4,

5,

6,

7,

8,

9,

10,

11,

12,

13,

14,

15,

16,

17,

18,

19,

20,

21,

22