CREATE OR REPLACE PROCEDURE SNOWPLOW.DERIVED.SP_POPULATE_INTERVAL_LAST_SESSION_P

CREATE OR REPLACE PROCEDURE SNOWPLOW.DERIVED.SP_POPULATE_INTERVAL_LAST_SESSION_PER_USER()

RETURNS VARCHAR(16777216)

LANGUAGE JAVASCRIPT

EXECUTE AS OWNER

AS '

var my_sql_command0 = "BEGIN TRANSACTION;"

var statement0 = snowflake.createStatement({sqlText: my_sql_command0});

statement0.execute();

var my_sql_command1 = "CREATE OR REPLACE TABLE SNOWPLOW.DERIVED.INTERVAL_DATE_SESSION_STARTS AS \\

SELECT DATEADD(''HOUR'',6,MAX(Interval_Date_Time)) as Next_Interval \\

FROM SNOWPLOW.DERIVED.INTERVAL_LAST_SESSION_PER_USER;"

var statement1 = snowflake.createStatement({sqlText: my_sql_command1});

statement1.execute();

var my_sql_command2 = "INSERT INTO SNOWPLOW.DERIVED.INTERVAL_LAST_SESSION_PER_USER \\

SELECT \\

NewData.USER_ID AS User_Id \\

,NewData.Interval_Date_Time \\

,NewData.Session_Id as Last_Session_Id \\

,NewData.LTV_GROUP as LTV_Group \\

,NewData.ltv_group_30d as ltv_group_30d \\

,NewData.SENIORITY as Seniority \\

,NewData.SENIORITY_BIN as Seniority_Bin \\

,NewData.ENGAGEMENT_GROUP as Engagement_Group \\

,NewData.IS_PAYER as Is_Payer \\

,NewData.CURRENT_ARENA_INDEX as Arena_Index \\

,NewData.Arena_Group

,NewData.trophy_group_2 \\

FROM ( \\

SELECT \\

USER_ID \\

,TIMESTAMP_FROM_PARTS(YEAR(Next_Interval),MONTH(Next_Interval),DAY(Next_Interval),DATE_PART(''HOUR'',Next_Interval),0,0,0) as Interval_Date_Time \\

,SESSION_ID \\

,LTV_Group \\

,case

when iap_30d = 0 or iap_30d is null then ''0''

when iap_30d <= 1 then ''Low (0-1)''

when iap_30d <= 2 then ''Med (1-3)''

when iap_30d <= 5 then ''High (3-8)''

when iap_30d <= 15 then ''Very High (8-15)''

when iap_30d > 15 then ''VIP (15+)''

end ltv_group_30d

, 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 \\

,SENIORITY \\

,SENIORITY_BIN \\

,ENGAGEMENT_GROUP \\

,IS_PAYER \\

,CURRENT_ARENA_INDEX \\

,CASE WHEN CURRENT_ARENA_INDEX <=13 THEN ''Studios'' \\

WHEN CURRENT_ARENA_INDEX <=22 THEN ''Master League'' \\

ELSE ''Legends League'' END as Arena_Group \\

,ROW_NUMBER() OVER (PARTITION BY USER_ID ORDER BY DERIVED_TSTAMP DESC) as Last_Daily_Session \\

FROM SNOWPLOW.DERIVED.USER_SESSION_START A \\

INNER JOIN SNOWPLOW.DERIVED.INTERVAL_DATE_SESSION_STARTS B \\

ON A.DERIVED_TSTAMP <= Next_Interval AND DERIVED_TSTAMP >= DATEADD(''HOUR'',-24,Next_Interval) \\

WHERE B.Next_Interval < GETDATE() \\

) NewData \\

LEFT JOIN ( \\

SELECT DISTINCT Interval_Date_Time \\

FROM SNOWPLOW.DERIVED.INTERVAL_LAST_SESSION_PER_USER \\

) OldData ON NewData.Interval_Date_Time = OldData.Interval_Date_Time \\

WHERE Last_Daily_Session = 1 \\

AND OldData.Interval_Date_Time IS NULL;"

var statement2 = snowflake.createStatement({sqlText: my_sql_command2});

statement2.execute();

var my_sql_command3 = "DROP TABLE IF EXISTS SNOWPLOW.DERIVED.INTERVAL_DATE_SESSION_STARTS;";

var statement3 = snowflake.createStatement({sqlText: my_sql_command3});

statement3.execute();

var my_sql_command4 = "COMMIT;";

var statement4 = snowflake.createStatement({sqlText: my_sql_command4});

statement4.execute();

';

Untitled