Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE VIEW SNOWPLOW.DERIVED."v_INTERVAL_SESSION_INVENTORY_TYPE"(
- USER_ID,
- INTERVAL_DATE_TIME,
- LAST_SESSION_ID,
- RESOURCE_TYPE,
- RESOURCE_TYPE_ID,
- RESOURCE_STATUS,
- LTV_GROUP,
- SENIORITY_BIN,
- ENGAGEMENT_GROUP,
- ARENA_GROUP,
- TROPHY_GROUP,
- START_SESSION_RESOURCE_COUNT
- ) AS
- SELECT user_id
- , interval_date_time
- , last_session_id
- , Resource_Type
- -- , case when Resource_Type = 'OnFire' then Resource_Name_Id else Resource_Type end as Resource_Type
- , Resource_Type_Id
- -- , Resource_Sub_Type
- -- , Resource_Sub_Type_Id
- -- , Booster_Tier_Group
- -- , Booster_Tier_Group_Id
- , Resource_Status
- , LTV_Group
- ,Seniority_Bin
- ,Engagement_Group
- ,Arena_Group
- ,Trophy_Group
- ,SUM(CASE WHEN Resource_Name_Id = 'OnFireTime' THEN Start_Session_Resource_Count/3600 ELSE Start_Session_Resource_Count END) AS Start_Session_Resource_Count
- FROM (SELECT
- I.User_Id
- ,S.Interval_Date_Time
- ,S.Last_Session_Id
- ,IFNULL(R.Resource_Type_Id,-1) AS Resource_Type_Id
- ,IFNULL(R.Resource_Type,'None') AS Resource_Type
- -- ,IFNULL(case when I.Resource_Name = 'AlbumPage' then 'AlbumPage'
- -- when R.Resource_Type = 'Sticker' then concat(I.Resource_Status,R.Resource_Type)
- -- when R.Resource_Type = 'OnFire' then I.Resource_Name
- -- when R.Resource_Type = 'Ticket' then I.Resource_Name
- -- else R.Resource_Type end,'None') as Resource_Type
- -- ,IFNULL(R.Resource_Sub_Type_Id,-1) as Resource_Sub_Type_Id
- -- ,IFNULL(R.Resource_Sub_Type,'None') as Resource_Sub_Type
- -- ,IFNULL(R.Resource_Id,-1) as Resource_Id
- ,I.Resource_Name AS Resource_Name_Id
- -- ,IFNULL(R.Resource_Display_Name,I.Resource_Name) as Resource_Display_Name
- -- , I.Resource_Name
- -- , R.Resource_Name
- -- ,IFNULL(R.Booster_Tier_Group,-1) as Booster_Tier_Group_Id
- -- ,IFNULL(R.Booster_Tier_Group_Name,'None') as Booster_Tier_Group
- ,INITCAP(I.Resource_Status) AS Resource_Status
- ,I.Start_Session_Resource_CNT AS Start_Session_Resource_Count
- ,S.LTV_Group
- -- ,S.Seniority
- ,S.Seniority_Bin
- ,S.Engagement_Group
- -- ,S.Is_Payer
- -- ,S.Arena_Index,
- ,S.Arena_Group
- ,CASE
- WHEN S.arena_index <=1 THEN '0-125'
- WHEN S.arena_index <=6 THEN '125-1400'
- WHEN S.arena_index <=8 THEN '1400-2000'
- WHEN S.arena_index <=13 THEN '2000-3800'
- WHEN S.arena_index <=16 THEN '3800-7000'
- WHEN S.arena_index <=19 THEN '7000-16000'
- WHEN S.arena_index <=22 THEN '16000-30000'
- WHEN S.arena_index >=23 THEN '30000+'
- END trophy_group
- FROM SNOWPLOW.DERIVED.USER_SESSION_INVENTORY I
- INNER JOIN SNOWPLOW.DERIVED.INTERVAL_LAST_SESSION_PER_USER S
- ON (S.USER_ID = I.USER_ID AND I.SESSION_ID = S.Last_Session_Id )
- LEFT JOIN (
- SELECT
- Resource_Id
- ,Resource_Name
- ,Resource_Display_Name
- ,Resource_Type_Id
- ,Resource_Type
- ,Resource_Sub_Type_Id
- ,Resource_Sub_Type
- ,Booster_Tier_Group
- ,Booster_Tier_Group_Name
- ,ROW_NUMBER() OVER (PARTITION BY Resource_Name, Resource_Type ORDER BY Resource_Version DESC) AS RN
- FROM SNOWPLOW.DERIVED.DIM_RESOURCE_VERSION
- WHERE (resource_UI_order > -1 OR resource_UI_order IS NULL)
- ) R ON (LOWER(I.Resource_Name) = LOWER(R.Resource_Name) AND R.RN = 1 AND LOWER(ifnull(I.RESOURCE_TYPE,'n/a')) = LOWER(ifnull(R.RESOURCE_TYPE,'n/a')))
- WHERE CASE WHEN R.Resource_Type != 'OnFire' THEN Start_Session_Resource_Count > 0 ELSE Start_Session_Resource_Count>=0 END AND Resource_Name_Id NOT IN ('Nuke','TapTime')
- AND R.Resource_Type != 'None' )
- GROUP BY 1,2,3,4,5,6,7,8,9,10,11 ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
