API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Jul 11th, 2023
152
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.62 KB | None | 0 0
raw download clone embed print report
  1. var my_sql_command = `insert into STAGING.TRANSFORM_TABLES.RN_USER_MATCH
  2. SELECT DISTINCT
  3. UM.EVENT_ID
  4. ,UM.derived_tstamp
  5. ,UM.MATCH_END_DATE
  6. ,UM.USER_ID
  7. ,UM.MATCH_SESSION_ID
  8. ,S.IS_SUBSCRIBER
  9. ,S.SUBSCRIPTION_TIER
  10. ,S.SUBSCRIPTION_TIER_S
  11. ,UM.Arena_Group
  12. ,UM.IS_PAYER
  13. ,UM.SENIORITY_BIN
  14. ,UM.LTV_GROUP
  15. ,UM.ENGAGEMENT_GROUP
  16. ,UM.MATCH_ID
  17. ,row_number() over (partition by UM.user_id, UM.match_end_date order by UM.derived_tstamp desc) as RN
  18. ,UM.PVP_MODE
  19. ,UM.REMATCH_CNT
  20. ,MATCH_EVENT_TYPE_ID as match_type_id
  21. ,ifnull(case when UM.MATCH_EVENT_TYPE_ID = 7 then LE.lo_event_type end,MATCH_EVENT_TYPE) as MATCH_TYPE
  22. ,ifnull(case when UM.MATCH_EVENT_TYPE_ID = 7 then CALENDAR.LO_EVENT_CONFIG_ID end,MATCH_EVENT_TYPE) as MATCH_SUB_TYPE
  23. ,case when UM.MATCH_EVENT_TYPE_ID = 7 and LE.LO_EVENT1_MATCH_MODE_TYPE is not null then LE.LO_EVENT1_MATCH_MODE_TYPE else MATCH_MODE_TYPE end as MATCH_MODE
  24. ,CASE WHEN MATCH_EVENT_TYPE_ID = 4 THEN CALENDAR.LO_ENTRY_MODIFIER_TYPE1 ELSE MATCH_MODIFIER_1 END as modifier_1
  25. ,CASE WHEN MATCH_EVENT_TYPE_ID = 4 THEN CALENDAR.LO_ENTRY_MODIFIER_TYPE2 ELSE MATCH_MODIFIER_2 END as modifier_2
  26. ,MATCH_LOP_EVENT_ID as calendar_entry_id
  27. ,CALENDAR.LO_ORIGINAL_ENTRY_ID AS ORIGINAL_CALENDAR_ENTRY_ID
  28. ,calendar.lo_event_dynamic_config_id as dynamic_config_id
  29. ,calendar.lo_event_config_id as config_id
  30. ,LE.lo_event_id
  31. ,LE.lo_solo_leaderboard_event_id as leaderboard_config_type
  32. ,LE.LO_EVENT1_SEGMENT AS EVENT_SEGMENT
  33. ,LE.LO_EVENT_SEGMENT_LOGIC AS EVENT_SEGMENT_LOGIC
  34. ,CALENDAR.LO_ENTRY_PUSH_NOTIFICATION_ENABLE AS IS_PUSH_NOTIFICATION_ENABLE
  35. ,CALENDAR.LO_ENTRY_PLAY_FOR_TROPHIES AS IS_RANKED
  36. ,CALENDAR.calendar_order AS TOURNAMENT_UI_ORDER
  37. ,CALENDAR.LO_ENTRY_TOURNAMENT_SIZE AS TOURNAMENT_SIZE
  38. ,CALENDAR.LO_TOURNAMENT_ENTRY_PRICE_COINS AS TOURNAMENT_ENTRY_COIN_PRICE
  39. ,CALENDAR.LO_ENTRY_MIN_BOOSTER_RARITY AS TOURNAMENT_MN_BOOSTER_RARITY
  40. ,CALENDAR.LO_ENTRY_MAX_BOOSTER_RARITY AS TOURNAMENT_MX_BOOSTER_RARITY
  41. ,R1.BOOSTER_TIER as BOOSTER_TIER_ID
  42. ,R1.BOOSTER_TIER_GROUP_NAME as BOOSTER_TIER
  43. ,R1.RESOURCE_TYPE_ID
  44. ,R1.RESOURCE_TYPE
  45. ,R1.RESOURCE_SUB_TYPE_ID
  46. ,R1.RESOURCE_SUB_TYPE
  47. ,R1.RESOURCE_ID
  48. ,BOOSTER_RESOURCE_NAME AS BOOSTER_NAME
  49. ,BOOSTER_RESOURCE_VERSION
  50. ,R1.RESOURCE_COIN_VALUE as BOOSTER_VALUE
  51. ,ABILITY_ACTIVATED_CNT
  52. ,ABILITY_FINAL_CHARGE_CNT
  53. ,R2.RESOURCE_TYPE as RIVAL_RESOURCE_TYPE
  54. ,R2.RESOURCE_SUB_TYPE as RIVAL_RESOURCE_SUB_TYPE
  55. ,MATCH_DURATION_SEC
  56. ,IS_BOT
  57. ,IS_RIVAL_BOT
  58. ,IS_WON
  59. ,IS_TECHNICAL
  60. ,IS_REMATCH
  61. ,IS_DEVELOPER
  62. ,IS_CONCEDED
  63. ,TURN_INDEX
  64. ,ROUNDS_CNT
  65. ,MATCH_SCORE
  66. ,IS_ON_FIRE_GAME
  67. ,IS_REACHED_ON_FIRE
  68. ,COINS_WON_AMT
  69. ,TROPHIES_WON_AMT
  70. ,WIN_STREAK_CNT
  71. ,LOSE_STREAK_CNT
  72. ,RESHUFFLE_CNT
  73. ,EXTRA_MOVES_CNT
  74. ,SPECIAL_EXPLODES_CNT
  75. ,RED_COMBOS_CNT
  76. ,GREEN_COMBOS_CNT
  77. ,BLUE_COMBOS_CNT
  78. ,YELLOW_COMBOS_CNT
  79. ,ORANGE_COMBOS_CNT
  80. ,PURPLE_COMBOS_CNT
  81. ,WHITE_COMBOS_CNT
  82. ,THREE_MATCHED_CNT
  83. ,FOUR_MATCHED_CNT
  84. ,FIVE_MATCHED_CNT
  85. ,RSHAPED_MATCHED_CNT
  86. ,BEST_MOVE_SCORE
  87. ,TIMEBANK_LEFT
  88. ,STARS_WON_AMT
  89. ,PREVIOUS_TROPHIES_BALANCE
  90. ,MATCH_BONUS_TROPHIES_CNT
  91. ,MATCH_SKILL_RATING
  92. ,RIVAL_USER_ID
  93. ,RIVAL_SESSION_ID
  94. ,RIVAL_BOOSTER_RESOURCE_NAME AS RIVAL_BOOSTER_NAME
  95. ,RIVAL_BOOSTER_RESOURCE_VERSION AS RIVAL_BOOSTER_VERSION
  96. ,PERK_1_RESOURCE_NAME
  97. ,PERK_2_RESOURCE_NAME
  98. ,RIVAL_TROPHIES_BALANCE
  99. ,RIVAL_WIN_RATE
  100. ,ARENA_INDEX
  101. ,MAX_PRIZE_ELIGIBILITY
  102. ,MAX_PRIZE_WON
  103. ,MOVES_LIMIT
  104. ,MOVES_MADE
  105. ,MOVES_LEFT
  106. ,MATCH_RANK
  107. ,ELIMINATION_ROUND
  108. ,LEVELS
  109. ,CS_RESYNCS
  110. ,INVALID_SWAPS
  111. ,ERROR_RESYNCS
  112. ,RIVAL_ERROR_RESYNCS
  113. ,ACTIVE_STYLE_PACK
  114. ,SHOW_OFF_BADGE_ID
  115. ,IN_GAME_SHOWN_TIME
  116. ,FIRST_ROUND_SHOWN_TIME
  117. ,TIMEBANK_SHOWN_RIVAL
  118. ,CONTEXT_SWITCHES
  119. ,INVALID_SWIPES
  120. ,EMOTE_SENT
  121. ,RIVAL_EMOTE_SENT
  122. ,MATCH_MAKING_FOUND_TIME
  123. ,TIMEBANK_SHOWN_PLAYER_AMT
  124. ,RIVAL_PERK_1_RESOURCE_NAME
  125. ,RIVAL_PERK_2_RESOURCE_NAME
  126. ,RIVAL_MATCH_SCORE
  127. ,RIVAL_AI_LEVEL
  128. ,RIVAL_PLAYED_OUT_OF_TIME
  129. ,TOTAL_RIVAL_PLAYED_OUT_OF_TIME_SEC
  130. ,LE.LO_EVENT_TYPE
  131. ,LE.LO_EVENT_CONFIG_ID
  132. ,UM.user_name
  133. ,UM.country
  134. ,UM.language
  135. ,UM.device_language
  136. ,UM.test_name
  137. ,UM.test_group_name
  138. ,UM.fb_user_ID
  139. ,UM.client_IP
  140. ,UM.is_testing_user
  141. ,UM.device_ID
  142. ,UM.device_model
  143. ,UM.device_os
  144. ,UM.platform
  145. ,UM.media_source
  146. ,UM.app_version
  147. ,UM.app_minor_version
  148. ,UM.first_install_DT
  149. ,UM.advertiser_ID
  150. ,UM.appsflyer_id
  151. ,UM.ad_name
  152. ,UM.campaign_name
  153. ,UM.total_IAP_amt
  154. ,UM.stars_cnt
  155. ,UM.LT_matches_played_cnt
  156. ,UM.LT_matches_won_cnt
  157. ,UM.LT_purchases_amt
  158. ,UM.event_source
  159. ,UM.is_shielded_game
  160. ,CASE WHEN LE.lo_event_id like (''%rumble%'') THEN
  161. TRY_TO_NUMBER(split_part(LE.lo_event_id, ''-'', 3)) else Null END AS rumble_size
  162. ,um.current_arena_index
  163. ,um.is_random_mixer
  164.  
  165. ,um.ad_id
  166. ,um.adset
  167. , um.adset_id
  168. ,um.campaign_id
  169. ,um.influencer_name
  170. ,um.team_ID
  171. ,um.trophies_cnt
  172.  
  173. ,um.af_keywords
  174. ,um.site_id
  175. ,um.team_name
  176. ,um.board_ability1_uses
  177. ,um.board_ability2_uses
  178. ,um.device_id_hash
  179. ,um.is_private
  180. ,um.live_event_pieces_collected
  181. ,um.mutations_pick
  182. ,um.mutation_options
  183. ,um.outfit_id
  184. ,um.live_event_pieces_spawned
  185. ,um.live_event_pieces_eligible
  186. ,um.match_modifier_3
  187. FROM
  188.  
  189. (
  190. SELECT distinct
  191. SERVER_EVENT_ID as event_id
  192. ,MATCH_END_DATE
  193. ,server_event_id
  194. ,server_derived_tstamp as derived_tstamp
  195. ,USER_ID
  196. ,MATCH_ID
  197. ,PVP_MODE
  198. ,REMATCH_CNT
  199. ,MATCH_SESSION_ID
  200. ,iff(SUBSCRIPTION_TIER=0, false,true) as IS_SUBSCRIBER
  201. ,SUBSCRIPTION_TIER
  202. ,CASE WHEN current_arena_index <=13 THEN ''Studios''
  203. WHEN current_arena_index between 14 and 22 THEN ''Master League''
  204. ELSE ''Legends League'' END as Arena_Group
  205. ,CASE WHEN TOTAL_IAP_AMT > 0 THEN True
  206. ELSE False
  207. END AS IS_PAYER
  208. , DATE(MATCH_END_DATE)-DATE(FIRST_INSTALL_DT) as seniority
  209. , CASE WHEN seniority<=2 THEN ''1_2''
  210. WHEN seniority<=10 THEN ''3-10''
  211. WHEN seniority<=30 THEN ''11_30''
  212. WHEN seniority<=90 THEN ''31_90''
  213. WHEN seniority<=180 THEN ''91_180''
  214. WHEN seniority<=360 THEN ''181_360''
  215. WHEN seniority<=720 THEN ''361_720''
  216. WHEN seniority>720 THEN ''>720''
  217. ELSE NULL
  218. END AS seniority_bin
  219. , CASE WHEN STARS_CNT*7>=70 THEN ''4 Very High''
  220. WHEN STARS_CNT*7>=30 THEN ''3 High''
  221. WHEN STARS_CNT*7>=10 THEN ''2 Med''
  222. WHEN STARS_CNT*7>=0 THEN ''1 Low''
  223. ELSE NULL
  224. END as ENGAGEMENT_GROUP
  225. , CASE WHEN TO_NUMBER(TOTAL_IAP_AMT) = 0 THEN ''0''
  226. WHEN TO_NUMBER(TOTAL_IAP_AMT) <= 10 THEN ''Low''
  227. WHEN TO_NUMBER(TOTAL_IAP_AMT) <= 100 THEN ''Med''
  228. WHEN TO_NUMBER(TOTAL_IAP_AMT) <= 299 THEN ''High''
  229. WHEN TO_NUMBER(TOTAL_IAP_AMT) <= 999 THEN ''Very High''
  230. WHEN TO_NUMBER(TOTAL_IAP_AMT) >= 1000 THEN ''VIP''
  231. ELSE NULL
  232. END AS LTV_group
  233. ,MATCH_MODE_TYPE
  234. ,MATCH_MODIFIER_1
  235. ,MATCH_MODIFIER_2
  236. ,MATCH_EVENT_TYPE_ID
  237. ,CASE WHEN MATCH_EVENT_TYPE_ID = 2 THEN ''Classic''
  238. WHEN MATCH_EVENT_TYPE_ID = 3 THEN ''Challenge''
  239. WHEN MATCH_EVENT_TYPE_ID = 4 THEN ''Tournament''
  240. WHEN MATCH_EVENT_TYPE_ID = 5 THEN ''Daily''
  241. WHEN MATCH_EVENT_TYPE_ID = 7 THEN ''Other''
  242. ELSE ''Other''
  243. END as MATCH_EVENT_TYPE
  244. ,CASE WHEN MATCH_EVENT_TYPE_ID in (4,5) THEN MATCH_MODE_TYPE
  245. WHEN MATCH_EVENT_TYPE_ID = 7 THEN MATCH_LOP_EVENT_TYPE
  246. ELSE ''N/A''
  247. END as MATCH_MODE
  248. ,BOOSTER_RESOURCE_NAME
  249. ,BOOSTER_RESOURCE_VERSION
  250. ,PERK_1_RESOURCE_NAME
  251. ,PERK_2_RESOURCE_NAME
  252. ,ABILITY_ACTIVATED_CNT
  253. ,ABILITY_FINAL_CHARGE_CNT
  254.  
  255. ,RIVAL_PLAYED_OUT_OF_TIME
  256. ,RIVAL_BOOSTER_RESOURCE_NAME
  257. ,RIVAL_BOOSTER_RESOURCE_VERSION
  258. ,TOTAL_RIVAL_PLAYED_OUT_OF_TIME_SEC
  259. ,MATCH_MAKING_FOUND_TIME
  260. ,TIMEBANK_SHOWN_PLAYER_AMT
  261. ,ROUNDS_CNT
  262. ,COINS_WON_AMT
  263. ,TROPHIES_WON_AMT
  264. ,IFF(IS_WON = TRUE, 1, 0) as IS_WON
  265. ,MATCH_SCORE
  266. ,MATCH_DURATION_SEC
  267. ,TURN_INDEX
  268. ,IFF(IS_BOT = TRUE , 1,0) as IS_BOT
  269. ,IFF(IS_TECHNICAL = TRUE, 1, 0) as IS_TECHNICAL
  270. ,IFF(IS_REMATCH = TRUE , 1,0) as IS_REMATCH
  271. ,IFF(IS_DEVELOPER = TRUE , 1,0) as IS_DEVELOPER
  272. ,IFF(IS_CONCEDED = TRUE , 1,0) as IS_CONCEDED
  273. ,IFF(IS_RIVAL_BOT = TRUE,1,0) as IS_RIVAL_BOT
  274. ,IFF(IS_ON_FIRE_GAME = TRUE,1,0) as IS_ON_FIRE_GAME
  275. ,IFF(IS_REACHED_ON_FIRE = TRUE,1,0) as IS_REACHED_ON_FIRE
  276. ,MATCH_LOP_EVENT_ID
  277. ,MATCH_LOP_EVENT_TYPE
  278. ,WIN_STREAK_CNT
  279. ,LOSE_STREAK_CNT
  280. ,RESHUFFLE_CNT
  281. ,EXTRA_MOVES_CNT
  282. ,SPECIAL_EXPLODES_CNT
  283. ,RED_COMBOS_CNT
  284. ,GREEN_COMBOS_CNT
  285. ,BLUE_COMBOS_CNT
  286. ,YELLOW_COMBOS_CNT
  287. ,ORANGE_COMBOS_CNT
  288. ,PURPLE_COMBOS_CNT
  289. ,WHITE_COMBOS_CNT
  290. ,THREE_MATCHED_CNT
  291. ,FOUR_MATCHED_CNT
  292. ,FIVE_MATCHED_CNT
  293. ,RSHAPED_MATCHED_CNT
  294. ,BEST_MOVE_SCORE
  295. ,TIMEBANK_LEFT
  296. ,STARS_WON_AMT
  297. ,PREVIOUS_TROPHIES_BALANCE
  298. ,MATCH_BONUS_TROPHIES_CNT
  299. ,MATCH_SKILL_RATING
  300. ,RIVAL_USER_ID
  301. ,RIVAL_SESSION_ID
  302. ,RIVAL_PERK_1_RESOURCE_NAME
  303. ,RIVAL_PERK_2_RESOURCE_NAME
  304. ,RIVAL_TROPHIES_BALANCE
  305. ,RIVAL_WIN_RATE
  306. ,RIVAL_MATCH_SCORE
  307. ,RIVAL_AI_LEVEL
  308. ,ARENA_INDEX
  309. ,MAX_PRIZE_ELIGIBILITY
  310. ,MAX_PRIZE_WON
  311. ,MOVES_LIMIT
  312. ,MOVES_MADE
  313. ,MOVES_LEFT
  314. ,LEVELS
  315. ,CS_RESYNCS
  316. ,MATCH_RANK
  317. ,ELIMINATION_ROUND
  318. ,INVALID_SWAPS
  319. ,ERROR_RESYNCS
  320. ,RIVAL_ERROR_RESYNCS
  321. ,ACTIVE_STYLE_PACK
  322. ,SHOW_OFF_BADGE_ID
  323. ,IN_GAME_SHOWN_TIME
  324. ,FIRST_ROUND_SHOWN_TIME
  325. ,TIMEBANK_SHOWN_RIVAL
  326. ,CONTEXT_SWITCHES
  327. ,INVALID_SWIPES
  328. ,EMOTE_SENT
  329. ,RIVAL_EMOTE_SENT
  330. ,user_name
  331. ,country
  332. ,language
  333. ,device_language
  334. ,test_name
  335. ,test_group_name
  336. ,fb_user_ID
  337. ,client_IP
  338. ,is_testing_user
  339. ,device_ID
  340. ,device_model
  341. ,device_os
  342. ,platform
  343. ,media_source
  344. ,app_version
  345. ,app_minor_version
  346. ,first_install_DT
  347. ,advertiser_ID
  348. ,appsflyer_id
  349. ,ad_name
  350. ,campaign_name
  351. ,total_IAP_amt
  352. ,stars_cnt
  353. ,LT_matches_played_cnt
  354. ,LT_matches_won_cnt
  355. ,LT_purchases_amt
  356. ,event_source
  357. ,is_shielded_game
  358. ,current_arena_index
  359. ,is_random_mixer
  360.  
  361. ,ad_id
  362. ,adset
  363. ,adset_id
  364. ,campaign_id
  365. ,influencer_name
  366. ,team_ID
  367. ,trophies_cnt
  368.  
  369. ,af_keywords
  370. ,site_id
  371. ,team_name
  372. ,board_ability1_uses
  373. ,board_ability2_uses
  374. ,device_id_hash
  375. ,is_private
  376. ,live_event_pieces_collected
  377. ,mutations_pick
  378. ,mutation_options
  379. ,outfit_id
  380. ,live_event_pieces_spawned
  381. ,live_event_pieces_eligible
  382. ,match_modifier_3
  383. FROM SNOWPLOW.DERIVED.USER_MATCH_TOTAL
  384. WHERE MATCH_END_DATE IS NOT NULL
  385. and match_end_date >= dateadd(day, -1,date(current_date())) and event_id not in (select
  386. distinct event_id from STAGING.TRANSFORM_TABLES.RN_USER_MATCH where
  387. match_end_date >= dateadd(day, -1,date(current_date())))
  388. and is_bot = false
  389. QUALIFY ROW_NUMBER() OVER (PARTITION by concat(user_id,match_id,rematch_cnt)
  390. ORDER BY CONTEXT_SWITCHES ASC) = 1
  391. ) as UM
  392. LEFT JOIN (SELECT *
  393. FROM CANDIVORE.PROD.DIM_RESOURCE_VERSION
  394. WHERE is_inventory_resource = true AND resource_type = 'Booster' AND (resource_UI_order > -1 or
  395. resource_UI_order is null)
  396. QUALIFY ROW_NUMBER() OVER (PARTITION by resource_name ORDER BY
  397. resource_version desc) = 1) R1
  398. ON UM.BOOSTER_RESOURCE_NAME = R1.RESOURCE_NAME AND
  399. UM.BOOSTER_RESOURCE_VERSION = R1.RESOURCE_VERSION
  400. LEFT JOIN
  401. (SELECT *
  402. FROM CANDIVORE.PROD.DIM_RESOURCE_VERSION
  403. WHERE is_inventory_resource = true AND resource_type = 'Booster' AND (resource_UI_order > -1 or
  404. resource_UI_order is null)
  405. QUALIFY ROW_NUMBER() OVER (PARTITION by resource_name ORDER BY
  406. resource_version desc) = 1) R2
  407. ON UM.RIVAL_BOOSTER_RESOURCE_NAME = R2.RESOURCE_NAME AND
  408. UM.RIVAL_BOOSTER_RESOURCE_VERSION = R2.RESOURCE_VERSION
  409.  
  410. LEFT JOIN (select * from SNOWPLOW.DERIVED.LIVEOPS_EVENT_CONFIG qualify row_number() over (partition by lo_event_id order by ifnull(lo_event_config_ts, lo_event_config_update_ts) desc) = 1) LE
  411. ON UM.MATCH_LOP_EVENT_TYPE = LE.LO_EVENT_ID
  412. LEFT JOIN (select * from SNOWPLOW.DERIVED.LIVEOPS_CALENDAR qualify row_number() over (partition by lo_entry_id order by ifnull(lo_entry_ts, lo_entry_update_ts) desc) = 1) calendar
  413. on UM.match_lop_event_id = calendar.LO_ENTRY_ID
  414. LEFT JOIN (select USER_ID
  415. ,MATCH_END_DATE
  416. ,iff(SUBSCRIPTION_TIER>0, true,false) as IS_SUBSCRIBER
  417. ,SUBSCRIPTION_TIER
  418. ,case when subscription_tier = 1 then ''Silver''
  419. when subscription_tier = 2 then ''Gold''
  420. when subscription_tier = 3 then ''Diamond'' else NULL end AS
  421. SUBSCRIPTION_TIER_S
  422.  
  423. FROM SNOWPLOW.DERIVED.USER_MATCH_TOTAL
  424. WHERE MATCH_END_DATE IS NOT NULL
  425. and match_end_date >= dateadd(day, -1,date(current_date())) and SERVER_EVENT_ID not in
  426. (select distinct event_id from STAGING.TRANSFORM_TABLES.RN_USER_MATCH where
  427. match_end_date >= dateadd(day, -1,date(current_date())))
  428. and is_bot = false
  429. QUALIFY row_number() OVER (PARTITION BY USER_ID, MATCH_END_DATE ORDER
  430. BY server_derived_tstamp DESC)=1 ) S
  431. ON S.USER_ID = UM.USER_ID AND S.MATCH_END_DATE = UM.MATCH_END_DATE
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!