CREATE OR REPLACE VIEW SNOWPLOW.DERIVED."v_INTERVAL_SESSION_INVENTORY_SUB_TYPE"(
    USER_ID,
    INTERVAL_DATE_TIME,
    LAST_SESSION_ID,
    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,
    START_SESSION_RESOURCE_COUNT
) AS

SELECT user_id
        , interval_date_time
        , last_session_id
        , Resource_Type
        , Resource_Type_id
        -- , IFNULL(case when Resource_Name_Id = 'AlbumPage' then 'AlbumPage'
        --          when Resource_Type = 'Sticker' then concat(Resource_Status,Resource_Type)
        --          when Resource_Type = 'OnFire' then Resource_Name_Id
        --          when Resource_Type = 'Ticket' then Resource_Name
        --          else Resource_Type end,'None') as Resource_Type
        ,IFNULL(CASE WHEN Resource_Type IN ('Booster', 'Perk') THEN Resource_Sub_Type ELSE Resource_Name_Id END,'None') AS Resource_Sub_Type
        , Resource_Sub_Type_Id

        -- ,IFNULL(case when Resource_Type in ('Booster', 'Perk') then Resource_Sub_Type
        --                  when Resource_Sub_Type = 'Sticker' then concat(Resource_Status,Resource_Sub_Type)
        --                  else Resource_Name_Id end,'None') as Resource_Sub_Type

        -- ,IFNULL(case when Resource_Name_Id = 'AlbumPage' then 'AlbumPage'
        --                  when Resource_Sub_Type = 'Sticker' then concat(Resource_Status,Resource_Sub_Type)
        --                  when Resource_Sub_Type = 'OnFire' then Resource_Name_Id
        --                  when Resource_Sub_Type = 'Ticket' then Resource_Name_Id
        --                  else Resource_Sub_Type end,'None') as Resource_Sub_Type

        , 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
--sum(Start_Session_Resource_Count) 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(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
    ,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,R.Resource_Type) 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,13,14,15 ;