API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Sep 17th, 2024
68
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 36.30 KB | None | 0 0
raw download clone embed print report
  1. f_user_resource_transaction = {
  2. '01_truncate_transformation_table': '''
  3. truncate MATCH_MASTERS.TRANSFORMATION.TRANS_USER_RESOURCE_TRANSACTION
  4. ''',
  5. '02_set_from_update_variable': '''
  6. set FROM_USER_TRANSACTION_UPDATED_DT_MNG = (
  7. SELECT
  8. NVL(max(LAST_LOAD_DATE), to_timestamp('2023-12-11')) 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_RESOURCE_TRANSACTION'
  14. ORDER BY
  15. ID DESC
  16. LIMIT
  17. 1
  18. );
  19. ''',
  20. '03_set_to_update_variable': '''
  21. set TO_USER_TRANSACTION_UPDATED_DT_MNG = (
  22. SELECT
  23. dateadd(day,-1,current_date())
  24. );
  25. ''',
  26. '04_insert_delta_into_trans_table': '''
  27. insert into MATCH_MASTERS.TRANSFORMATION.TRANS_USER_RESOURCE_TRANSACTION (
  28. EVENT_ID
  29. ,DERIVED_TSTAMP
  30. ,TRANSACTION_DATE
  31. ,TRANSACTION_DT
  32. ,SESSION_ID
  33. ,SESSION_START_DT
  34. ,USER_ID
  35. ,IS_RECEIVED_RESOURCE
  36. ,TRANSACTION_SOURCE
  37. ,BUSINESS_SOURCE
  38. ,BUSINESS_SOURCE_TYPE
  39. ,BUSINESS_SOURCE_SUB_TYPE
  40. ,RESOURCE_ID
  41. ,RESOURCE_TYPE
  42. ,RESOURCE_SUB_TYPE
  43. ,BOOSTER_TIER_GROUP_NAME
  44. ,RESOURCE_CNT
  45. ,RESOURCE_ITEM_STATUS
  46. ,RESOURCE_VERSION
  47. ,MATCH_TYPE_ID
  48. ,MATCH_MODE_TYPE
  49. ,modifier_1
  50. ,modifier_2
  51. ,modifier_3
  52. ,MATCH_ID
  53. ,OFFER_INDEX
  54. ,OFFER_ID
  55. ,SUB_OFFER_ID
  56. ,LO_EVENT_ID
  57. ,LO_EVENT_TYPE_CONFIG
  58. ,LO_EVENT_CONFIG_ID
  59. ,EVENT_TYPE
  60. ,LO_ENTRY_ID
  61. ,TEAM_ID
  62. ,TEAM_NAME
  63. ,TRANSACTION_PARTICIPATE_ID
  64. ,IS_REROLLED
  65. ,IS_COIN_PAID
  66. ,IS_USD_PAID
  67. ,PRIZE_CONTAINER
  68. ,FULL_CONTAINER
  69. ,ARRAY_SIZE
  70. ,PRIZE_TYPE_HISTORIC
  71. ,IS_CHEST
  72. ,IS_STICKER
  73. ,IS_PINIATA
  74. ,PRIZE_TIER_HISTORIC
  75. ,IS_UNIQUE_HISTORIC
  76. ,PINIATA_STICKER_TYPE
  77. ,PINIATA_STICKER_TIER
  78. ,PINIATA_IS_STICKER_GURANTEED_UNIQUE
  79. ,UUID
  80. ,COUNTRY
  81. ,LANGUAGE
  82. ,DEVICE_LANGUAGE
  83. ,TEST_NAME
  84. ,TEST_GROUP_NAME
  85. ,FB_USER_ID
  86. ,CLIENT_IP
  87. ,IS_TESTING_USER
  88. ,IS_DEVELOPER
  89. ,DEVICE_MODEL
  90. ,DEVICE_OS
  91. ,PLATFORM
  92. ,MEDIA_SOURCE
  93. ,APP_VERSION
  94. ,APP_MINOR_VERSION
  95. ,FIRST_INSTALL_DT
  96. ,ADVERTISER_ID
  97. ,APPSFLYER_ID
  98. ,AD_NAME
  99. ,CAMPAIGN_NAME
  100. ,SUBSCRIPTION_TIER
  101. ,TOTAL_IAP_AMT
  102. ,CURRENT_ARENA_INDEX
  103. ,TROPHIES_CNT
  104. ,STARS_CNT
  105. ,LT_MATCHES_PLAYED_CNT
  106. ,LT_MATCHES_WON_CNT
  107. ,LT_PURCHASES_AMT
  108. ,EVENT_SOURCE
  109. ,LTV_GROUP
  110. ,AD_ID
  111. ,ADSET
  112. ,ADSET_ID
  113. ,CAMPAIGN_ID
  114. ,INFLUENCER_NAME
  115. ,USER_NAME
  116. ,AF_KEYWORDS
  117. ,SITE_ID
  118. ,COIN_PRICE
  119. ,DOLLAR_PRICE
  120. ,VIDEO_PRICE
  121. ,ALBUM_ID
  122. ,STICKER_NUMBER
  123. ,SHIELDED_BOOSTER_VALUE
  124. ,PINATA_OR_CHEST_ID
  125. ,PRIZE_TIER
  126. ,PRIZE_TYPE
  127. ,IS_GURANTEED_UNIQUE
  128. ,PINATA_TYPE
  129. ,CHEST_TYPE
  130. ,STAKES_MULTIPLIER
  131. ,ON_FIRE
  132. ,SOURCE_ALBUM_ID
  133. ,ELO
  134. ,LAST_UPDATED_DT
  135. ,DW_INSERT_DT
  136. ,PRIZE_COIN_VALUE_CALCULATED
  137. ,RESOURCE_COIN_VALUE_CALCULATED
  138. ,REGION
  139. ,LO_SEGMENT
  140. ,LO_EVENT_DIFFICULTY
  141. ,CONFIG_INDEX
  142. ,LO_EVENT_LEVEL
  143. ,LO_EVENT_TYPE
  144. ,PREMIUM_PASS_END_TIME
  145. ,RELATIVE_SR
  146. ,PREMIUM_PASS_ACTIVE
  147. ,LO_EVENT_SLOT
  148. ,DEVICE_ID_HASH
  149. ,APP_ORIGINAL_VERSION
  150. ,BADGE_GROUP
  151. ,BADGE_ID
  152. ,BADGE_LEVEL
  153. ,BADGE_TYPE
  154. ,ITEM_ID
  155. ,PVP_MODE
  156. ,transaction_sub_source
  157. ,MATCH_TYPE
  158. ,rumble_type
  159. )
  160. SELECT distinct
  161. t.EVENT_ID
  162. ,t.DERIVED_TSTAMP
  163. ,t.TRANSACTION_DATE
  164. ,t.TRANSACTION_DT
  165. ,t.SESSION_ID
  166. ,t.SESSION_START_DT
  167. ,t.USER_ID
  168. ,t.IS_RECEIVED_RESOURCE
  169. ,t.TRANSACTION_SOURCE
  170. ,b.business_source
  171. ,b.business_source_type
  172. ,b.business_source_sub_type
  173. ,t.RESOURCE_ID
  174. ,t.RESOURCE_TYPE
  175. ,r.resource_sub_type
  176. ,r.booster_tier_group_name
  177. ,t.RESOURCE_CNT
  178. ,t.RESOURCE_ITEM_STATUS
  179. ,t.RESOURCE_VERSION
  180. ,t.MATCH_TYPE_ID
  181. ,t.MATCH_MODE_TYPE
  182. ,ifnull(e.LO_EVENT1_MODIFIER1_ID,e.LO_ENTRY_MODIFIER_TYPE1) modifier_1
  183. ,ifnull(e.LO_EVENT1_MODIFIER2_ID,e.LO_ENTRY_MODIFIER_TYPE2) modifier_2
  184. ,ifnull(e.LO_EVENT1_MODIFIER3_ID,e.LO_ENTRY_MODIFIER_TYPE3) modifier_3
  185. ,t.MATCH_ID
  186. ,t.OFFER_INDEX
  187. ,t.OFFER_ID
  188. ,t.SUB_OFFER_ID
  189. ,t.LO_EVENT_ID
  190. ,e.lo_event_type as lo_event_type_config
  191. ,e.LO_EVENT_CONFIG_ID
  192. ,b.event_id as event_type
  193. ,t.LO_ENTRY_ID
  194. ,t.TEAM_ID
  195. ,t.TEAM_NAME
  196. ,t.TRANSACTION_PARTICIPATE_ID
  197. ,t.IS_REROLLED
  198. ,IFNULL(t.IS_COIN_PAID, b.PURCHASED_COINS) as IS_COIN_PAID
  199. ,IFNULL(t.IS_USD_PAID, b.purchase_usd) as IS_USD_PAID
  200. ,t.prize_container
  201. ,t.full_container
  202. ,t.ARRAY_SIZE
  203. ,t.prize_type_historic
  204. ,t.is_chest
  205. ,t.is_sticker
  206. ,t.is_piniata
  207. ,t.prize_tier_historic
  208. ,t.is_unique_historic
  209. ,t.piniata_sticker_type
  210. ,t.piniata_sticker_tier
  211. ,t.piniata_is_sticker_guranteed_unique
  212. ,t.uuid
  213. ,t.country
  214. ,t.language
  215. ,t.device_language
  216. ,t.test_name
  217. ,t.test_group_name
  218. ,t.fb_user_ID
  219. ,t.client_IP
  220. ,t.is_testing_user
  221. ,t.is_developer
  222. ,t.device_model
  223. ,t.device_os
  224. ,t.platform
  225. ,t.media_source
  226. ,t.app_version
  227. ,t.app_minor_version
  228. ,t.first_install_DT
  229. ,t.advertiser_ID
  230. ,t.appsflyer_id
  231. ,t.ad_name
  232. ,t.campaign_name
  233. ,t.subscription_tier
  234. ,t.total_IAP_amt
  235. ,t.current_arena_index
  236. ,t.trophies_cnt
  237. ,t.stars_cnt
  238. ,t.LT_matches_played_cnt
  239. ,t.LT_matches_won_cnt
  240. ,t.LT_purchases_amt
  241. ,t.event_source
  242. ,t.LTV_group
  243. ,t.ad_id
  244. ,t.adset
  245. ,t.adset_id
  246. ,t.campaign_id
  247. ,t.influencer_name
  248. ,t.user_name
  249. ,t.af_keywords
  250. ,t.site_id
  251. ,t.coin_price
  252. ,t.dollar_price
  253. ,t.video_price
  254. ,t.album_id
  255. ,t.sticker_number
  256. ,t.shielded_booster_value
  257. ,t.pinata_or_chest_id
  258. ,t.prize_tier
  259. ,t.prize_type
  260. ,t.is_guranteed_unique
  261. ,t.pinata_type
  262. ,t.chest_type
  263. ,t.stakes_multiplier
  264. ,t.on_fire
  265. ,t.source_album_id
  266. ,t.elo
  267. ,t.DERIVED_TSTAMP as LAST_UPDATED_DT
  268. ,current_timestamp() as DW_INSERT_DT
  269. ,PRIZE_COIN_VALUE_CALCULATED
  270. ,RESOURCE_COIN_VALUE_CALCULATED
  271. ,t.region
  272. ,t.lo_segment
  273. ,t.lo_event_difficulty
  274. ,t.config_index
  275. ,t.lo_event_level
  276. ,t.lo_event_type
  277. ,t.premium_pass_end_time
  278. ,t.relative_SR
  279. ,t.premium_pass_active
  280. ,e.lo_event_slot
  281. ,t.device_id_hash
  282. ,t.app_original_version
  283. ,t.badge_group
  284. ,t.badge_id
  285. ,t.badge_level
  286. ,t.badge_type
  287. ,t.item_id
  288. ,t.pvp_mode
  289. ,CASE
  290. when e.lo_event_slot = 'Main' and e.lo_event_id like 'top-masters%' then 'top_masters'
  291. when e.lo_event_slot = 'Main' and e.lo_event_id like 'sticker-staes%' then 'sticker_stars(triggered_early_main_event)'
  292. when e.lo_event_slot = 'Main' then 'main_event'
  293. when t.transaction_source = 'live_event' and e.lo_event_type in ('TeamPrizeLadder','TeamPrizeUpgrade') then b.business_source
  294. when t.transaction_source = 'live_event' and e.lo_event_type = 'PrizeLadder' then nvl(b.business_source_sub_type,e.lo_event_config_id)
  295. when t.transaction_source = 'live_event' then nvl(nvl(e.lo_event_type,e.lo_event_type),t.transaction_source)
  296. when t.lo_event_id like 'lucky-spin%' then 'spin'
  297. when t.lo_event_id like 'lucky-super-spin%' then 'super_spin'
  298. ---
  299. WHEN t.transaction_source LIKE 'subscription%' THEN 'subscription'
  300. WHEN t.transaction_source LIKE 'no_activity%' THEN 'no_activity'
  301. WHEN t.transaction_source LIKE 'admin%'
  302. OR t.transaction_source LIKE 'promote' THEN 'admin_dashboard'
  303. WHEN t.transaction_source LIKE '%auto_comp%' THEN 'auto_compensations'
  304. WHEN t.transaction_source = 'daily_deals' AND is_coin_paid = TRUE THEN 'daily_deals_coins'
  305. WHEN t.transaction_source = 'daily_deals' AND is_usd_paid = TRUE THEN 'daily_deals_usd'
  306. WHEN t.transaction_source = 'daily_deals' AND is_coin_paid = FALSE AND is_usd_paid = FALSE THEN 'daily_deals_free'
  307. WHEN t.transaction_source in ('fb_connect','google_connect','apple_connect') THEN 'fb_google_apple_connect'
  308. WHEN t.transaction_source LIKE 'tournament%' THEN 'tournament'
  309. else t.transaction_source
  310. end transaction_sub_source
  311. ,CASE
  312. WHEN MATCH_TYPE_ID = 2 THEN 'Classic'
  313. WHEN MATCH_TYPE_ID = 3 THEN 'Challenge'
  314. WHEN MATCH_TYPE_ID = 4 OR transaction_source LIKE 'tournament%' THEN 'Tournament'
  315. when lo_event_id like '%boosters-clash%' or lo_event_id like '%booster-clash%' then 'BoostersClash'
  316. WHEN lo_event_id LIKE '%mutation%' and PVP_MODE = 'Solo' THEN 'SoloMutationLab'
  317. WHEN MATCH_TYPE_ID = 5 and (MODIFIER_1 = 'win-by-knockout' or MODIFIER_2 = 'win-by-knockout' or modifier_3 = 'win-by-knockout') AND is_random_mixer = TRUE then 'Random Knockout'
  318. WHEN MATCH_TYPE_ID = 5 and (MODIFIER_1 = 'win-by-knockout' or MODIFIER_2 = 'win-by-knockout' or modifier_3 = 'win-by-knockout') then 'Knockout'
  319. WHEN MATCH_TYPE_ID = 5 and (MODIFIER_1 = 'rumble-duo-modifier' or MODIFIER_2 = 'rumble-duo-modifier' or modifier_3 = 'rumble-duo-modifier' or lower(rumble_type) = 'showdown') AND is_random_mixer = TRUE then 'Random Showdown'
  320. WHEN MATCH_TYPE_ID = 5 AND is_random_mixer = TRUE then 'Random Mixer'
  321. WHEN MATCH_TYPE_ID = 5 and (MODIFIER_1 = 'rumble-duo-modifier' or MODIFIER_2 = 'rumble-duo-modifier' or modifier_3 = 'rumble-duo-modifier' or lower(rumble_type) = 'showdown') then 'Showdown'
  322. WHEN MATCH_TYPE_ID = 5 THEN 'Daily'
  323. when lower(rumble_type) = 'party' then 'Rumble Party'
  324. when pvp_mode = 'Rumble' or lower(rumble_type) = 'classic' then 'Rumble'
  325. when lo_event_id like '%onboarding%' then 'Onboarding'
  326. when PVP_MODE = 'Solo' then 'Solo'
  327. when lo_event_id like '%adventure%' then 'Adventure'
  328. when lo_event_id like '%score-race%' or config_id = 'score-race' then 'Leaderboard'
  329. ELSE 'Other'
  330. END AS MATCH_TYPE
  331. ,t.rumble_type
  332. from snowplow.derived.core_user_resource_transaction t
  333. left join match_masters.prod.f_liveops_calendar_with_config e
  334. on t.lo_entry_id = e.lo_entry_id
  335. left join snowplow.derived.business_transaction_sources b
  336. on t.transaction_source = b.transaction_source and e.lo_event_config_id = b.business_source
  337. left join (select * from
  338. (select *, rank() over (partition by resource_name,resource_type order by db_create_date desc, resource_version
  339. desc) as resource_rank
  340. from MATCH_MASTERS.prod.DIM_RESOURCE_VERSION)
  341. where resource_rank = 1) r
  342. on t.resource_id = r.resource_name and t.resource_type = r.resource_type
  343. where t.DERIVED_TSTAMP >= DATEADD(hour, -7, $FROM_USER_TRANSACTION_UPDATED_DT_MNG)
  344.  
  345. ;
  346. ''',
  347. '05_set_merge_error_helper': '''
  348. ALTER SESSION SET ERROR_ON_NONDETERMINISTIC_MERGE = false;
  349. ''',
  350. '06_merge_prize_missing_value': '''
  351. MERGE INTO MATCH_MASTERS.TRANSFORMATION.TRANS_USER_RESOURCE_TRANSACTION a
  352. USING (
  353. select resource_type, resource_type_id, resource_id, resource_name, resource_version,
  354. resource_coin_value, resource_sub_type, prize_missing_values,
  355. prize_missing_values_parsed.value as prize_missing_values_parsed
  356. FROM snowplow.derived.resource_version_events,
  357. LATERAL FLATTEN(input => prize_missing_values) AS prize_missing_values_parsed
  358. ) b
  359. on a.resource_type = b.resource_type
  360. AND a.resource_id = b.resource_name
  361. AND a.resource_version = b.resource_version
  362. WHEN MATCHED and a.resource_type = 'Sticker' and a.is_guranteed_unique = True
  363. and a.trophies_cnt BETWEEN b.prize_missing_values_parsed:min AND b.prize_missing_values_parsed:max
  364. THEN UPDATE SET
  365. a.PRIZE_COIN_VALUE_CALCULATED = b.prize_missing_values_parsed:valueNum
  366.  
  367. WHEN MATCHED and a.resource_type = 'Sticker' and a.is_guranteed_unique = True
  368. and a.trophies_cnt >= b.prize_missing_values_parsed:min AND b.prize_missing_values_parsed:max IS NULL
  369. THEN UPDATE SET
  370. a.PRIZE_COIN_VALUE_CALCULATED = b.prize_missing_values_parsed:valueNum
  371. ''',
  372. '07_merge_prize_coin_value': '''
  373. MERGE INTO MATCH_MASTERS.TRANSFORMATION.TRANS_USER_RESOURCE_TRANSACTION a
  374. USING (
  375. select resource_type, resource_type_id, resource_id, resource_name, resource_version,
  376. resource_coin_value, resource_sub_type, prize_coin_values,
  377. prize_coin_values_parsed.value as prize_coin_values_parsed
  378. FROM snowplow.derived.resource_version_events,
  379. LATERAL FLATTEN(input => prize_coin_values) AS prize_coin_values_parsed
  380. ) b
  381. on a.resource_type = b.resource_type
  382. AND a.resource_id = b.resource_name
  383. AND a.resource_version = b.resource_version
  384. WHEN MATCHED and a.resource_type = 'Sticker' and a.is_guranteed_unique = False
  385. and a.trophies_cnt BETWEEN b.prize_coin_values_parsed:min AND b.prize_coin_values_parsed:max
  386. THEN UPDATE SET
  387. a.PRIZE_COIN_VALUE_CALCULATED = b.prize_coin_values_parsed:valueNum
  388.  
  389. WHEN MATCHED and a.resource_type = 'Sticker' and a.is_guranteed_unique = False
  390. and a.trophies_cnt >= b.prize_coin_values_parsed:min AND b.prize_coin_values_parsed:max IS NULL
  391. THEN UPDATE SET
  392. a.PRIZE_COIN_VALUE_CALCULATED = b.prize_coin_values_parsed:valueNum
  393.  
  394. ''',
  395. '08_merge_resource_coin_values': '''
  396. MERGE INTO MATCH_MASTERS.TRANSFORMATION.TRANS_USER_RESOURCE_TRANSACTION a
  397. USING (
  398. select resource_type, resource_type_id, resource_id, resource_name, resource_version,
  399. resource_coin_value, resource_sub_type, resource_coin_values,
  400. resource_coin_values_parsed.value as resource_coin_values_parsed
  401. FROM snowplow.derived.resource_version_events,
  402. LATERAL FLATTEN(input => resource_coin_values) AS resource_coin_values_parsed
  403. ) b
  404. on a.resource_type = b.resource_type
  405. AND a.resource_id = b.resource_name
  406. AND a.resource_version = b.resource_version
  407. WHEN MATCHED and a.resource_type = 'Sticker' and a.resource_item_status = 'unique'
  408. and a.trophies_cnt BETWEEN b.resource_coin_values_parsed:min AND b.resource_coin_values_parsed:max
  409. THEN UPDATE SET
  410. a.RESOURCE_COIN_VALUE_CALCULATED = b.resource_coin_values_parsed:valueNum
  411.  
  412. WHEN MATCHED and a.resource_type = 'Sticker' and a.resource_item_status = 'unique' and
  413. a.trophies_cnt >= b.resource_coin_values_parsed:min AND b.resource_coin_values_parsed:max IS NULL
  414. THEN UPDATE SET
  415. a.RESOURCE_COIN_VALUE_CALCULATED = b.resource_coin_values_parsed:valueNum
  416.  
  417. WHEN MATCHED and a.resource_type != 'Sticker' and array_size(b.resource_coin_values) = 1
  418. THEN UPDATE SET
  419. a.RESOURCE_COIN_VALUE_CALCULATED = b.resource_coin_values_parsed:valueNum
  420.  
  421. WHEN MATCHED and a.resource_type != 'Sticker' and array_size(resource_coin_values) > 1 and
  422. a.trophies_cnt BETWEEN b.resource_coin_values_parsed:min AND b.resource_coin_values_parsed:max
  423. THEN UPDATE SET
  424. a.RESOURCE_COIN_VALUE_CALCULATED = b.resource_coin_values_parsed:valueNum
  425.  
  426. WHEN MATCHED and a.resource_type != 'Sticker' and array_size(resource_coin_values) > 1 and
  427. a.trophies_cnt >= b.resource_coin_values_parsed:min AND b.resource_coin_values_parsed:max IS NULL
  428. THEN UPDATE SET
  429. a.RESOURCE_COIN_VALUE_CALCULATED = b.resource_coin_values_parsed:valueNum
  430.  
  431. ''',
  432. '09_merge_duplicate_coin_value': '''
  433. MERGE INTO MATCH_MASTERS.TRANSFORMATION.TRANS_USER_RESOURCE_TRANSACTION a
  434. USING (
  435. select resource_type, resource_type_id, resource_id, resource_name, resource_version,
  436. resource_coin_value, resource_sub_type, duplicate_coin_values,
  437. duplicate_coin_values_parsed.value as duplicate_coin_values_parsed
  438. FROM snowplow.derived.resource_version_events,
  439. LATERAL FLATTEN(input => duplicate_coin_values) AS duplicate_coin_values_parsed
  440.  
  441. ) b
  442. on a.resource_type = b.resource_type
  443. AND a.resource_id = b.resource_name
  444. AND a.resource_version = b.resource_version
  445. WHEN MATCHED and a.resource_type = 'Sticker' and a.resource_item_status = 'duplicate'
  446. and a.trophies_cnt BETWEEN b.duplicate_coin_values_parsed:min AND b.duplicate_coin_values_parsed:max
  447. THEN UPDATE SET
  448. a.RESOURCE_COIN_VALUE_CALCULATED = b.duplicate_coin_values_parsed:valueNum
  449.  
  450. WHEN MATCHED and a.resource_type = 'Sticker' and a.resource_item_status = 'duplicate'
  451. and a.trophies_cnt >= b.duplicate_coin_values_parsed:min AND b.duplicate_coin_values_parsed:max IS NULL
  452. THEN UPDATE SET
  453. a.RESOURCE_COIN_VALUE_CALCULATED = b.duplicate_coin_values_parsed:valueNum
  454. ''',
  455. '10_merge_temp_to_prod': '''
  456. MERGE INTO MATCH_MASTERS.PROD.F_USER_RESOURCE_TRANSACTION AS t
  457. USING MATCH_MASTERS.TRANSFORMATION.TRANS_USER_RESOURCE_TRANSACTION AS S
  458. ON s.EVENT_ID = t.EVENT_ID
  459. AND t.DERIVED_TSTAMP > DATEADD(week, -1, $FROM_USER_TRANSACTION_UPDATED_DT_MNG)
  460. WHEN MATCHED THEN UPDATE SET
  461. t.EVENT_ID = s.EVENT_ID
  462. ,t.DERIVED_TSTAMP = s.DERIVED_TSTAMP
  463. ,t.TRANSACTION_DATE = s.TRANSACTION_DATE
  464. ,t.TRANSACTION_DT = s.TRANSACTION_DT
  465. ,t.SESSION_ID = s.SESSION_ID
  466. ,t.SESSION_START_DT = s.SESSION_START_DT
  467. ,t.USER_ID = s.USER_ID
  468. ,t.IS_RECEIVED_RESOURCE = s.IS_RECEIVED_RESOURCE
  469. ,t.TRANSACTION_SOURCE = s.TRANSACTION_SOURCE
  470. ,t.business_source = s.business_source
  471. ,t.business_source_type = s.business_source_type
  472. ,t.business_source_sub_type = s.business_source_sub_type
  473. ,t.RESOURCE_ID = s.RESOURCE_ID
  474. ,t.RESOURCE_TYPE = s.RESOURCE_TYPE
  475. ,t.resource_sub_type = s.resource_sub_type
  476. ,t.booster_tier_group_name = s.booster_tier_group_name
  477. ,t.RESOURCE_CNT = s.RESOURCE_CNT
  478. ,t.RESOURCE_ITEM_STATUS = s.RESOURCE_ITEM_STATUS
  479. ,t.RESOURCE_VERSION = s.RESOURCE_VERSION
  480. ,t.MATCH_TYPE_ID = s.MATCH_TYPE_ID
  481. ,t.MATCH_MODE_TYPE = s.MATCH_MODE_TYPE
  482. ,t.modifier_1 = s.modifier_1
  483. ,t.modifier_2 = s.modifier_2
  484. ,t.modifier_3 = s.modifier_3
  485. ,t.MATCH_ID = s.MATCH_ID
  486. ,t.OFFER_INDEX = s.OFFER_INDEX
  487. ,t.OFFER_ID = s.OFFER_ID
  488. ,t.SUB_OFFER_ID = s.SUB_OFFER_ID
  489. ,t.LO_EVENT_ID = s.LO_EVENT_ID
  490. ,t.lo_event_type_config = s.lo_event_type_config
  491. ,t.LO_EVENT_CONFIG_ID = s.LO_EVENT_CONFIG_ID
  492. ,t.event_type = s.event_type
  493. ,t.LO_ENTRY_ID = s.LO_ENTRY_ID
  494. ,t.TEAM_ID = s.TEAM_ID
  495. ,t.TEAM_NAME = s.TEAM_NAME
  496. ,t.TRANSACTION_PARTICIPATE_ID = s.TRANSACTION_PARTICIPATE_ID
  497. ,t.IS_REROLLED = s.IS_REROLLED
  498. ,t.IS_COIN_PAID = s.IS_COIN_PAID
  499. ,t.IS_USD_PAID = s.IS_USD_PAID
  500. ,t.prize_container = s.prize_container
  501. ,t.full_container = s.full_container
  502. ,t.ARRAY_SIZE = s.ARRAY_SIZE
  503. ,t.prize_type_historic = s.prize_type_historic
  504. ,t.is_chest = s.is_chest
  505. ,t.is_sticker = s.is_sticker
  506. ,t.is_piniata = s.is_piniata
  507. ,t.prize_tier_historic = s.prize_tier_historic
  508. ,t.is_unique_historic = s.is_unique_historic
  509. ,t.piniata_sticker_type = s.piniata_sticker_type
  510. ,t.piniata_sticker_tier = s.piniata_sticker_tier
  511. ,t.piniata_is_sticker_guranteed_unique = s.piniata_is_sticker_guranteed_unique
  512. ,t.uuid = s.uuid
  513. ,t.country = s.country
  514. ,t.language = s.language
  515. ,t.device_language = s.device_language
  516. ,t.test_name = s.test_name
  517. ,t.test_group_name = s.test_group_name
  518. ,t.fb_user_ID = s.fb_user_ID
  519. ,t.client_IP = s.client_IP
  520. ,t.is_testing_user = s.is_testing_user
  521. ,t.is_developer = s.is_developer
  522. ,t.device_model = s.device_model
  523. ,t.device_os = s.device_os
  524. ,t.platform = s.platform
  525. ,t.media_source = s.media_source
  526. ,t.app_version = s.app_version
  527. ,t.app_minor_version = s.app_minor_version
  528. ,t.first_install_DT = s.first_install_DT
  529. ,t.advertiser_ID = s.advertiser_ID
  530. ,t.appsflyer_id = s.appsflyer_id
  531. ,t.ad_name = s.ad_name
  532. ,t.campaign_name = s.campaign_name
  533. ,t.subscription_tier = s.subscription_tier
  534. ,t.total_IAP_amt = s.total_IAP_amt
  535. ,t.current_arena_index = s.current_arena_index
  536. ,t.trophies_cnt = s.trophies_cnt
  537. ,t.stars_cnt = s.stars_cnt
  538. ,t.LT_matches_played_cnt = s.LT_matches_played_cnt
  539. ,t.LT_matches_won_cnt = s.LT_matches_won_cnt
  540. ,t.LT_purchases_amt = s.LT_purchases_amt
  541. ,t.event_source = s.event_source
  542. ,t.LTV_group = s.LTV_group
  543. ,t.ad_id = s.ad_id
  544. ,t.adset = s.adset
  545. ,t.adset_id = s.adset_id
  546. ,t.campaign_id = s.campaign_id
  547. ,t.influencer_name = s.influencer_name
  548. ,t.user_name = s.user_name
  549. ,t.af_keywords = s.af_keywords
  550. ,t.site_id = s.site_id
  551. ,t.coin_price = s.coin_price
  552. ,t.dollar_price = s.dollar_price
  553. ,t.video_price = s.video_price
  554. ,t.album_id = s.album_id
  555. ,t.sticker_number = s.sticker_number
  556. ,t.shielded_booster_value = s.shielded_booster_value
  557. ,t.pinata_or_chest_id = s.pinata_or_chest_id
  558. ,t.prize_tier = s.prize_tier
  559. ,t.prize_type = s.prize_type
  560. ,t.is_guranteed_unique = s.is_guranteed_unique
  561. ,t.pinata_type = s.pinata_type
  562. ,t.chest_type = s.chest_type
  563. ,t.stakes_multiplier = s.stakes_multiplier
  564. ,t.on_fire = s.on_fire
  565. ,t.source_album_id = s.source_album_id
  566. ,t.elo = s.elo
  567. ,t.LAST_UPDATED_DT = s.LAST_UPDATED_DT
  568. ,t.DW_INSERT_DT = s.DW_INSERT_DT
  569. ,t.PRIZE_COIN_VALUE_CALCULATED = s.PRIZE_COIN_VALUE_CALCULATED
  570. ,t.RESOURCE_COIN_VALUE_CALCULATED = s.RESOURCE_COIN_VALUE_CALCULATED
  571. ,t.region = s.region
  572. ,t.lo_segment = s.lo_segment
  573. ,t.lo_event_difficulty = s.lo_event_difficulty
  574. ,t.config_index = s.config_index
  575. ,t.lo_event_level = s.lo_event_level
  576. ,t.lo_event_type = s.lo_event_type
  577. ,t.premium_pass_end_time = s.premium_pass_end_time
  578. ,t.relative_SR = s.relative_SR
  579. ,t.premium_pass_active = s.premium_pass_active
  580. ,t.lo_event_slot = s.lo_event_slot
  581. ,t.device_id_hash = s.device_id_hash
  582. ,t.app_original_version = s.app_original_version
  583. ,t.badge_group = s.badge_group
  584. ,t.badge_id = s.badge_id
  585. ,t.badge_level = s.badge_level
  586. ,t.badge_type = s.badge_type
  587. ,t.item_id = s.item_id
  588. ,t.pvp_mode = s.pvp_mode
  589. ,t.transaction_sub_source = s.transaction_sub_source
  590. ,t.MATCH_TYPE = s.MATCH_TYPE
  591. ,t.rumble_type = s.rumble_type
  592.  
  593. WHEN NOT MATCHED THEN INSERT (
  594. EVENT_ID
  595. ,DERIVED_TSTAMP
  596. ,TRANSACTION_DATE
  597. ,TRANSACTION_DT
  598. ,SESSION_ID
  599. ,SESSION_START_DT
  600. ,USER_ID
  601. ,IS_RECEIVED_RESOURCE
  602. ,TRANSACTION_SOURCE
  603. ,business_source
  604. ,business_source_type
  605. ,business_source_sub_type
  606. ,RESOURCE_ID
  607. ,RESOURCE_TYPE
  608. ,resource_sub_type
  609. ,booster_tier_group_name
  610. ,RESOURCE_CNT
  611. ,RESOURCE_ITEM_STATUS
  612. ,RESOURCE_VERSION
  613. ,MATCH_TYPE_ID
  614. ,MATCH_MODE_TYPE
  615. ,modifier_1
  616. ,modifier_2
  617. ,modifier_3
  618. ,MATCH_ID
  619. ,OFFER_INDEX
  620. ,OFFER_ID
  621. ,SUB_OFFER_ID
  622. ,LO_EVENT_ID
  623. ,lo_event_type_config
  624. ,LO_EVENT_CONFIG_ID
  625. ,event_type
  626. ,LO_ENTRY_ID
  627. ,TEAM_ID
  628. ,TEAM_NAME
  629. ,TRANSACTION_PARTICIPATE_ID
  630. ,IS_REROLLED
  631. ,IS_COIN_PAID
  632. ,IS_USD_PAID
  633. ,prize_container
  634. ,full_container
  635. ,ARRAY_SIZE
  636. ,prize_type_historic
  637. ,is_chest
  638. ,is_sticker
  639. ,is_piniata
  640. ,prize_tier_historic
  641. ,is_unique_historic
  642. ,piniata_sticker_type
  643. ,piniata_sticker_tier
  644. ,piniata_is_sticker_guranteed_unique
  645. ,uuid
  646. ,country
  647. ,language
  648. ,device_language
  649. ,test_name
  650. ,test_group_name
  651. ,fb_user_ID
  652. ,client_IP
  653. ,is_testing_user
  654. ,is_developer
  655. ,device_model
  656. ,device_os
  657. ,platform
  658. ,media_source
  659. ,app_version
  660. ,app_minor_version
  661. ,first_install_DT
  662. ,advertiser_ID
  663. ,appsflyer_id
  664. ,ad_name
  665. ,campaign_name
  666. ,subscription_tier
  667. ,total_IAP_amt
  668. ,current_arena_index
  669. ,trophies_cnt
  670. ,stars_cnt
  671. ,LT_matches_played_cnt
  672. ,LT_matches_won_cnt
  673. ,LT_purchases_amt
  674. ,event_source
  675. ,LTV_group
  676. ,ad_id
  677. ,adset
  678. ,adset_id
  679. ,campaign_id
  680. ,influencer_name
  681. ,user_name
  682. ,af_keywords
  683. ,site_id
  684. ,coin_price
  685. ,dollar_price
  686. ,video_price
  687. ,album_id
  688. ,sticker_number
  689. ,shielded_booster_value
  690. ,pinata_or_chest_id
  691. ,prize_tier
  692. ,prize_type
  693. ,is_guranteed_unique
  694. ,pinata_type
  695. ,chest_type
  696. ,stakes_multiplier
  697. ,on_fire
  698. ,source_album_id
  699. ,elo
  700. ,LAST_UPDATED_DT
  701. ,DW_INSERT_DT
  702. ,PRIZE_COIN_VALUE_CALCULATED
  703. ,RESOURCE_COIN_VALUE_CALCULATED
  704. ,region
  705. ,lo_segment
  706. ,lo_event_difficulty
  707. ,config_index
  708. ,lo_event_level
  709. ,lo_event_type
  710. ,premium_pass_end_time
  711. ,relative_SR
  712. ,premium_pass_active
  713. ,lo_event_slot
  714. ,device_id_hash
  715. ,app_original_version
  716. ,badge_group
  717. ,badge_id
  718. ,badge_level
  719. ,badge_type
  720. ,item_id
  721. ,pvp_mode
  722. ,transaction_sub_source
  723. ,MATCH_TYPE
  724. ,rumble_type
  725. )
  726. VALUES
  727. (
  728. EVENT_ID
  729. ,s.DERIVED_TSTAMP
  730. ,s.TRANSACTION_DATE
  731. ,s.TRANSACTION_DT
  732. ,s.SESSION_ID
  733. ,s.SESSION_START_DT
  734. ,s.USER_ID
  735. ,s.IS_RECEIVED_RESOURCE
  736. ,s.TRANSACTION_SOURCE
  737. ,s.business_source
  738. ,s.business_source_type
  739. ,s.business_source_sub_type
  740. ,s.RESOURCE_ID
  741. ,s.RESOURCE_TYPE
  742. ,s.resource_sub_type
  743. ,s.booster_tier_group_name
  744. ,s.RESOURCE_CNT
  745. ,s.RESOURCE_ITEM_STATUS
  746. ,s.RESOURCE_VERSION
  747. ,s.MATCH_TYPE_ID
  748. ,s.MATCH_MODE_TYPE
  749. ,s.modifier_1
  750. ,s.modifier_2
  751. ,s.modifier_3
  752. ,s.MATCH_ID
  753. ,s.OFFER_INDEX
  754. ,s.OFFER_ID
  755. ,s.SUB_OFFER_ID
  756. ,s.LO_EVENT_ID
  757. ,s.lo_event_type_config
  758. ,s.LO_EVENT_CONFIG_ID
  759. ,s.event_type
  760. ,s.LO_ENTRY_ID
  761. ,s.TEAM_ID
  762. ,s.TEAM_NAME
  763. ,s.TRANSACTION_PARTICIPATE_ID
  764. ,s.IS_REROLLED
  765. ,s.IS_COIN_PAID
  766. ,s.IS_USD_PAID
  767. ,s.prize_container
  768. ,s.full_container
  769. ,s.ARRAY_SIZE
  770. ,s.prize_type_historic
  771. ,s.is_chest
  772. ,s.is_sticker
  773. ,s.is_piniata
  774. ,s.prize_tier_historic
  775. ,s.is_unique_historic
  776. ,s.piniata_sticker_type
  777. ,s.piniata_sticker_tier
  778. ,s.piniata_is_sticker_guranteed_unique
  779. ,s.uuid
  780. ,s.country
  781. ,s.language
  782. ,s.device_language
  783. ,s.test_name
  784. ,s.test_group_name
  785. ,s.fb_user_ID
  786. ,s.client_IP
  787. ,s.is_testing_user
  788. ,s.is_developer
  789. ,s.device_model
  790. ,s.device_os
  791. ,s.platform
  792. ,s.media_source
  793. ,s.app_version
  794. ,s.app_minor_version
  795. ,s.first_install_DT
  796. ,s.advertiser_ID
  797. ,s.appsflyer_id
  798. ,s.ad_name
  799. ,s.campaign_name
  800. ,s.subscription_tier
  801. ,s.total_IAP_amt
  802. ,s.current_arena_index
  803. ,s.trophies_cnt
  804. ,s.stars_cnt
  805. ,s.LT_matches_played_cnt
  806. ,s.LT_matches_won_cnt
  807. ,s.LT_purchases_amt
  808. ,s.event_source
  809. ,s.LTV_group
  810. ,s.ad_id
  811. ,s.adset
  812. ,s.adset_id
  813. ,s.campaign_id
  814. ,s.influencer_name
  815. ,s.user_name
  816. ,s.af_keywords
  817. ,s.site_id
  818. ,s.coin_price
  819. ,s.dollar_price
  820. ,s.video_price
  821. ,s.album_id
  822. ,s.sticker_number
  823. ,s.shielded_booster_value
  824. ,s.pinata_or_chest_id
  825. ,s.prize_tier
  826. ,s.prize_type
  827. ,s.is_guranteed_unique
  828. ,s.pinata_type
  829. ,s.chest_type
  830. ,s.stakes_multiplier
  831. ,s.on_fire
  832. ,s.source_album_id
  833. ,s.elo
  834. ,s.LAST_UPDATED_DT
  835. ,s.DW_INSERT_DT
  836. ,s.PRIZE_COIN_VALUE_CALCULATED
  837. ,s.RESOURCE_COIN_VALUE_CALCULATED
  838. ,s.region
  839. ,s.lo_segment
  840. ,s.lo_event_difficulty
  841. ,s.config_index
  842. ,s.lo_event_level
  843. ,s.lo_event_type
  844. ,s.premium_pass_end_time
  845. ,s.relative_SR
  846. ,s.premium_pass_active
  847. ,s.lo_event_slot
  848. ,s.device_id_hash
  849. ,s.app_original_version
  850. ,s.badge_group
  851. ,s.badge_id
  852. ,s.badge_level
  853. ,s.badge_type
  854. ,s.item_id
  855. ,s.pvp_mode
  856. ,s.transaction_sub_source
  857. ,s.MATCH_TYPE
  858. ,s.rumble_type
  859.  
  860. );
  861. ''',
  862. '11_update_mng_table': '''
  863. INSERT INTO MATCH_MASTERS.MANAGE.MANAGEMENT_TABLE(
  864. TABLE_NAME
  865. ,DW_INSERT_DATE
  866. ,LAST_LOAD_DATE
  867. ,ROWS_LOADED
  868. ) VALUES (
  869. 'CANDIVORE.PROD.F_USER_RESOURCE_TRANSACTION'
  870. ,current_timestamp()
  871. ,(select max(LAST_UPDATED_DT) from MATCH_MASTERS.TRANSFORMATION.TRANS_USER_RESOURCE_TRANSACTION)
  872. ,(select count(*) from MATCH_MASTERS.TRANSFORMATION.TRANS_USER_RESOURCE_TRANSACTION)
  873. )
  874. '''
  875. }
  876.  
  877.  
  878.  
  879.  
  880.  
  881. ;
  882.  
  883.  
  884.  
  885.  
  886.  
  887.  
  888. select * from match_masters.prod.f_liveops_calendar_with_config where lo_entry_name = 'match-rumble-15-30k-gold-v4'
  889.  
  890.  
  891. ;
  892.  
  893. LEFT JOIN (
  894. select
  895.  
  896. calendar_order,
  897. lo_entry_is_premium_pass_exclusive,
  898. lo_event_config_id,
  899. lo_event_dynamic_config_id,
  900. LO_ORIGINAL_ENTRY_ID,
  901. LO_ENTRY_MODIFIER_TYPE2,
  902. LO_ENTRY_MODIFIER_TYPE1,
  903. LO_ENTRY_ID
  904. FROM
  905. SNOWPLOW.DERIVED.LIVEOPS_CALENDAR QUALIFY row_number() over (
  906. partition by lo_entry_id
  907. order by
  908. ifnull(lo_entry_ts, lo_entry_update_ts) DESC
  909. ) = 1
  910. ) calendar on UM.match_lop_event_id = calendar.LO_ENTRY_ID
  911.  
  912.  
  913. ;
  914. ifnull(LO_EVENT1_MODIFIER1_ID,LO_ENTRY_MODIFIER_TYPE1) modifier_1, ifnull(LO_EVENT1_MODIFIER2_ID,LO_ENTRY_MODIFIER_TYPE2) LO_EVENT1_MODIFIER2_ID
  915.  
  916.  
  917. ;
  918.  
  919.  
  920. select * from MATCH_MASTERS.PROD.DIM_EVENT_CONFIG where lo_event_id = 'match-rumble-15-30k-gold-v4'
  921.  
  922.  
  923. ;
  924.  
  925. select * from match_masters.prod.f_liveops_calendar where date(derived_tstamp) >= current_date -10 limit 100
  926.  
  927.  
  928. ;
  929.  
  930.  
  931.  
  932. select a.*,b.*
  933. from
  934.  
  935. (SELECT
  936. *, to_timestamp(lo_entry_start_ts) lo_entry_start_ts_2
  937. FROM match_masters.PROD.F_LIVEOPS_CALENDAR where lo_entry_name = 'match-rumble-15-30k-gold-v4') a
  938.  
  939. left join
  940.  
  941. (SELECT
  942. nvl(lead(lo_event_config_update_ts) over(partition by lo_event_id order by lo_event_config_update_ts),current_date+20) lagged_ts
  943. , * exclude (derived_tstamp, event_id)
  944. from
  945. (select *
  946. FROM match_masters.PROD.DIM_EVENT_CONFIG
  947. qualify row_number() over(partition by event_id order by derived_tstamp) = 1
  948. )) b
  949.  
  950. on a.lo_entry_start_ts_2 >= b.lo_event_config_update_ts and a.lo_entry_start_ts_2 <= b.lagged_ts and a.lo_entry_name = b.lo_event_id
  951.  
  952.  
  953.  
  954.  
  955.  
  956.  
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!