API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Jun 20th, 2023 (edited)
185
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.89 KB | None | 0 0
raw download clone embed print report
  1. select
  2. create or replace view SNOWPLOW.DERIVED."v_INTERVAL_SESSION_INVENTORY_SUB_TYPE"(
  3. USER_ID,
  4. INTERVAL_DATE_TIME,
  5. LAST_SESSION_ID,
  6. RESOURCE_TYPE,
  7. RESOURCE_TYPE_ID,
  8. RESOURCE_SUB_TYPE,
  9. RESOURCE_SUB_TYPE_ID,
  10. BOOSTER_TIER_GROUP,
  11. BOOSTER_TIER_GROUP_ID,
  12. RESOURCE_STATUS,
  13. LTV_GROUP,
  14. LTV_GROUP_30D,
  15. SENIORITY_BIN,
  16. ENGAGEMENT_GROUP,
  17. ARENA_GROUP,
  18. payers_segment,
  19. TROPHY_GROUP,
  20. trophy_group_2,
  21. START_SESSION_RESOURCE_COUNT
  22. ) as
  23.  
  24. select user_id
  25. , interval_date_time
  26. , last_session_id
  27. , Resource_Type
  28. , Resource_Type_id
  29. -- , IFNULL(case when Resource_Name_Id = 'AlbumPage' then 'AlbumPage'
  30. -- when Resource_Type = 'Sticker' then concat(Resource_Status,Resource_Type)
  31. -- when Resource_Type = 'OnFire' then Resource_Name_Id
  32. -- when Resource_Type = 'Ticket' then Resource_Name
  33. -- else Resource_Type end,'None') as Resource_Type
  34. ,IFNULL(case when Resource_Type in ('Booster', 'Perk') then Resource_Sub_Type else Resource_Name_Id end,'None') as Resource_Sub_Type
  35. , Resource_Sub_Type_Id
  36.  
  37. -- ,IFNULL(case when Resource_Type in ('Booster', 'Perk') then Resource_Sub_Type
  38. -- when Resource_Sub_Type = 'Sticker' then concat(Resource_Status,Resource_Sub_Type)
  39. -- else Resource_Name_Id end,'None') as Resource_Sub_Type
  40.  
  41. -- ,IFNULL(case when Resource_Name_Id = 'AlbumPage' then 'AlbumPage'
  42. -- when Resource_Sub_Type = 'Sticker' then concat(Resource_Status,Resource_Sub_Type)
  43. -- when Resource_Sub_Type = 'OnFire' then Resource_Name_Id
  44. -- when Resource_Sub_Type = 'Ticket' then Resource_Name_Id
  45. -- else Resource_Sub_Type end,'None') as Resource_Sub_Type
  46.  
  47. , Booster_Tier_Group
  48. , Booster_Tier_Group_Id
  49. , Resource_Status
  50. , LTV_Group
  51. , LTV_Group_30D
  52. , Seniority_Bin
  53. , Engagement_Group
  54. , Arena_Group
  55. , payers_segment
  56. , trophy_group
  57. , trophy_group_2
  58. , sum(case when Resource_Name_Id = 'OnFireTime' then Start_Session_Resource_Count/3600 else Start_Session_Resource_Count end) as Start_Session_Resource_Count
  59. --sum(Start_Session_Resource_Count) as Start_Session_Resource_Count
  60. from (select
  61. I.User_Id
  62. ,S.Interval_Date_Time
  63. ,S.Last_Session_Id
  64. ,IFNULL(R.Resource_Type_Id,-1) as Resource_Type_Id
  65. ,IFNULL(R.Resource_Type,'None') as Resource_Type
  66. ,IFNULL(R.Resource_Sub_Type_Id,-1) as Resource_Sub_Type_Id
  67. ,IFNULL(R.Resource_Sub_Type,'None') as Resource_Sub_Type
  68. -- ,IFNULL(R.Resource_Id,-1) as Resource_Id
  69. ,I.Resource_Name as Resource_Name_Id
  70. -- ,IFNULL(R.Resource_Display_Name,I.Resource_Name) as Resource_Display_Name
  71. , I.Resource_Name
  72. ,IFNULL(R.Booster_Tier_Group,-1) as Booster_Tier_Group_Id
  73. ,IFNULL(R.Booster_Tier_Group_Name,'None') as Booster_Tier_Group
  74. ,INITCAP(I.Resource_Status) as Resource_Status
  75. ,I.Start_Session_Resource_CNT as Start_Session_Resource_Count
  76. ,S.LTV_Group
  77. ,S.LTV_Group_30D
  78. -- ,S.Seniority
  79. ,S.Seniority_Bin
  80. ,S.Engagement_Group
  81. -- ,S.Is_Payer
  82. -- ,S.Arena_Index,
  83. ,S.Arena_Group
  84. ,Daily.payers_segment
  85. ,CASE
  86. WHEN S.arena_index <=1 THEN '0-125'
  87. WHEN S.arena_index <=6 THEN '125-1400'
  88. WHEN S.arena_index <=8 THEN '1400-2000'
  89. WHEN S.arena_index <=13 THEN '2000-3800'
  90. WHEN S.arena_index <=16 THEN '3800-7000'
  91. WHEN S.arena_index <=19 THEN '7000-16000'
  92. WHEN S.arena_index <=22 THEN '16000-30000'
  93. WHEN S.arena_index >=23 THEN '30000+'
  94. end trophy_group
  95. , S.trophy_group_2
  96. FROM SNOWPLOW.DERIVED.USER_SESSION_INVENTORY I
  97. INNER JOIN SNOWPLOW.DERIVED.INTERVAL_LAST_SESSION_PER_USER S
  98. ON (S.USER_ID = I.USER_ID AND I.SESSION_ID = S.Last_Session_Id )
  99. LEFT JOIN candivore.prod.daily_users_from_params Daily ON I.USER_ID = Daily.user_id and S.Interval_Date_Time = Daily.interval_Date
  100. LEFT JOIN (
  101. SELECT
  102. Resource_Id
  103. ,Resource_Name
  104. ,Resource_Display_Name
  105. ,Resource_Type_Id
  106. ,Resource_Type
  107. ,Resource_Sub_Type_Id
  108. ,Resource_Sub_Type
  109. ,Booster_Tier_Group
  110. ,Booster_Tier_Group_Name
  111. ,ROW_NUMBER() OVER (PARTITION BY Resource_Name,RESOURCE_TYPE ORDER BY Resource_Version DESC) as RN
  112. FROM SNOWPLOW.DERIVED.DIM_RESOURCE_VERSION
  113. WHERE (resource_UI_order > -1 or resource_UI_order is null)
  114. ) 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')))
  115. WHERE case when R.Resource_Type != 'OnFire' then Start_Session_Resource_Count > 0 else Start_Session_Resource_Count>=0 end
  116. and Resource_Name_Id not in ('Nuke','TapTime') and R.Resource_Type != 'None')
  117. group by 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!