Advertisement
YuvalGai

Untitled

Sep 8th, 2024
69
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 46.31 KB | None | 0 0
  1. select
  2. -- group by fields:
  3. segment,
  4. segment2 as user_segment,
  5. payers_segment,
  6. interval_date,
  7. lo_entry_id,
  8. match_type,
  9. LTV_group,
  10. trophy_group,
  11. arena_group,
  12. -- dimensions
  13. mode(match_type_id) match_type_id,
  14. mode(match_mode) match_mode,
  15. mode(modifier_1) modifier_1,
  16. mode(modifier_2) modifier_2,
  17. mode(match_modifier_3) match_modifier_3,
  18. mode(is_random_mixer) is_random_mixer,
  19. mode(rounds_cnt) rounds_cnt,
  20. mode(event_start_date) event_start_date,
  21. mode(min_time) min_time,
  22. mode(max_time) max_time,
  23. mode(unique_users_with_LESE_boosters) unique_users_with_LESE_boosters,
  24. mode(rumble_size) rumble_size,
  25. mode(tournament_size) tournament_size,
  26. mode(lo_event_id) lo_event_id,
  27. mode(lo_event_min_trophies) lo_event_min_trophies,
  28. mode(lo_event_max_trophies) lo_event_max_trophies,
  29. mode(calendar_order) calendar_order,
  30. mode(hours) hours,
  31. mode(lo_entry_tickets_required) lo_entry_tickets_required,
  32. mode(lo_visible_on_homescreen) lo_visible_on_homescreen,
  33. mode(lo_entry_min_booster_rarity) lo_entry_min_booster_rarity,
  34. mode(lo_entry_max_booster_rarity) lo_entry_max_booster_rarity,
  35. mode(lo_event_solo_moves_per_turn) lo_event_solo_moves_per_turn,
  36. mode(elimination_round) elimination_round,
  37. mode(lo_event_turn_duration_sec) lo_event_turn_duration_sec,
  38. mode(lo_entry_is_private) lo_entry_is_private,
  39. mode(LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE) LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE,
  40. mode(lo_event_wins_limit) lo_event_wins_limit,
  41. mode(lo_is_friendly) lo_is_friendly,
  42. mode(lo_event_max_lives) lo_event_max_lives,
  43. mode(lo_event_badge) lo_event_badge,
  44. mode(lo_entry_trophy_mode) lo_entry_trophy_mode,
  45. mode(lo_entry_trophy_gain) lo_entry_trophy_gain,
  46. mode(lo_entry_trophy_loss) lo_entry_trophy_loss,
  47. mode(unique_users_in_event_and_segment) unique_users_in_event_and_segment,
  48. -- measure fields
  49. sum(legendary_boosters_spent) legendary_boosters_spent,
  50. sum(se_boosters_spent) se_boosters_spent,
  51. sum(DIAMOND1_BOOSTERS_SPENT) DIAMOND1_BOOSTERS_SPENT,
  52. sum(DIAMOND2_BOOSTERS_SPENT) DIAMOND2_BOOSTERS_SPENT,
  53. sum(DIAMOND3_BOOSTERS_SPENT) DIAMOND3_BOOSTERS_SPENT,
  54. sum(GOLD_BOOSTERS_SPENT) GOLD_BOOSTERS_SPENT,
  55. sum(SILVER_BOOSTERS_SPENT) SILVER_BOOSTERS_SPENT,
  56. sum(BRONZE_BOOSTERS_SPENT) BRONZE_BOOSTERS_SPENT,
  57. sum(LEGENDARY_BOOSTERS_EARNED) LEGENDARY_BOOSTERS_EARNED,
  58. sum(SE_BOOSTERS_EARNED) SE_BOOSTERS_EARNED,
  59. sum(DIAMOND1_BOOSTERS_EARNED) DIAMOND1_BOOSTERS_EARNED,
  60. sum(DIAMOND2_BOOSTERS_EARNED) DIAMOND2_BOOSTERS_EARNED,
  61. sum(DIAMOND3_BOOSTERS_EARNED) DIAMOND3_BOOSTERS_EARNED,
  62. sum(GOLD_BOOSTERS_EARNED) GOLD_BOOSTERS_EARNED,
  63. sum(SILVER_BOOSTERS_EARNED) SILVER_BOOSTERS_EARNED,
  64. sum(BRONZE_BOOSTERS_EARNED) BRONZE_BOOSTERS_EARNED,
  65. sum(SHIELDS_EARNED) SHIELDS_EARNED,
  66. sum(SHIELDS_SPENT) SHIELDS_SPENT,
  67. sum(PERKS_EARNED) PERKS_EARNED,
  68. sum(PERKS_SPENT) PERKS_SPENT,
  69. sum(TICKETS_EARNED) TICKETS_EARNED,
  70. sum(TICKETS_SPENT) TICKETS_SPENT,
  71. sum(TROPHY_EARNED) TROPHY_EARNED,
  72. sum(TROPHY_SPENT) TROPHY_SPENT,
  73. sum(COINS_EARNED) COINS_EARNED,
  74. sum(COINS_SPENT) COINS_SPENT,
  75. sum(STICKERSEASONAL_EARNED) STICKERSEASONAL_EARNED,
  76. sum(STICKERSILVER_EARNED) STICKERSILVER_EARNED,
  77. sum(STICKERWHITE_EARNED) STICKERWHITE_EARNED,
  78. sum(SPIN_EARNED) SPIN_EARNED,
  79. sum(SUPERSPIN_EARNED) SUPERSPIN_EARNED,
  80. sum(STICKERADVENTURE_EARNED) STICKERADVENTURE_EARNED,
  81. sum(STICKERDIAMOND_EARNED) STICKERDIAMOND_EARNED,
  82. sum(STICKERGOLD_EARNED) STICKERGOLD_EARNED,
  83. sum(STICKERTOKEN_EARNED) STICKERTOKEN_EARNED,
  84. sum(stickerseasonal_earned_duplicate) stickerseasonal_earned_duplicate,
  85. sum(stickersilver_earned_duplicate) stickersilver_earned_duplicate,
  86. sum(stickerwhite_earned_duplicate) stickerwhite_earned_duplicate,
  87. sum(stickeradventure_earned_duplicate) stickeradventure_earned_duplicate,
  88. sum(stickerdiamond_earned_duplicate) stickerdiamond_earned_duplicate,
  89. sum(stickergold_earned_duplicate) stickergold_earned_duplicate,
  90. sum(SUPERSPIN_EARNED_VALUE) SUPERSPIN_EARNED_VALUE,
  91. sum(STICKERADVENTURE_EARNED_VALUE) STICKERADVENTURE_EARNED_VALUE,
  92. sum(STICKERDIAMOND_EARNED_VALUE) STICKERDIAMOND_EARNED_VALUE,
  93. sum(STICKERGOLD_EARNED_VALUE) STICKERGOLD_EARNED_VALUE,
  94. sum(STICKERTOKEN_EARNED_VALUE) STICKERTOKEN_EARNED_VALUE,
  95. sum(LEGENDARY_BOOSTERS_SPENT_VALUE) LEGENDARY_BOOSTERS_SPENT_VALUE,
  96. sum(SE_BOOSTERS_SPENT_VALUE) SE_BOOSTERS_SPENT_VALUE,
  97. sum(DIAMOND1_BOOSTERS_SPENT_VALUE) DIAMOND1_BOOSTERS_SPENT_VALUE,
  98. sum(DIAMOND2_BOOSTERS_SPENT_VALUE) DIAMOND2_BOOSTERS_SPENT_VALUE,
  99. sum(DIAMOND3_BOOSTERS_SPENT_VALUE) DIAMOND3_BOOSTERS_SPENT_VALUE,
  100. sum(GOLD_BOOSTERS_SPENT_VALUE) GOLD_BOOSTERS_SPENT_VALUE,
  101. sum(SILVER_BOOSTERS_SPENT_VALUE) SILVER_BOOSTERS_SPENT_VALUE,
  102. sum(BRONZE_BOOSTERS_SPENT_VALUE) BRONZE_BOOSTERS_SPENT_VALUE,
  103. sum(LEGENDARY_BOOSTERS_EARNED_VALUE) LEGENDARY_BOOSTERS_EARNED_VALUE,
  104. sum(SE_BOOSTERS_EARNED_VALUE) SE_BOOSTERS_EARNED_VALUE,
  105. sum(DIAMOND1_BOOSTERS_EARNED_VALUE) DIAMOND1_BOOSTERS_EARNED_VALUE,
  106. sum(DIAMOND2_BOOSTERS_EARNED_VALUE) DIAMOND2_BOOSTERS_EARNED_VALUE,
  107. sum(DIAMOND3_BOOSTERS_EARNED_VALUE) DIAMOND3_BOOSTERS_EARNED_VALUE,
  108. sum(GOLD_BOOSTERS_EARNED_VALUE) GOLD_BOOSTERS_EARNED_VALUE,
  109. sum(SILVER_BOOSTERS_EARNED_VALUE) SILVER_BOOSTERS_EARNED_VALUE,
  110. sum(BRONZE_BOOSTERS_EARNED_VALUE) BRONZE_BOOSTERS_EARNED_VALUE,
  111. sum(SHIELDS_EARNED_VALUE) SHIELDS_EARNED_VALUE,
  112. sum(SHIELDS_SPENT_VALUE) SHIELDS_SPENT_VALUE,
  113. sum(PERKS_EARNED_VALUE) PERKS_EARNED_VALUE,
  114. sum(PERKS_SPENT_VALUE) PERKS_SPENT_VALUE,
  115. sum(TICKETS_EARNED_VALUE) TICKETS_EARNED_VALUE,
  116. sum(TICKETS_SPENT_VALUE) TICKETS_SPENT_VALUE,
  117. sum(TROPHY_EARNED_VALUE) TROPHY_EARNED_VALUE,
  118. sum(TROPHY_SPENT_VALUE) TROPHY_SPENT_VALUE,
  119. sum(STICKERSEASONAL_EARNED_VALUE) STICKERSEASONAL_EARNED_VALUE,
  120. sum(STICKERSILVER_EARNED_VALUE) STICKERSILVER_EARNED_VALUE,
  121. sum(STICKERWHITE_EARNED_VALUE) STICKERWHITE_EARNED_VALUE,
  122. sum(SPIN_EARNED_VALUE) SPIN_EARNED_VALUE,
  123. sum(total_matches) total_matches,
  124. sum(total_seconds_played_in_segment) total_seconds_played_in_segment,
  125. sum(total_booster_activations_in_segment) total_booster_activations_in_segment,
  126. sum(total_match_score_in_segment) total_match_score_in_segment,
  127. sum(games_with_legendary_boosters) games_with_legendary_boosters,
  128. sum(games_with_SE_boosters) games_with_SE_boosters,
  129. sum(diamond_losses) diamond_losses,
  130. sum(diamond3_losses) diamond3_losses,
  131. sum(legendary_losses) legendary_losses,
  132. sum(SE_losses) SE_losses,
  133. sum(total_losses) total_losses,
  134. sum(total_wins) total_wins,
  135. sum(legendary_wins) legendary_wins,
  136. sum(SE_wins) SE_wins,
  137. sum(total_coin_value) total_coin_value,
  138. sum(total_match_ids) total_match_ids,
  139. sum(total_bots) total_bots,
  140. sum(total_on_fire) total_on_fire,
  141. sum(conceded_matches) conceded_matches,
  142. sum(total_perk_activations) total_perk_activations,
  143. sum(Match_making_found_time) Match_making_found_time,
  144. sum(total_matches_for_tournament_calc) total_matches_for_tournament_calc,
  145. sum(TWO_X_MATCHES) TWO_X_MATCHES,
  146. sum(THREE_X_MATCHES) THREE_X_MATCHES,
  147. sum(FOUR_X_MATCHES) FOUR_X_MATCHES,
  148. sum(FIVE_X_MATCHES) FIVE_X_MATCHES,
  149. sum(LE_points_earned) LE_points_earned,
  150. sum(LE_value_earned) LE_value_earned,
  151. to_timestamp(INTERVAL_DATE) as LAST_UPDATED_DT,
  152. current_timestamp() as DW_INSERT_DT
  153. from (
  154. select
  155. COALESCE(Transactions.interval_date,Matches.interval_date) interval_date,
  156. COALESCE(Transactions.user_id,Matches.user_id) user_id,
  157. COALESCE(Transactions.lo_entry_id,Matches.lo_entry_id) lo_entry_id,
  158. COALESCE(Transactions.match_type,Matches.match_type,'NA') match_type,
  159. Transactions.legendary_boosters_spent,
  160. Transactions.se_boosters_spent,
  161. nvl(Transactions.segment2,'NA') segment2,
  162. Transactions.DIAMOND1_BOOSTERS_SPENT,
  163. Transactions.DIAMOND2_BOOSTERS_SPENT,
  164. Transactions.DIAMOND3_BOOSTERS_SPENT,
  165. Transactions.GOLD_BOOSTERS_SPENT,
  166. Transactions.SILVER_BOOSTERS_SPENT,
  167. Transactions.BRONZE_BOOSTERS_SPENT,
  168. Transactions.LEGENDARY_BOOSTERS_EARNED,
  169. Transactions.SE_BOOSTERS_EARNED,
  170. Transactions.DIAMOND1_BOOSTERS_EARNED,
  171. Transactions.DIAMOND2_BOOSTERS_EARNED,
  172. Transactions.DIAMOND3_BOOSTERS_EARNED,
  173. Transactions.GOLD_BOOSTERS_EARNED,
  174. Transactions.SILVER_BOOSTERS_EARNED,
  175. Transactions.BRONZE_BOOSTERS_EARNED,
  176. Transactions.SHIELDS_EARNED,
  177. Transactions.SHIELDS_SPENT,
  178. Transactions.PERKS_EARNED,
  179. Transactions.PERKS_SPENT,
  180. Transactions.TICKETS_EARNED,
  181. Transactions.TICKETS_SPENT,
  182. Transactions.TROPHY_EARNED,
  183. Transactions.TROPHY_SPENT,
  184. Transactions.COINS_EARNED,
  185. Transactions.COINS_SPENT,
  186. Transactions.STICKERSEASONAL_EARNED,
  187. Transactions.STICKERSILVER_EARNED,
  188. Transactions.STICKERWHITE_EARNED,
  189. Transactions.SPIN_EARNED,
  190. Transactions.SUPERSPIN_EARNED,
  191. Transactions.STICKERADVENTURE_EARNED,
  192. Transactions.STICKERDIAMOND_EARNED,
  193. Transactions.STICKERGOLD_EARNED,
  194. Transactions.STICKERTOKEN_EARNED,
  195. Transactions.stickerseasonal_earned_duplicate,
  196. Transactions.stickersilver_earned_duplicate,
  197. Transactions.stickerwhite_earned_duplicate,
  198. Transactions.stickeradventure_earned_duplicate,
  199. Transactions.stickerdiamond_earned_duplicate,
  200. Transactions.stickergold_earned_duplicate,
  201. Transactions.SUPERSPIN_EARNED_VALUE,
  202. Transactions.STICKERADVENTURE_EARNED_VALUE,
  203. Transactions.STICKERDIAMOND_EARNED_VALUE,
  204. Transactions.STICKERGOLD_EARNED_VALUE,
  205. Transactions.STICKERTOKEN_EARNED_VALUE,
  206. Transactions.LEGENDARY_BOOSTERS_SPENT_VALUE,
  207. Transactions.SE_BOOSTERS_SPENT_VALUE,
  208. Transactions.DIAMOND1_BOOSTERS_SPENT_VALUE,
  209. Transactions.DIAMOND2_BOOSTERS_SPENT_VALUE,
  210. Transactions.DIAMOND3_BOOSTERS_SPENT_VALUE,
  211. Transactions.GOLD_BOOSTERS_SPENT_VALUE,
  212. Transactions.SILVER_BOOSTERS_SPENT_VALUE,
  213. Transactions.BRONZE_BOOSTERS_SPENT_VALUE,
  214. Transactions.LEGENDARY_BOOSTERS_EARNED_VALUE,
  215. Transactions.SE_BOOSTERS_EARNED_VALUE,
  216. Transactions.DIAMOND1_BOOSTERS_EARNED_VALUE,
  217. Transactions.DIAMOND2_BOOSTERS_EARNED_VALUE,
  218. Transactions.DIAMOND3_BOOSTERS_EARNED_VALUE,
  219. Transactions.GOLD_BOOSTERS_EARNED_VALUE,
  220. Transactions.SILVER_BOOSTERS_EARNED_VALUE,
  221. Transactions.BRONZE_BOOSTERS_EARNED_VALUE,
  222. Transactions.SHIELDS_EARNED_VALUE,
  223. Transactions.SHIELDS_SPENT_VALUE,
  224. Transactions.PERKS_EARNED_VALUE,
  225. Transactions.PERKS_SPENT_VALUE,
  226. Transactions.TICKETS_EARNED_VALUE,
  227. Transactions.TICKETS_SPENT_VALUE,
  228. Transactions.TROPHY_EARNED_VALUE,
  229. Transactions.TROPHY_SPENT_VALUE,
  230. Transactions.STICKERSEASONAL_EARNED_VALUE,
  231. Transactions.STICKERSILVER_EARNED_VALUE,
  232. Transactions.STICKERWHITE_EARNED_VALUE,
  233. Transactions.SPIN_EARNED_VALUE,
  234. Matches.match_type_id,
  235. Matches.match_mode,
  236. Matches.modifier_1,
  237. Matches.modifier_2,
  238. Matches.match_modifier_3,
  239. Matches.is_random_mixer,
  240. Matches.rounds_cnt,
  241. Matches.total_matches,
  242. Matches.event_start_date,
  243. Matches.total_seconds_played_in_segment,
  244. Matches.total_booster_activations_in_segment,
  245. Matches.total_match_score_in_segment,
  246. Matches.games_with_legendary_boosters,
  247. Matches.games_with_SE_boosters,
  248. Matches.diamond_losses,
  249. Matches.diamond3_losses,
  250. Matches.legendary_losses,
  251. Matches.SE_losses,
  252. Matches.total_losses,
  253. Matches.total_wins,
  254. Matches.legendary_wins,
  255. Matches.SE_wins,
  256. Matches.min_time,
  257. Matches.max_time,
  258. Matches.unique_users_with_LESE_boosters,
  259. Matches.total_coin_value,
  260. Matches.total_match_ids,
  261. Matches.rumble_size,
  262. Matches.tournament_size,
  263. Matches.total_bots,
  264. Matches.total_on_fire,
  265. Matches.conceded_matches,
  266. Matches.total_perk_activations,
  267. Matches.Match_making_found_time,
  268. Matches.total_matches_for_tournament_calc,
  269. Matches.TWO_X_MATCHES,
  270. Matches.THREE_X_MATCHES,
  271. Matches.FOUR_X_MATCHES,
  272. Matches.FIVE_X_MATCHES,
  273. Matches.LTV_group,
  274. Matches.trophy_group,
  275. Matches.arena_group,
  276. Event_Dim.lo_event_id,
  277. Event_Dim.lo_event_min_trophies,
  278. Event_Dim.lo_event_max_trophies,
  279. Event_Dim.calendar_order,
  280. Event_Dim.hours,
  281. Event_Dim.lo_entry_tickets_required,
  282. Event_Dim.lo_visible_on_homescreen,
  283. Event_Dim.lo_entry_min_booster_rarity,
  284. Event_Dim.lo_entry_max_booster_rarity,
  285. Event_Dim.lo_event_solo_moves_per_turn,
  286. Event_Dim.lo_event_turn_amt elimination_round,
  287. Event_Dim.lo_event_turn_duration_sec,
  288. Event_Dim.lo_entry_is_private,
  289. Event_Dim.LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE,
  290. Event_Dim.lo_event_wins_limit,
  291. Event_Dim.lo_is_friendly,
  292. Event_Dim.lo_event_max_lives,
  293. Event_Dim.lo_event_badge,
  294. Event_Dim.lo_entry_trophy_mode,
  295. Event_Dim.lo_entry_trophy_gain,
  296. Event_Dim.lo_entry_trophy_loss,
  297. Lo_points.points_earned LE_points_earned,
  298. Lo_points.value_earned LE_value_earned,
  299. nvl(Lo_points.segment,'NA') segment,
  300. nvl(Daily_Segments.payers_segment,'NA') payers_segment,
  301. count(distinct Transactions.user_id) over(partition by Transactions.lo_entry_id,Matches.LTV_group,Matches.trophy_group,Matches.arena_group,segment,payers_segment,segment2) unique_users_in_event_and_segment
  302. from
  303.  
  304. (SELECT
  305. DATE(derived_tstamp) interval_date,
  306. user_id,
  307. ifnull(CASE WHEN lo_entry_id = '' THEN NULL ELSE lo_entry_id END, 'NA') as lo_entry_id,
  308. CASE
  309. WHEN match_type_id = 4 OR transaction_source LIKE 'tournament%' THEN 'tournament'
  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_config = 'Rumble' THEN 'rumble'
  313. WHEN lo_event_type_config = 'AdventureMode' THEN 'adventure'
  314. WHEN lo_event_type_config LIKE 'Leaderboard%' THEN 'leaderboard'
  315. WHEN pvp_mode = 'Showdown' then 'Showdown'
  316. WHEN lo_event_type_config LIKE 'BoostersClash' THEN 'boosters_clash'
  317. WHEN match_type_id = 5 THEN 'daily'
  318. WHEN match_type_id = 2 THEN 'classic'
  319. ELSE 'other'
  320. END AS match_type,
  321. case when lo_segment in ('Default','Change') then lo_segment else null end segment2,
  322. SUM(
  323. CASE
  324. WHEN resource_sub_type = 'Legendary'
  325. AND is_received_resource = 0 THEN resource_cnt
  326. END
  327. ) AS legendary_boosters_spent,
  328. SUM(
  329. CASE
  330. WHEN resource_sub_type = 'Special Edition'
  331. AND is_received_resource = 0 THEN resource_cnt
  332. END
  333. ) AS se_boosters_spent,
  334. SUM(
  335. CASE
  336. WHEN resource_sub_type = 'Diamond 1'
  337. AND is_received_resource = 0 THEN resource_cnt
  338. END
  339. ) AS diamond1_boosters_spent,
  340. SUM(
  341. CASE
  342. WHEN resource_sub_type = 'Diamond 2'
  343. AND is_received_resource = 0 THEN resource_cnt
  344. END
  345. ) AS diamond2_boosters_spent,
  346. SUM(
  347. CASE
  348. WHEN resource_sub_type = 'Diamond 3'
  349. AND is_received_resource = 0 THEN resource_cnt
  350. END
  351. ) AS diamond3_boosters_spent,
  352. SUM(
  353. CASE
  354. WHEN resource_sub_type = 'Gold'
  355. and resource_type = 'Booster'
  356. AND is_received_resource = 0 THEN resource_cnt
  357. END
  358. ) AS gold_boosters_spent,
  359. SUM(
  360. CASE
  361. WHEN resource_sub_type = 'Silver'
  362. and resource_type = 'Booster'
  363. AND is_received_resource = 0 THEN resource_cnt
  364. END
  365. ) AS silver_boosters_spent,
  366. SUM(
  367. CASE
  368. WHEN resource_sub_type = 'Bronze'
  369. AND is_received_resource = 0 THEN resource_cnt
  370. END
  371. ) AS bronze_boosters_spent,
  372. SUM(
  373. CASE
  374. WHEN resource_sub_type = 'Legendary'
  375. AND is_received_resource = resource_cnt THEN 1
  376. END
  377. ) AS legendary_boosters_earned,
  378. SUM(
  379. CASE
  380. WHEN resource_sub_type = 'Special Edition'
  381. AND is_received_resource = resource_cnt THEN 1
  382. END
  383. ) AS se_boosters_earned,
  384. SUM(
  385. CASE
  386. WHEN resource_sub_type = 'Diamond 1'
  387. AND is_received_resource = 1 THEN resource_cnt
  388. END
  389. ) AS diamond1_boosters_earned,
  390. SUM(
  391. CASE
  392. WHEN resource_sub_type = 'Diamond 2'
  393. AND is_received_resource = 1 THEN resource_cnt
  394. END
  395. ) AS diamond2_boosters_earned,
  396. SUM(
  397. CASE
  398. WHEN resource_sub_type = 'Diamond 3'
  399. AND is_received_resource = 1 THEN resource_cnt
  400. END
  401. ) AS diamond3_boosters_earned,
  402. SUM(
  403. CASE
  404. WHEN resource_sub_type = 'Gold'
  405. and resource_type = 'Booster'
  406. AND is_received_resource = 1 THEN resource_cnt
  407. END
  408. ) AS gold_boosters_earned,
  409. SUM(
  410. CASE
  411. WHEN resource_sub_type = 'Silver'
  412. AND is_received_resource = 1 THEN resource_cnt
  413. END
  414. ) AS silver_boosters_earned,
  415. SUM(
  416. CASE
  417. WHEN resource_sub_type = 'Bronze'
  418. AND is_received_resource = 1 THEN resource_cnt
  419. END
  420. ) AS bronze_boosters_earned,
  421. SUM(
  422. CASE
  423. WHEN resource_type = 'Shield'
  424. AND is_received_resource = 1 THEN resource_cnt
  425. END
  426. ) AS shields_earned,
  427. SUM(
  428. CASE
  429. WHEN resource_type = 'Shield'
  430. AND is_received_resource = 0 THEN resource_cnt
  431. END
  432. ) AS shields_spent,
  433. SUM(
  434. CASE
  435. WHEN resource_type = 'Perk'
  436. AND is_received_resource = 1 THEN resource_cnt
  437. END
  438. ) AS perks_earned,
  439. SUM(
  440. CASE
  441. WHEN resource_type = 'Perk'
  442. AND is_received_resource = 0 THEN resource_cnt
  443. END
  444. ) AS perks_spent,
  445. SUM(
  446. CASE
  447. WHEN resource_type = 'Ticket'
  448. AND is_received_resource = 1 THEN resource_cnt
  449. END
  450. ) AS tickets_earned,
  451. SUM(
  452. CASE
  453. WHEN resource_type = 'Ticket'
  454. AND is_received_resource = 0 THEN resource_cnt
  455. END
  456. ) AS tickets_spent,
  457. SUM(
  458. CASE
  459. WHEN resource_type = 'Trophy'
  460. AND is_received_resource = 1 THEN resource_cnt
  461. END
  462. ) AS trophy_earned,
  463. SUM(
  464. CASE
  465. WHEN resource_type = 'Trophy'
  466. AND is_received_resource = 0 THEN resource_cnt
  467. END
  468. ) AS trophy_spent,
  469. SUM(
  470. CASE
  471. WHEN resource_id = 'Coin'
  472. AND is_received_resource = 1 THEN resource_cnt
  473. END
  474. ) AS coins_earned,
  475. SUM(
  476. CASE
  477. WHEN resource_id = 'Coin'
  478. AND is_received_resource = 0 THEN resource_cnt
  479. END
  480. ) AS coins_spent,
  481. sum(
  482. case
  483. when resource_id = 'StickerSeasonal'
  484. and is_received_resource = 1
  485. and resource_item_status = 'unique' then resource_cnt
  486. else 0
  487. end
  488. ) as StickerSeasonal_earned,
  489. sum(
  490. case
  491. when resource_id = 'StickerSilver'
  492. and is_received_resource = 1
  493. and resource_item_status = 'unique' then resource_cnt
  494. else 0
  495. end
  496. ) as StickerSilver_earned,
  497. sum(
  498. case
  499. when resource_id = 'StickerWhite'
  500. and is_received_resource = 1
  501. and resource_item_status = 'unique' then resource_cnt
  502. else 0
  503. end
  504. ) as StickerWhite_earned,
  505. sum(
  506. case
  507. when resource_id = 'StickerAdventure'
  508. and is_received_resource = 1
  509. and resource_item_status = 'unique' then resource_cnt
  510. end
  511. ) as StickerAdventure_earned,
  512. sum(
  513. case
  514. when resource_id = 'StickerDiamond'
  515. and is_received_resource = 1
  516. and resource_item_status = 'unique' then resource_cnt
  517. end
  518. ) as StickerDiamond_earned,
  519. sum(
  520. case
  521. when resource_id = 'StickerGold'
  522. and is_received_resource = 1
  523. and resource_item_status = 'unique' then resource_cnt
  524. end
  525. ) as StickerGold_earned,
  526. sum(
  527. case
  528. when resource_id = 'StickerSeasonal'
  529. and is_received_resource = 1
  530. and resource_item_status = 'duplicate' then resource_cnt
  531. else 0
  532. end
  533. ) as StickerSeasonal_earned_duplicate,
  534. sum(
  535. case
  536. when resource_id = 'StickerSilver'
  537. and is_received_resource = 1
  538. and resource_item_status = 'duplicate' then resource_cnt
  539. else 0
  540. end
  541. ) as StickerSilver_earned_duplicate,
  542. sum(
  543. case
  544. when resource_id = 'StickerWhite'
  545. and is_received_resource = 1
  546. and resource_item_status = 'duplicate' then resource_cnt
  547. else 0
  548. end
  549. ) as StickerWhite_earned_duplicate,
  550. sum(
  551. case
  552. when resource_id = 'StickerAdventure'
  553. and is_received_resource = 1
  554. and resource_item_status = 'duplicate' then resource_cnt
  555. end
  556. ) as StickerAdventure_earned_duplicate,
  557. sum(
  558. case
  559. when resource_id = 'StickerDiamond'
  560. and is_received_resource = 1
  561. and resource_item_status = 'duplicate' then resource_cnt
  562. end
  563. ) as StickerDiamond_earned_duplicate,
  564. sum(
  565. case
  566. when resource_id = 'StickerGold'
  567. and is_received_resource = 1
  568. and resource_item_status = 'duplicate' then resource_cnt
  569. end
  570. ) as StickerGold_earned_duplicate,
  571. sum(
  572. case
  573. when resource_id = 'Spin'
  574. and is_received_resource = 1 then resource_cnt
  575. else 0
  576. end
  577. ) as Spin_earned,
  578. sum(
  579. case
  580. when resource_id = 'SuperSpin'
  581. and is_received_resource = 1 then resource_cnt
  582. end
  583. ) as SuperSpin_earned,
  584. sum(
  585. case
  586. when resource_id = 'StickerToken'
  587. and is_received_resource = 1 then resource_cnt
  588. end
  589. ) as StickerToken_earned,
  590. sum(
  591. case
  592. when resource_id = 'SuperSpin'
  593. and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
  594. end
  595. ) as SuperSpin_earned_value,
  596. sum(
  597. case
  598. when resource_id = 'StickerAdventure'
  599. and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
  600. end
  601. ) as StickerAdventure_earned_value,
  602. sum(
  603. case
  604. when resource_id = 'StickerDiamond'
  605. and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
  606. end
  607. ) as StickerDiamond_earned_value,
  608. sum(
  609. case
  610. when resource_id = 'StickerGold'
  611. and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
  612. end
  613. ) as StickerGold_earned_value,
  614. sum(
  615. case
  616. when resource_id = 'StickerToken'
  617. and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
  618. end
  619. ) as StickerToken_earned_value,
  620. SUM(
  621. CASE
  622. WHEN resource_sub_type = 'Legendary'
  623. AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
  624. END
  625. ) AS legendary_boosters_spent_value,
  626. SUM(
  627. CASE
  628. WHEN resource_sub_type = 'Special Edition'
  629. AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
  630. END
  631. ) AS se_boosters_spent_value,
  632. SUM(
  633. CASE
  634. WHEN resource_sub_type = 'Diamond 1'
  635. AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
  636. END
  637. ) AS diamond1_boosters_spent_value,
  638. SUM(
  639. CASE
  640. WHEN resource_sub_type = 'Diamond 2'
  641. AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
  642. END
  643. ) AS diamond2_boosters_spent_value,
  644. SUM(
  645. CASE
  646. WHEN resource_sub_type = 'Diamond 3'
  647. AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
  648. END
  649. ) AS diamond3_boosters_spent_value,
  650. SUM(
  651. CASE
  652. WHEN resource_sub_type = 'Gold'
  653. and resource_type = 'Booster'
  654. AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
  655. END
  656. ) AS gold_boosters_spent_value,
  657. SUM(
  658. CASE
  659. WHEN resource_sub_type = 'Silver'
  660. AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
  661. END
  662. ) AS silver_boosters_spent_value,
  663. SUM(
  664. CASE
  665. WHEN resource_sub_type = 'Bronze'
  666. AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
  667. END
  668. ) AS bronze_boosters_spent_value,
  669. SUM(
  670. CASE
  671. WHEN resource_sub_type = 'Legendary'
  672. AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
  673. END
  674. ) AS legendary_boosters_earned_value,
  675. SUM(
  676. CASE
  677. WHEN resource_sub_type = 'Special Edition'
  678. AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
  679. END
  680. ) AS se_boosters_earned_value,
  681. SUM(
  682. CASE
  683. WHEN resource_sub_type = 'Diamond 1'
  684. AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
  685. END
  686. ) AS diamond1_boosters_earned_value,
  687. SUM(
  688. CASE
  689. WHEN resource_sub_type = 'Diamond 2'
  690. AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
  691. END
  692. ) AS diamond2_boosters_earned_value,
  693. SUM(
  694. CASE
  695. WHEN resource_sub_type = 'Diamond 3'
  696. AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
  697. END
  698. ) AS diamond3_boosters_earned_value,
  699. SUM(
  700. CASE
  701. WHEN resource_sub_type = 'Gold'
  702. and resource_type = 'Booster'
  703. AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
  704. END
  705. ) AS gold_boosters_earned_value,
  706. SUM(
  707. CASE
  708. WHEN resource_sub_type = 'Silver'
  709. AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
  710. END
  711. ) AS silver_boosters_earned_value,
  712. SUM(
  713. CASE
  714. WHEN resource_sub_type = 'Bronze'
  715. AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
  716. END
  717. ) AS bronze_boosters_earned_value,
  718. SUM(
  719. CASE
  720. WHEN resource_type = 'Shield'
  721. AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
  722. END
  723. ) AS shields_earned_value,
  724. SUM(
  725. CASE
  726. WHEN resource_type = 'Shield'
  727. AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
  728. END
  729. ) AS shields_spent_value,
  730. SUM(
  731. CASE
  732. WHEN resource_type = 'Perk' and resource_sub_type = 'Paid'
  733. AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
  734. END
  735. ) AS perks_earned_value,
  736. SUM(
  737. CASE
  738. WHEN resource_type = 'Perk' and resource_sub_type = 'Paid'
  739. AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
  740. END
  741. ) AS perks_spent_value,
  742. SUM(
  743. CASE
  744. WHEN resource_type = 'Ticket'
  745. AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
  746. END
  747. ) AS tickets_earned_value,
  748. SUM(
  749. CASE
  750. WHEN resource_type = 'Ticket'
  751. AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
  752. END
  753. ) AS tickets_spent_value,
  754. SUM(
  755. CASE
  756. WHEN resource_type = 'Trophy'
  757. AND is_received_resource = 1 THEN resource_cnt * resource_coin_value_calculated
  758. END
  759. ) AS trophy_earned_value,
  760. SUM(
  761. CASE
  762. WHEN resource_type = 'Trophy'
  763. AND is_received_resource = 0 THEN resource_cnt * resource_coin_value_calculated
  764. END
  765. ) AS trophy_spent_value,
  766. sum(
  767. case
  768. when resource_id = 'StickerSeasonal'
  769. and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
  770. else 0
  771. end
  772. ) as StickerSeasonal_earned_value,
  773. sum(
  774. case
  775. when resource_id = 'StickerSilver'
  776. and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
  777. else 0
  778. end
  779. ) as StickerSilver_earned_value,
  780. sum(
  781. case
  782. when resource_id = 'StickerWhite'
  783. and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
  784. else 0
  785. end
  786. ) as StickerWhite_earned_value,
  787. sum(
  788. case
  789. when resource_id = 'Spin'
  790. and is_received_resource = 1 then resource_cnt * resource_coin_value_calculated
  791. else 0
  792. end
  793. ) as Spin_earned_value
  794. FROM MATCH_MASTERS.PROD.F_USER_RESOURCE_TRANSACTION
  795. WHERE
  796. (match_type_id > 0 OR transaction_source = 'live_event')
  797. and (
  798. transaction_source in('live_event','match_end','disconnection_refund','match_start')
  799. OR transaction_source LIKE 'tournament%'
  800. )
  801. AND DATE(derived_tstamp) >= current_date - 91
  802. GROUP BY ALL -- (5)
  803. ) Transactions
  804. full outer JOIN
  805. (SELECT
  806. date(derived_tstamp) interval_date
  807. ,user_id
  808. ,ifnull(calendar_entry_id, 'NA') lo_entry_id
  809. ,CASE
  810. WHEN lo_event_id LIKE '%mutation%' THEN 'solo_mutation'
  811. WHEN lo_event_type = 'Rumble' THEN 'rumble'
  812. WHEN lo_event_type = 'AdventureMode' THEN 'adventure'
  813. WHEN lo_event_type = 'Onboarding' THEN 'onboarding'
  814. WHEN lo_event_id LIKE 'Solo%' OR lo_event_id LIKE 'solo%' THEN 'solo'
  815. WHEN pvp_mode = 'Showdown' then 'Showdown'
  816. WHEN lo_event_type LIKE 'Leaderboard%' THEN 'leaderboard'
  817. WHEN lo_event_type LIKE 'BoostersClash' THEN 'boosters_clash'
  818. WHEN match_type_id = 4 THEN 'tournament'
  819. WHEN match_type_id = 5 THEN 'daily'
  820. WHEN match_type_id = 2 THEN 'classic'
  821. ELSE match_type
  822. END AS match_type
  823. ,CASE
  824. WHEN mode(trophies_cnt) < 800 THEN '800'
  825. WHEN mode(trophies_cnt) < 3800 THEN 'Studios'
  826. WHEN mode(trophies_cnt) < 30000 THEN 'Master_League'
  827. WHEN mode(trophies_cnt) >= 30000 THEN 'Legends_League'
  828. END arena_group
  829. ,CASE
  830. WHEN mode(trophies_cnt) < 1500 THEN '0-1500'
  831. WHEN mode(trophies_cnt) < 3800 THEN '1500-3800'
  832. WHEN mode(trophies_cnt) < 8000 THEN '3800-8000'
  833. WHEN mode(trophies_cnt) < 17000 THEN '8000-17000'
  834. WHEN mode(trophies_cnt) < 30000 THEN '17000-30000'
  835. ELSE '30000+'
  836. END AS trophy_group,
  837. mode(LTV_group) LTV_group,
  838. mode(unique_users) unique_users,
  839. mode(unique_users_with_LESE_boosters) unique_users_with_LESE_boosters,
  840. min(event_start_date) event_start_date,
  841. mode(match_type_id) match_type_id,
  842. mode(tournament_size) tournament_size,
  843. min(min_time) min_time,
  844. min(max_time) max_time,
  845. mode(rumble_size) rumble_size,
  846. mode(rounds_cnt) rounds_cnt,
  847. mode(is_random_mixer) is_random_mixer,
  848. mode(modifier_1) modifier_1,
  849. mode(modifier_2) modifier_2,
  850. mode(ifnull(match_modifier_3, '')) match_modifier_3,
  851. CASE
  852. WHEN mode(is_random_mixer) = TRUE
  853. OR mode(match_type_id) = 2 THEN 'random'
  854. ELSE mode(match_mode)
  855. END match_mode,
  856. --, max_max_prize_eligibility
  857. --, mode(unique_users_per_solo_prize) unique_users_per_solo_prize
  858. sum(match_making_found_time) as match_making_found_time,
  859. sum(
  860. case
  861. when current_tournament_stage = 0
  862. or current_tournament_stage is null then 1
  863. end
  864. ) as TOTAL_MATCHES_FOR_TOURNAMENT_CALC,
  865. COUNT(*) AS total_matches,
  866. SUM(
  867. CASE
  868. WHEN is_conceded = 0 THEN 1
  869. ELSE 0
  870. END
  871. ) unconceded_matches,
  872. COUNT(DISTINCT match_id) AS total_match_ids,
  873. SUM(MATCH_DURATION_SEC) AS total_seconds_played_in_segment,
  874. SUM(
  875. CASE
  876. WHEN is_conceded = 0 THEN MATCH_DURATION_SEC
  877. ELSE 0
  878. END
  879. ) AS total_seconds_played_in_segment_unconceded,
  880. SUM(ability_activated_cnt) AS total_booster_activations_in_segment,
  881. SUM(
  882. CASE
  883. WHEN is_conceded = 0 THEN ability_activated_cnt
  884. ELSE 0
  885. END
  886. ) AS total_booster_activations_in_segment_unconceded,
  887. SUM(match_score) AS total_match_score_in_segment,
  888. SUM(
  889. CASE
  890. WHEN is_conceded = 0 THEN match_score
  891. ELSE 0
  892. END
  893. ) AS total_match_score_in_segment_unconceded,
  894. SUM(
  895. CASE
  896. WHEN resource_sub_type = 'Legendary' THEN 1
  897. END
  898. ) AS games_with_legendary_boosters,
  899. SUM(
  900. CASE
  901. WHEN resource_type = 'Booster'
  902. AND resource_sub_type = 'Special Edition' THEN 1
  903. END
  904. ) AS games_with_SE_boosters,
  905. SUM(
  906. CASE
  907. WHEN booster_tier = 'Diamond'
  908. AND is_won = 0 THEN 1
  909. END
  910. ) AS diamond_losses,
  911. SUM(
  912. CASE
  913. WHEN resource_sub_type = 'Diamond 3'
  914. AND is_won = 0 THEN 1
  915. END
  916. ) AS diamond3_losses,
  917. SUM(
  918. CASE
  919. WHEN resource_sub_type = 'Legendary'
  920. AND is_won = 0 THEN 1
  921. END
  922. ) AS legendary_losses,
  923. SUM(
  924. CASE
  925. WHEN resource_sub_type = 'Special Edition'
  926. AND is_won = 0 THEN 1
  927. END
  928. ) AS SE_losses,
  929. SUM(
  930. CASE
  931. WHEN is_won = 0 THEN 1
  932. END
  933. ) AS total_losses,
  934. SUM(is_won) AS total_wins,
  935. SUM(
  936. CASE
  937. WHEN resource_sub_type = 'Legendary'
  938. THEN is_won
  939. END
  940. ) legendary_wins,
  941. SUM(
  942. CASE
  943. WHEN resource_type = 'Booster'
  944. AND resource_sub_type = 'Special Edition'
  945. THEN is_won
  946. END
  947. ) SE_wins,
  948. SUM(
  949. CASE
  950. WHEN resource_type = 'Booster'
  951. and (
  952. current_tournament_stage = 0
  953. or current_tournament_stage is null
  954. ) THEN Booster_value
  955. END
  956. ) AS total_coin_value,
  957. SUM(is_on_fire_game) total_on_fire,
  958. SUM(
  959. CASE
  960. WHEN match_type = 'Rumble' THEN (
  961. (rumble_size - unique_users_in_match) / unique_users_in_match
  962. )
  963. ELSE is_rival_bot
  964. END
  965. ) total_bots,
  966. SUM(board_ability1_uses + board_ability2_uses) AS total_perk_activations,
  967. SUM(
  968. CASE
  969. WHEN is_conceded = 1
  970. AND is_won = 0 THEN 1
  971. ELSE 0
  972. END
  973. ) conceded_matches,
  974. SUM(
  975. case
  976. when stakes_multiplier = 2 then 1
  977. end
  978. ) TWO_X_MATCHES,
  979. SUM(
  980. case
  981. when stakes_multiplier = 3 then 1
  982. end
  983. ) THREE_X_MATCHES,
  984. SUM(
  985. case
  986. when stakes_multiplier = 4 then 1
  987. end
  988. ) FOUR_X_MATCHES,
  989. SUM(
  990. case
  991. when stakes_multiplier = 5 then 1
  992. end
  993. ) FIVE_X_MATCHES
  994. FROM
  995. (
  996. SELECT
  997. *,
  998. MIN(derived_tstamp) OVER(partition BY calendar_entry_id) min_time,
  999. MAX(derived_tstamp) OVER(partition BY calendar_entry_id) max_time,
  1000. COUNT(DISTINCT user_id) OVER(partition BY calendar_entry_id) unique_users --, COUNT(DISTINCT user_id) OVER(partition BY calendar_entry_id,max_max_prize_eligibility) unique_users_per_solo_prize
  1001. ,
  1002. COUNT(DISTINCT user_id) OVER(partition BY match_id) unique_users_in_match,
  1003. MIN(DATE(derived_tstamp)) OVER(partition BY calendar_entry_id) event_start_date,
  1004. MIN(date_trunc('hour', derived_tstamp)) OVER(partition BY calendar_entry_id) event_start_ts,
  1005. COUNT(
  1006. DISTINCT CASE
  1007. WHEN resource_sub_type = 'Legendary'
  1008. OR resource_sub_type = 'Special Edition' THEN user_id
  1009. END
  1010. ) OVER(partition BY calendar_entry_id) unique_users_with_LESE_boosters
  1011. FROM
  1012. (
  1013. SELECT
  1014. *,
  1015. MAX(max_prize_eligibility) OVER(partition BY calendar_entry_id, user_id) max_max_prize_eligibility
  1016. FROM
  1017. MATCH_MASTERS.PROD.F_USER_MATCH WHERE DATE(derived_tstamp) >= current_date - 91
  1018. )
  1019. )
  1020. GROUP BY ALL ) Matches
  1021. ON Transactions.interval_date = Matches.interval_date and Transactions.user_id = Matches.user_id and Transactions.lo_entry_id = Matches.lo_entry_id and Transactions.match_type = Matches.match_type
  1022. LEFT JOIN
  1023. (SELECT
  1024. A.LO_ENTRY_ID,
  1025. B.lo_event_id,
  1026. A.interval_date,
  1027. A.lo_entry_name,
  1028. A.calendar_order,
  1029. A.lo_visible_on_homescreen,
  1030. nvl(min0,min1) lo_event_min_trophies,
  1031. nvl(max0,max1) lo_event_max_trophies,
  1032. hours,
  1033. lo_entry_tickets_required,
  1034. lo_entry_min_booster_rarity,
  1035. lo_entry_max_booster_rarity,
  1036. lo_event_solo_moves_per_turn,
  1037. lo_event_badge,
  1038. lo_event_turn_amt,
  1039. lo_event_turn_duration_sec,
  1040. lo_entry_is_private,
  1041. lo_event_wins_limit,
  1042. LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE,
  1043. lo_is_friendly,
  1044. lo_entry_trophy_mode,
  1045. lo_entry_trophy_gain,
  1046. lo_entry_trophy_loss,
  1047. B.lo_event_max_lives
  1048. FROM
  1049. (
  1050. (
  1051. SELECT
  1052. nvl(LO_ENTRY_ID,lo_original_entry_id) LO_ENTRY_ID,
  1053. DATE(derived_tstamp) interval_date,
  1054. LO_ENTRY_NAME,
  1055. calendar_order,
  1056. lo_visible_on_homescreen,
  1057. lo_entry_min_trophies min0,
  1058. lo_entry_max_trophies max0,
  1059. round(
  1060. (lo_entry_end_ts - lo_entry_start_ts) / 60 / 60,
  1061. 0
  1062. ) hours,
  1063. lo_entry_tickets_required,
  1064. lo_entry_min_booster_rarity,
  1065. lo_entry_max_booster_rarity,
  1066. lo_entry_is_private,
  1067. lo_entry_trophy_mode,
  1068. lo_entry_trophy_gain,
  1069. lo_entry_trophy_loss,
  1070. LO_ENTRY_IS_PREMIUM_PASS_EXCLUSIVE
  1071. FROM
  1072. (
  1073. SELECT
  1074. *
  1075. ,ifnull(LO_ENTRY_UPDATE_TS,LO_ENTRY_TS) lo_entry_ts2
  1076. FROM
  1077. MATCH_MASTERS.PROD.F_LIVEOPS_CALENDAR
  1078. qualify ROW_NUMBER() OVER (
  1079. partition BY lo_entry_id
  1080. ORDER BY
  1081. lo_entry_ts2 DESC
  1082. ) = 1
  1083. )
  1084. ) A
  1085. LEFT JOIN (
  1086. SELECT
  1087. DATE(derived_tstamp) interval_date,
  1088. lo_event_id,
  1089. lo_event_min_trophies min1,
  1090. lo_event_max_trophies max1,
  1091. lo_event_solo_moves_per_turn,
  1092. lo_event_turn_amt,
  1093. lo_event_turn_duration_sec,
  1094. lo_event_wins_limit,
  1095. lo_is_friendly,
  1096. lo_event_badge,
  1097. lo_event_max_lives
  1098. FROM
  1099. MATCH_MASTERS.PROD.DIM_EVENT_CONFIG
  1100. qualify ROW_NUMBER() OVER (
  1101. partition BY LO_EVENT_ID
  1102. ORDER BY
  1103. derived_tstamp DESC
  1104. ) = 1
  1105. ) B ON A.LO_ENTRY_NAME = B.LO_EVENT_ID
  1106. ) QUALIFY ROW_NUMBER() OVER (
  1107. PARTITION by A.LO_ENTRY_ID
  1108. ORDER BY
  1109. A.interval_date desc
  1110. ) = 1
  1111. ) Event_Dim
  1112. ON coalesce(Transactions.lo_entry_id,Matches.lo_entry_id) = Event_Dim.lo_entry_id
  1113. LEFT JOIN
  1114. ( SELECT
  1115. source_calendar_entry_id,
  1116. DATE(derived_tstamp) AS interval_date,
  1117. user_id,
  1118. nvl(mode(segment),'No-Segment') segment,
  1119. SUM(points_earned) points_earned,
  1120. SUM(points_earned * event_point_value) value_earned
  1121. FROM
  1122. MATCH_MASTERS.PROD.F_LIVE_EVENT_PROGRESSION
  1123. WHERE
  1124. action = 'points_earned'
  1125. AND DATE(derived_tstamp) >= current_date - 91
  1126. GROUP BY all ) Lo_points
  1127. ON COALESCE(Transactions.interval_date,Matches.interval_date) = Lo_points.interval_date and COALESCE(Transactions.user_id,Matches.user_id) = Lo_points.user_id and coalesce(Transactions.lo_entry_id,Matches.lo_entry_id) = Lo_points.source_calendar_entry_id
  1128. LEFT JOIN
  1129. (select interval_date,user_id,payers_segment from MATCH_MASTERS.prod.daily_users_from_params where interval_date >= current_date - 91
  1130. ) Daily_Segments
  1131. ON COALESCE(Transactions.user_id,Matches.user_id) = Daily_Segments.user_id and COALESCE(Transactions.interval_date,Matches.interval_date) = Daily_Segments.interval_date
  1132. )
  1133. WHERE 1 = 1
  1134. AND interval_date is not null
  1135. AND interval_date >= current_date - 91
  1136. GROUP BY ALL
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement