Untitled

YuvalGai

Apr 23rd, 2025

675

Never

Add comment

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

SQL 2.47 KB | None | 0 0

raw download clone embed print report

SELECT
MATCH_LOP_EVENT_ID calendar_entry_id
,MATCH_LOP_EVENT_TYPE lo_event_id
,is_random_mixer
,CASE
WHEN MATCH_EVENT_TYPE_ID = 2 THEN 'Classic'
WHEN MATCH_EVENT_TYPE_ID = 3 THEN 'Challenge'
WHEN MATCH_EVENT_TYPE_ID = 4 THEN 'Tournament'
WHEN MATCH_LOP_EVENT_ID LIKE '%boosters-clash%' OR MATCH_LOP_EVENT_ID LIKE '%booster-clash%' THEN 'BoostersClash'
WHEN MATCH_LOP_EVENT_ID LIKE '%mutation%' AND PVP_MODE = 'Solo' THEN 'SoloMutationLab'
WHEN MATCH_EVENT_TYPE_ID = 5 AND (MATCH_MODIFIER_1 = 'win-by-knockout' OR MATCH_MODIFIER_2 = 'win-by-knockout' OR match_modifier_3 = 'win-by-knockout') AND is_random_mixer = TRUE THEN 'Random Knockout'
WHEN MATCH_EVENT_TYPE_ID = 5 AND (MATCH_MODIFIER_1 = 'win-by-knockout' OR MATCH_MODIFIER_2 = 'win-by-knockout' OR match_modifier_3 = 'win-by-knockout') THEN 'Knockout'
WHEN MATCH_EVENT_TYPE_ID = 5 AND (MATCH_MODIFIER_1 = 'rumble-duo-modifier' OR MATCH_MODIFIER_2 = 'rumble-duo-modifier' OR match_modifier_3 = 'rumble-duo-modifier' OR LOWER(rumble_type) = 'showdown') AND is_random_mixer = TRUE THEN 'Random Showdown'
WHEN MATCH_EVENT_TYPE_ID = 5 AND (MATCH_MODIFIER_1 = 'rumble-duo-modifier' OR MATCH_MODIFIER_2 = 'rumble-duo-modifier' OR match_modifier_3 = 'rumble-duo-modifier' OR LOWER(rumble_type) = 'showdown') THEN 'Showdown'
WHEN LOWER(rumble_type) = 'party' THEN 'Rumble Party'
WHEN MATCH_EVENT_TYPE_ID = 5 AND is_random_mixer = TRUE THEN 'Random Mixer'
WHEN MATCH_EVENT_TYPE_ID = 5 THEN 'Daily'
WHEN pvp_mode = 'Rumble' OR LOWER(rumble_type) = 'classic' THEN 'Rumble'
WHEN MATCH_LOP_EVENT_ID LIKE '%onboarding%' THEN 'Onboarding'
WHEN PVP_MODE = 'Solo' THEN 'Solo'
WHEN MATCH_LOP_EVENT_ID LIKE '%adventure%' THEN 'Adventure'
ELSE 'Other'
END AS MATCH_type
,COUNT(DISTINCT nvl(match_mode_type,'Null')) modes
, COUNT(*) events
, SUM(CASE WHEN match_mode_type IS NULL THEN 1 ELSE 0 END) null_cases
FROM SNOWPLOW.DERIVED.USER_MATCH_TOTAL WHERE match_end_date >= CURRENT_DATE - 30 AND is_random_mixer = FALSE AND (is_developer = FALSE OR IS_DEVELOPER IS NULL) GROUP BY ALL HAVING modes>1 ORDER BY modes DESC

Add Comment

Please, Sign In to add comment