Untitled

YuvalGai

Jun 27th, 2023

155

Never

Add comment

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

text 5.96 KB | None | 0 0

raw download clone embed print report

CREATE OR REPLACE PROCEDURE CANDIVORE.SEMANTIC_LAYER.T_PERKS_DASHBOARD()
RETURNS VARCHAR(16777216)
LANGUAGE JAVASCRIPT
EXECUTE AS CALLER
AS '
// Insert segmented data into T_PERKS_DASHBOARD table
var my_sql_command = `
CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.T_PERKS_DASHBOARD AS
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
, 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 < 30000 THEN ''16000-30000''
WHEN trophies_cnt >= 30000 THEN ''30000+''
END AS trophy_group_2
, 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
, 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
union all
(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+''
else Null
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 < 30000 THEN ''16000-30000''
WHEN trophies_cnt >= 30000 THEN ''30000+''
END AS trophy_group_2
, 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
, 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)
`
var statement = snowflake.createStatement({sqlText: my_sql_command});
statement.execute();
';

Add Comment

Please, Sign In to add comment