Untitled

YuvalGai

Mar 23rd, 2023 (edited)

151

Never

Add comment

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

SQL 4.78 KB | None | 0 0

raw download clone embed print report

CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.T_BOOSTER_MODE_DASHBOARD AS
SELECT
A.*,
B.lo_event_id,
B.lo_event_min_trophies,
B.lo_event_max_trophies,
B.calendar_order,
B.lo_visible_on_homescreen,
B.lo_entry_tickets_required
FROM
(
SELECT
DISTINCT match_end_date AS INTERVAL_DATE,
arena_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 < 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,
ltv_group,
NULL AS engagement_group,
is_payer,
NULL AS is_subscriber,
NULL AS SUBSCRIPTION_TIER,
NULL AS 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,
calendar_entry_id,
concat(
MATCH_SUB_TYPE,
''-'',
MATCH_MODE,
''+'',
MODIFIER_1,
''-'',
MODIFIER_2
) 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,
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_won) AS wins,
AVG(MATCH_RANK) AS AVG_MATCH_RANK
FROM
CANDIVORE.PROD.F_USER_MATCH
WHERE
is_bot = FALSE
AND MATCH_END_DATE > DATEADD(DAY, -61, 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,
23,
24
) A
LEFT JOIN (
SELECT
A.LO_ENTRY_ID,
B.lo_event_id,
A.interval_date,
A.lo_entry_name,
A.calendar_order,
A.lo_visible_on_homescreen,
CASE WHEN min0 IS NULL THEN min1 ELSE min0 END AS lo_event_min_trophies,
CASE WHEN max0 IS NULL THEN max1 ELSE max0 END AS lo_event_max_trophies,
lo_entry_tickets_required
FROM
(
(
SELECT
LO_ENTRY_ID,
DATE(derived_tstamp) interval_date,
LO_ENTRY_NAME,
calendar_order,
lo_visible_on_homescreen,
lo_entry_min_trophies min0,
lo_entry_max_trophies max0,
lo_entry_tickets_required
FROM
(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 CANDIVORE.PROD.F_LIVEOPS_CALENDAR) WHERE rn = 1)
) A
LEFT JOIN (
SELECT
DATE(derived_tstamp) AS interval_date,
lo_event_id,
lo_event_min_trophies AS min1,
lo_event_max_trophies AS max1
FROM
CANDIVORE.PROD.DIM_EVENT_CONFIG
) B ON A.LO_ENTRY_NAME = B.LO_EVENT_ID
)
) B ON A.calendar_entry_id = B.lo_entry_id

Add Comment

Please, Sign In to add comment