API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Jun 27th, 2023 (edited)
175
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 23.27 KB | None | 0 0
raw download clone embed print report
  1. SELECT
  2. A.*
  3. , G.match_type2
  4. , G.match_type_id
  5. , G.match_mode
  6. , G.modifier_1
  7. , G.modifier_2
  8. , G.is_random_mixer
  9. , G.rounds_cnt
  10. , G.total_matches
  11. , G.unique_users_from_match_end
  12. , G.event_start_date
  13. , G.event_start_ts
  14. , G.total_seconds_played_in_segment
  15. , G.total_booster_activations_in_segment
  16. , G.total_match_score_in_segment
  17. , G.games_with_legendary_boosters
  18. , G.games_with_SE_boosters
  19. , G.diamond_losses
  20. , G.diamond3_losses
  21. , G.legendary_losses
  22. , G.SE_losses
  23. , G.total_losses
  24. , G.total_wins
  25. , G.legendary_wins
  26. , G.SE_wins
  27. , G.min_time
  28. , G.max_time
  29. , G.unique_users
  30. --, G.unique_users_per_solo_prize
  31. , G.unique_users_with_LESE_boosters
  32. , G.total_coin_value
  33. , G.total_match_ids
  34. , G.rumble_size
  35. , G.tournament_size
  36. , G.total_bots
  37. , G.total_on_fire
  38. , G.conceded_matches
  39. , G.total_perk_activations
  40. --, G.max_max_prize_eligibility
  41. , D.points_earned LE_points_earned
  42. , D.value_earned LE_value_earned
  43. , C.lo_event_id
  44. , C.lo_event_min_trophies
  45. , C.lo_event_max_trophies
  46. , C.calendar_order
  47. , C.hours
  48. , C.lo_entry_tickets_required
  49. , C.lo_visible_on_homescreen
  50. , C.lo_entry_min_booster_rarity
  51. , C.lo_entry_max_booster_rarity
  52. , C.lo_event_solo_moves_per_turn
  53. , C.lo_event_turn_amt elimination_round
  54. , C.lo_event_turn_duration_sec
  55. , C.lo_entry_is_private
  56. , C.lo_event_wins_limit
  57. , C.lo_is_friendly
  58. FROM (SELECT
  59. DATE(derived_tstamp) interval_date
  60. , CASE
  61. WHEN trophies_cnt < 800 THEN '800'
  62. WHEN CURRENT_ARENA_INDEX<=13 THEN 'Studios'
  63. WHEN CURRENT_ARENA_INDEX<=22 THEN 'Master_League'
  64. WHEN CURRENT_ARENA_INDEX>=23 THEN 'Legends_League'
  65. END arena_group
  66. , CASE
  67. WHEN trophies_cnt < 125 THEN '0-125'
  68. WHEN trophies_cnt < 1400 THEN '125-1400'
  69. WHEN trophies_cnt < 2000 THEN '1400-2000'
  70. WHEN trophies_cnt < 3800 THEN '2000-3800'
  71. WHEN trophies_cnt < 7000 THEN '3800-7000'
  72. WHEN trophies_cnt < 16000 THEN '7000-16000'
  73. WHEN trophies_cnt < 30000 THEN '16000-30000'
  74. WHEN trophies_cnt >= 30000 THEN '30000+'
  75. END AS trophy_group
  76. , CASE
  77. WHEN trophies_cnt < 125 THEN '0-125'
  78. WHEN trophies_cnt < 1400 THEN '125-1400'
  79. WHEN trophies_cnt < 2000 THEN '1400-2000'
  80. WHEN trophies_cnt < 3800 THEN '2000-3800'
  81. WHEN trophies_cnt < 4500 THEN '3800-4500'
  82. WHEN trophies_cnt < 5500 THEN '4500-5500'
  83. WHEN trophies_cnt < 7000 THEN '5500-7000'
  84. WHEN trophies_cnt < 10000 THEN '7000-10000'
  85. WHEN trophies_cnt < 13000 THEN '10000-13000'
  86. WHEN trophies_cnt < 16000 THEN '13000-16000'
  87. WHEN trophies_cnt < 20000 THEN '16000-20000'
  88. WHEN trophies_cnt < 30000 THEN '20000-30000'
  89. WHEN trophies_cnt >= 30000 THEN '30000+'
  90. END AS trophy_group_2
  91. , LTV_group
  92. , ifnull(
  93. CASE
  94. WHEN lo_entry_id = '' THEN NULL
  95. ELSE lo_entry_id
  96. END
  97. , 'NA') as lo_entry_id
  98. , lo_event_type
  99. , lo_event_config_id
  100. , CASE
  101. WHEN lo_event_id LIKE '%mutation%' THEN 'solo_mutation'
  102. WHEN lo_event_id LIKE 'Solo%' OR lo_event_id LIKE 'solo%' THEN 'solo'
  103. WHEN lo_event_type = 'Rumble' THEN 'rumble'
  104. WHEN lo_event_type = 'AdventureMode' THEN 'adventure'
  105. WHEN lo_event_type LIKE 'Leaderboard%' THEN 'leaderboard'
  106. WHEN lo_event_type LIKE 'BoostersClash' THEN 'boosters_clash'
  107. WHEN match_type_id = 4 OR transaction_source LIKE 'tournament%' THEN 'tournament'
  108. WHEN match_type_id = 5 THEN 'daily'
  109. WHEN match_type_id = 2 THEN 'classic'
  110. ELSE 'other'
  111. END AS match_type
  112. , COUNT(DISTINCT match_id) transaction_matches
  113. , COUNT(DISTINCT user_id) AS unique_users_from_transaction
  114. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_received_resource = 0 THEN resource_cnt END) AS legendary_boosters_spent
  115. , SUM(CASE WHEN resource_sub_type = 'Special Edition' AND is_received_resource = 0 THEN resource_cnt END) AS se_boosters_spent
  116. , SUM(CASE WHEN resource_sub_type = 'Diamond 1' AND is_received_resource = 0 THEN resource_cnt END) AS diamond1_boosters_spent
  117. , SUM(CASE WHEN resource_sub_type = 'Diamond 2' AND is_received_resource = 0 THEN resource_cnt END) AS diamond2_boosters_spent
  118. , SUM(CASE WHEN resource_sub_type = 'Diamond 3' AND is_received_resource = 0 THEN resource_cnt END) AS diamond3_boosters_spent
  119. , SUM(CASE WHEN resource_sub_type = 'Gold' and resource_type = 'Booster' AND is_received_resource = 0 THEN resource_cnt END) AS gold_boosters_spent
  120. , SUM(CASE WHEN resource_sub_type = 'Silver' AND is_received_resource = 0 THEN resource_cnt END) AS silver_boosters_spent
  121. , SUM(CASE WHEN resource_sub_type = 'Bronze' AND is_received_resource = 0 THEN resource_cnt END) AS bronze_boosters_spent
  122. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_received_resource = resource_cnt THEN 1 END) AS legendary_boosters_earned
  123. , SUM(CASE WHEN resource_sub_type = 'Special Edition' AND is_received_resource = resource_cnt THEN 1 END) AS se_boosters_earned
  124. , SUM(CASE WHEN resource_sub_type = 'Diamond 1' AND is_received_resource = 1 THEN resource_cnt END) AS diamond1_boosters_earned
  125. , SUM(CASE WHEN resource_sub_type = 'Diamond 2' AND is_received_resource = 1 THEN resource_cnt END) AS diamond2_boosters_earned
  126. , SUM(CASE WHEN resource_sub_type = 'Diamond 3' AND is_received_resource = 1 THEN resource_cnt END) AS diamond3_boosters_earned
  127. , SUM(CASE WHEN resource_sub_type = 'Gold' and resource_type = 'Booster' AND is_received_resource = 1 THEN resource_cnt END) AS gold_boosters_earned
  128. , SUM(CASE WHEN resource_sub_type = 'Silver' AND is_received_resource = 1 THEN resource_cnt END) AS silver_boosters_earned
  129. , SUM(CASE WHEN resource_sub_type = 'Bronze' AND is_received_resource = 1 THEN resource_cnt END) AS bronze_boosters_earned
  130. , SUM(CASE WHEN resource_type = 'Shield' AND is_received_resource = 1 THEN resource_cnt END) AS shields_earned
  131. , SUM(CASE WHEN resource_type = 'Shield' AND is_received_resource = 0 THEN resource_cnt END) AS shields_spent
  132. , SUM(CASE WHEN resource_type = 'Perk' AND is_received_resource = 1 THEN resource_cnt END) AS perks_earned
  133. , SUM(CASE WHEN resource_type = 'Perk' AND is_received_resource = 0 THEN resource_cnt END) AS perks_spent
  134. , SUM(CASE WHEN resource_type = 'Ticket' AND is_received_resource = 1 THEN resource_cnt END) AS tickets_earned
  135. , SUM(CASE WHEN resource_type = 'Ticket' AND is_received_resource = 0 THEN resource_cnt END) AS tickets_spent
  136. , SUM(CASE WHEN resource_type = 'Trophy' AND is_received_resource = 1 THEN resource_cnt END) AS trophy_earned
  137. , SUM(CASE WHEN resource_type = 'Trophy' AND is_received_resource = 0 THEN resource_cnt END) AS trophy_spent
  138. , SUM(CASE WHEN resource_id = 'Coin' AND is_received_resource = 1 THEN resource_cnt END) AS coins_earned
  139. , SUM(CASE WHEN resource_id = 'Coin' AND is_received_resource = 0 THEN resource_cnt END) AS coins_spent
  140.  
  141. , sum(case when resource_id = 'StickerSeasonal' and is_received_resource = 1 then resource_cnt else 0 end) as StickerSeasonal_earned
  142. , sum(case when resource_id = 'StickerSilver' and is_received_resource = 1 then resource_cnt else 0 end) as StickerSilver_earned
  143. , sum(case when resource_id = 'StickerWhite' and is_received_resource = 1 then resource_cnt else 0 end) as StickerWhite_earned
  144. , sum(case when resource_id = 'Spin' and is_received_resource = 1 then resource_cnt else 0 end) as Spin_earned
  145. , sum(case when resource_id = 'SuperSpin' and is_received_resource = 1 then resource_cnt end) as SuperSpin_earned
  146. , sum(case when resource_id = 'StickerAdventure' and is_received_resource = 1 then resource_cnt end) as StickerAdventure_earned
  147. , sum(case when resource_id = 'StickerDiamond' and is_received_resource = 1 then resource_cnt end) as StickerDiamond_earned
  148. , sum(case when resource_id = 'StickerGold' and is_received_resource = 1 then resource_cnt end) as StickerGold_earned
  149. , sum(case when resource_id = 'StickerToken' and is_received_resource = 1 then resource_cnt end) as StickerToken_earned
  150. ---
  151. , sum(case when resource_id = 'SuperSpin' and is_received_resource = 1 then resource_cnt*resource_coin_value end ) as SuperSpin_earned_value
  152. , sum(case when resource_id = 'StickerAdventure' and is_received_resource = 1 then resource_cnt*resource_coin_value end) as StickerAdventure_earned_value
  153. , sum(case when resource_id = 'StickerDiamond' and is_received_resource = 1 then resource_cnt*resource_coin_value end) as StickerDiamond_earned_value
  154. , sum(case when resource_id = 'StickerGold' and is_received_resource = 1 then resource_cnt*resource_coin_value end) as StickerGold_earned_value
  155. , sum(case when resource_id = 'StickerToken' and is_received_resource = 1 then resource_cnt*resource_coin_value end) as StickerToken_earned_value
  156.  
  157. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS legendary_boosters_spent_value
  158. , SUM(CASE WHEN resource_sub_type = 'Special Edition' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS se_boosters_spent_value
  159. , SUM(CASE WHEN resource_sub_type = 'Diamond 1' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS diamond1_boosters_spent_value
  160. , SUM(CASE WHEN resource_sub_type = 'Diamond 2' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS diamond2_boosters_spent_value
  161. , SUM(CASE WHEN resource_sub_type = 'Diamond 3' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS diamond3_boosters_spent_value
  162. , SUM(CASE WHEN resource_sub_type = 'Gold' and resource_type = 'Booster' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS gold_boosters_spent_value
  163. , SUM(CASE WHEN resource_sub_type = 'Silver' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS silver_boosters_spent_value
  164. , SUM(CASE WHEN resource_sub_type = 'Bronze' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS bronze_boosters_spent_value
  165. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS legendary_boosters_earned_value
  166. , SUM(CASE WHEN resource_sub_type = 'Special Edition' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS se_boosters_earned_value
  167. , SUM(CASE WHEN resource_sub_type = 'Diamond 1' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS diamond1_boosters_earned_value
  168. , SUM(CASE WHEN resource_sub_type = 'Diamond 2' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS diamond2_boosters_earned_value
  169. , SUM(CASE WHEN resource_sub_type = 'Diamond 3' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS diamond3_boosters_earned_value
  170. , SUM(CASE WHEN resource_sub_type = 'Gold' and resource_type = 'Booster' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS gold_boosters_earned_value
  171. , SUM(CASE WHEN resource_sub_type = 'Silver' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS silver_boosters_earned_value
  172. , SUM(CASE WHEN resource_sub_type = 'Bronze' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS bronze_boosters_earned_value
  173. , SUM(CASE WHEN resource_type = 'Shield' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS shields_earned_value
  174. , SUM(CASE WHEN resource_type = 'Shield' AND is_received_resource = 0 THEN resource_cnt*shielded_booster_value END) AS shields_spent_value
  175. , SUM(CASE WHEN resource_type = 'Perk' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS perks_earned_value
  176. , SUM(CASE WHEN resource_type = 'Perk' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS perks_spent_value
  177. , SUM(CASE WHEN resource_type = 'Ticket' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS tickets_earned_value
  178. , SUM(CASE WHEN resource_type = 'Ticket' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS tickets_spent_value
  179. , SUM(CASE WHEN resource_type = 'Trophy' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS trophy_earned_value
  180. , SUM(CASE WHEN resource_type = 'Trophy' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS trophy_spent_value
  181.  
  182. , sum(case when resource_id = 'StickerSeasonal' and is_received_resource = 1 then resource_cnt*resource_coin_value else 0 end) as StickerSeasonal_earned_value
  183. , sum(case when resource_id = 'StickerSilver' and is_received_resource = 1 then resource_cnt*resource_coin_value else 0 end) as StickerSilver_earned_value
  184. , sum(case when resource_id = 'StickerWhite' and is_received_resource = 1 then resource_cnt*resource_coin_value else 0 end) as StickerWhite_earned_value
  185. , sum(case when resource_id = 'Spin' and is_received_resource = 1 then resource_cnt*resource_coin_value else 0 end) as Spin_earned_value
  186.  
  187. FROM (SELECT temp1.*, temp2.resource_coin_value, temp1.resource_cnt*temp2.resource_coin_value AS total_coins_value FROM (SELECT * FROM CANDIVORE.PROD.F_USER_RESOURCE_TRANSACTION WHERE (transaction_source LIKE 'live_event' OR transaction_source LIKE 'match_end' OR transaction_source LIKE 'tournament%' OR transaction_source LIKE 'disconnection_refund') AND DATE(derived_tstamp) > DATEADD(DAY,-121,GETDATE())) temp1 LEFT JOIN
  188. (SELECT resource_name, resource_version,resource_type, resource_sub_type, resource_coin_value FROM (SELECT * FROM
  189. (SELECT *, ROW_NUMBER() OVER (partition BY resource_name, resource_version ORDER BY db_create_date DESC, resource_id DESC) AS resource_rank
  190. FROM candivore.prod.DIM_RESOURCE_VERSION)
  191. WHERE resource_rank = 1)) temp2
  192. ON temp1.resource_id = temp2.resource_name AND temp1.resource_version = temp2.resource_version AND temp1.resource_sub_type = temp2.resource_sub_type AND temp1.resource_type = temp2.resource_type)
  193. GROUP BY 1,2,3,4,5,6,7,8,9) A
  194.  
  195. LEFT JOIN
  196. (SELECT
  197. match_end_date interval_date
  198. ,CASE
  199. WHEN trophies_cnt < 800 THEN '800'
  200. WHEN CURRENT_ARENA_INDEX<=13 THEN 'Studios'
  201. WHEN CURRENT_ARENA_INDEX<=22 THEN 'Master_League'
  202. WHEN CURRENT_ARENA_INDEX>=23 THEN 'Legends_League'
  203. END arena_group
  204. , CASE
  205. WHEN trophies_cnt < 125 THEN '0-125'
  206. WHEN trophies_cnt < 1400 THEN '125-1400'
  207. WHEN trophies_cnt < 2000 THEN '1400-2000'
  208. WHEN trophies_cnt < 3800 THEN '2000-3800'
  209. WHEN trophies_cnt < 7000 THEN '3800-7000'
  210. WHEN trophies_cnt < 16000 THEN '7000-16000'
  211. WHEN trophies_cnt < 30000 THEN '16000-30000'
  212. WHEN trophies_cnt >= 30000 THEN '30000+'
  213. END AS trophy_group
  214. , CASE
  215. WHEN trophies_cnt < 125 THEN '0-125'
  216. WHEN trophies_cnt < 1400 THEN '125-1400'
  217. WHEN trophies_cnt < 2000 THEN '1400-2000'
  218. WHEN trophies_cnt < 3800 THEN '2000-3800'
  219. WHEN trophies_cnt < 4500 THEN '3800-4500'
  220. WHEN trophies_cnt < 5500 THEN '4500-5500'
  221. WHEN trophies_cnt < 7000 THEN '5500-7000'
  222. WHEN trophies_cnt < 10000 THEN '7000-10000'
  223. WHEN trophies_cnt < 13000 THEN '10000-13000'
  224. WHEN trophies_cnt < 16000 THEN '13000-16000'
  225. WHEN trophies_cnt < 20000 THEN '16000-20000'
  226. WHEN trophies_cnt < 30000 THEN '20000-30000'
  227. WHEN trophies_cnt >= 30000 THEN '30000+'
  228. END AS trophy_group_2
  229. , LTV_group
  230. , ifnull(calendar_entry_id,'NA') lo_entry_id
  231. , unique_users
  232. , unique_users_with_LESE_boosters
  233. , event_start_date
  234. , event_start_ts
  235. , CASE
  236. WHEN lo_event_id LIKE '%mutation%' THEN 'solo_mutation'
  237. WHEN lo_event_id LIKE 'Solo%' OR lo_event_id LIKE 'solo%' THEN 'solo'
  238. WHEN lo_event_type = 'Rumble' THEN 'rumble'
  239. WHEN lo_event_type = 'AdventureMode' THEN 'adventure'
  240. WHEN lo_event_type LIKE 'Leaderboard%' THEN 'leaderboard'
  241. WHEN lo_event_type LIKE 'BoostersClash' THEN 'boosters_clash'
  242. WHEN match_type_id = 4 THEN 'tournament'
  243. WHEN match_type_id = 5 THEN 'daily'
  244. WHEN match_type_id = 2 THEN 'classic'
  245. ELSE match_type
  246. END AS match_type2
  247. , match_type_id
  248. , CASE
  249. WHEN is_random_mixer = TRUE OR match_type_id = 2 THEN 'random'
  250. ELSE match_mode
  251. END match_mode
  252. , is_random_mixer
  253. ,case when match_type_id = 2 then 5 else rounds_cnt end rounds_cnt
  254. , tournament_size
  255. , modifier_1
  256. , modifier_2
  257. , match_modifier_3
  258. , min_time
  259. , max_time
  260. , rumble_size
  261. --, max_max_prize_eligibility
  262. --, mode(unique_users_per_solo_prize) unique_users_per_solo_prize
  263. , COUNT(*) AS total_matches
  264. , SUM(CASE WHEN is_conceded = 0 THEN 1 ELSE 0 END) unconceded_matches
  265. , COUNT(DISTINCT match_id) AS total_match_ids
  266. , COUNT(DISTINCT user_id) AS unique_users_from_match_end
  267. , SUM(MATCH_DURATION_SEC) AS total_seconds_played_in_segment
  268. , SUM(CASE WHEN is_conceded = 0 THEN MATCH_DURATION_SEC ELSE 0 END) AS total_seconds_played_in_segment_unconceded
  269. , SUM(ability_activated_cnt) AS total_booster_activations_in_segment
  270. , SUM(CASE WHEN is_conceded = 0 THEN ability_activated_cnt ELSE 0 END) AS total_booster_activations_in_segment_unconceded
  271. , SUM(match_score) AS total_match_score_in_segment
  272. , SUM(CASE WHEN is_conceded = 0 THEN match_score ELSE 0 END) AS total_match_score_in_segment_unconceded
  273. , SUM(CASE WHEN resource_sub_type = 'Legendary' THEN 1 END) AS games_with_legendary_boosters
  274. , SUM(CASE WHEN resource_type = 'Booster' AND resource_sub_type = 'Special Edition' THEN 1 END) AS games_with_SE_boosters
  275. , SUM(CASE WHEN booster_tier = 'Diamond' AND is_won = 0 THEN 1 END) AS diamond_losses
  276. , SUM(CASE WHEN resource_sub_type = 'Diamond 3' AND is_won = 0 THEN 1 END) AS diamond3_losses
  277. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_won = 0 THEN 1 END) AS legendary_losses
  278. , SUM(CASE WHEN resource_sub_type = 'Special Edition' AND is_won = 0 THEN 1 END) AS SE_losses
  279. , SUM(CASE WHEN is_won = 0 THEN 1 END) AS total_losses
  280. , SUM(CASE WHEN is_won = 1 THEN 1 END) AS total_wins
  281. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_won = 1 THEN 1 END) legendary_wins
  282. , SUM(CASE WHEN resource_type = 'Booster' AND resource_sub_type = 'Special Edition' AND is_won = 1 THEN 1 END) SE_wins
  283. , SUM(CASE WHEN resource_type = 'Booster' THEN resource_coin_value END) AS total_coin_value
  284. , SUM(is_on_fire_game) total_on_fire
  285. , SUM(CASE WHEN match_type = 'Rumble' THEN ((rumble_size-unique_users_in_match)/unique_users_in_match) ELSE is_rival_bot END) total_bots
  286. , SUM(board_ability1_uses + board_ability2_uses) AS total_perk_activations
  287. , SUM(CASE WHEN is_conceded = 1 AND is_won = 0 THEN 1 ELSE 0 END) conceded_matches
  288. FROM (SELECT *, MIN(derived_tstamp) OVER(partition BY calendar_entry_id) min_time
  289. , MAX(derived_tstamp) OVER(partition BY calendar_entry_id) max_time
  290. , COUNT(DISTINCT user_id) OVER(partition BY calendar_entry_id) unique_users
  291. --, COUNT(DISTINCT user_id) OVER(partition BY calendar_entry_id,max_max_prize_eligibility) unique_users_per_solo_prize
  292. , COUNT(DISTINCT user_id) OVER(partition BY match_id) unique_users_in_match
  293. , MIN(DATE(derived_tstamp)) OVER(partition BY calendar_entry_id) event_start_date
  294. , MIN(date_trunc('hour', derived_tstamp)) OVER(partition BY calendar_entry_id) event_start_ts
  295. , COUNT(DISTINCT CASE WHEN resource_sub_type = 'Legendary' OR resource_sub_type = 'Special Edition' THEN user_id END) OVER(partition BY calendar_entry_id) unique_users_with_LESE_boosters
  296. FROM (SELECT *, MAX(max_prize_eligibility) OVER(partition BY calendar_entry_id,user_id) max_max_prize_eligibility FROM CANDIVORE.PROD.F_USER_MATCH LEFT JOIN
  297. (SELECT resource_id, resource_coin_value FROM CANDIVORE.PROD.DIM_RESOURCE_VERSION) temp2
  298. ON CANDIVORE.PROD.F_USER_MATCH.resource_id = temp2.resource_id WHERE is_bot = 0 AND DATE(CANDIVORE.PROD.F_USER_MATCH.derived_tstamp) > DATEADD(DAY,-121,GETDATE())))
  299. GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22) G
  300. ON A.interval_date = G.interval_date AND A.arena_group = G.arena_group AND A.trophy_group = G.trophy_group AND A.trophy_group_2 = G.trophy_group_2 AND A.LTV_group = G.LTV_group AND A.lo_entry_id = G.lo_entry_id AND A.match_type = G.match_type2
  301.  
  302. LEFT JOIN
  303.  
  304. (SELECT A.LO_ENTRY_ID, B.lo_event_id, A.interval_date, A.lo_entry_name, A.calendar_order, A.lo_visible_on_homescreen,
  305. CASE WHEN min0 IS NULL THEN min1 ELSE min0 END lo_event_min_trophies,
  306. CASE WHEN max0 IS NULL THEN max1 ELSE max0 END lo_event_max_trophies,
  307. hours, lo_entry_tickets_required, lo_entry_min_booster_rarity, lo_entry_max_booster_rarity,lo_event_solo_moves_per_turn, lo_event_turn_amt, lo_event_turn_duration_sec, lo_entry_is_private, lo_event_wins_limit, lo_is_friendly
  308. FROM
  309. ((SELECT CASE WHEN lo_original_entry_id IS NULL THEN LO_ENTRY_ID ELSE LO_original_ENTRY_ID END LO_ENTRY_ID,DATE(derived_tstamp) interval_date, LO_ENTRY_NAME, calendar_order, lo_visible_on_homescreen, lo_entry_min_trophies min0, lo_entry_max_trophies max0, round((lo_entry_end_ts-lo_entry_start_ts)/60/60,0) hours, lo_entry_tickets_required,lo_entry_min_booster_rarity, lo_entry_max_booster_rarity, lo_entry_is_private FROM (SELECT * FROM (SELECT *,CASE WHEN lo_entry_ts IS NULL THEN lo_entry_update_ts ELSE lo_entry_ts END lo_entry_ts2,ROW_NUMBER() OVER (partition BY lo_entry_id ORDER BY lo_entry_ts2 DESC) rn FROM CANDIVORE.PROD.F_LIVEOPS_CALENDAR) WHERE rn = 1)) A
  310. LEFT JOIN
  311. (SELECT DATE(derived_tstamp) interval_date, lo_event_id, lo_event_min_trophies min1, lo_event_max_trophies max1, lo_event_solo_moves_per_turn, lo_event_turn_amt, lo_event_turn_duration_sec, lo_event_wins_limit, lo_is_friendly FROM CANDIVORE.PROD.DIM_EVENT_CONFIG) B
  312. ON A.LO_ENTRY_NAME = B.LO_EVENT_ID))
  313. C
  314. ON A.lo_entry_id = C.lo_entry_id
  315.  
  316. LEFT JOIN
  317. (SELECT table1.source_calendar_entry_id, table1.interval_date interval_date, table2.arena_group, table2.trophy_group,table2.trophy_group_2, table2.LTV_group, SUM(table1.points_earned) points_earned, SUM(table1.points_earned*table1.event_point_value) value_earned FROM (SELECT *, DATE(derived_tstamp) AS interval_date FROM CANDIVORE.PROD.F_LIVE_EVENT_PROGRESSION WHERE action = 'points_earned' AND event_slot = 'Main' AND DATE(derived_tstamp) > DATEADD(DAY,-121,GETDATE())) AS table1 LEFT JOIN (SELECT
  318. user_id
  319. , interval_date
  320. , CASE
  321. WHEN trophies_cnt < 800 THEN '800'
  322. WHEN CURRENT_ARENA_INDEX<=13 THEN 'Studios'
  323. WHEN CURRENT_ARENA_INDEX<=22 THEN 'Master_League'
  324. WHEN CURRENT_ARENA_INDEX>=23 THEN 'Legends_League'
  325. END ARENA_GROUP
  326. , CASE
  327. WHEN trophies_cnt < 125 THEN '0-125'
  328. WHEN trophies_cnt < 1400 THEN '125-1400'
  329. WHEN trophies_cnt < 2000 THEN '1400-2000'
  330. WHEN trophies_cnt < 3800 THEN '2000-3800'
  331. WHEN trophies_cnt < 7000 THEN '3800-7000'
  332. WHEN trophies_cnt < 16000 THEN '7000-16000'
  333. WHEN trophies_cnt < 30000 THEN '16000-30000'
  334. WHEN trophies_cnt >= 30000 THEN '30000+'
  335. END AS trophy_group
  336. , CASE
  337. WHEN trophies_cnt < 125 THEN '0-125'
  338. WHEN trophies_cnt < 1400 THEN '125-1400'
  339. WHEN trophies_cnt < 2000 THEN '1400-2000'
  340. WHEN trophies_cnt < 3800 THEN '2000-3800'
  341. WHEN trophies_cnt < 4500 THEN '3800-4500'
  342. WHEN trophies_cnt < 5500 THEN '4500-5500'
  343. WHEN trophies_cnt < 7000 THEN '5500-7000'
  344. WHEN trophies_cnt < 10000 THEN '7000-10000'
  345. WHEN trophies_cnt < 13000 THEN '10000-13000'
  346. WHEN trophies_cnt < 16000 THEN '13000-16000'
  347. WHEN trophies_cnt < 20000 THEN '16000-20000'
  348. WHEN trophies_cnt < 30000 THEN '20000-30000'
  349. WHEN trophies_cnt >= 30000 THEN '30000+'
  350. END AS trophy_group_2
  351. , CASE WHEN total_iap_amt = 0 THEN '0'
  352. WHEN total_iap_amt <= 10 THEN 'Low'
  353. WHEN total_iap_amt <= 100 THEN 'Med'
  354. WHEN total_iap_amt <= 299 THEN 'High'
  355. WHEN total_iap_amt <= 999 THEN 'Very High'
  356. WHEN total_iap_amt >= 1000 THEN 'VIP'
  357. ELSE NULL
  358. END AS LTV_group
  359. FROM CANDIVORE.PROD.USER_DAILY_PARAMS) table2 ON table1.user_id = table2.user_id AND table1.interval_date = table2.interval_date GROUP BY 1,2,3,4,5,6) D
  360. ON A.lo_entry_id = D.source_calendar_entry_id AND A.interval_date = D.interval_date AND A.LTV_group = D.LTV_group AND A.trophy_group = D.trophy_group AND A.ARENA_GROUP = D.ARENA_GROUP AND A.trophy_group_2 = D.trophy_group_2
  361.  
  362.  
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!