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,
    LTV_GROUP_30D,
	SENIORITY,
	SENIORITY_BIN,
	ENGAGEMENT_GROUP,
	IS_PAYER,
	ARENA_INDEX,
	ARENA_GROUP,
    payers_segment
    trophy_group_2,
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.LTV_GROUP_30D
    ,S.Seniority
    ,S.Seniority_Bin
    ,S.Engagement_Group
    ,S.Is_Payer
    ,S.Arena_Index
    ,S.Arena_Group
    ,Daily.payers_segment
,S.trophy_group_2
    ,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 candivore.prod.daily_users_from_params Daily ON I.USER_ID = Daily.user_id and S.Interval_Date_Time = Daily.interval_Date
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' ;