Untitled

YuvalGai

Sep 8th, 2024

Never

Add comment

Not a member of Pastebin yet? Sign Up, it unlocks many cool features!

text 11.68 KB | None | 0 0

raw download clone embed print report

select
INTERVAL_DATE
,ARENA_GROUP
,CURRENT_ARENA_INDEX
,TROPHY_GROUP
,TROPHY_GROUP_2
,ARENA
,ARENA_TIERS
,ARENA_RANKS
,LTV_GROUP
,IS_PAYER
,LO_ENTRY_TROPHY_MODE
,LO_ENTRY_TROPHY_GAIN
,LO_ENTRY_TROPHY_LOSS
,LO_ENTRY_MODIFIER_TYPE1
,PVP_MODE
,MATCH_TYPE_ID
,MATCH_TYPE
,MATCH_MODE
,MODIFIER_1
,MODIFIER_2
,MATCH_MODIFIER_3
,MATCH_SUB_TYPE
,RESOURCE_SUB_TYPE
,BOOSTER_NAME
,RIVAL_RESOURCE_SUB_TYPE
,RUMBLE_SIZE
,RIVAL_BOOSTER_NAME
,ROUNDS_CNT
,IS_PRIVATE
,IS_MC_MATCH
,IS_RIVAL_BOT
,TURN_INDEX
,APP_VERSION
,SOLO_TYPE
,MUTATION_OPTIONS
,MUTATIONS_PICK
,ROUNDS
,MATCH_SCORE
,RIVAL_MATCH_SCORE
,MATCH_DURATION_MINS
,BOOSTER_ACTIVATIONS
,RESYNCS
,RESYNC_MATCHES
,ERROR_RESYNCS
,ERROR_RESYNC_MATCHES
,CONCEDED_MATCHES
,CONCEDED_MATCHES_1_10
,CONCEDED_MATCHES_10_60
,CONCEDED_MATCHES_AFTER_60SEC
,OOT
,OOT_MATCHES
,MATCHES
,USERS
,WINS
,AVG_MATCH_RANK
,AGIANST_BOT_MATCHES
,TOTAL_SECONDS_PLAYED_IN_SEGMENT
,TOTAL_BOOSTER_ACTIVATIONS_IN_SEGMENT
,TOTAL_FOURS
,TOTAL_FIVES
,MATCH_MAKING_FOUND_TIME
,PICK_NM
,PICKS
,MATCH_ID
,USER_ID
,to_timestamp(INTERVAL_DATE) as LAST_UPDATED_DT
,current_timestamp() as DW_INSERT_DT
from
(
SELECT
match_end_date AS INTERVAL_DATE,
match_id,
user_id,
arena_group,
current_arena_index,
CASE
WHEN trophies_cnt < 1500 THEN '0-1500'
WHEN trophies_cnt < 3800 THEN '1500-3800'
WHEN trophies_cnt < 8000 THEN '3800-8000'
WHEN trophies_cnt < 17000 THEN '8000-17000'
WHEN trophies_cnt < 30000 THEN '17000-30000'
ELSE '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,
case when trophies_cnt<50 then '0_spotlight_Stars'
when trophies_cnt<300 then '1_Jungle_Jam'
when trophies_cnt<500 then '2_Dustville_Duel'
when trophies_cnt<800 then '3_FireWorks_Festival'
when trophies_cnt<1100 then '4_Crystal_cave'
when trophies_cnt<1400 then '5_Laser_Matches'
when trophies_cnt<1700 then '6_Funcky_Tiles'
when trophies_cnt<2000 then '7_Plunder_Pirates'
when trophies_cnt<2300 then '8_Sugar_Rush'
when trophies_cnt<2600 then '9_Cloud_City'
when trophies_cnt<2900 then '10_Vegas_Views'
when trophies_cnt<3200 then '11_Bombs_Away'
when trophies_cnt<3500 then '12_Tribal_Trouble'
when trophies_cnt<3800 then '13_Color_Crystals'
when trophies_cnt<4500 then '14_Challenger_1'
when trophies_cnt<5500 then '15_Challenger_2'
when trophies_cnt<7000 then '16_Challenger_3'
when trophies_cnt<10000 then '17_Master_1'
when trophies_cnt<13000 then '18_Master_2'
when trophies_cnt<16000 then '19_Master_3'
when trophies_cnt<20000 then '20_Grand_Master_1'
when trophies_cnt<25000 then '21_Grand_Master_2'
when trophies_cnt<30000 then '22_Grand_Master_3'
when trophies_cnt<32000 then '23_Legend_1'
when trophies_cnt<34000 then '24_Legend_2'
when trophies_cnt<36000 then '25_Legend_3'
when trophies_cnt<38000 then '26_Legend_4'
when trophies_cnt<40000 then '27_Legend_5'
when trophies_cnt<43000 then '28_Supreme_Legend_1'
when trophies_cnt<46000 then '29_Supreme_Legend_2'
when trophies_cnt<50000 then '30_Supreme_Legend_3'
when trophies_cnt<55000 then '31_Supreme_Legend_4'
when trophies_cnt<60000 then '32_Supreme_Legend_5'
when trophies_cnt<70000 then '33_Ultimate_Legend_1'
when trophies_cnt<85000 then '34_Ultimate_Legend_2'
when trophies_cnt<105000 then '35_Ultimate_Legend_3'
when trophies_cnt<130000 then '36_Ultimate_Legend_4'
else '37_Ultimate_Legend_5' end as arena,
case when trophies_cnt<3800 then '00_Studio'
when trophies_cnt<7000 then '01_Challenger'
when trophies_cnt<16000 then '02_Master'
when trophies_cnt<30000 then '03_Grand_Master'
when trophies_cnt<40000 then '04_Legends'
when trophies_cnt<43000 then '05_Supreme_Legend'
else '06_Ultimate_Legend' end as arena_tiers,
case when trophies_cnt<3800 then '00_Studio'
when trophies_cnt<30000 then '01_Masters'
else '03_Legends_league' end as arena_ranks,
ltv_group,
is_payer,
lo_entry_trophy_mode,
lo_entry_trophy_gain,
lo_entry_trophy_loss,
lo_entry_modifier_type1,
pvp_mode,
MATCH_TYPE_ID,
CASE
WHEN is_random_mixer = TRUE THEN 'Daily_Random_Mixer'
ELSE match_type
END AS MATCH_TYPE,
MATCH_MODE,
MODIFIER_1,
MODIFIER_2,
match_modifier_3,
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_private,
is_mc_match,
is_rival_bot,
turn_index,
app_version,
CASE
when LO_EVENT_ID like '%-mc-%' then 'MC'
when LO_EVENT_ID like '%cosmic%' then 'cosmic'
when LO_EVENT_ID like '%rally%' then 'rally'
when LO_EVENT_ID like '%heist%' then 'heist'
end Solo_Type,
mutation_options,
mutations_pick,
to_varchar(mutations_pick [0]) pick0,
to_varchar(mutations_pick [1]) pick1,
to_varchar(mutations_pick [2]) pick2,
to_varchar(mutations_pick [3]) pick3,
to_varchar(mutations_pick [4]) pick4,
to_varchar(mutations_pick [5]) pick5,
to_varchar(mutations_pick [6]) pick6,
to_varchar(mutations_pick [7]) pick7,
to_varchar(mutations_pick [8]) pick8,
to_varchar(mutations_pick [9]) pick9,
SUM(ROUNDS_CNT) AS ROUNDS,
SUM(MATCH_SCORE) AS MATCH_SCORE,
SUM(rival_MATCH_SCORE) AS rival_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,
count(distinct user_id) users,
SUM(is_won) AS wins,
AVG(MATCH_RANK) AS AVG_MATCH_RANK,
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(match_making_found_time) as match_making_found_time
FROM
MATCH_MASTERS.PROD.F_USER_MATCH a
left join (
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
MATCH_MASTERS.PROD.F_LIVEOPS_CALENDAR
)
WHERE 1=1
AND rn = 1
AND date(DERIVED_TSTAMP) >= current_date-91
) b on a.calendar_entry_id = b.lo_entry_id
WHERE
is_bot = FALSE
AND MATCH_END_DATE >= current_date-91
AND match_mode = 'mutation-lab-pvp-mode'
GROUP BY ALL
)
UNPIVOT(
picks FOR pick_nm IN (
pick0,
pick1,
pick2,
pick3,
pick4,
pick5,
pick6,
pick7,
pick8,
pick9
))

Add Comment

Please, Sign In to add comment