Untitled

YuvalGai

Sep 8th, 2024

Never

Add comment

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

text 5.37 KB | None | 0 0

raw download clone embed print report

SELECT
match_end_date AS INTERVAL_DATE,
case
when current_arena_index <= 13 then 'Studios'
when current_arena_index <= 22 then 'Master Ranks'
else 'Legends League'
End as arena_group,
case
when current_arena_index <= 22 then current_arena_index
else 100
end 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,
daily_p.PAYERS_SEGMENT,
calendar.LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE,
pvp_mode,
MATCH_TYPE_ID,
case
when pvp_mode = 'Rumble' then 'Rumble'
when calendar_entry_id like '%onboarding%' then 'Onboarding'
when PVP_MODE = 'Solo' then 'Solo'
when PVP_MODE = 'Showdown' then 'Showdown'
when calendar_entry_id like '%adventure%' then 'Adventure'
when match_sub_type = 'boosters-clash-friendly' then 'Booster Clash'
when match_sub_type = 'score-race' then 'Leaderboard'
when is_random_mixer = TRUE then 'Random Mixer'
else MATCH_TYPE
end match_type,
MATCH_MODE,
MODIFIER_1,
MODIFIER_2,
match_modifier_3,
RESOURCE_SUB_TYPE,
BOOSTER_NAME,
RIVAL_RESOURCE_SUB_TYPE,
RUMBLE_SIZE,
CASE
WHEN PVP_MODE In ('Solo','Rumble') THEN 'No Rival(Solo/Rumble)'
ELSE RIVAL_BOOSTER_NAME
END RIVAL_BOOSTER_NAME,
rounds_cnt,
is_private,
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,
ifnull(stakes_multiplier,1) as stakes_multiplier,
match_end_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_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, match.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, match.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, match.user_id, rematch_cnt)
END
) AS OOT_MATCHES,
COUNT(DISTINCT concat(match_id, match.user_id, rematch_cnt)) AS MATCHES,
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(rshaped_matched_cnt) as total_rshaped,
sum(square_shapes_cnt) as total_square_shaped,
sum(extra_moves_cnt) as total_extra_moves,
sum(match_making_found_time) as match_making_found_time
FROM
MATCH_MASTERS.PROD.F_USER_MATCH match
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 derived_tstamp >= current_date - 91
)
WHERE
rn = 1
) calendar on match.calendar_entry_id = calendar.lo_entry_id
left join
(select user_id,interval_date,payers_segment from MATCH_MASTERS.prod.daily_users_from_params where interval_date >= current_date - 91) daily_p
on match.user_id = daily_p.user_id and match.match_end_date = daily_p.interval_date
WHERE
is_bot = FALSE
AND MATCH_END_DATE >= current_date - 91
GROUP BY ALL

Add Comment

Please, Sign In to add comment