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,
- LTV_Group_30D,
- SENIORITY_BIN,
- ENGAGEMENT_GROUP,
- ARENA_GROUP,
- payers_segment,
- TROPHY_GROUP,
- trophy_group_2,
- 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
- ,payers_segment
- ,Trophy_Group
- ,trophy_group_2
- ,LTV_Group_30D
- ,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.LTV_Group_30D
- -- ,S.Seniority
- ,S.Seniority_Bin
- ,S.Engagement_Group
- -- ,S.Is_Payer
- -- ,S.Arena_Index,
- ,S.Arena_Group
- ,Daily.payers_segment
- ,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
- , S.trophy_group_2
- 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 candivore.prod.daily_users_from_params Daily ON I.USER_ID = Daily.user_id and S.Interval_Date_Time = Daily.interval_Date
- 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 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,12 ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
