Untitled

YuvalGai

Jul 12th, 2023

156

Never

Add comment

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

text 9.34 KB | None | 0 0

raw download clone embed print report

SELECT \\
ND.Interval_Date \\
,ND.Interval_Date_Time \\
,ND.LTV_Group \\
,ND.Seniority_Bin \\
,ND.Arena_Group
,ND.ltv_group_30d
,ND.trophy_group_2
,S.INTERVAL_SESSIONS \\
,COUNT(DISTINCT ND.User_Id) as DAU \\
FROM ( \\
SELECT \\
TO_DATE(Next_Interval) as Interval_Date \\
,TIMESTAMP_FROM_PARTS(YEAR(Next_Interval),MONTH(Next_Interval),DAY(Next_Interval),DATE_PART(''HOUR'',Next_Interval),0,0,0) as Interval_Date_Time \\
,LTV_GROUP \\
,SENIORITY_BIN \\
,CASE WHEN CURRENT_ARENA_INDEX <=13 THEN ''Studios'' \\
WHEN CURRENT_ARENA_INDEX <=22 THEN ''Master League'' \\
ELSE ''Legends League'' \\
END as Arena_Group \\
,USER_ID
,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
,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.TMP_NEXT_INTERVAL_DAU_SESSION_STARTS B \\
ON A.DERIVED_TSTAMP <= Next_Interval AND DERIVED_TSTAMP >= DATEADD(''HOUR'',-24,Next_Interval) \\
WHERE B.Next_Interval < GETDATE() \\
) ND \\
LEFT JOIN ( \\
SELECT DISTINCT Interval_Date_Time \\
FROM SNOWPLOW.DERIVED.INTERVAL_DAU_SESSION_STARTS \\
) OldData ON ND.Interval_Date_Time = OldData.Interval_Date_Time \\
INNER JOIN ( \\
SELECT \\
TO_DATE(Next_Interval) as Interval_Date \\
,TIMESTAMP_FROM_PARTS(YEAR(Next_Interval),MONTH(Next_Interval),DAY(Next_Interval),DATE_PART(''HOUR'',Next_Interval),0,0,0) as Interval_Date_Time \\
,LTV_GROUP \\
,SENIORITY_BIN \\
,CASE WHEN CURRENT_ARENA_INDEX <=13 THEN ''Studios'' \\
WHEN CURRENT_ARENA_INDEX <=22 THEN ''Master League'' \\
ELSE ''Legends League'' \\
END as Arena_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
,COUNT (*) as INTERVAL_SESSIONS \\
FROM SNOWPLOW.DERIVED.USER_SESSION_START A \\
INNER JOIN SNOWPLOW.DERIVED.TMP_NEXT_INTERVAL_DAU_SESSION_STARTS B \\
ON A.DERIVED_TSTAMP <= Next_Interval AND DERIVED_TSTAMP >= DATEADD(''HOUR'',-24,Next_Interval) \\
WHERE B.Next_Interval < GETDATE() \\
GROUP BY 1,2,3,4,5,6,7 \\
) S ON ND.Interval_Date_Time = S.Interval_Date_Time AND ND.LTV_Group = S.LTV_Group AND ND.Seniority_Bin = S.Seniority_Bin AND ND.Arena_Group = S.Arena_Group AND ND.trophy_group_2 = S.trophy_group_2 AND ND.ltv_group_30d = S.ltv_group_30d \\
WHERE Last_Daily_Session = 1 \\
AND OldData.Interval_Date_Time IS NULL \\
GROUP BY 1,2,3,4,5,6,7,8;

Add Comment

Please, Sign In to add comment