API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Sep 17th, 2024
73
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 66.72 KB | None | 0 0
raw download clone embed print report
  1. f_user_match = {
  2. '01_truncate_transformation_table': '''
  3. truncate MATCH_MASTERS.TRANSFORMATION.TRANS_USER_MATCH
  4. ''',
  5. '02_set_from_update_variable': '''
  6. set FROM_USER_MATCH_UPDATED_DT_MNG = (
  7. SELECT
  8. NVL(max(LAST_LOAD_DATE), to_timestamp('2023-12-25')) as LAST_UPDATED_DT_MNG
  9. FROM
  10. MATCH_MASTERS.manage.management_table
  11. WHERE
  12. 1 = 1
  13. AND table_name = 'CANDIVORE.PROD.F_USER_MATCH'
  14. ORDER BY
  15. ID DESC
  16. LIMIT
  17. 1
  18. );
  19.  
  20. ''',
  21. '03_set_to_update_variable': '''
  22. set TO_USER_MATCH_UPDATED_DT_MNG = (
  23. SELECT DATEADD(min,-1,current_timestamp())
  24. );
  25. ''',
  26. '04_insert_delta_into_trans_table': '''
  27. insert into MATCH_MASTERS.TRANSFORMATION.TRANS_USER_MATCH (
  28. EVENT_ID
  29. ,DERIVED_TSTAMP
  30. ,MATCH_END_DATE
  31. ,USER_ID
  32. ,MATCH_SESSION_ID
  33. ,IS_SUBSCRIBER
  34. ,SUBSCRIPTION_TIER
  35. ,SUBSCRIPTION_TIER_S
  36. ,ARENA_GROUP
  37. ,IS_PAYER
  38. ,SENIORITY_BIN
  39. ,LTV_GROUP
  40. ,ENGAGEMENT_GROUP
  41. ,MATCH_ID
  42. ,PVP_MODE
  43. ,REMATCH_CNT
  44. ,MATCH_TYPE_ID
  45. ,MATCH_TYPE
  46. ,MATCH_SUB_TYPE
  47. ,MATCH_MODE
  48. ,MODIFIER_1
  49. ,MODIFIER_2
  50. ,CALENDAR_ENTRY_ID
  51. ,ORIGINAL_CALENDAR_ENTRY_ID
  52. ,DYNAMIC_CONFIG_ID
  53. ,CONFIG_ID
  54. ,LO_EVENT_ID
  55. ,LEADERBOARD_CONFIG_TYPE
  56. ,EVENT_SEGMENT
  57. ,EVENT_SEGMENT_LOGIC
  58. ,IS_PUSH_NOTIFICATION_ENABLE
  59. ,IS_RANKED
  60. ,TOURNAMENT_UI_ORDER
  61. ,TOURNAMENT_SIZE
  62. ,TOURNAMENT_ENTRY_COIN_PRICE
  63. ,TOURNAMENT_MN_BOOSTER_RARITY
  64. ,TOURNAMENT_MX_BOOSTER_RARITY
  65. ,calendar_order
  66. ,lo_entry_is_premium_pass_exclusive
  67. ,lo_limited_boosters
  68. ,lo_limited_boosters_are_whitelist
  69. ,BOOSTER_TIER_ID
  70. ,BOOSTER_TIER
  71. ,RESOURCE_TYPE_ID
  72. ,RESOURCE_TYPE
  73. ,RESOURCE_SUB_TYPE_ID
  74. ,RESOURCE_SUB_TYPE
  75. ,RESOURCE_ID
  76. ,BOOSTER_NAME
  77. ,BOOSTER_RESOURCE_VERSION
  78. ,BOOSTER_VALUE
  79. ,ABILITY_ACTIVATED_CNT
  80. ,ABILITY_FINAL_CHARGE_CNT
  81. ,RIVAL_RESOURCE_TYPE
  82. ,RIVAL_RESOURCE_SUB_TYPE
  83. ,MATCH_DURATION_SEC
  84. ,IS_BOT
  85. ,IS_RIVAL_BOT
  86. ,IS_WON
  87. ,IS_TECHNICAL
  88. ,IS_REMATCH
  89. ,IS_DEVELOPER
  90. ,IS_CONCEDED
  91. ,TURN_INDEX
  92. ,ROUNDS_CNT
  93. ,MATCH_SCORE
  94. ,IS_ON_FIRE_GAME
  95. ,IS_REACHED_ON_FIRE
  96. ,COINS_WON_AMT
  97. ,TROPHIES_WON_AMT
  98. ,WIN_STREAK_CNT
  99. ,LOSE_STREAK_CNT
  100. ,RESHUFFLE_CNT
  101. ,EXTRA_MOVES_CNT
  102. ,SPECIAL_EXPLODES_CNT
  103. ,RED_COMBOS_CNT
  104. ,GREEN_COMBOS_CNT
  105. ,BLUE_COMBOS_CNT
  106. ,YELLOW_COMBOS_CNT
  107. ,ORANGE_COMBOS_CNT
  108. ,PURPLE_COMBOS_CNT
  109. ,WHITE_COMBOS_CNT
  110. ,THREE_MATCHED_CNT
  111. ,FOUR_MATCHED_CNT
  112. ,FIVE_MATCHED_CNT
  113. ,RSHAPED_MATCHED_CNT
  114. ,BEST_MOVE_SCORE
  115. ,TIMEBANK_LEFT
  116. ,STARS_WON_AMT
  117. ,PREVIOUS_TROPHIES_BALANCE
  118. ,MATCH_BONUS_TROPHIES_CNT
  119. ,MATCH_SKILL_RATING
  120. ,RIVAL_USER_ID
  121. ,RIVAL_SESSION_ID
  122. ,RIVAL_BOOSTER_NAME
  123. ,RIVAL_BOOSTER_VERSION
  124. ,RIVAL_TROPHIES_BALANCE
  125. ,RIVAL_WIN_RATE
  126. ,ARENA_INDEX
  127. ,MAX_PRIZE_ELIGIBILITY
  128. ,MAX_PRIZE_WON
  129. ,MOVES_LIMIT
  130. ,MOVES_MADE
  131. ,MOVES_LEFT
  132. ,MATCH_RANK
  133. ,ELIMINATION_ROUND
  134. ,LEVELS
  135. ,CS_RESYNCS
  136. ,INVALID_SWAPS
  137. ,ERROR_RESYNCS
  138. ,RIVAL_ERROR_RESYNCS
  139. ,ACTIVE_STYLE_PACK
  140. ,SHOW_OFF_BADGE_ID
  141. ,IN_GAME_SHOWN_TIME
  142. ,FIRST_ROUND_SHOWN_TIME
  143. ,TIMEBANK_SHOWN_RIVAL
  144. ,CONTEXT_SWITCHES
  145. ,INVALID_SWIPES
  146. ,EMOTE_SENT
  147. ,RIVAL_EMOTE_SENT
  148. ,MATCH_MAKING_FOUND_TIME
  149. ,TIMEBANK_SHOWN_PLAYER_AMT
  150. ,RIVAL_PERK_1_RESOURCE_NAME
  151. ,RIVAL_PERK_2_RESOURCE_NAME
  152. ,RIVAL_MATCH_SCORE
  153. ,RIVAL_AI_LEVEL
  154. ,RIVAL_PLAYED_OUT_OF_TIME
  155. ,TOTAL_RIVAL_PLAYED_OUT_OF_TIME_SEC
  156. ,LO_EVENT_TYPE
  157. ,LO_EVENT_CONFIG_ID
  158. ,USER_NAME
  159. ,COUNTRY
  160. ,LANGUAGE
  161. ,DEVICE_LANGUAGE
  162. ,TEST_NAME
  163. ,TEST_GROUP_NAME
  164. ,FB_USER_ID
  165. ,CLIENT_IP
  166. ,IS_TESTING_USER
  167. ,DEVICE_MODEL
  168. ,DEVICE_OS
  169. ,PLATFORM
  170. ,MEDIA_SOURCE
  171. ,APP_VERSION
  172. ,APP_MINOR_VERSION
  173. ,FIRST_INSTALL_DT
  174. ,ADVERTISER_ID
  175. ,APPSFLYER_ID
  176. ,AD_NAME
  177. ,CAMPAIGN_NAME
  178. ,TOTAL_IAP_AMT
  179. ,STARS_CNT
  180. ,LT_MATCHES_PLAYED_CNT
  181. ,LT_MATCHES_WON_CNT
  182. ,LT_PURCHASES_AMT
  183. ,EVENT_SOURCE
  184. ,IS_SHIELDED_GAME
  185. ,RUMBLE_SIZE
  186. ,CURRENT_ARENA_INDEX
  187. ,IS_RANDOM_MIXER
  188. ,AD_ID
  189. ,ADSET
  190. ,ADSET_ID
  191. ,CAMPAIGN_ID
  192. ,INFLUENCER_NAME
  193. ,TEAM_ID
  194. ,TROPHIES_CNT
  195. ,PERK_1_RESOURCE_NAME
  196. ,PERK_2_RESOURCE_NAME
  197. ,AF_KEYWORDS
  198. ,SITE_ID
  199. ,TEAM_NAME
  200. ,BOARD_ABILITY1_USES
  201. ,BOARD_ABILITY2_USES
  202. ,DEVICE_ID_HASH
  203. ,IS_PRIVATE
  204. ,LIVE_EVENT_PIECES_COLLECTED
  205. ,MUTATIONS_PICK
  206. ,MUTATION_OPTIONS
  207. ,OUTFIT_ID
  208. ,LIVE_EVENT_PIECES_SPAWNED
  209. ,LIVE_EVENT_PIECES_ELIGIBLE
  210. ,MATCH_MODIFIER_3
  211. ,IS_MC_MATCH
  212. ,LO_IS_FRIENDLY
  213. ,TOURNAMENT_ID
  214. ,CURRENT_TOURNAMENT_STAGE
  215.  
  216. ,STAKES_MULTIPLIER
  217. ,ELO
  218. ,USED_BOOSTER_BALANCE
  219. ,BOOSTERS_LOST_AMOUNT
  220. ,RIVAL_ELO
  221. ,TIMEUPS_CNT
  222. ,LAST_UPDATED_DT
  223. ,DW_INSERT_DT
  224. ,bracket_count
  225. ,bracket_index
  226. ,premium_pass_end_time
  227. ,relative_SR
  228. ,premium_pass_active
  229. ,rival_booster_tier_id
  230. ,rival_booster_tier
  231. ,ai_analyzer_type
  232. ,ai_bad_play_target
  233. ,rival_ai_analyzer_type
  234. ,rival_ai_bad_play_target
  235. ,app_original_version
  236. ,square_shapes_cnt
  237. ,segment
  238. ,PREVIOUS_ELO_BALANCE
  239. ,elo_games
  240. ,rumble_type
  241. ,current_round
  242. ,initial_points
  243. ,rival_initial_points
  244. ,rival_moves_left
  245.  
  246. )
  247. SELECT DISTINCT
  248. RN_UM.EVENT_ID,
  249. RN_UM.DERIVED_TSTAMP,
  250. RN_UM.MATCH_END_DATE,
  251. RN_UM.USER_ID,
  252. RN_UM.MATCH_SESSION_ID,
  253. RN_UM.IS_SUBSCRIBER,
  254. RN_UM.SUBSCRIPTION_TIER,
  255. RN_UM.SUBSCRIPTION_TIER_S,
  256. RN_UM.ARENA_GROUP,
  257. RN_UM.IS_PAYER,
  258. RN_UM.SENIORITY_BIN,
  259. RN_UM.LTV_GROUP,
  260. RN_UM.ENGAGEMENT_GROUP,
  261. RN_UM.MATCH_ID,
  262. RN_UM.PVP_MODE,
  263. RN_UM.REMATCH_CNT,
  264. RN_UM.MATCH_TYPE_ID,
  265. RN_UM.MATCH_TYPE,
  266. RN_UM.MATCH_SUB_TYPE,
  267. RN_UM.MATCH_MODE,
  268. RN_UM.MODIFIER_1,
  269. RN_UM.MODIFIER_2,
  270. RN_UM.CALENDAR_ENTRY_ID,
  271. RN_UM.ORIGINAL_CALENDAR_ENTRY_ID,
  272. RN_UM.DYNAMIC_CONFIG_ID,
  273. RN_UM.CONFIG_ID,
  274. RN_UM.LO_EVENT_ID,
  275. RN_UM.LEADERBOARD_CONFIG_TYPE,
  276. RN_UM.EVENT_SEGMENT,
  277. RN_UM.EVENT_SEGMENT_LOGIC,
  278. RN_UM.IS_PUSH_NOTIFICATION_ENABLE,
  279. RN_UM.IS_RANKED,
  280. RN_UM.TOURNAMENT_UI_ORDER,
  281. RN_UM.TOURNAMENT_SIZE,
  282. RN_UM.TOURNAMENT_ENTRY_COIN_PRICE,
  283. RN_UM.TOURNAMENT_MN_BOOSTER_RARITY,
  284. RN_UM.TOURNAMENT_MX_BOOSTER_RARITY,
  285. RN_UM.calendar_order,
  286. RN_UM.lo_entry_is_premium_pass_exclusive,
  287. RN_UM.lo_limited_boosters,
  288. RN_UM.lo_limited_boosters_are_whitelist,
  289. RN_UM.BOOSTER_TIER_ID,
  290. RN_UM.BOOSTER_TIER,
  291. RN_UM.RESOURCE_TYPE_ID,
  292. RN_UM.RESOURCE_TYPE,
  293. RN_UM.RESOURCE_SUB_TYPE_ID,
  294. RN_UM.RESOURCE_SUB_TYPE,
  295. RN_UM.RESOURCE_ID,
  296. RN_UM.BOOSTER_NAME,
  297. RN_UM.BOOSTER_RESOURCE_VERSION,
  298. RN_UM.BOOSTER_VALUE,
  299. RN_UM.ABILITY_ACTIVATED_CNT,
  300. RN_UM.ABILITY_FINAL_CHARGE_CNT,
  301. RN_UM.RIVAL_RESOURCE_TYPE,
  302. RN_UM.RIVAL_RESOURCE_SUB_TYPE,
  303. RN_UM.MATCH_DURATION_SEC,
  304. RN_UM.IS_BOT,
  305. RN_UM.IS_RIVAL_BOT,
  306. RN_UM.IS_WON,
  307. RN_UM.IS_TECHNICAL,
  308. RN_UM.IS_REMATCH,
  309. RN_UM.IS_DEVELOPER,
  310. RN_UM.IS_CONCEDED,
  311. RN_UM.TURN_INDEX,
  312. RN_UM.ROUNDS_CNT,
  313. RN_UM.MATCH_SCORE,
  314. RN_UM.IS_ON_FIRE_GAME,
  315. RN_UM.IS_REACHED_ON_FIRE,
  316. RN_UM.COINS_WON_AMT,
  317. RN_UM.TROPHIES_WON_AMT,
  318. RN_UM.WIN_STREAK_CNT,
  319. RN_UM.LOSE_STREAK_CNT,
  320. RN_UM.RESHUFFLE_CNT,
  321. RN_UM.EXTRA_MOVES_CNT,
  322. RN_UM.SPECIAL_EXPLODES_CNT,
  323. RN_UM.RED_COMBOS_CNT,
  324. RN_UM.GREEN_COMBOS_CNT,
  325. RN_UM.BLUE_COMBOS_CNT,
  326. RN_UM.YELLOW_COMBOS_CNT,
  327. RN_UM.ORANGE_COMBOS_CNT,
  328. RN_UM.PURPLE_COMBOS_CNT,
  329. RN_UM.WHITE_COMBOS_CNT,
  330. RN_UM.THREE_MATCHED_CNT,
  331. RN_UM.FOUR_MATCHED_CNT,
  332. RN_UM.FIVE_MATCHED_CNT,
  333. RN_UM.RSHAPED_MATCHED_CNT,
  334. RN_UM.BEST_MOVE_SCORE,
  335. RN_UM.TIMEBANK_LEFT,
  336. RN_UM.STARS_WON_AMT,
  337. RN_UM.PREVIOUS_TROPHIES_BALANCE,
  338. RN_UM.MATCH_BONUS_TROPHIES_CNT,
  339. RN_UM.MATCH_SKILL_RATING,
  340. RN_UM.RIVAL_USER_ID,
  341. RN_UM.RIVAL_SESSION_ID,
  342. RN_UM.RIVAL_BOOSTER_NAME,
  343. RN_UM.RIVAL_BOOSTER_VERSION,
  344. RN_UM.RIVAL_TROPHIES_BALANCE,
  345. RN_UM.RIVAL_WIN_RATE,
  346. RN_UM.ARENA_INDEX,
  347. RN_UM.MAX_PRIZE_ELIGIBILITY,
  348. RN_UM.MAX_PRIZE_WON,
  349. RN_UM.MOVES_LIMIT,
  350. RN_UM.MOVES_MADE,
  351. RN_UM.MOVES_LEFT,
  352. RN_UM.MATCH_RANK,
  353. RN_UM.ELIMINATION_ROUND,
  354. RN_UM.LEVELS,
  355. RN_UM.CS_RESYNCS,
  356. RN_UM.INVALID_SWAPS,
  357. RN_UM.ERROR_RESYNCS,
  358. RN_UM.RIVAL_ERROR_RESYNCS,
  359. RN_UM.ACTIVE_STYLE_PACK,
  360. RN_UM.SHOW_OFF_BADGE_ID,
  361. RN_UM.IN_GAME_SHOWN_TIME,
  362. RN_UM.FIRST_ROUND_SHOWN_TIME,
  363. RN_UM.TIMEBANK_SHOWN_RIVAL,
  364. RN_UM.CONTEXT_SWITCHES,
  365. RN_UM.INVALID_SWIPES,
  366. RN_UM.EMOTE_SENT,
  367. RN_UM.RIVAL_EMOTE_SENT,
  368. RN_UM.MATCH_MAKING_FOUND_TIME,
  369. RN_UM.TIMEBANK_SHOWN_PLAYER_AMT,
  370. RN_UM.RIVAL_PERK_1_RESOURCE_NAME,
  371. RN_UM.RIVAL_PERK_2_RESOURCE_NAME,
  372. RN_UM.RIVAL_MATCH_SCORE,
  373. RN_UM.RIVAL_AI_LEVEL,
  374. RN_UM.RIVAL_PLAYED_OUT_OF_TIME,
  375. RN_UM.TOTAL_RIVAL_PLAYED_OUT_OF_TIME_SEC,
  376. RN_UM.LO_EVENT_TYPE,
  377. RN_UM.LO_EVENT_CONFIG_ID,
  378. RN_UM.USER_NAME,
  379. RN_UM.COUNTRY,
  380. RN_UM.LANGUAGE,
  381. RN_UM.DEVICE_LANGUAGE,
  382. RN_UM.TEST_NAME,
  383. RN_UM.TEST_GROUP_NAME,
  384. RN_UM.FB_USER_ID,
  385. RN_UM.CLIENT_IP,
  386. RN_UM.IS_TESTING_USER,
  387. RN_UM.DEVICE_MODEL,
  388. RN_UM.DEVICE_OS,
  389. RN_UM.PLATFORM,
  390. RN_UM.MEDIA_SOURCE,
  391. RN_UM.APP_VERSION,
  392. RN_UM.APP_MINOR_VERSION,
  393. RN_UM.FIRST_INSTALL_DT,
  394. RN_UM.ADVERTISER_ID,
  395. RN_UM.APPSFLYER_ID,
  396. RN_UM.AD_NAME,
  397. RN_UM.CAMPAIGN_NAME,
  398. RN_UM.TOTAL_IAP_AMT,
  399. RN_UM.STARS_CNT,
  400. RN_UM.LT_MATCHES_PLAYED_CNT,
  401. RN_UM.LT_MATCHES_WON_CNT,
  402. RN_UM.LT_PURCHASES_AMT,
  403. RN_UM.EVENT_SOURCE,
  404. RN_UM.is_shielded_game,
  405. RN_UM.rumble_size,
  406. RN_UM.current_arena_index,
  407. RN_UM.is_random_mixer,
  408. RN_UM.ad_id,
  409. RN_UM.adset,
  410. RN_UM.adset_id,
  411. RN_UM.campaign_id,
  412. RN_UM.influencer_name,
  413. RN_UM.team_ID,
  414. RN_UM.trophies_cnt,
  415. RN_UM.PERK_1_RESOURCE_NAME,
  416. RN_UM.PERK_2_RESOURCE_NAME,
  417. RN_UM.af_keywords,
  418. RN_UM.site_id,
  419. RN_UM.team_name,
  420. RN_UM.board_ability1_uses,
  421. RN_UM.board_ability2_uses,
  422. RN_UM.device_id_hASh,
  423. RN_UM.is_private,
  424. RN_UM.live_event_pieces_collected,
  425. RN_UM.mutations_pick,
  426. RN_UM.mutation_options,
  427. RN_UM.outfit_id,
  428. RN_UM.live_event_pieces_spawned,
  429. RN_UM.live_event_pieces_eligible,
  430. RN_UM.match_modifier_3,
  431. RN_UM.LO_ENTRY_IS_MC_EXCLUSIVE AS is_mc_match,
  432. RN_UM.lo_is_friendly,
  433. RN_UM.tournament_id,
  434. RN_UM.current_tournament_stage,
  435. RN_UM.stakes_multiplier,
  436. RN_UM.elo,
  437. RN_UM.used_booster_balance,
  438. RN_UM.boosters_lost_amount,
  439. RN_UM.RIVAL_ELO,
  440. RN_UM.timeups_cnt,
  441. RN_UM.DERIVED_TSTAMP AS LAST_UPDATED_DT,
  442. current_timestamp() AS DW_INSERT_D,
  443. RN_UM.bracket_count,
  444. RN_UM.bracket_index,
  445. RN_UM.premium_pass_end_time,
  446. RN_UM.relative_SR,
  447. RN_UM.premium_pass_active,
  448. RN_UM.rival_booster_tier_id,
  449. RN_UM.rival_booster_tier,
  450. RN_UM.ai_analyzer_type,
  451. RN_UM.ai_bad_play_target,
  452. RN_UM.rival_ai_analyzer_type,
  453. RN_UM.rival_ai_bad_play_target,
  454. RN_UM.app_original_version,
  455. RN_UM.square_shapes_cnt,
  456. RN_UM.segment,
  457. RN_UM.PREVIOUS_ELO_BALANCE,
  458. RN_UM.elo_games,
  459. RN_UM.rumble_type,
  460. RN_UM.current_round,
  461. RN_UM.initial_points,
  462. RN_UM.rival_initial_points,
  463. RN_UM.rival_moves_left
  464.  
  465.  
  466.  
  467. FROM
  468. (
  469. SELECT DISTINCT
  470. UM.EVENT_ID,
  471. UM.derived_tstamp,
  472. UM.MATCH_END_DATE,
  473. UM.USER_ID,
  474. UM.MATCH_SESSION_ID,
  475. S.IS_SUBSCRIBER,
  476. S.SUBSCRIPTION_TIER,
  477. S.SUBSCRIPTION_TIER_S,
  478. UM.Arena_Group,
  479. UM.IS_PAYER,
  480. UM.SENIORITY_BIN,
  481. UM.LTV_GROUP,
  482. UM.ENGAGEMENT_GROUP,
  483. UM.MATCH_ID,
  484. row_number() over (
  485. partition by UM.user_id,
  486. UM.match_end_date
  487. order by
  488. UM.derived_tstamp DESC
  489. ) AS RN,
  490. UM.PVP_MODE,
  491. UM.REMATCH_CNT,
  492. MATCH_EVENT_TYPE_ID AS match_type_id,
  493. ifnull(
  494. cASe
  495. when UM.MATCH_EVENT_TYPE_ID = 7 then LE.lo_event_type
  496. end,
  497. MATCH_EVENT_TYPE
  498. ) AS MATCH_TYPE,
  499. ifnull(
  500. cASe
  501. when UM.MATCH_EVENT_TYPE_ID = 7 then CALENDAR.LO_EVENT_CONFIG_ID
  502. end,
  503. MATCH_EVENT_TYPE
  504. ) AS MATCH_SUB_TYPE,cASe
  505. when UM.MATCH_EVENT_TYPE_ID = 7
  506. AND LE.LO_EVENT1_MATCH_MODE_TYPE is not null then LE.LO_EVENT1_MATCH_MODE_TYPE
  507. else MATCH_MODE_TYPE
  508. end AS MATCH_MODE,CASE
  509. WHEN MATCH_EVENT_TYPE_ID = 4 THEN CALENDAR.LO_ENTRY_MODIFIER_TYPE1
  510. ELSE MATCH_MODIFIER_1
  511. END AS modifier_1,CASE
  512. WHEN MATCH_EVENT_TYPE_ID = 4 THEN CALENDAR.LO_ENTRY_MODIFIER_TYPE2
  513. ELSE MATCH_MODIFIER_2
  514. END AS modifier_2,
  515. MATCH_LOP_EVENT_ID AS calendar_entry_id,
  516. CALENDAR.LO_ORIGINAL_ENTRY_ID AS ORIGINAL_CALENDAR_ENTRY_ID,
  517. calendar.lo_event_dynamic_config_id AS dynamic_config_id,
  518. calendar.lo_event_config_id AS config_id,
  519. LE.lo_event_id,
  520. LE.lo_solo_leaderboard_event_id AS leaderboard_config_type,
  521. LE.LO_EVENT1_SEGMENT AS EVENT_SEGMENT,
  522. LE.LO_EVENT_SEGMENT_LOGIC AS EVENT_SEGMENT_LOGIC,
  523. CALENDAR.LO_ENTRY_PUSH_NOTIFICATION_ENABLE AS IS_PUSH_NOTIFICATION_ENABLE,
  524. CALENDAR.LO_ENTRY_PLAY_FOR_TROPHIES AS IS_RANKED,
  525. CALENDAR.calendar_order AS TOURNAMENT_UI_ORDER,
  526. CALENDAR.LO_ENTRY_TOURNAMENT_SIZE AS TOURNAMENT_SIZE,
  527. CALENDAR.LO_TOURNAMENT_ENTRY_PRICE_COINS AS TOURNAMENT_ENTRY_COIN_PRICE,
  528. CALENDAR.LO_ENTRY_MIN_BOOSTER_RARITY AS TOURNAMENT_MN_BOOSTER_RARITY,
  529. CALENDAR.LO_ENTRY_MAX_BOOSTER_RARITY AS TOURNAMENT_MX_BOOSTER_RARITY,
  530. CALENDAR.calendar_order,
  531. CALENDAR.lo_entry_is_premium_pass_exclusive,
  532. CALENDAR.lo_limited_boosters,
  533. CALENDAR.lo_limited_boosters_are_whitelist,
  534. R1.BOOSTER_TIER AS BOOSTER_TIER_ID,
  535. R1.BOOSTER_TIER_GROUP_NAME AS BOOSTER_TIER,
  536. R1.RESOURCE_TYPE_ID,
  537. R1.RESOURCE_TYPE,
  538. R1.RESOURCE_SUB_TYPE_ID,
  539. R1.RESOURCE_SUB_TYPE,
  540. R1.RESOURCE_ID,
  541. BOOSTER_RESOURCE_NAME AS BOOSTER_NAME,
  542. BOOSTER_RESOURCE_VERSION,
  543. R1.RESOURCE_COIN_VALUE AS BOOSTER_VALUE,
  544. ABILITY_ACTIVATED_CNT,
  545. ABILITY_FINAL_CHARGE_CNT,
  546. R2.RESOURCE_TYPE AS RIVAL_RESOURCE_TYPE,
  547. R2.RESOURCE_SUB_TYPE AS RIVAL_RESOURCE_SUB_TYPE,
  548. MATCH_DURATION_SEC,
  549. IS_BOT,
  550. IS_RIVAL_BOT,
  551. IS_WON,
  552. IS_TECHNICAL,
  553. IS_REMATCH,
  554. IS_DEVELOPER,
  555. IS_CONCEDED,
  556. TURN_INDEX,
  557. ROUNDS_CNT,
  558. MATCH_SCORE,
  559. IS_ON_FIRE_GAME,
  560. IS_REACHED_ON_FIRE,
  561. COINS_WON_AMT,
  562. TROPHIES_WON_AMT,
  563. WIN_STREAK_CNT,
  564. LOSE_STREAK_CNT,
  565. RESHUFFLE_CNT,
  566. EXTRA_MOVES_CNT,
  567. SPECIAL_EXPLODES_CNT,
  568. RED_COMBOS_CNT,
  569. GREEN_COMBOS_CNT,
  570. BLUE_COMBOS_CNT,
  571. YELLOW_COMBOS_CNT,
  572. ORANGE_COMBOS_CNT,
  573. PURPLE_COMBOS_CNT,
  574. WHITE_COMBOS_CNT,
  575. THREE_MATCHED_CNT,
  576. FOUR_MATCHED_CNT,
  577. FIVE_MATCHED_CNT,
  578. RSHAPED_MATCHED_CNT,
  579. BEST_MOVE_SCORE,
  580. TIMEBANK_LEFT,
  581. STARS_WON_AMT,
  582. PREVIOUS_TROPHIES_BALANCE,
  583. MATCH_BONUS_TROPHIES_CNT,
  584. MATCH_SKILL_RATING,
  585. RIVAL_USER_ID,
  586. RIVAL_SESSION_ID,
  587. RIVAL_BOOSTER_RESOURCE_NAME AS RIVAL_BOOSTER_NAME,
  588. RIVAL_BOOSTER_RESOURCE_VERSION AS RIVAL_BOOSTER_VERSION,
  589. PERK_1_RESOURCE_NAME,
  590. PERK_2_RESOURCE_NAME,
  591. RIVAL_TROPHIES_BALANCE,
  592. RIVAL_WIN_RATE,
  593. ARENA_INDEX,
  594. MAX_PRIZE_ELIGIBILITY,
  595. MAX_PRIZE_WON,
  596. MOVES_LIMIT,
  597. MOVES_MADE,
  598. MOVES_LEFT,
  599. MATCH_RANK,
  600. ELIMINATION_ROUND,
  601. LEVELS,
  602. CS_RESYNCS,
  603. INVALID_SWAPS,
  604. ERROR_RESYNCS,
  605. RIVAL_ERROR_RESYNCS,
  606. ACTIVE_STYLE_PACK,
  607. SHOW_OFF_BADGE_ID,
  608. IN_GAME_SHOWN_TIME,
  609. FIRST_ROUND_SHOWN_TIME,
  610. TIMEBANK_SHOWN_RIVAL,
  611. CONTEXT_SWITCHES,
  612. INVALID_SWIPES,
  613. EMOTE_SENT,
  614. RIVAL_EMOTE_SENT,
  615. MATCH_MAKING_FOUND_TIME,
  616. TIMEBANK_SHOWN_PLAYER_AMT,
  617. RIVAL_PERK_1_RESOURCE_NAME,
  618. RIVAL_PERK_2_RESOURCE_NAME,
  619. RIVAL_MATCH_SCORE,
  620. RIVAL_AI_LEVEL,
  621. RIVAL_PLAYED_OUT_OF_TIME,
  622. TOTAL_RIVAL_PLAYED_OUT_OF_TIME_SEC,
  623. LE.LO_EVENT_TYPE,
  624. LE.LO_EVENT_CONFIG_ID,
  625. UM.user_name,
  626. UM.country,
  627. UM.language,
  628. UM.device_language,
  629. UM.test_name,
  630. UM.test_group_name,
  631. UM.fb_user_ID,
  632. UM.client_IP,
  633. UM.is_testing_user,
  634. UM.device_model,
  635. UM.device_os,
  636. UM.platform,
  637. UM.media_source,
  638. UM.app_version,
  639. UM.app_minor_version,
  640. UM.first_install_DT,
  641. UM.advertiser_ID,
  642. UM.appsflyer_id,
  643. UM.ad_name,
  644. UM.campaign_name,
  645. UM.total_IAP_amt,
  646. UM.stars_cnt,
  647. UM.LT_matches_played_cnt,
  648. UM.LT_matches_won_cnt,
  649. UM.LT_purchASes_amt,
  650. UM.event_source,
  651. UM.is_shielded_game,
  652. CASE
  653. WHEN LE.lo_event_id like ('%rumble%') THEN TRY_TO_NUMBER(split_part(LE.lo_event_id, '-', 3))
  654. else Null
  655. END AS rumble_size,
  656. um.current_arena_index,
  657. um.is_random_mixer,
  658. um.ad_id,
  659. um.adset,
  660. um.adset_id,
  661. um.campaign_id,
  662. um.influencer_name,
  663. um.team_ID,
  664. um.trophies_cnt,
  665. um.af_keywords,
  666. um.site_id,
  667. um.team_name,
  668. um.board_ability1_uses,
  669. um.board_ability2_uses,
  670. um.device_id_hASh,
  671. um.is_private,
  672. um.live_event_pieces_collected,
  673. um.mutations_pick,
  674. um.mutation_options,
  675. um.outfit_id,
  676. um.live_event_pieces_spawned,
  677. um.live_event_pieces_eligible,
  678. um.match_modifier_3,
  679. CALENDAR.LO_ENTRY_IS_MC_EXCLUSIVE,
  680. LE.lo_is_friendly,
  681. um.tournament_id,
  682. um.current_tournament_stage,
  683. um.stakes_multiplier,
  684. um.elo,
  685. um.used_booster_balance,
  686. um.boosters_lost_amount,
  687. um.rival_elo,
  688. um.timeups_cnt,
  689. um.bracket_count,
  690. um.bracket_index,
  691. um.premium_pass_end_time,
  692. um.relative_SR,
  693. um.premium_pass_active,
  694. R2.BOOSTER_TIER AS rival_booster_tier_id,
  695. R2.BOOSTER_TIER_GROUP_NAME AS rival_BOOSTER_TIER,
  696. um.ai_analyzer_type,
  697. um.ai_bad_play_target,
  698. um.rival_ai_analyzer_type,
  699. um.rival_ai_bad_play_target,
  700. um.app_original_version,
  701. um.square_shapes_cnt,
  702. um.segment,
  703. um.PREVIOUS_ELO_BALANCE,
  704. um.elo_games,
  705. um.is_valid_player,
  706. um.rumble_type,
  707. um.current_round,
  708. um.initial_points,
  709. um.rival_initial_points,
  710. um.rival_moves_left
  711. FROM
  712. (
  713. SELECT DISTINCT
  714. SERVER_EVENT_ID AS event_id,
  715. MATCH_END_DATE,
  716. server_event_id,
  717. server_derived_tstamp AS derived_tstamp,
  718. USER_ID,
  719. MATCH_ID,
  720. PVP_MODE,
  721. REMATCH_CNT,
  722. MATCH_SESSION_ID,
  723. iff(SUBSCRIPTION_TIER = 0, false, true) AS IS_SUBSCRIBER,
  724. SUBSCRIPTION_TIER,CASE
  725. WHEN current_arena_index <= 13 THEN 'Studios'
  726. WHEN current_arena_index BETWEEN 14
  727. AND 22 THEN 'Master League'
  728. ELSE 'Legends League'
  729. END AS Arena_Group,CASE
  730. WHEN TOTAL_IAP_AMT > 0 THEN True
  731. ELSE False
  732. END AS IS_PAYER,
  733. DATE(MATCH_END_DATE) - DATE(FIRST_INSTALL_DT) AS seniority,
  734. CASE
  735. WHEN seniority <= 2 THEN '1_2'
  736. WHEN seniority <= 10 THEN '3-10'
  737. WHEN seniority <= 30 THEN '11_30'
  738. WHEN seniority <= 90 THEN '31_90'
  739. WHEN seniority <= 180 THEN '91_180'
  740. WHEN seniority <= 360 THEN '181_360'
  741. WHEN seniority <= 720 THEN '361_720'
  742. WHEN seniority > 720 THEN '>720'
  743. ELSE NULL
  744. END AS seniority_bin,
  745. CASE
  746. WHEN STARS_CNT * 7 >= 70 THEN '4 Very High'
  747. WHEN STARS_CNT * 7 >= 30 THEN '3 High'
  748. WHEN STARS_CNT * 7 >= 10 THEN '2 Med'
  749. WHEN STARS_CNT * 7 >= 0 THEN '1 Low'
  750. ELSE NULL
  751. END AS ENGAGEMENT_GROUP,
  752. CASE
  753. WHEN TO_NUMBER(TOTAL_IAP_AMT) = 0 THEN '0'
  754. WHEN TO_NUMBER(TOTAL_IAP_AMT) <= 10 THEN 'Low'
  755. WHEN TO_NUMBER(TOTAL_IAP_AMT) <= 100 THEN 'Med'
  756. WHEN TO_NUMBER(TOTAL_IAP_AMT) <= 299 THEN 'High'
  757. WHEN TO_NUMBER(TOTAL_IAP_AMT) <= 999 THEN 'Very High'
  758. WHEN TO_NUMBER(TOTAL_IAP_AMT) >= 1000 THEN 'VIP'
  759. ELSE NULL
  760. END AS LTV_group,
  761. MATCH_MODE_TYPE,
  762. MATCH_MODIFIER_1,
  763. MATCH_MODIFIER_2,
  764. MATCH_EVENT_TYPE_ID,
  765. CASE
  766. WHEN MATCH_EVENT_TYPE_ID = 2 THEN 'Classic'
  767. WHEN MATCH_EVENT_TYPE_ID = 3 THEN 'Challenge'
  768. WHEN MATCH_EVENT_TYPE_ID = 4 THEN 'Tournament'
  769. when MATCH_LOP_EVENT_ID like '%boosters-clash%' or MATCH_LOP_EVENT_ID like '%booster-clash%' then 'BoostersClash'
  770. WHEN MATCH_LOP_EVENT_ID LIKE '%mutation%' and PVP_MODE = 'Solo' THEN 'SoloMutationLab'
  771. WHEN MATCH_EVENT_TYPE_ID = 5 and (MATCH_MODIFIER_1 = 'win-by-knockout' or MATCH_MODIFIER_2 = 'win-by-knockout' or match_modifier_3 = 'win-by-knockout') AND is_random_mixer = TRUE then 'Random Knockout'
  772. WHEN MATCH_EVENT_TYPE_ID = 5 and (MATCH_MODIFIER_1 = 'win-by-knockout' or MATCH_MODIFIER_2 = 'win-by-knockout' or match_modifier_3 = 'win-by-knockout') then 'Knockout'
  773. WHEN MATCH_EVENT_TYPE_ID = 5 and (MATCH_MODIFIER_1 = 'rumble-duo-modifier' or MATCH_MODIFIER_2 = 'rumble-duo-modifier' or match_modifier_3 = 'rumble-duo-modifier' or lower(rumble_type) = 'showdown') AND is_random_mixer = TRUE then 'Random Showdown'
  774. WHEN MATCH_EVENT_TYPE_ID = 5 AND is_random_mixer = TRUE then 'Random Mixer'
  775. WHEN MATCH_EVENT_TYPE_ID = 5 and (MATCH_MODIFIER_1 = 'rumble-duo-modifier' or MATCH_MODIFIER_2 = 'rumble-duo-modifier' or match_modifier_3 = 'rumble-duo-modifier' or lower(rumble_type) = 'showdown') then 'Showdown'
  776. WHEN MATCH_EVENT_TYPE_ID = 5 THEN 'Daily'
  777. when lower(rumble_type) = 'party' then 'Rumble Party'
  778. when pvp_mode = 'Rumble' or lower(rumble_type) = 'classic' then 'Rumble'
  779. when MATCH_LOP_EVENT_ID like '%onboarding%' then 'Onboarding'
  780. when PVP_MODE = 'Solo' then 'Solo'
  781. when MATCH_LOP_EVENT_ID like '%adventure%' then 'Adventure'
  782. when MATCH_LOP_EVENT_ID like '%score-race%' or config_id = 'score-race' then 'Leaderboard'
  783. ELSE 'Other'
  784. END AS MATCH_EVENT_TYPE
  785. ,CASE
  786. WHEN MATCH_EVENT_TYPE_ID in (4, 5) THEN MATCH_MODE_TYPE
  787. WHEN MATCH_EVENT_TYPE_ID = 7 THEN MATCH_LOP_EVENT_TYPE
  788. ELSE 'N/A'
  789. END AS MATCH_MODE,
  790. BOOSTER_RESOURCE_NAME,
  791. BOOSTER_RESOURCE_VERSION,
  792. PERK_1_RESOURCE_NAME,
  793. PERK_2_RESOURCE_NAME,
  794. ABILITY_ACTIVATED_CNT,
  795. ABILITY_FINAL_CHARGE_CNT,
  796. RIVAL_PLAYED_OUT_OF_TIME,
  797. RIVAL_BOOSTER_RESOURCE_NAME,
  798. RIVAL_BOOSTER_RESOURCE_VERSION,
  799. TOTAL_RIVAL_PLAYED_OUT_OF_TIME_SEC,
  800. MATCH_MAKING_FOUND_TIME,
  801. TIMEBANK_SHOWN_PLAYER_AMT,
  802. ROUNDS_CNT,
  803. COINS_WON_AMT,
  804. TROPHIES_WON_AMT,
  805. IFF(IS_WON = TRUE, 1, 0) AS IS_WON,
  806. MATCH_SCORE,
  807. MATCH_DURATION_SEC,
  808. TURN_INDEX,
  809. IFF(IS_BOT = TRUE, 1, 0) AS IS_BOT,
  810. IFF(IS_TECHNICAL = TRUE, 1, 0) AS IS_TECHNICAL,
  811. IFF(IS_REMATCH = TRUE, 1, 0) AS IS_REMATCH,
  812. IFF(IS_DEVELOPER = TRUE, 1, 0) AS IS_DEVELOPER,
  813. IFF(IS_CONCEDED = TRUE, 1, 0) AS IS_CONCEDED,
  814. IFF(IS_RIVAL_BOT = TRUE, 1, 0) AS IS_RIVAL_BOT,
  815. IFF(IS_ON_FIRE_GAME = TRUE, 1, 0) AS IS_ON_FIRE_GAME,
  816. IFF(IS_REACHED_ON_FIRE = TRUE, 1, 0) AS IS_REACHED_ON_FIRE,
  817. MATCH_LOP_EVENT_ID,
  818. MATCH_LOP_EVENT_TYPE,
  819. WIN_STREAK_CNT,
  820. LOSE_STREAK_CNT,
  821. RESHUFFLE_CNT,
  822. EXTRA_MOVES_CNT,
  823. SPECIAL_EXPLODES_CNT,
  824. RED_COMBOS_CNT,
  825. GREEN_COMBOS_CNT,
  826. BLUE_COMBOS_CNT,
  827. YELLOW_COMBOS_CNT,
  828. ORANGE_COMBOS_CNT,
  829. PURPLE_COMBOS_CNT,
  830. WHITE_COMBOS_CNT,
  831. THREE_MATCHED_CNT,
  832. FOUR_MATCHED_CNT,
  833. FIVE_MATCHED_CNT,
  834. RSHAPED_MATCHED_CNT,
  835. BEST_MOVE_SCORE,
  836. TIMEBANK_LEFT,
  837. STARS_WON_AMT,
  838. PREVIOUS_TROPHIES_BALANCE,
  839. MATCH_BONUS_TROPHIES_CNT,
  840. MATCH_SKILL_RATING,
  841. RIVAL_USER_ID,
  842. RIVAL_SESSION_ID,
  843. RIVAL_PERK_1_RESOURCE_NAME,
  844. RIVAL_PERK_2_RESOURCE_NAME,
  845. RIVAL_TROPHIES_BALANCE,
  846. RIVAL_WIN_RATE,
  847. RIVAL_MATCH_SCORE,
  848. RIVAL_AI_LEVEL,
  849. ARENA_INDEX,
  850. MAX_PRIZE_ELIGIBILITY,
  851. MAX_PRIZE_WON,
  852. MOVES_LIMIT,
  853. MOVES_MADE,
  854. MOVES_LEFT,
  855. LEVELS,
  856. CS_RESYNCS,
  857. MATCH_RANK,
  858. ELIMINATION_ROUND,
  859. INVALID_SWAPS,
  860. ERROR_RESYNCS,
  861. RIVAL_ERROR_RESYNCS,
  862. ACTIVE_STYLE_PACK,
  863. SHOW_OFF_BADGE_ID,
  864. IN_GAME_SHOWN_TIME,
  865. FIRST_ROUND_SHOWN_TIME,
  866. TIMEBANK_SHOWN_RIVAL,
  867. CONTEXT_SWITCHES,
  868. INVALID_SWIPES,
  869. EMOTE_SENT,
  870. RIVAL_EMOTE_SENT,
  871. user_name,
  872. country,
  873. language,
  874. device_language,
  875. test_name,
  876. test_group_name,
  877. fb_user_ID,
  878. client_IP,
  879. is_testing_user,
  880. device_model,
  881. device_os,
  882. platform,
  883. media_source,
  884. app_version,
  885. app_minor_version,
  886. first_install_DT,
  887. advertiser_ID,
  888. appsflyer_id,
  889. ad_name,
  890. campaign_name,
  891. total_IAP_amt,
  892. stars_cnt,
  893. LT_matches_played_cnt,
  894. LT_matches_won_cnt,
  895. LT_purchASes_amt,
  896. event_source,
  897. is_shielded_game,
  898. current_arena_index,
  899. is_random_mixer,
  900. ad_id,
  901. adset,
  902. adset_id,
  903. campaign_id,
  904. influencer_name,
  905. team_ID,
  906. trophies_cnt,
  907. af_keywords,
  908. site_id,
  909. team_name,
  910. board_ability1_uses,
  911. board_ability2_uses,
  912. device_id_hASh,
  913. is_private,
  914. live_event_pieces_collected,
  915. mutations_pick,
  916. mutation_options,
  917. outfit_id,
  918. live_event_pieces_spawned,
  919. live_event_pieces_eligible,
  920. match_modifier_3,
  921. tournament_id,
  922. current_tournament_stage,
  923. stakes_multiplier,
  924. elo,
  925. used_booster_balance,
  926. boosters_lost_amount,
  927. rival_elo,
  928. timeups_cnt,
  929. bracket_count,
  930. bracket_index,
  931. premium_pass_end_time,
  932. relative_SR,
  933. premium_pass_active,
  934. ai_analyzer_type,
  935. ai_bad_play_target,
  936. rival_ai_analyzer_type,
  937. rival_ai_bad_play_target,
  938. app_original_version,
  939. square_shapes_cnt,
  940. segment,
  941. PREVIOUS_ELO_BALANCE,
  942. elo_games,
  943. is_valid_player,
  944. rumble_type,
  945. current_round,
  946. initial_points,
  947. rival_initial_points,
  948. rival_moves_left
  949.  
  950. FROM
  951. SNOWPLOW.DERIVED.USER_MATCH_TOTAL
  952. WHERE
  953. 1 = 1
  954. AND MATCH_END_DATE IS NOT NULL
  955. AND server_derived_tstamp BETWEEN dateadd(hour, -7, $FROM_USER_MATCH_UPDATED_DT_MNG)
  956. AND $TO_USER_MATCH_UPDATED_DT_MNG
  957. AND is_bot = false
  958. QUALIFY ROW_NUMBER() OVER (
  959. PARTITION by concat(user_id, match_id, rematch_cnt)
  960. ORDER BY
  961. CONTEXT_SWITCHES ASC
  962. ) = 1
  963. ) AS UM
  964. LEFT JOIN (
  965. SELECT
  966. BOOSTER_TIER,
  967. BOOSTER_TIER_GROUP_NAME,
  968. RESOURCE_TYPE_ID,
  969. RESOURCE_TYPE,
  970. RESOURCE_SUB_TYPE_ID,
  971. RESOURCE_SUB_TYPE,
  972. RESOURCE_ID,
  973. RESOURCE_COIN_VALUE,
  974. RESOURCE_NAME,
  975. RESOURCE_VERSION
  976. FROM
  977. MATCH_MASTERS.PROD.DIM_RESOURCE_VERSION
  978. WHERE
  979. 1 = 1
  980. AND is_inventory_resource = true
  981. AND resource_type = 'Booster'
  982. AND (
  983. resource_UI_order > -1
  984. or resource_UI_order is null
  985. ) QUALIFY ROW_NUMBER() OVER (
  986. PARTITION by resource_name,
  987. resource_version
  988. ORDER BY
  989. resource_version DESC
  990. ) = 1
  991. ) R1 ON UM.BOOSTER_RESOURCE_NAME = R1.RESOURCE_NAME
  992. AND UM.BOOSTER_RESOURCE_VERSION = R1.RESOURCE_VERSION
  993. LEFT JOIN (
  994. SELECT
  995. BOOSTER_TIER,
  996. BOOSTER_TIER_GROUP_NAME,
  997. RESOURCE_TYPE_ID,
  998. RESOURCE_TYPE,
  999. RESOURCE_SUB_TYPE_ID,
  1000. RESOURCE_SUB_TYPE,
  1001. RESOURCE_ID,
  1002. RESOURCE_COIN_VALUE,
  1003. RESOURCE_NAME,
  1004. RESOURCE_VERSION
  1005. FROM
  1006. MATCH_MASTERS.PROD.DIM_RESOURCE_VERSION
  1007. WHERE
  1008. 1 = 1
  1009. AND is_inventory_resource = true
  1010. AND resource_type = 'Booster'
  1011. AND (
  1012. resource_UI_order > -1
  1013. or resource_UI_order is null
  1014. ) QUALIFY ROW_NUMBER() OVER (
  1015. PARTITION by resource_name, resource_version
  1016. ORDER BY
  1017. resource_version DESC
  1018. ) = 1
  1019. ) R2 ON UM.RIVAL_BOOSTER_RESOURCE_NAME = R2.RESOURCE_NAME
  1020. AND UM.RIVAL_BOOSTER_RESOURCE_VERSION = R2.RESOURCE_VERSION
  1021. LEFT JOIN (
  1022. select
  1023. LO_EVENT_TYPE,
  1024. LO_EVENT_CONFIG_ID,
  1025. LO_EVENT1_MATCH_MODE_TYPE,
  1026. lo_is_friendly,
  1027. lo_solo_leaderboard_event_id,
  1028. LO_EVENT_SEGMENT_LOGIC,
  1029. LO_EVENT1_SEGMENT,
  1030. lo_event_id
  1031. FROM
  1032. SNOWPLOW.DERIVED.LIVEOPS_EVENT_CONFIG QUALIFY row_number() over (
  1033. partition by lo_event_id
  1034. order by
  1035. ifnull(lo_event_config_ts, lo_event_config_update_ts) DESC
  1036. ) = 1
  1037. ) LE ON UM.MATCH_LOP_EVENT_TYPE = LE.LO_EVENT_ID
  1038. LEFT JOIN (
  1039. select
  1040. LO_ENTRY_IS_MC_EXCLUSIVE,
  1041. LO_ENTRY_MAX_BOOSTER_RARITY,
  1042. LO_ENTRY_MIN_BOOSTER_RARITY,
  1043. LO_TOURNAMENT_ENTRY_PRICE_COINS,
  1044. LO_ENTRY_TOURNAMENT_SIZE,
  1045. calendar_order,
  1046. lo_entry_is_premium_pass_exclusive,
  1047. lo_limited_boosters,
  1048. lo_limited_boosters_are_whitelist,
  1049. LO_ENTRY_PLAY_FOR_TROPHIES,
  1050. LO_ENTRY_PUSH_NOTIFICATION_ENABLE,
  1051. lo_event_config_id,
  1052. lo_event_dynamic_config_id,
  1053. LO_ORIGINAL_ENTRY_ID,
  1054. LO_ENTRY_MODIFIER_TYPE2,
  1055. LO_ENTRY_MODIFIER_TYPE1,
  1056. LO_ENTRY_ID
  1057. FROM
  1058. SNOWPLOW.DERIVED.LIVEOPS_CALENDAR QUALIFY row_number() over (
  1059. partition by lo_entry_id
  1060. order by
  1061. ifnull(lo_entry_ts, lo_entry_update_ts) DESC
  1062. ) = 1
  1063. ) calendar on UM.match_lop_event_id = calendar.LO_ENTRY_ID
  1064. LEFT JOIN (
  1065. select distinct
  1066. USER_ID,
  1067. MATCH_END_DATE,
  1068. server_derived_tstamp,
  1069. iff(SUBSCRIPTION_TIER > 0, true, false) AS IS_SUBSCRIBER,
  1070. SUBSCRIPTION_TIER,cASe
  1071. when subscription_tier = 1 then 'Silver'
  1072. when subscription_tier = 2 then 'Gold'
  1073. when subscription_tier = 3 then 'Diamond'
  1074. else NULL
  1075. end AS SUBSCRIPTION_TIER_S
  1076. FROM
  1077. SNOWPLOW.DERIVED.USER_MATCH_TOTAL
  1078. WHERE
  1079. 1 = 1
  1080. AND MATCH_END_DATE IS NOT NULL
  1081. AND server_derived_tstamp BETWEEN dateadd(hour, -7, $FROM_USER_MATCH_UPDATED_DT_MNG) AND $TO_USER_MATCH_UPDATED_DT_MNG
  1082. AND is_bot = false
  1083. QUALIFY row_number() OVER (
  1084. PARTITION BY USER_ID,
  1085. MATCH_END_DATE
  1086. ORDER BY
  1087. server_derived_tstamp DESC
  1088. ) = 1
  1089. ) S ON S.USER_ID = UM.USER_ID
  1090. AND S.MATCH_END_DATE = UM.MATCH_END_DATE
  1091. ) AS RN_UM
  1092.  
  1093.  
  1094. WHERE
  1095. 1 = 1
  1096. AND RN_UM.derived_tstamp BETWEEN dateadd(hour, -7, $FROM_USER_MATCH_UPDATED_DT_MNG) AND $TO_USER_MATCH_UPDATED_DT_MNG
  1097. AND RN_UM.is_valid_player = TRUE
  1098. ;
  1099. ''',
  1100. '05_set_merge_error_helper': '''
  1101. ALTER SESSION SET ERROR_ON_NONDETERMINISTIC_MERGE = false;
  1102. ''',
  1103. '06_merge_temp_to_prod': '''
  1104. MERGE INTO MATCH_MASTERS.PROD.F_USER_MATCH AS t
  1105. USING MATCH_MASTERS.TRANSFORMATION.TRANS_USER_MATCH AS S
  1106. ON s.MATCH_END_DATE = t.MATCH_END_DATE
  1107. AND s.USER_ID = t.USER_ID
  1108. AND s.EVENT_ID = t.EVENT_ID
  1109. AND t.DERIVED_TSTAMP > DATEADD(week, -1, $FROM_USER_MATCH_UPDATED_DT_MNG)
  1110. WHEN MATCHED THEN UPDATE SET
  1111. t.EVENT_ID = s.EVENT_ID
  1112. ,t.DERIVED_TSTAMP = s.DERIVED_TSTAMP
  1113. ,t.MATCH_END_DATE = s.MATCH_END_DATE
  1114. ,t.USER_ID = s.USER_ID
  1115. ,t.MATCH_SESSION_ID = s.MATCH_SESSION_ID
  1116. ,t.IS_SUBSCRIBER = s.IS_SUBSCRIBER
  1117. ,t.SUBSCRIPTION_TIER = s.SUBSCRIPTION_TIER
  1118. ,t.SUBSCRIPTION_TIER_S = s.SUBSCRIPTION_TIER_S
  1119. ,t.ARENA_GROUP = s.ARENA_GROUP
  1120. ,t.IS_PAYER = s.IS_PAYER
  1121. ,t.SENIORITY_BIN = s.SENIORITY_BIN
  1122. ,t.LTV_GROUP = s.LTV_GROUP
  1123. ,t.ENGAGEMENT_GROUP = s.ENGAGEMENT_GROUP
  1124. ,t.MATCH_ID = s.MATCH_ID
  1125. ,t.PVP_MODE = s.PVP_MODE
  1126. ,t.REMATCH_CNT = s.REMATCH_CNT
  1127. ,t.MATCH_TYPE_ID = s.MATCH_TYPE_ID
  1128. ,t.MATCH_TYPE = s.MATCH_TYPE
  1129. ,t.MATCH_SUB_TYPE = s.MATCH_SUB_TYPE
  1130. ,t.MATCH_MODE = s.MATCH_MODE
  1131. ,t.MODIFIER_1 = s.MODIFIER_1
  1132. ,t.MODIFIER_2 = s.MODIFIER_2
  1133. ,t.CALENDAR_ENTRY_ID = s.CALENDAR_ENTRY_ID
  1134. ,t.ORIGINAL_CALENDAR_ENTRY_ID = s.ORIGINAL_CALENDAR_ENTRY_ID
  1135. ,t.DYNAMIC_CONFIG_ID = s.DYNAMIC_CONFIG_ID
  1136. ,t.CONFIG_ID = s.CONFIG_ID
  1137. ,t.LO_EVENT_ID = s.LO_EVENT_ID
  1138. ,t.LEADERBOARD_CONFIG_TYPE = s.LEADERBOARD_CONFIG_TYPE
  1139. ,t.EVENT_SEGMENT = s.EVENT_SEGMENT
  1140. ,t.EVENT_SEGMENT_LOGIC = s.EVENT_SEGMENT_LOGIC
  1141. ,t.IS_PUSH_NOTIFICATION_ENABLE = s.IS_PUSH_NOTIFICATION_ENABLE
  1142. ,t.IS_RANKED = s.IS_RANKED
  1143. ,t.TOURNAMENT_UI_ORDER = s.TOURNAMENT_UI_ORDER
  1144. ,t.TOURNAMENT_SIZE = s.TOURNAMENT_SIZE
  1145. ,t.TOURNAMENT_ENTRY_COIN_PRICE = s.TOURNAMENT_ENTRY_COIN_PRICE
  1146. ,t.TOURNAMENT_MN_BOOSTER_RARITY = s.TOURNAMENT_MN_BOOSTER_RARITY
  1147. ,t.TOURNAMENT_MX_BOOSTER_RARITY = s.TOURNAMENT_MX_BOOSTER_RARITY
  1148. ,t.calendar_order = s.calendar_order
  1149. ,t.lo_entry_is_premium_pass_exclusive = s.lo_entry_is_premium_pass_exclusive
  1150. ,t.lo_limited_boosters = s.lo_limited_boosters
  1151. ,t.lo_limited_boosters_are_whitelist = s.lo_limited_boosters_are_whitelist
  1152. ,t.BOOSTER_TIER_ID = s.BOOSTER_TIER_ID
  1153. ,t.BOOSTER_TIER = s.BOOSTER_TIER
  1154. ,t.RESOURCE_TYPE_ID = s.RESOURCE_TYPE_ID
  1155. ,t.RESOURCE_TYPE = s.RESOURCE_TYPE
  1156. ,t.RESOURCE_SUB_TYPE_ID = s.RESOURCE_SUB_TYPE_ID
  1157. ,t.RESOURCE_SUB_TYPE = s.RESOURCE_SUB_TYPE
  1158. ,t.RESOURCE_ID = s.RESOURCE_ID
  1159. ,t.BOOSTER_NAME = s.BOOSTER_NAME
  1160. ,t.BOOSTER_RESOURCE_VERSION = s.BOOSTER_RESOURCE_VERSION
  1161. ,t.BOOSTER_VALUE = s.BOOSTER_VALUE
  1162. ,t.ABILITY_ACTIVATED_CNT = s.ABILITY_ACTIVATED_CNT
  1163. ,t.ABILITY_FINAL_CHARGE_CNT = s.ABILITY_FINAL_CHARGE_CNT
  1164. ,t.RIVAL_RESOURCE_TYPE = s.RIVAL_RESOURCE_TYPE
  1165. ,t.RIVAL_RESOURCE_SUB_TYPE = s.RIVAL_RESOURCE_SUB_TYPE
  1166. ,t.MATCH_DURATION_SEC = s.MATCH_DURATION_SEC
  1167. ,t.IS_BOT = s.IS_BOT
  1168. ,t.IS_RIVAL_BOT = s.IS_RIVAL_BOT
  1169. ,t.IS_WON = s.IS_WON
  1170. ,t.IS_TECHNICAL = s.IS_TECHNICAL
  1171. ,t.IS_REMATCH = s.IS_REMATCH
  1172. ,t.IS_DEVELOPER = s.IS_DEVELOPER
  1173. ,t.IS_CONCEDED = s.IS_CONCEDED
  1174. ,t.TURN_INDEX = s.TURN_INDEX
  1175. ,t.ROUNDS_CNT = s.ROUNDS_CNT
  1176. ,t.MATCH_SCORE = s.MATCH_SCORE
  1177. ,t.IS_ON_FIRE_GAME = s.IS_ON_FIRE_GAME
  1178. ,t.IS_REACHED_ON_FIRE = s.IS_REACHED_ON_FIRE
  1179. ,t.COINS_WON_AMT = s.COINS_WON_AMT
  1180. ,t.TROPHIES_WON_AMT = s.TROPHIES_WON_AMT
  1181. ,t.WIN_STREAK_CNT = s.WIN_STREAK_CNT
  1182. ,t.LOSE_STREAK_CNT = s.LOSE_STREAK_CNT
  1183. ,t.RESHUFFLE_CNT = s.RESHUFFLE_CNT
  1184. ,t.EXTRA_MOVES_CNT = s.EXTRA_MOVES_CNT
  1185. ,t.SPECIAL_EXPLODES_CNT = s.SPECIAL_EXPLODES_CNT
  1186. ,t.RED_COMBOS_CNT = s.RED_COMBOS_CNT
  1187. ,t.GREEN_COMBOS_CNT = s.GREEN_COMBOS_CNT
  1188. ,t.BLUE_COMBOS_CNT = s.BLUE_COMBOS_CNT
  1189. ,t.YELLOW_COMBOS_CNT = s.YELLOW_COMBOS_CNT
  1190. ,t.ORANGE_COMBOS_CNT = s.ORANGE_COMBOS_CNT
  1191. ,t.PURPLE_COMBOS_CNT = s.PURPLE_COMBOS_CNT
  1192. ,t.WHITE_COMBOS_CNT = s.WHITE_COMBOS_CNT
  1193. ,t.THREE_MATCHED_CNT = s.THREE_MATCHED_CNT
  1194. ,t.FOUR_MATCHED_CNT = s.FOUR_MATCHED_CNT
  1195. ,t.FIVE_MATCHED_CNT = s.FIVE_MATCHED_CNT
  1196. ,t.RSHAPED_MATCHED_CNT = s.RSHAPED_MATCHED_CNT
  1197. ,t.BEST_MOVE_SCORE = s.BEST_MOVE_SCORE
  1198. ,t.TIMEBANK_LEFT = s.TIMEBANK_LEFT
  1199. ,t.STARS_WON_AMT = s.STARS_WON_AMT
  1200. ,t.PREVIOUS_TROPHIES_BALANCE = s.PREVIOUS_TROPHIES_BALANCE
  1201. ,t.MATCH_BONUS_TROPHIES_CNT = s.MATCH_BONUS_TROPHIES_CNT
  1202. ,t.MATCH_SKILL_RATING = s.MATCH_SKILL_RATING
  1203. ,t.RIVAL_USER_ID = s.RIVAL_USER_ID
  1204. ,t.RIVAL_SESSION_ID = s.RIVAL_SESSION_ID
  1205. ,t.RIVAL_BOOSTER_NAME = s.RIVAL_BOOSTER_NAME
  1206. ,t.RIVAL_BOOSTER_VERSION = s.RIVAL_BOOSTER_VERSION
  1207. ,t.RIVAL_TROPHIES_BALANCE = s.RIVAL_TROPHIES_BALANCE
  1208. ,t.RIVAL_WIN_RATE = s.RIVAL_WIN_RATE
  1209. ,t.ARENA_INDEX = s.ARENA_INDEX
  1210. ,t.MAX_PRIZE_ELIGIBILITY = s.MAX_PRIZE_ELIGIBILITY
  1211. ,t.MAX_PRIZE_WON = s.MAX_PRIZE_WON
  1212. ,t.MOVES_LIMIT = s.MOVES_LIMIT
  1213. ,t.MOVES_MADE = s.MOVES_MADE
  1214. ,t.MOVES_LEFT = s.MOVES_LEFT
  1215. ,t.MATCH_RANK = s.MATCH_RANK
  1216. ,t.ELIMINATION_ROUND = s.ELIMINATION_ROUND
  1217. ,t.LEVELS = s.LEVELS
  1218. ,t.CS_RESYNCS = s.CS_RESYNCS
  1219. ,t.INVALID_SWAPS = s.INVALID_SWAPS
  1220. ,t.ERROR_RESYNCS = s.ERROR_RESYNCS
  1221. ,t.RIVAL_ERROR_RESYNCS = s.RIVAL_ERROR_RESYNCS
  1222. ,t.ACTIVE_STYLE_PACK = s.ACTIVE_STYLE_PACK
  1223. ,t.SHOW_OFF_BADGE_ID = s.SHOW_OFF_BADGE_ID
  1224. ,t.IN_GAME_SHOWN_TIME = s.IN_GAME_SHOWN_TIME
  1225. ,t.FIRST_ROUND_SHOWN_TIME = s.FIRST_ROUND_SHOWN_TIME
  1226. ,t.TIMEBANK_SHOWN_RIVAL = s.TIMEBANK_SHOWN_RIVAL
  1227. ,t.CONTEXT_SWITCHES = s.CONTEXT_SWITCHES
  1228. ,t.INVALID_SWIPES = s.INVALID_SWIPES
  1229. ,t.EMOTE_SENT = s.EMOTE_SENT
  1230. ,t.RIVAL_EMOTE_SENT = s.RIVAL_EMOTE_SENT
  1231. ,t.MATCH_MAKING_FOUND_TIME = s.MATCH_MAKING_FOUND_TIME
  1232. ,t.TIMEBANK_SHOWN_PLAYER_AMT = s.TIMEBANK_SHOWN_PLAYER_AMT
  1233. ,t.RIVAL_PERK_1_RESOURCE_NAME = s.RIVAL_PERK_1_RESOURCE_NAME
  1234. ,t.RIVAL_PERK_2_RESOURCE_NAME = s.RIVAL_PERK_2_RESOURCE_NAME
  1235. ,t.RIVAL_MATCH_SCORE = s.RIVAL_MATCH_SCORE
  1236. ,t.RIVAL_AI_LEVEL = s.RIVAL_AI_LEVEL
  1237. ,t.RIVAL_PLAYED_OUT_OF_TIME = s.RIVAL_PLAYED_OUT_OF_TIME
  1238. ,t.TOTAL_RIVAL_PLAYED_OUT_OF_TIME_SEC = s.TOTAL_RIVAL_PLAYED_OUT_OF_TIME_SEC
  1239. ,t.LO_EVENT_TYPE = s.LO_EVENT_TYPE
  1240. ,t.LO_EVENT_CONFIG_ID = s.LO_EVENT_CONFIG_ID
  1241. ,t.USER_NAME = s.USER_NAME
  1242. ,t.COUNTRY = s.COUNTRY
  1243. ,t.LANGUAGE = s.LANGUAGE
  1244. ,t.DEVICE_LANGUAGE = s.DEVICE_LANGUAGE
  1245. ,t.TEST_NAME = s.TEST_NAME
  1246. ,t.TEST_GROUP_NAME = s.TEST_GROUP_NAME
  1247. ,t.FB_USER_ID = s.FB_USER_ID
  1248. ,t.CLIENT_IP = s.CLIENT_IP
  1249. ,t.IS_TESTING_USER = s.IS_TESTING_USER
  1250. ,t.DEVICE_MODEL = s.DEVICE_MODEL
  1251. ,t.DEVICE_OS = s.DEVICE_OS
  1252. ,t.PLATFORM = s.PLATFORM
  1253. ,t.MEDIA_SOURCE = s.MEDIA_SOURCE
  1254. ,t.APP_VERSION = s.APP_VERSION
  1255. ,t.APP_MINOR_VERSION = s.APP_MINOR_VERSION
  1256. ,t.FIRST_INSTALL_DT = s.FIRST_INSTALL_DT
  1257. ,t.ADVERTISER_ID = s.ADVERTISER_ID
  1258. ,t.APPSFLYER_ID = s.APPSFLYER_ID
  1259. ,t.AD_NAME = s.AD_NAME
  1260. ,t.CAMPAIGN_NAME = s.CAMPAIGN_NAME
  1261. ,t.TOTAL_IAP_AMT = s.TOTAL_IAP_AMT
  1262. ,t.STARS_CNT = s.STARS_CNT
  1263. ,t.LT_MATCHES_PLAYED_CNT = s.LT_MATCHES_PLAYED_CNT
  1264. ,t.LT_MATCHES_WON_CNT = s.LT_MATCHES_WON_CNT
  1265. ,t.LT_PURCHASES_AMT = s.LT_PURCHASES_AMT
  1266. ,t.EVENT_SOURCE = s.EVENT_SOURCE
  1267. ,t.IS_SHIELDED_GAME = s.IS_SHIELDED_GAME
  1268. ,t.RUMBLE_SIZE = s.RUMBLE_SIZE
  1269. ,t.CURRENT_ARENA_INDEX = s.CURRENT_ARENA_INDEX
  1270. ,t.IS_RANDOM_MIXER = s.IS_RANDOM_MIXER
  1271. ,t.AD_ID = s.AD_ID
  1272. ,t.ADSET = s.ADSET
  1273. ,t.ADSET_ID = s.ADSET_ID
  1274. ,t.CAMPAIGN_ID = s.CAMPAIGN_ID
  1275. ,t.INFLUENCER_NAME = s.INFLUENCER_NAME
  1276. ,t.TEAM_ID = s.TEAM_ID
  1277. ,t.TROPHIES_CNT = s.TROPHIES_CNT
  1278. ,t.PERK_1_RESOURCE_NAME = s.PERK_1_RESOURCE_NAME
  1279. ,t.PERK_2_RESOURCE_NAME = s.PERK_2_RESOURCE_NAME
  1280. ,t.AF_KEYWORDS = s.AF_KEYWORDS
  1281. ,t.SITE_ID = s.SITE_ID
  1282. ,t.TEAM_NAME = s.TEAM_NAME
  1283. ,t.BOARD_ABILITY1_USES = s.BOARD_ABILITY1_USES
  1284. ,t.BOARD_ABILITY2_USES = s.BOARD_ABILITY2_USES
  1285. ,t.DEVICE_ID_HASH = s.DEVICE_ID_HASH
  1286. ,t.IS_PRIVATE = s.IS_PRIVATE
  1287. ,t.LIVE_EVENT_PIECES_COLLECTED = s.LIVE_EVENT_PIECES_COLLECTED
  1288. ,t.MUTATIONS_PICK = s.MUTATIONS_PICK
  1289. ,t.MUTATION_OPTIONS = s.MUTATION_OPTIONS
  1290. ,t.OUTFIT_ID = s.OUTFIT_ID
  1291. ,t.LIVE_EVENT_PIECES_SPAWNED = s.LIVE_EVENT_PIECES_SPAWNED
  1292. ,t.LIVE_EVENT_PIECES_ELIGIBLE = s.LIVE_EVENT_PIECES_ELIGIBLE
  1293. ,t.MATCH_MODIFIER_3 = s.MATCH_MODIFIER_3
  1294. ,t.IS_MC_MATCH = s.IS_MC_MATCH
  1295. ,t.LO_IS_FRIENDLY = s.LO_IS_FRIENDLY
  1296. ,t.TOURNAMENT_ID = s.TOURNAMENT_ID
  1297. ,t.CURRENT_TOURNAMENT_STAGE = s.CURRENT_TOURNAMENT_STAGE
  1298. ,t.STAKES_MULTIPLIER = s.STAKES_MULTIPLIER
  1299. ,t.ELO = s.ELO
  1300. ,t.USED_BOOSTER_BALANCE = s.USED_BOOSTER_BALANCE
  1301. ,t.BOOSTERS_LOST_AMOUNT = s.BOOSTERS_LOST_AMOUNT
  1302. ,t.RIVAL_ELO = s.RIVAL_ELO
  1303. ,t.TIMEUPS_CNT = s.TIMEUPS_CNT
  1304. ,t.LAST_UPDATED_DT = s.LAST_UPDATED_DT
  1305. ,t.DW_INSERT_DT = s.DW_INSERT_DT
  1306. ,t.bracket_count = s.bracket_count
  1307. ,t.bracket_index = s.bracket_index
  1308. ,t.premium_pass_end_time = s.premium_pass_end_time
  1309. ,t.relative_SR = s.relative_SR
  1310. ,t.premium_pass_active = s.premium_pass_active
  1311. ,t.rival_BOOSTER_TIER_ID = s.rival_BOOSTER_TIER_ID
  1312. ,t.rival_BOOSTER_TIER = s.rival_BOOSTER_TIER
  1313. ,t.ai_analyzer_type = s.ai_analyzer_type
  1314. ,t.ai_bad_play_target = s.ai_bad_play_target
  1315. ,t.rival_ai_analyzer_type = s.rival_ai_analyzer_type
  1316. ,t.rival_ai_bad_play_target = s.rival_ai_bad_play_target
  1317. ,t.app_original_version = s.app_original_version
  1318. ,t.square_shapes_cnt = s.square_shapes_cnt
  1319. ,t.segment = s.segment
  1320. ,t.PREVIOUS_ELO_BALANCE = s.PREVIOUS_ELO_BALANCE
  1321. ,t.elo_games = s.elo_games
  1322. ,t.rumble_type = s.rumble_type
  1323. ,t.current_round = s.current_round
  1324. ,t.initial_points = s.initial_points
  1325. ,t.rival_initial_points = s.rival_initial_points
  1326. ,t.rival_moves_left = s.rival_moves_left
  1327.  
  1328.  
  1329. WHEN NOT MATCHED THEN INSERT (
  1330. EVENT_ID
  1331. ,DERIVED_TSTAMP
  1332. ,MATCH_END_DATE
  1333. ,USER_ID
  1334. ,MATCH_SESSION_ID
  1335. ,IS_SUBSCRIBER
  1336. ,SUBSCRIPTION_TIER
  1337. ,SUBSCRIPTION_TIER_S
  1338. ,ARENA_GROUP
  1339. ,IS_PAYER
  1340. ,SENIORITY_BIN
  1341. ,LTV_GROUP
  1342. ,ENGAGEMENT_GROUP
  1343. ,MATCH_ID
  1344. ,PVP_MODE
  1345. ,REMATCH_CNT
  1346. ,MATCH_TYPE_ID
  1347. ,MATCH_TYPE
  1348. ,MATCH_SUB_TYPE
  1349. ,MATCH_MODE
  1350. ,MODIFIER_1
  1351. ,MODIFIER_2
  1352. ,CALENDAR_ENTRY_ID
  1353. ,ORIGINAL_CALENDAR_ENTRY_ID
  1354. ,DYNAMIC_CONFIG_ID
  1355. ,CONFIG_ID
  1356. ,LO_EVENT_ID
  1357. ,LEADERBOARD_CONFIG_TYPE
  1358. ,EVENT_SEGMENT
  1359. ,EVENT_SEGMENT_LOGIC
  1360. ,IS_PUSH_NOTIFICATION_ENABLE
  1361. ,IS_RANKED
  1362. ,TOURNAMENT_UI_ORDER
  1363. ,TOURNAMENT_SIZE
  1364. ,TOURNAMENT_ENTRY_COIN_PRICE
  1365. ,TOURNAMENT_MN_BOOSTER_RARITY
  1366. ,TOURNAMENT_MX_BOOSTER_RARITY
  1367. ,calendar_order
  1368. ,lo_entry_is_premium_pass_exclusive
  1369. ,lo_limited_boosters
  1370. ,lo_limited_boosters_are_whitelist
  1371. ,BOOSTER_TIER_ID
  1372. ,BOOSTER_TIER
  1373. ,RESOURCE_TYPE_ID
  1374. ,RESOURCE_TYPE
  1375. ,RESOURCE_SUB_TYPE_ID
  1376. ,RESOURCE_SUB_TYPE
  1377. ,RESOURCE_ID
  1378. ,BOOSTER_NAME
  1379. ,BOOSTER_RESOURCE_VERSION
  1380. ,BOOSTER_VALUE
  1381. ,ABILITY_ACTIVATED_CNT
  1382. ,ABILITY_FINAL_CHARGE_CNT
  1383. ,RIVAL_RESOURCE_TYPE
  1384. ,RIVAL_RESOURCE_SUB_TYPE
  1385. ,MATCH_DURATION_SEC
  1386. ,IS_BOT
  1387. ,IS_RIVAL_BOT
  1388. ,IS_WON
  1389. ,IS_TECHNICAL
  1390. ,IS_REMATCH
  1391. ,IS_DEVELOPER
  1392. ,IS_CONCEDED
  1393. ,TURN_INDEX
  1394. ,ROUNDS_CNT
  1395. ,MATCH_SCORE
  1396. ,IS_ON_FIRE_GAME
  1397. ,IS_REACHED_ON_FIRE
  1398. ,COINS_WON_AMT
  1399. ,TROPHIES_WON_AMT
  1400. ,WIN_STREAK_CNT
  1401. ,LOSE_STREAK_CNT
  1402. ,RESHUFFLE_CNT
  1403. ,EXTRA_MOVES_CNT
  1404. ,SPECIAL_EXPLODES_CNT
  1405. ,RED_COMBOS_CNT
  1406. ,GREEN_COMBOS_CNT
  1407. ,BLUE_COMBOS_CNT
  1408. ,YELLOW_COMBOS_CNT
  1409. ,ORANGE_COMBOS_CNT
  1410. ,PURPLE_COMBOS_CNT
  1411. ,WHITE_COMBOS_CNT
  1412. ,THREE_MATCHED_CNT
  1413. ,FOUR_MATCHED_CNT
  1414. ,FIVE_MATCHED_CNT
  1415. ,RSHAPED_MATCHED_CNT
  1416. ,BEST_MOVE_SCORE
  1417. ,TIMEBANK_LEFT
  1418. ,STARS_WON_AMT
  1419. ,PREVIOUS_TROPHIES_BALANCE
  1420. ,MATCH_BONUS_TROPHIES_CNT
  1421. ,MATCH_SKILL_RATING
  1422. ,RIVAL_USER_ID
  1423. ,RIVAL_SESSION_ID
  1424. ,RIVAL_BOOSTER_NAME
  1425. ,RIVAL_BOOSTER_VERSION
  1426. ,RIVAL_TROPHIES_BALANCE
  1427. ,RIVAL_WIN_RATE
  1428. ,ARENA_INDEX
  1429. ,MAX_PRIZE_ELIGIBILITY
  1430. ,MAX_PRIZE_WON
  1431. ,MOVES_LIMIT
  1432. ,MOVES_MADE
  1433. ,MOVES_LEFT
  1434. ,MATCH_RANK
  1435. ,ELIMINATION_ROUND
  1436. ,LEVELS
  1437. ,CS_RESYNCS
  1438. ,INVALID_SWAPS
  1439. ,ERROR_RESYNCS
  1440. ,RIVAL_ERROR_RESYNCS
  1441. ,ACTIVE_STYLE_PACK
  1442. ,SHOW_OFF_BADGE_ID
  1443. ,IN_GAME_SHOWN_TIME
  1444. ,FIRST_ROUND_SHOWN_TIME
  1445. ,TIMEBANK_SHOWN_RIVAL
  1446. ,CONTEXT_SWITCHES
  1447. ,INVALID_SWIPES
  1448. ,EMOTE_SENT
  1449. ,RIVAL_EMOTE_SENT
  1450. ,MATCH_MAKING_FOUND_TIME
  1451. ,TIMEBANK_SHOWN_PLAYER_AMT
  1452. ,RIVAL_PERK_1_RESOURCE_NAME
  1453. ,RIVAL_PERK_2_RESOURCE_NAME
  1454. ,RIVAL_MATCH_SCORE
  1455. ,RIVAL_AI_LEVEL
  1456. ,RIVAL_PLAYED_OUT_OF_TIME
  1457. ,TOTAL_RIVAL_PLAYED_OUT_OF_TIME_SEC
  1458. ,LO_EVENT_TYPE
  1459. ,LO_EVENT_CONFIG_ID
  1460. ,USER_NAME
  1461. ,COUNTRY
  1462. ,LANGUAGE
  1463. ,DEVICE_LANGUAGE
  1464. ,TEST_NAME
  1465. ,TEST_GROUP_NAME
  1466. ,FB_USER_ID
  1467. ,CLIENT_IP
  1468. ,IS_TESTING_USER
  1469. ,DEVICE_MODEL
  1470. ,DEVICE_OS
  1471. ,PLATFORM
  1472. ,MEDIA_SOURCE
  1473. ,APP_VERSION
  1474. ,APP_MINOR_VERSION
  1475. ,FIRST_INSTALL_DT
  1476. ,ADVERTISER_ID
  1477. ,APPSFLYER_ID
  1478. ,AD_NAME
  1479. ,CAMPAIGN_NAME
  1480. ,TOTAL_IAP_AMT
  1481. ,STARS_CNT
  1482. ,LT_MATCHES_PLAYED_CNT
  1483. ,LT_MATCHES_WON_CNT
  1484. ,LT_PURCHASES_AMT
  1485. ,EVENT_SOURCE
  1486. ,IS_SHIELDED_GAME
  1487. ,RUMBLE_SIZE
  1488. ,CURRENT_ARENA_INDEX
  1489. ,IS_RANDOM_MIXER
  1490. ,AD_ID
  1491. ,ADSET
  1492. ,ADSET_ID
  1493. ,CAMPAIGN_ID
  1494. ,INFLUENCER_NAME
  1495. ,TEAM_ID
  1496. ,TROPHIES_CNT
  1497. ,PERK_1_RESOURCE_NAME
  1498. ,PERK_2_RESOURCE_NAME
  1499. ,AF_KEYWORDS
  1500. ,SITE_ID
  1501. ,TEAM_NAME
  1502. ,BOARD_ABILITY1_USES
  1503. ,BOARD_ABILITY2_USES
  1504. ,DEVICE_ID_HASH
  1505. ,IS_PRIVATE
  1506. ,LIVE_EVENT_PIECES_COLLECTED
  1507. ,MUTATIONS_PICK
  1508. ,MUTATION_OPTIONS
  1509. ,OUTFIT_ID
  1510. ,LIVE_EVENT_PIECES_SPAWNED
  1511. ,LIVE_EVENT_PIECES_ELIGIBLE
  1512. ,MATCH_MODIFIER_3
  1513. ,IS_MC_MATCH
  1514. ,LO_IS_FRIENDLY
  1515. ,TOURNAMENT_ID
  1516. ,CURRENT_TOURNAMENT_STAGE
  1517. ,STAKES_MULTIPLIER
  1518. ,ELO
  1519. ,USED_BOOSTER_BALANCE
  1520. ,BOOSTERS_LOST_AMOUNT
  1521. ,RIVAL_ELO
  1522. ,TIMEUPS_CNT
  1523. ,LAST_UPDATED_DT
  1524. ,DW_INSERT_DT
  1525. ,bracket_count
  1526. ,bracket_index
  1527. ,premium_pass_end_time
  1528. ,relative_SR
  1529. ,premium_pass_active
  1530. ,rival_BOOSTER_TIER_ID
  1531. ,rival_BOOSTER_TIER
  1532. ,ai_analyzer_type
  1533. ,ai_bad_play_target
  1534. ,rival_ai_analyzer_type
  1535. ,rival_ai_bad_play_target
  1536. ,app_original_version
  1537. ,square_shapes_cnt
  1538. ,segment
  1539. ,PREVIOUS_ELO_BALANCE
  1540. ,elo_games
  1541. ,rumble_type
  1542. ,current_round
  1543. ,initial_points
  1544. ,rival_initial_points
  1545. ,rival_moves_left
  1546. )
  1547. VALUES (
  1548. s.EVENT_ID
  1549. ,s.DERIVED_TSTAMP
  1550. ,s.MATCH_END_DATE
  1551. ,s.USER_ID
  1552. ,s.MATCH_SESSION_ID
  1553. ,s.IS_SUBSCRIBER
  1554. ,s.SUBSCRIPTION_TIER
  1555. ,s.SUBSCRIPTION_TIER_S
  1556. ,s.ARENA_GROUP
  1557. ,s.IS_PAYER
  1558. ,s.SENIORITY_BIN
  1559. ,s.LTV_GROUP
  1560. ,s.ENGAGEMENT_GROUP
  1561. ,s.MATCH_ID
  1562. ,s.PVP_MODE
  1563. ,s.REMATCH_CNT
  1564. ,s.MATCH_TYPE_ID
  1565. ,s.MATCH_TYPE
  1566. ,s.MATCH_SUB_TYPE
  1567. ,s.MATCH_MODE
  1568. ,s.MODIFIER_1
  1569. ,s.MODIFIER_2
  1570. ,s.CALENDAR_ENTRY_ID
  1571. ,s.ORIGINAL_CALENDAR_ENTRY_ID
  1572. ,s.DYNAMIC_CONFIG_ID
  1573. ,s.CONFIG_ID
  1574. ,s.LO_EVENT_ID
  1575. ,s.LEADERBOARD_CONFIG_TYPE
  1576. ,s.EVENT_SEGMENT
  1577. ,s.EVENT_SEGMENT_LOGIC
  1578. ,s.IS_PUSH_NOTIFICATION_ENABLE
  1579. ,s.IS_RANKED
  1580. ,s.TOURNAMENT_UI_ORDER
  1581. ,s.TOURNAMENT_SIZE
  1582. ,s.TOURNAMENT_ENTRY_COIN_PRICE
  1583. ,s.TOURNAMENT_MN_BOOSTER_RARITY
  1584. ,s.TOURNAMENT_MX_BOOSTER_RARITY
  1585. ,s.calendar_order
  1586. ,s.lo_entry_is_premium_pass_exclusive
  1587. ,s.lo_limited_boosters
  1588. ,s.lo_limited_boosters_are_whitelist
  1589. ,s.BOOSTER_TIER_ID
  1590. ,s.BOOSTER_TIER
  1591. ,s.RESOURCE_TYPE_ID
  1592. ,s.RESOURCE_TYPE
  1593. ,s.RESOURCE_SUB_TYPE_ID
  1594. ,s.RESOURCE_SUB_TYPE
  1595. ,s.RESOURCE_ID
  1596. ,s.BOOSTER_NAME
  1597. ,s.BOOSTER_RESOURCE_VERSION
  1598. ,s.BOOSTER_VALUE
  1599. ,s.ABILITY_ACTIVATED_CNT
  1600. ,s.ABILITY_FINAL_CHARGE_CNT
  1601. ,s.RIVAL_RESOURCE_TYPE
  1602. ,s.RIVAL_RESOURCE_SUB_TYPE
  1603. ,s.MATCH_DURATION_SEC
  1604. ,s.IS_BOT
  1605. ,s.IS_RIVAL_BOT
  1606. ,s.IS_WON
  1607. ,s.IS_TECHNICAL
  1608. ,s.IS_REMATCH
  1609. ,s.IS_DEVELOPER
  1610. ,s.IS_CONCEDED
  1611. ,s.TURN_INDEX
  1612. ,s.ROUNDS_CNT
  1613. ,s.MATCH_SCORE
  1614. ,s.IS_ON_FIRE_GAME
  1615. ,s.IS_REACHED_ON_FIRE
  1616. ,s.COINS_WON_AMT
  1617. ,s.TROPHIES_WON_AMT
  1618. ,s.WIN_STREAK_CNT
  1619. ,s.LOSE_STREAK_CNT
  1620. ,s.RESHUFFLE_CNT
  1621. ,s.EXTRA_MOVES_CNT
  1622. ,s.SPECIAL_EXPLODES_CNT
  1623. ,s.RED_COMBOS_CNT
  1624. ,s.GREEN_COMBOS_CNT
  1625. ,s.BLUE_COMBOS_CNT
  1626. ,s.YELLOW_COMBOS_CNT
  1627. ,s.ORANGE_COMBOS_CNT
  1628. ,s.PURPLE_COMBOS_CNT
  1629. ,s.WHITE_COMBOS_CNT
  1630. ,s.THREE_MATCHED_CNT
  1631. ,s.FOUR_MATCHED_CNT
  1632. ,s.FIVE_MATCHED_CNT
  1633. ,s.RSHAPED_MATCHED_CNT
  1634. ,s.BEST_MOVE_SCORE
  1635. ,s.TIMEBANK_LEFT
  1636. ,s.STARS_WON_AMT
  1637. ,s.PREVIOUS_TROPHIES_BALANCE
  1638. ,s.MATCH_BONUS_TROPHIES_CNT
  1639. ,s.MATCH_SKILL_RATING
  1640. ,s.RIVAL_USER_ID
  1641. ,s.RIVAL_SESSION_ID
  1642. ,s.RIVAL_BOOSTER_NAME
  1643. ,s.RIVAL_BOOSTER_VERSION
  1644. ,s.RIVAL_TROPHIES_BALANCE
  1645. ,s.RIVAL_WIN_RATE
  1646. ,s.ARENA_INDEX
  1647. ,s.MAX_PRIZE_ELIGIBILITY
  1648. ,s.MAX_PRIZE_WON
  1649. ,s.MOVES_LIMIT
  1650. ,s.MOVES_MADE
  1651. ,s.MOVES_LEFT
  1652. ,s.MATCH_RANK
  1653. ,s.ELIMINATION_ROUND
  1654. ,s.LEVELS
  1655. ,s.CS_RESYNCS
  1656. ,s.INVALID_SWAPS
  1657. ,s.ERROR_RESYNCS
  1658. ,s.RIVAL_ERROR_RESYNCS
  1659. ,s.ACTIVE_STYLE_PACK
  1660. ,s.SHOW_OFF_BADGE_ID
  1661. ,s.IN_GAME_SHOWN_TIME
  1662. ,s.FIRST_ROUND_SHOWN_TIME
  1663. ,s.TIMEBANK_SHOWN_RIVAL
  1664. ,s.CONTEXT_SWITCHES
  1665. ,s.INVALID_SWIPES
  1666. ,s.EMOTE_SENT
  1667. ,s.RIVAL_EMOTE_SENT
  1668. ,s.MATCH_MAKING_FOUND_TIME
  1669. ,s.TIMEBANK_SHOWN_PLAYER_AMT
  1670. ,s.RIVAL_PERK_1_RESOURCE_NAME
  1671. ,s.RIVAL_PERK_2_RESOURCE_NAME
  1672. ,s.RIVAL_MATCH_SCORE
  1673. ,s.RIVAL_AI_LEVEL
  1674. ,s.RIVAL_PLAYED_OUT_OF_TIME
  1675. ,s.TOTAL_RIVAL_PLAYED_OUT_OF_TIME_SEC
  1676. ,s.LO_EVENT_TYPE
  1677. ,s.LO_EVENT_CONFIG_ID
  1678. ,s.USER_NAME
  1679. ,s.COUNTRY
  1680. ,s.LANGUAGE
  1681. ,s.DEVICE_LANGUAGE
  1682. ,s.TEST_NAME
  1683. ,s.TEST_GROUP_NAME
  1684. ,s.FB_USER_ID
  1685. ,s.CLIENT_IP
  1686. ,s.IS_TESTING_USER
  1687. ,s.DEVICE_MODEL
  1688. ,s.DEVICE_OS
  1689. ,s.PLATFORM
  1690. ,s.MEDIA_SOURCE
  1691. ,s.APP_VERSION
  1692. ,s.APP_MINOR_VERSION
  1693. ,s.FIRST_INSTALL_DT
  1694. ,s.ADVERTISER_ID
  1695. ,s.APPSFLYER_ID
  1696. ,s.AD_NAME
  1697. ,s.CAMPAIGN_NAME
  1698. ,s.TOTAL_IAP_AMT
  1699. ,s.STARS_CNT
  1700. ,s.LT_MATCHES_PLAYED_CNT
  1701. ,s.LT_MATCHES_WON_CNT
  1702. ,s.LT_PURCHASES_AMT
  1703. ,s.EVENT_SOURCE
  1704. ,s.IS_SHIELDED_GAME
  1705. ,s.RUMBLE_SIZE
  1706. ,s.CURRENT_ARENA_INDEX
  1707. ,s.IS_RANDOM_MIXER
  1708. ,s.AD_ID
  1709. ,s.ADSET
  1710. ,s.ADSET_ID
  1711. ,s.CAMPAIGN_ID
  1712. ,s.INFLUENCER_NAME
  1713. ,s.TEAM_ID
  1714. ,s.TROPHIES_CNT
  1715. ,s.PERK_1_RESOURCE_NAME
  1716. ,s.PERK_2_RESOURCE_NAME
  1717. ,s.AF_KEYWORDS
  1718. ,s.SITE_ID
  1719. ,s.TEAM_NAME
  1720. ,s.BOARD_ABILITY1_USES
  1721. ,s.BOARD_ABILITY2_USES
  1722. ,s.DEVICE_ID_HASH
  1723. ,s.IS_PRIVATE
  1724. ,s.LIVE_EVENT_PIECES_COLLECTED
  1725. ,s.MUTATIONS_PICK
  1726. ,s.MUTATION_OPTIONS
  1727. ,s.OUTFIT_ID
  1728. ,s.LIVE_EVENT_PIECES_SPAWNED
  1729. ,s.LIVE_EVENT_PIECES_ELIGIBLE
  1730. ,s.MATCH_MODIFIER_3
  1731. ,s.IS_MC_MATCH
  1732. ,s.LO_IS_FRIENDLY
  1733. ,s.TOURNAMENT_ID
  1734. ,s.CURRENT_TOURNAMENT_STAGE
  1735. ,s.STAKES_MULTIPLIER
  1736. ,s.ELO
  1737. ,s.USED_BOOSTER_BALANCE
  1738. ,s.BOOSTERS_LOST_AMOUNT
  1739. ,s.RIVAL_ELO
  1740. ,s.TIMEUPS_CNT
  1741. ,s.LAST_UPDATED_DT
  1742. ,s.DW_INSERT_DT
  1743. ,s.bracket_count
  1744. ,s.bracket_index
  1745. ,s.premium_pass_end_time
  1746. ,s.relative_SR
  1747. ,s.premium_pass_active
  1748. ,s.rival_BOOSTER_TIER_ID
  1749. ,s.rival_BOOSTER_TIER
  1750. ,s.ai_analyzer_type
  1751. ,s.ai_bad_play_target
  1752. ,s.rival_ai_analyzer_type
  1753. ,s.rival_ai_bad_play_target
  1754. ,s.app_original_version
  1755. ,s.square_shapes_cnt
  1756. ,s.segment
  1757. ,s.PREVIOUS_ELO_BALANCE
  1758. ,s.elo_games
  1759. ,s.rumble_type
  1760. ,s.current_round
  1761. ,s.initial_points
  1762. ,s.rival_initial_points
  1763. ,s.rival_moves_left
  1764.  
  1765. );
  1766. ''',
  1767. '07_update_mng_table': '''
  1768. INSERT INTO MATCH_MASTERS.MANAGE.MANAGEMENT_TABLE(
  1769. TABLE_NAME
  1770. ,DW_INSERT_DATE
  1771. ,LAST_LOAD_DATE
  1772. ,ROWS_LOADED
  1773. ) VALUES (
  1774. 'CANDIVORE.PROD.F_USER_MATCH'
  1775. ,current_timestamp()
  1776. ,(select max(LAST_UPDATED_DT) from MATCH_MASTERS.TRANSFORMATION.TRANS_USER_MATCH)
  1777. ,(select count(*) from MATCH_MASTERS.TRANSFORMATION.TRANS_USER_MATCH)
  1778. )
  1779. '''
  1780. }
  1781.  
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!