Untitled

YuvalGai

Sep 8th, 2024

Never

Add comment

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

text 7.88 KB | None | 0 0

raw download clone embed print report

select
INTERVAL_DATE,
ARENA_GROUP,
TROPHY_GROUP,
LTV_GROUP,
IS_PAYER,
SENIORITY_BIN,
PVP_MODE,
MATCH_TYPE_ID,
MATCH_TYPE,
MATCH_MODE,
MODIFIER_1,
MODIFIER_2,
RESOURCE_SUB_TYPE,
RIVAL_RESOURCE_SUB_TYPE,
RUMBLE_SIZE,
ROUNDS_CNT,
PERK_USED,
INTERVAL_DATE as LAST_UPDATED_DT,
current_timestamp() as DW_INSERT_DT,
SUM(ROUNDS_CNT) AS ROUNDS,
SUM(MATCH_SCORE) AS MATCH_SCORE,
SUM(MATCH_DURATION_MINS) AS MATCH_DURATION_MINS,
SUM(BOOSTER_ACTIVATIONS) AS BOOSTER_ACTIVATIONS,
SUM(RESYNCS) as RESYNCS,
COUNT(distinct RESYNC_MATCHES) as RESYNC_MATCHES,
SUM(ERROR_RESYNCS) AS ERROR_RESYNCS,
COUNT(distinct ERROR_RESYNC_MATCHES) AS ERROR_RESYNC_MATCHES,
SUM(CONCEDED_MATCHES) AS CONCEDED_MATCHES,
SUM(CONCEDED_MATCHES_1_10) AS CONCEDED_MATCHES_1_10,
SUM(CONCEDED_MATCHES_10_60) AS CONCEDED_MATCHES_10_60,
SUM(CONCEDED_MATCHES_AFTER_60sec) AS CONCEDED_MATCHES_AFTER_60sec,
SUM(OOT) AS OOT,
COUNT(OOT_MATCHES) AS OOT_MATCHES,
COUNT(distinct MATCHES) as MATCHES,
sum(wins) as wins,
AVG(AVG_MATCH_RANK) AS AVG_MATCH_RANK
from
(
(
select
distinct match_end_date AS INTERVAL_DATE,
arena_group,
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,
ltv_group,
is_payer,
seniority_bin,
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,
RESOURCE_SUB_TYPE,
rival_resource_sub_type,
RUMBLE_SIZE,
rounds_cnt,
perk_1_resource_name perk_used,
ROUNDS_CNT AS ROUNDS,
MATCH_SCORE AS MATCH_SCORE,
MATCH_DURATION_SEC / 60 AS MATCH_DURATION_MINS,
ABILITY_ACTIVATED_CNT AS BOOSTER_ACTIVATIONS,
ifnull(CS_RESYNCS, 0) AS RESYNCS,
CASE
WHEN CS_RESYNCS > 0 THEN concat(match_id, user_id, rematch_cnt)
END AS RESYNC_MATCHES,
ifnull(ERROR_RESYNCS, 0) AS ERROR_RESYNCS,
CASE
WHEN ERROR_RESYNCS > 0 THEN concat(match_id, user_id, rematch_cnt)
END AS ERROR_RESYNC_MATCHES,
Case
when is_conceded = 1
and is_won = 0
and match_duration_sec = 0 then 1
end AS CONCEDED_MATCHES,
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,
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,
Case
when is_conceded = 1
and is_won = 0
and match_duration_sec > 60 then 1
end AS CONCEDED_MATCHES_AFTER_60sec,
ifnull(rival_played_out_of_time, 0) AS OOT,
CASE
WHEN rival_played_out_of_time > 0 THEN concat(match_id, user_id, rematch_cnt)
END as OOT_MATCHES,
concat(match_id, user_id, rematch_cnt) as MATCHES,
is_won as wins,
MATCH_RANK AS AVG_MATCH_RANK
FROM
MATCH_MASTERS.PROD.F_USER_MATCH
where
is_bot = false
and MATCH_END_DATE >= current_date - 61
)
union all
(
select
distinct match_end_date AS INTERVAL_DATE,
arena_group,
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,
ltv_group,
is_payer,
seniority_bin,
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,
RESOURCE_SUB_TYPE,
rival_resource_sub_type,
RUMBLE_SIZE,
rounds_cnt,
perk_2_resource_name perk_used,
ROUNDS_CNT AS ROUNDS,
MATCH_SCORE AS MATCH_SCORE,
MATCH_DURATION_SEC / 60 AS MATCH_DURATION_MINS,
ABILITY_ACTIVATED_CNT AS BOOSTER_ACTIVATIONS,
ifnull(CS_RESYNCS, 0) AS RESYNCS,
CASE
WHEN CS_RESYNCS > 0 THEN concat(match_id, user_id, rematch_cnt)
END AS RESYNC_MATCHES,
ifnull(ERROR_RESYNCS, 0) AS ERROR_RESYNCS,
CASE
WHEN ERROR_RESYNCS > 0 THEN concat(match_id, user_id, rematch_cnt)
END AS ERROR_RESYNC_MATCHES,
Case
when is_conceded = 1
and is_won = 0
and match_duration_sec = 0 then 1
end AS CONCEDED_MATCHES,
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,
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,
Case
when is_conceded = 1
and is_won = 0
and match_duration_sec > 60 then 1
end AS CONCEDED_MATCHES_AFTER_60sec,
ifnull(rival_played_out_of_time, 0) AS OOT,
CASE
WHEN rival_played_out_of_time > 0 THEN concat(match_id, user_id, rematch_cnt)
END as OOT_MATCHES,
concat(match_id, user_id, rematch_cnt) as MATCHES,
is_won as wins,
MATCH_RANK AS AVG_MATCH_RANK
FROM
MATCH_MASTERS.PROD.F_USER_MATCH
where
1 = 1
and is_bot = false
and MATCH_END_DATE >= current_date - 61
)
)
GROUP BY
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18

Add Comment

Please, Sign In to add comment