Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE VIEW SNOWPLOW.DERIVED."v_INTERVAL_SESSION_INVENTORY"(
- USER_ID,
- INTERVAL_DATE_TIME,
- LAST_SESSION_ID,
- RESOURCE_TYPE_ID,
- RESOURCE_TYPE,
- RESOURCE_SUB_TYPE_ID,
- RESOURCE_SUB_TYPE,
- RESOURCE_ID,
- RESOURCE_NAME_ID,
- RESOURCE_DISPLAY_NAME,
- BOOSTER_TIER_GROUP_ID,
- BOOSTER_TIER_GROUP,
- RESOURCE_STATUS,
- START_SESSION_RESOURCE_COUNT,
- LTV_GROUP,
- SENIORITY,
- SENIORITY_BIN,
- ENGAGEMENT_GROUP,
- IS_PAYER,
- ARENA_INDEX,
- ARENA_GROUP,
- trophy_group
- ) AS
- 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(CASE WHEN R.Resource_Type IN ('Booster', 'Perk') THEN R.Resource_Sub_Type ELSE I.Resource_Name END,'None') AS Resource_Sub_Type
- -- ,IFNULL(case when R.Resource_Type in ('Booster', 'Perk') then R.Resource_Sub_Type
- -- when R.Resource_Sub_Type = 'Sticker' then concat(I.Resource_Status,R.Resource_Sub_Type)
- -- else I.Resource_Name end,'None') as Resource_Sub_Type
- -- ,IFNULL(case when I.Resource_Name = 'AlbumPage' then 'AlbumPage'
- -- when R.Resource_Sub_Type = 'Sticker' then concat(I.Resource_Status,R.Resource_Sub_Type)
- -- when R.Resource_Sub_Type = 'OnFire' then I.Resource_Name
- -- when R.Resource_Type = 'Ticket' then I.Resource_Name else R.Resource_Sub_Type end,'None') as Resource_Sub_Type
- -- ,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
- ,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
- ,CASE WHEN I.Resource_Name = 'OnFireTime' THEN I.Start_Session_Resource_CNT/3600 ELSE I.Start_Session_Resource_CNT END AS Start_Session_Resource_Count
- -- 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)
- INNER 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
- ,resource_UI_order
- ,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 R.Resource_Type != 'None'
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
