API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Jul 11th, 2023
153
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 24.96 KB | None | 0 0
raw download clone embed print report
  1. SELECT
  2. A.INTERVAL_DATE
  3. ,A.ARENA_GROUP
  4. ,A.TROPHY_GROUP
  5. ,A.TROPHY_GROUP_2
  6. ,A.LTV_GROUP
  7. ,A.LO_ENTRY_ID
  8. ,A.MATCH_TYPE
  9. ,A.TRANSACTION_MATCHES
  10. ,A.UNIQUE_USERS_FROM_TRANSACTION
  11. ,A.LEGENDARY_BOOSTERS_SPENT
  12. ,A.SE_BOOSTERS_SPENT
  13. ,A.DIAMOND1_BOOSTERS_SPENT
  14. ,A.DIAMOND2_BOOSTERS_SPENT
  15. ,A.DIAMOND3_BOOSTERS_SPENT
  16. ,A.GOLD_BOOSTERS_SPENT
  17. ,A.SILVER_BOOSTERS_SPENT
  18. ,A.BRONZE_BOOSTERS_SPENT
  19. ,A.LEGENDARY_BOOSTERS_EARNED
  20. ,A.SE_BOOSTERS_EARNED
  21. ,A.DIAMOND1_BOOSTERS_EARNED
  22. ,A.DIAMOND2_BOOSTERS_EARNED
  23. ,A.DIAMOND3_BOOSTERS_EARNED
  24. ,A.GOLD_BOOSTERS_EARNED
  25. ,A.SILVER_BOOSTERS_EARNED
  26. ,A.BRONZE_BOOSTERS_EARNED
  27. ,A.SHIELDS_EARNED
  28. ,A.SHIELDS_SPENT
  29. ,A.PERKS_EARNED
  30. ,A.PERKS_SPENT
  31. ,A.TICKETS_EARNED
  32. ,A.TICKETS_SPENT
  33. ,A.TROPHY_EARNED
  34. ,A.TROPHY_SPENT
  35. ,A.COINS_EARNED
  36. ,A.COINS_SPENT
  37. ,A.STICKERSEASONAL_EARNED
  38. ,A.STICKERSILVER_EARNED
  39. ,A.STICKERWHITE_EARNED
  40. ,A.SPIN_EARNED
  41. ,A.SUPERSPIN_EARNED
  42. ,A.STICKERADVENTURE_EARNED
  43. ,A.STICKERDIAMOND_EARNED
  44. ,A.STICKERGOLD_EARNED
  45. ,A.STICKERTOKEN_EARNED
  46. ,A.SUPERSPIN_EARNED_VALUE
  47. ,A.STICKERADVENTURE_EARNED_VALUE
  48. ,A.STICKERDIAMOND_EARNED_VALUE
  49. ,A.STICKERGOLD_EARNED_VALUE
  50. ,A.STICKERTOKEN_EARNED_VALUE
  51. ,A.LEGENDARY_BOOSTERS_SPENT_VALUE
  52. ,A.SE_BOOSTERS_SPENT_VALUE
  53. ,A.DIAMOND1_BOOSTERS_SPENT_VALUE
  54. ,A.DIAMOND2_BOOSTERS_SPENT_VALUE
  55. ,A.DIAMOND3_BOOSTERS_SPENT_VALUE
  56. ,A.GOLD_BOOSTERS_SPENT_VALUE
  57. ,A.SILVER_BOOSTERS_SPENT_VALUE
  58. ,A.BRONZE_BOOSTERS_SPENT_VALUE
  59. ,A.LEGENDARY_BOOSTERS_EARNED_VALUE
  60. ,A.SE_BOOSTERS_EARNED_VALUE
  61. ,A.DIAMOND1_BOOSTERS_EARNED_VALUE
  62. ,A.DIAMOND2_BOOSTERS_EARNED_VALUE
  63. ,A.DIAMOND3_BOOSTERS_EARNED_VALUE
  64. ,A.GOLD_BOOSTERS_EARNED_VALUE
  65. ,A.SILVER_BOOSTERS_EARNED_VALUE
  66. ,A.BRONZE_BOOSTERS_EARNED_VALUE
  67. ,A.SHIELDS_EARNED_VALUE
  68. ,A.SHIELDS_SPENT_VALUE
  69. ,A.PERKS_EARNED_VALUE
  70. ,A.PERKS_SPENT_VALUE
  71. ,A.TICKETS_EARNED_VALUE
  72. ,A.TICKETS_SPENT_VALUE
  73. ,A.TROPHY_EARNED_VALUE
  74. ,A.TROPHY_SPENT_VALUE
  75. ,A.STICKERSEASONAL_EARNED_VALUE
  76. ,A.STICKERSILVER_EARNED_VALUE
  77. ,A.STICKERWHITE_EARNED_VALUE
  78. ,A.SPIN_EARNED_VALUE
  79. , G.match_type2
  80. , G.match_type_id
  81. , G.match_mode
  82. , G.modifier_1
  83. , G.modifier_2
  84. , G.is_random_mixer
  85. , G.rounds_cnt
  86. , G.total_matches
  87. , G.unique_users_from_match_end
  88. , G.event_start_date
  89. , G.event_start_ts
  90. , G.total_seconds_played_in_segment
  91. , G.total_booster_activations_in_segment
  92. , G.total_match_score_in_segment
  93. , G.games_with_legendary_boosters
  94. , G.games_with_SE_boosters
  95. , G.diamond_losses
  96. , G.diamond3_losses
  97. , G.legendary_losses
  98. , G.SE_losses
  99. , G.total_losses
  100. , G.total_wins
  101. , G.legendary_wins
  102. , G.SE_wins
  103. , G.min_time
  104. , G.max_time
  105. , G.unique_users
  106. --, G.unique_users_per_solo_prize
  107. , G.unique_users_with_LESE_boosters
  108. , G.total_coin_value
  109. , G.total_match_ids
  110. , G.rumble_size
  111. , G.tournament_size
  112. , G.total_bots
  113. , G.total_on_fire
  114. , G.conceded_matches
  115. , G.total_perk_activations
  116. --, G.max_max_prize_eligibility
  117. , D.points_earned LE_points_earned
  118. , D.value_earned LE_value_earned
  119. , C.lo_event_id
  120. , C.lo_event_min_trophies
  121. , C.lo_event_max_trophies
  122. , C.calendar_order
  123. , C.hours
  124. , C.lo_entry_tickets_required
  125. , C.lo_visible_on_homescreen
  126. , C.lo_entry_min_booster_rarity
  127. , C.lo_entry_max_booster_rarity
  128. , C.lo_event_solo_moves_per_turn
  129. , C.lo_event_turn_amt elimination_round
  130. , C.lo_event_turn_duration_sec
  131. , C.lo_entry_is_private
  132. , C.lo_event_wins_limit
  133. , C.lo_is_friendly
  134. FROM (SELECT
  135. DATE(derived_tstamp) interval_date
  136. , CASE
  137. WHEN trophies_cnt < 800 THEN '800'
  138. WHEN CURRENT_ARENA_INDEX<=13 THEN 'Studios'
  139. WHEN CURRENT_ARENA_INDEX<=22 THEN 'Master_League'
  140. WHEN CURRENT_ARENA_INDEX>=23 THEN 'Legends_League'
  141. END arena_group
  142. , CASE
  143. WHEN trophies_cnt < 125 THEN '0-125'
  144. WHEN trophies_cnt < 1400 THEN '125-1400'
  145. WHEN trophies_cnt < 2000 THEN '1400-2000'
  146. WHEN trophies_cnt < 3800 THEN '2000-3800'
  147. WHEN trophies_cnt < 7000 THEN '3800-7000'
  148. WHEN trophies_cnt < 16000 THEN '7000-16000'
  149. WHEN trophies_cnt < 30000 THEN '16000-30000'
  150. WHEN trophies_cnt >= 30000 THEN '30000+'
  151. END AS trophy_group
  152. , CASE
  153. WHEN trophies_cnt < 125 THEN '0-125'
  154. WHEN trophies_cnt < 1400 THEN '125-1400'
  155. WHEN trophies_cnt < 2000 THEN '1400-2000'
  156. WHEN trophies_cnt < 3800 THEN '2000-3800'
  157. WHEN trophies_cnt < 4500 THEN '3800-4500'
  158. WHEN trophies_cnt < 5500 THEN '4500-5500'
  159. WHEN trophies_cnt < 7000 THEN '5500-7000'
  160. WHEN trophies_cnt < 10000 THEN '7000-10000'
  161. WHEN trophies_cnt < 13000 THEN '10000-13000'
  162. WHEN trophies_cnt < 16000 THEN '13000-16000'
  163. WHEN trophies_cnt < 20000 THEN '16000-20000'
  164. WHEN trophies_cnt < 30000 THEN '20000-30000'
  165. WHEN trophies_cnt >= 30000 THEN '30000+'
  166. END AS trophy_group_2
  167. , LTV_group
  168. , ifnull(
  169. CASE
  170. WHEN lo_entry_id = '' THEN NULL
  171. ELSE lo_entry_id
  172. END
  173. , 'NA') as lo_entry_id
  174. , CASE
  175. WHEN lo_event_id LIKE '%mutation%' THEN 'solo_mutation'
  176. WHEN lo_event_id LIKE 'Solo%' OR lo_event_id LIKE 'solo%' THEN 'solo'
  177. WHEN lo_event_type = 'Rumble' THEN 'rumble'
  178. WHEN lo_event_type = 'AdventureMode' THEN 'adventure'
  179. WHEN lo_event_type LIKE 'Leaderboard%' THEN 'leaderboard'
  180. WHEN lo_event_type LIKE 'BoostersClash' THEN 'boosters_clash'
  181. WHEN match_type_id = 4 OR transaction_source LIKE 'tournament%' THEN 'tournament'
  182. WHEN match_type_id = 5 THEN 'daily'
  183. WHEN match_type_id = 2 THEN 'classic'
  184. ELSE 'other'
  185. END AS match_type
  186. , COUNT(DISTINCT match_id) transaction_matches
  187. , COUNT(DISTINCT user_id) AS unique_users_from_transaction
  188. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_received_resource = 0 THEN resource_cnt END) AS legendary_boosters_spent
  189. , SUM(CASE WHEN resource_sub_type = 'Special Edition' AND is_received_resource = 0 THEN resource_cnt END) AS se_boosters_spent
  190. , SUM(CASE WHEN resource_sub_type = 'Diamond 1' AND is_received_resource = 0 THEN resource_cnt END) AS diamond1_boosters_spent
  191. , SUM(CASE WHEN resource_sub_type = 'Diamond 2' AND is_received_resource = 0 THEN resource_cnt END) AS diamond2_boosters_spent
  192. , SUM(CASE WHEN resource_sub_type = 'Diamond 3' AND is_received_resource = 0 THEN resource_cnt END) AS diamond3_boosters_spent
  193. , SUM(CASE WHEN resource_sub_type = 'Gold' and resource_type = 'Booster' AND is_received_resource = 0 THEN resource_cnt END) AS gold_boosters_spent
  194. , SUM(CASE WHEN resource_sub_type = 'Silver' and resource_type = 'Booster' AND is_received_resource = 0 THEN resource_cnt END) AS silver_boosters_spent
  195. , SUM(CASE WHEN resource_sub_type = 'Bronze' AND is_received_resource = 0 THEN resource_cnt END) AS bronze_boosters_spent
  196. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_received_resource = resource_cnt THEN 1 END) AS legendary_boosters_earned
  197. , SUM(CASE WHEN resource_sub_type = 'Special Edition' AND is_received_resource = resource_cnt THEN 1 END) AS se_boosters_earned
  198. , SUM(CASE WHEN resource_sub_type = 'Diamond 1' AND is_received_resource = 1 THEN resource_cnt END) AS diamond1_boosters_earned
  199. , SUM(CASE WHEN resource_sub_type = 'Diamond 2' AND is_received_resource = 1 THEN resource_cnt END) AS diamond2_boosters_earned
  200. , SUM(CASE WHEN resource_sub_type = 'Diamond 3' AND is_received_resource = 1 THEN resource_cnt END) AS diamond3_boosters_earned
  201. , SUM(CASE WHEN resource_sub_type = 'Gold' and resource_type = 'Booster' AND is_received_resource = 1 THEN resource_cnt END) AS gold_boosters_earned
  202. , SUM(CASE WHEN resource_sub_type = 'Silver' AND is_received_resource = 1 THEN resource_cnt END) AS silver_boosters_earned
  203. , SUM(CASE WHEN resource_sub_type = 'Bronze' AND is_received_resource = 1 THEN resource_cnt END) AS bronze_boosters_earned
  204. , SUM(CASE WHEN resource_type = 'Shield' AND is_received_resource = 1 THEN resource_cnt END) AS shields_earned
  205. , SUM(CASE WHEN resource_type = 'Shield' AND is_received_resource = 0 THEN resource_cnt END) AS shields_spent
  206. , SUM(CASE WHEN resource_type = 'Perk' AND is_received_resource = 1 THEN resource_cnt END) AS perks_earned
  207. , SUM(CASE WHEN resource_type = 'Perk' AND is_received_resource = 0 THEN resource_cnt END) AS perks_spent
  208. , SUM(CASE WHEN resource_type = 'Ticket' AND is_received_resource = 1 THEN resource_cnt END) AS tickets_earned
  209. , SUM(CASE WHEN resource_type = 'Ticket' AND is_received_resource = 0 THEN resource_cnt END) AS tickets_spent
  210. , SUM(CASE WHEN resource_type = 'Trophy' AND is_received_resource = 1 THEN resource_cnt END) AS trophy_earned
  211. , SUM(CASE WHEN resource_type = 'Trophy' AND is_received_resource = 0 THEN resource_cnt END) AS trophy_spent
  212. , SUM(CASE WHEN resource_id = 'Coin' AND is_received_resource = 1 THEN resource_cnt END) AS coins_earned
  213. , SUM(CASE WHEN resource_id = 'Coin' AND is_received_resource = 0 THEN resource_cnt END) AS coins_spent
  214.  
  215. , sum(case when resource_id = 'StickerSeasonal' and is_received_resource = 1 then resource_cnt else 0 end) as StickerSeasonal_earned
  216. , sum(case when resource_id = 'StickerSilver' and is_received_resource = 1 then resource_cnt else 0 end) as StickerSilver_earned
  217. , sum(case when resource_id = 'StickerWhite' and is_received_resource = 1 then resource_cnt else 0 end) as StickerWhite_earned
  218. , sum(case when resource_id = 'Spin' and is_received_resource = 1 then resource_cnt else 0 end) as Spin_earned
  219. , sum(case when resource_id = 'SuperSpin' and is_received_resource = 1 then resource_cnt end) as SuperSpin_earned
  220. , sum(case when resource_id = 'StickerAdventure' and is_received_resource = 1 then resource_cnt end) as StickerAdventure_earned
  221. , sum(case when resource_id = 'StickerDiamond' and is_received_resource = 1 then resource_cnt end) as StickerDiamond_earned
  222. , sum(case when resource_id = 'StickerGold' and is_received_resource = 1 then resource_cnt end) as StickerGold_earned
  223. , sum(case when resource_id = 'StickerToken' and is_received_resource = 1 then resource_cnt end) as StickerToken_earned
  224. ---
  225. , sum(case when resource_id = 'SuperSpin' and is_received_resource = 1 then resource_cnt*resource_coin_value end ) as SuperSpin_earned_value
  226. , sum(case when resource_id = 'StickerAdventure' and is_received_resource = 1 then resource_cnt*resource_coin_value end) as StickerAdventure_earned_value
  227. , sum(case when resource_id = 'StickerDiamond' and is_received_resource = 1 then resource_cnt*resource_coin_value end) as StickerDiamond_earned_value
  228. , sum(case when resource_id = 'StickerGold' and is_received_resource = 1 then resource_cnt*resource_coin_value end) as StickerGold_earned_value
  229. , sum(case when resource_id = 'StickerToken' and is_received_resource = 1 then resource_cnt*resource_coin_value end) as StickerToken_earned_value
  230.  
  231. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS legendary_boosters_spent_value
  232. , 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
  233. , 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
  234. , 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
  235. , 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
  236. , 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
  237. , SUM(CASE WHEN resource_sub_type = 'Silver' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS silver_boosters_spent_value
  238. , SUM(CASE WHEN resource_sub_type = 'Bronze' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS bronze_boosters_spent_value
  239. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS legendary_boosters_earned_value
  240. , 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
  241. , 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
  242. , 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
  243. , 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
  244. , 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
  245. , SUM(CASE WHEN resource_sub_type = 'Silver' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS silver_boosters_earned_value
  246. , SUM(CASE WHEN resource_sub_type = 'Bronze' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS bronze_boosters_earned_value
  247. , SUM(CASE WHEN resource_type = 'Shield' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS shields_earned_value
  248. , SUM(CASE WHEN resource_type = 'Shield' AND is_received_resource = 0 THEN resource_cnt*shielded_booster_value END) AS shields_spent_value
  249. , SUM(CASE WHEN resource_type = 'Perk' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS perks_earned_value
  250. , SUM(CASE WHEN resource_type = 'Perk' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS perks_spent_value
  251. , SUM(CASE WHEN resource_type = 'Ticket' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS tickets_earned_value
  252. , SUM(CASE WHEN resource_type = 'Ticket' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS tickets_spent_value
  253. , SUM(CASE WHEN resource_type = 'Trophy' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS trophy_earned_value
  254. , SUM(CASE WHEN resource_type = 'Trophy' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS trophy_spent_value
  255.  
  256. , sum(case when resource_id = 'StickerSeasonal' and is_received_resource = 1 then resource_cnt*resource_coin_value else 0 end) as StickerSeasonal_earned_value
  257. , sum(case when resource_id = 'StickerSilver' and is_received_resource = 1 then resource_cnt*resource_coin_value else 0 end) as StickerSilver_earned_value
  258. , sum(case when resource_id = 'StickerWhite' and is_received_resource = 1 then resource_cnt*resource_coin_value else 0 end) as StickerWhite_earned_value
  259. , sum(case when resource_id = 'Spin' and is_received_resource = 1 then resource_cnt*resource_coin_value else 0 end) as Spin_earned_value
  260.  
  261. 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 match_type_id > 0 and (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
  262. (SELECT resource_name, resource_version,resource_type, resource_sub_type, resource_coin_value FROM (SELECT * FROM
  263. (SELECT *, ROW_NUMBER() OVER (partition BY resource_name, resource_version ORDER BY db_create_date DESC, resource_id DESC) AS resource_rank
  264. FROM candivore.prod.DIM_RESOURCE_VERSION)
  265. WHERE resource_rank = 1)) temp2
  266. 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)
  267. GROUP BY 1,2,3,4,5,6,7) A
  268.  
  269. LEFT JOIN
  270. (SELECT
  271. match_end_date interval_date
  272. ,CASE
  273. WHEN trophies_cnt < 800 THEN '800'
  274. WHEN CURRENT_ARENA_INDEX<=13 THEN 'Studios'
  275. WHEN CURRENT_ARENA_INDEX<=22 THEN 'Master_League'
  276. WHEN CURRENT_ARENA_INDEX>=23 THEN 'Legends_League'
  277. END arena_group
  278. , CASE
  279. WHEN trophies_cnt < 125 THEN '0-125'
  280. WHEN trophies_cnt < 1400 THEN '125-1400'
  281. WHEN trophies_cnt < 2000 THEN '1400-2000'
  282. WHEN trophies_cnt < 3800 THEN '2000-3800'
  283. WHEN trophies_cnt < 7000 THEN '3800-7000'
  284. WHEN trophies_cnt < 16000 THEN '7000-16000'
  285. WHEN trophies_cnt < 30000 THEN '16000-30000'
  286. WHEN trophies_cnt >= 30000 THEN '30000+'
  287. END AS trophy_group
  288. , CASE
  289. WHEN trophies_cnt < 125 THEN '0-125'
  290. WHEN trophies_cnt < 1400 THEN '125-1400'
  291. WHEN trophies_cnt < 2000 THEN '1400-2000'
  292. WHEN trophies_cnt < 3800 THEN '2000-3800'
  293. WHEN trophies_cnt < 4500 THEN '3800-4500'
  294. WHEN trophies_cnt < 5500 THEN '4500-5500'
  295. WHEN trophies_cnt < 7000 THEN '5500-7000'
  296. WHEN trophies_cnt < 10000 THEN '7000-10000'
  297. WHEN trophies_cnt < 13000 THEN '10000-13000'
  298. WHEN trophies_cnt < 16000 THEN '13000-16000'
  299. WHEN trophies_cnt < 20000 THEN '16000-20000'
  300. WHEN trophies_cnt < 30000 THEN '20000-30000'
  301. WHEN trophies_cnt >= 30000 THEN '30000+'
  302. END AS trophy_group_2
  303. , LTV_group
  304. , ifnull(calendar_entry_id,'NA') lo_entry_id
  305. , unique_users
  306. , unique_users_with_LESE_boosters
  307. , event_start_date
  308. , event_start_ts
  309. , CASE
  310. WHEN lo_event_id LIKE '%mutation%' THEN 'solo_mutation'
  311. WHEN lo_event_id LIKE 'Solo%' OR lo_event_id LIKE 'solo%' THEN 'solo'
  312. WHEN lo_event_type = 'Rumble' THEN 'rumble'
  313. WHEN lo_event_type = 'AdventureMode' THEN 'adventure'
  314. WHEN lo_event_type = 'Onboarding' THEN 'onboarding'
  315. WHEN lo_event_type LIKE 'Leaderboard%' THEN 'leaderboard'
  316. WHEN lo_event_type LIKE 'BoostersClash' THEN 'boosters_clash'
  317. WHEN match_type_id = 4 THEN 'tournament'
  318. WHEN match_type_id = 5 THEN 'daily'
  319. WHEN match_type_id = 2 THEN 'classic'
  320. ELSE match_type
  321. END AS match_type2
  322. , match_type_id
  323. , tournament_size
  324. , min_time
  325. , max_time
  326. , rumble_size
  327. , mode(rounds_cnt) rounds_cnt
  328. , mode(is_random_mixer) is_random_mixer
  329. , mode(modifier_1) modifier_1
  330. , mode(modifier_2) modifier_2
  331. , mode(ifnull(match_modifier_3,'')) match_modifier_3
  332. , CASE
  333. WHEN mode(is_random_mixer) = TRUE OR match_type_id = 2 THEN 'random'
  334. ELSE mode(match_mode)
  335. END match_mode
  336. --, max_max_prize_eligibility
  337. --, mode(unique_users_per_solo_prize) unique_users_per_solo_prize
  338. , COUNT(*) AS total_matches
  339. , SUM(CASE WHEN is_conceded = 0 THEN 1 ELSE 0 END) unconceded_matches
  340. , COUNT(DISTINCT match_id) AS total_match_ids
  341. , COUNT(DISTINCT user_id) AS unique_users_from_match_end
  342. , SUM(MATCH_DURATION_SEC) AS total_seconds_played_in_segment
  343. , SUM(CASE WHEN is_conceded = 0 THEN MATCH_DURATION_SEC ELSE 0 END) AS total_seconds_played_in_segment_unconceded
  344. , SUM(ability_activated_cnt) AS total_booster_activations_in_segment
  345. , SUM(CASE WHEN is_conceded = 0 THEN ability_activated_cnt ELSE 0 END) AS total_booster_activations_in_segment_unconceded
  346. , SUM(match_score) AS total_match_score_in_segment
  347. , SUM(CASE WHEN is_conceded = 0 THEN match_score ELSE 0 END) AS total_match_score_in_segment_unconceded
  348. , SUM(CASE WHEN resource_sub_type = 'Legendary' THEN 1 END) AS games_with_legendary_boosters
  349. , SUM(CASE WHEN resource_type = 'Booster' AND resource_sub_type = 'Special Edition' THEN 1 END) AS games_with_SE_boosters
  350. , SUM(CASE WHEN booster_tier = 'Diamond' AND is_won = 0 THEN 1 END) AS diamond_losses
  351. , SUM(CASE WHEN resource_sub_type = 'Diamond 3' AND is_won = 0 THEN 1 END) AS diamond3_losses
  352. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_won = 0 THEN 1 END) AS legendary_losses
  353. , SUM(CASE WHEN resource_sub_type = 'Special Edition' AND is_won = 0 THEN 1 END) AS SE_losses
  354. , SUM(CASE WHEN is_won = 0 THEN 1 END) AS total_losses
  355. , SUM(CASE WHEN is_won = 1 THEN 1 END) AS total_wins
  356. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_won = 1 THEN 1 END) legendary_wins
  357. , SUM(CASE WHEN resource_type = 'Booster' AND resource_sub_type = 'Special Edition' AND is_won = 1 THEN 1 END) SE_wins
  358. , SUM(CASE WHEN resource_type = 'Booster' THEN resource_coin_value END) AS total_coin_value
  359. , SUM(is_on_fire_game) total_on_fire
  360. , SUM(CASE WHEN match_type = 'Rumble' THEN ((rumble_size-unique_users_in_match)/unique_users_in_match) ELSE is_rival_bot END) total_bots
  361. , SUM(board_ability1_uses + board_ability2_uses) AS total_perk_activations
  362. , SUM(CASE WHEN is_conceded = 1 AND is_won = 0 THEN 1 ELSE 0 END) conceded_matches
  363. FROM (SELECT *, MIN(derived_tstamp) OVER(partition BY calendar_entry_id) min_time
  364. , MAX(derived_tstamp) OVER(partition BY calendar_entry_id) max_time
  365. , COUNT(DISTINCT user_id) OVER(partition BY calendar_entry_id) unique_users
  366. --, COUNT(DISTINCT user_id) OVER(partition BY calendar_entry_id,max_max_prize_eligibility) unique_users_per_solo_prize
  367. , COUNT(DISTINCT user_id) OVER(partition BY match_id) unique_users_in_match
  368. , MIN(DATE(derived_tstamp)) OVER(partition BY calendar_entry_id) event_start_date
  369. , MIN(date_trunc('hour', derived_tstamp)) OVER(partition BY calendar_entry_id) event_start_ts
  370. , 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
  371. 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
  372. (SELECT resource_id, resource_coin_value FROM CANDIVORE.PROD.DIM_RESOURCE_VERSION) temp2
  373. 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())))
  374. GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16) G
  375. 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
  376.  
  377. LEFT JOIN
  378.  
  379. (SELECT A.LO_ENTRY_ID, B.lo_event_id, A.interval_date, A.lo_entry_name, A.calendar_order, A.lo_visible_on_homescreen,
  380. CASE WHEN min0 IS NULL THEN min1 ELSE min0 END lo_event_min_trophies,
  381. CASE WHEN max0 IS NULL THEN max1 ELSE max0 END lo_event_max_trophies,
  382. 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
  383. FROM
  384. ((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
  385. LEFT JOIN
  386. (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
  387. ON A.LO_ENTRY_NAME = B.LO_EVENT_ID))
  388. C
  389. ON A.lo_entry_id = C.lo_entry_id
  390.  
  391. LEFT JOIN
  392. (SELECT
  393. source_calendar_entry_id
  394. , DATE(derived_tstamp) AS interval_date
  395. , CASE
  396. WHEN trophies_cnt < 800 THEN '800'
  397. WHEN trophies_cnt<3800 THEN 'Studios'
  398. WHEN trophies_cnt<30000 THEN 'Master_League'
  399. WHEN trophies_cnt>=30000 THEN 'Legends_League'
  400. END arena_group
  401. , CASE
  402. WHEN trophies_cnt < 125 THEN '0-125'
  403. WHEN trophies_cnt < 1400 THEN '125-1400'
  404. WHEN trophies_cnt < 2000 THEN '1400-2000'
  405. WHEN trophies_cnt < 3800 THEN '2000-3800'
  406. WHEN trophies_cnt < 7000 THEN '3800-7000'
  407. WHEN trophies_cnt < 16000 THEN '7000-16000'
  408. WHEN trophies_cnt < 30000 THEN '16000-30000'
  409. WHEN trophies_cnt >= 30000 THEN '30000+'
  410. END AS trophy_group
  411. , CASE
  412. WHEN trophies_cnt < 125 THEN '0-125'
  413. WHEN trophies_cnt < 1400 THEN '125-1400'
  414. WHEN trophies_cnt < 2000 THEN '1400-2000'
  415. WHEN trophies_cnt < 3800 THEN '2000-3800'
  416. WHEN trophies_cnt < 4500 THEN '3800-4500'
  417. WHEN trophies_cnt < 5500 THEN '4500-5500'
  418. WHEN trophies_cnt < 7000 THEN '5500-7000'
  419. WHEN trophies_cnt < 10000 THEN '7000-10000'
  420. WHEN trophies_cnt < 13000 THEN '10000-13000'
  421. WHEN trophies_cnt < 16000 THEN '13000-16000'
  422. WHEN trophies_cnt < 20000 THEN '16000-20000'
  423. WHEN trophies_cnt < 30000 THEN '20000-30000'
  424. WHEN trophies_cnt >= 30000 THEN '30000+'
  425. END AS trophy_group_2
  426. , CASE WHEN total_iap_amt = 0 THEN '0'
  427. WHEN total_iap_amt <= 10 THEN 'Low'
  428. WHEN total_iap_amt <= 100 THEN 'Med'
  429. WHEN total_iap_amt <= 299 THEN 'High'
  430. WHEN total_iap_amt <= 999 THEN 'Very High'
  431. WHEN total_iap_amt >= 1000 THEN 'VIP'
  432. ELSE NULL
  433. END AS LTV_group
  434. ,SUM(points_earned) points_earned
  435. ,SUM(points_earned*event_point_value) value_earned
  436. FROM CANDIVORE.PROD.F_LIVE_EVENT_PROGRESSION WHERE action = 'points_earned' AND event_slot = 'Main' AND DATE(derived_tstamp) > DATEADD(DAY,-121,GETDATE())
  437. GROUP BY 1,2,3,4,5,6 ) D
  438. 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
  439.  
  440.  
  441.  
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!