API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Sep 17th, 2024
63
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.48 KB | None | 0 0
raw download clone embed print report
  1. t_earn_spent_light = {
  2. '01_truncate_transformation_table': '''
  3. truncate MATCH_MASTERS.TRANSFORMATION.TRANS_EARN_SPENT_LIGHT
  4. ''',
  5. '02_set_last_update_variable': '''
  6. set FROM_EARN_SPENT_LIGHT_UPDATED_DT_MNG = (
  7. SELECT
  8. date(
  9. NVL(max(LAST_LOAD_DATE), to_timestamp('2023-01-01'))
  10. ) as LAST_UPDATED_DT_MNG
  11. FROM
  12. MATCH_MASTERS.manage.management_table
  13. WHERE
  14. 1 = 1
  15. AND table_name = 'CANDIVORE.SEMANTIC_LAYER.T_EARN_SPENT_LIGHT'
  16. ORDER BY
  17. ID DESC
  18. LIMIT
  19. 1
  20. );
  21. ''',
  22. '03_set_to_update_variable': '''
  23. set TO_EARN_SPENT_LIGHT_PARAMS_UPDATED_DT_MNG = (
  24. SELECT DATEADD(day,-1,current_date())
  25. );''',
  26. '04_insert_delta_into_trans_table': '''
  27. INSERT INTO MATCH_MASTERS.TRANSFORMATION.TRANS_EARN_SPENT_LIGHT (
  28. INTERVAL_DATE
  29. ,LEAGUE
  30. ,TROPHY_GROUP
  31. ,payers_segment
  32. ,LTV_GROUP
  33. ,LO_EVENT_TYPE
  34. ,EVENT_TYPE
  35. ,IS_RECEIVED_RESOURCE
  36. ,TRANSACTION_TYPE
  37. ,TRANSACTION_SOURCE_LVL_0
  38. ,TRANSACTION_SOURCE
  39. ,IS_BARGAIN
  40. ,MATCH_TYPE
  41. ,transaction_source_with_match_type
  42. ,MATCH_TYPE_ID
  43. ,MATCH_MODE_TYPE
  44. ,RESOURCE_TYPE
  45. ,RESOURCE_SUB_TYPE
  46. ,RESOURCE_ID
  47. ,RESOURCE_VERSION
  48. ,STAKES_MULTIPLIER
  49. ,RESOURCE_CNT
  50. ,RESOURCE_COIN_VALUE
  51. ,TOTAL_COINS_VALUE
  52. ,LAST_UPDATED_DT
  53. ,DW_INSERT_DT
  54. )
  55. WITH DATA AS (
  56. SELECT
  57. A.*,
  58. CASE
  59. WHEN is_usd_paid = TRUE THEN 'Paid'
  60. WHEN is_coin_paid = TRUE THEN 'Coin Paid'
  61. ELSE 'Unpayed'
  62. END AS transaction_type,
  63. transaction_sub_source transaction_source_lvl_0,
  64. CASE
  65. WHEN transaction_source = 'booster_select'
  66. OR (
  67. transaction_source = 'daily_deals'
  68. AND is_coin_paid = TRUE
  69. )
  70. OR transaction_source = 'helper_select_popup_chest'
  71. OR transaction_source LIKE 'perk_select%'
  72. OR transaction_source LIKE 'special_offer%'
  73. OR transaction_source = 'spin'
  74. OR transaction_source = 'spin_chest'
  75. OR transaction_source = 'super_spin'
  76. OR transaction_source LIKE '%donation%'
  77. OR transaction_source = 'tickets_pack' THEN TRUE
  78. ELSE FALSE
  79. END AS is_bargain,
  80. match_type,
  81. nvl(match_type,transaction_source) transaction_source_with_match_type,
  82. payers_segment
  83. FROM
  84. MATCH_MASTERS.PROD.f_user_resource_transaction a
  85. left join
  86. (select user_id,interval_date,payers_segment from MATCH_MASTERS.prod.daily_users_from_params where interval_date between DATEADD(DAY, -1, $FROM_EARN_SPENT_LIGHT_UPDATED_DT_MNG) and $TO_EARN_SPENT_LIGHT_PARAMS_UPDATED_DT_MNG) B
  87. on A.user_id = B.user_id and A.transaction_date = B.interval_date
  88. WHERE
  89. DATE(derived_tstamp) between DATEADD(DAY, -1, $FROM_EARN_SPENT_LIGHT_UPDATED_DT_MNG) and $TO_EARN_SPENT_LIGHT_PARAMS_UPDATED_DT_MNG
  90. )
  91. --------------------
  92. --end_with_statement
  93. --------------------
  94.  
  95. SELECT
  96. A.INTERVAL_DATE
  97. ,A.LEAGUE
  98. ,A.TROPHY_GROUP
  99. ,A.payers_segment
  100. ,A.LTV_GROUP
  101. ,A.LO_EVENT_TYPE
  102. ,A.EVENT_TYPE
  103. ,A.IS_RECEIVED_RESOURCE
  104. ,A.TRANSACTION_TYPE
  105. ,A.TRANSACTION_SOURCE_LVL_0
  106. ,A.TRANSACTION_SOURCE
  107. ,A.IS_BARGAIN
  108. ,A.MATCH_TYPE
  109. ,A.transaction_source_with_match_type
  110. ,A.MATCH_TYPE_ID
  111. ,A.MATCH_MODE_TYPE
  112. ,A.RESOURCE_TYPE
  113. ,A.RESOURCE_SUB_TYPE
  114. ,A.RESOURCE_ID
  115. ,A.RESOURCE_VERSION
  116. ,A.STAKES_MULTIPLIER
  117. ,A.RESOURCE_CNT
  118. ,A.resource_coin_value_calculated resource_coin_value
  119. ,A.resource_cnt * A.resource_coin_value_calculated AS total_coins_value
  120. ,A.INTERVAL_DATE as LAST_UPDATED_DT
  121. ,current_timestamp() as DW_INSERT_DT
  122. FROM
  123. (
  124. SELECT
  125. DATE(derived_tstamp) interval_date
  126. ,CASE
  127. WHEN trophies_cnt < 800 THEN '800'
  128. WHEN trophies_cnt < 3800 THEN 'Studios'
  129. WHEN trophies_cnt < 30000 THEN 'Master_League'
  130. WHEN trophies_cnt >= 30000 THEN 'Legends_League'
  131. END league,
  132. CASE
  133. WHEN trophies_cnt < 1500 THEN '0-1500'
  134. WHEN trophies_cnt < 3800 THEN '1500-3800'
  135. WHEN trophies_cnt < 8000 THEN '3800-8000'
  136. WHEN trophies_cnt < 17000 THEN '8000-17000'
  137. WHEN trophies_cnt < 30000 THEN '17000-30000'
  138. ELSE '30000+'
  139. END AS trophy_group,
  140. payers_segment,
  141. LTV_group,
  142. LO_EVENT_TYPE_config LO_EVENT_TYPE,
  143. event_type,
  144. is_received_resource,
  145. transaction_type,
  146. transaction_source_lvl_0,
  147. transaction_source,
  148. is_bargain,
  149. match_type,
  150. transaction_source_with_match_type,
  151. match_type_id,
  152. match_mode_type,
  153. resource_type,
  154. resource_sub_type,
  155. resource_id,
  156. resource_version,
  157. NVL(STAKES_MULTIPLIER, 1) AS STAKES_MULTIPLIER,
  158. resource_coin_value_calculated,
  159. SUM(resource_cnt) resource_cnt
  160.  
  161. FROM
  162. DATA
  163. GROUP BY all
  164. ) A
  165. WHERE 1=1
  166. AND A.interval_date between DATEADD(DAY, -1, $FROM_EARN_SPENT_LIGHT_UPDATED_DT_MNG) and $TO_EARN_SPENT_LIGHT_PARAMS_UPDATED_DT_MNG
  167. ;
  168. ''',
  169. '05_set_merge_error_helper': '''
  170. ALTER SESSION SET ERROR_ON_NONDETERMINISTIC_MERGE = false;
  171. ''',
  172. '06_merge_temp_to_prod': '''
  173. MERGE INTO MATCH_MASTERS.SEMANTIC_LAYER.T_EARN_SPENT_LIGHT AS t
  174. USING MATCH_MASTERS.TRANSFORMATION.TRANS_EARN_SPENT_LIGHT AS S
  175. ON s.INTERVAL_DATE = t.INTERVAL_DATE
  176. AND s.LEAGUE = t.LEAGUE
  177. AND s.TROPHY_GROUP = t.TROPHY_GROUP
  178. AND (s.payers_segment = t.payers_segment OR (s.payers_segment IS NULL AND t.payers_segment IS NULL ) )
  179. AND s.LTV_GROUP = t.LTV_GROUP
  180. AND (s.LO_EVENT_TYPE = t.LO_EVENT_TYPE OR (s.LO_EVENT_TYPE IS NULL AND t.LO_EVENT_TYPE IS NULL ) )
  181. AND (s.EVENT_TYPE = t.EVENT_TYPE OR (s.EVENT_TYPE IS NULL AND t.EVENT_TYPE IS NULL ) )
  182. AND s.IS_RECEIVED_RESOURCE = t.IS_RECEIVED_RESOURCE
  183. AND s.TRANSACTION_TYPE = t.TRANSACTION_TYPE
  184. AND s.TRANSACTION_SOURCE = t.TRANSACTION_SOURCE
  185. AND (s.MATCH_TYPE = t.MATCH_TYPE OR (s.MATCH_TYPE IS NULL AND t.MATCH_TYPE IS NULL ) )
  186. AND (s.transaction_source_with_match_type = t.transaction_source_with_match_type or (s.transaction_source_with_match_type is null and t.transaction_source_with_match_type is null))
  187. AND s.MATCH_TYPE_ID = t.MATCH_TYPE_ID
  188. AND (s.MATCH_MODE_TYPE = t.MATCH_MODE_TYPE OR (s.MATCH_MODE_TYPE IS NULL AND t.MATCH_MODE_TYPE IS NULL ) )
  189. AND s.RESOURCE_TYPE = t.RESOURCE_TYPE
  190. AND s.RESOURCE_SUB_TYPE = t.RESOURCE_SUB_TYPE
  191. AND s.RESOURCE_ID = t.RESOURCE_ID
  192. AND s.RESOURCE_VERSION = t.RESOURCE_VERSION
  193. AND s.STAKES_MULTIPLIER = t.STAKES_MULTIPLIER
  194. AND t.INTERVAL_DATE > DATEADD(DAY, -7, $FROM_EARN_SPENT_LIGHT_UPDATED_DT_MNG)
  195. WHEN MATCHED THEN UPDATE SET
  196. t.INTERVAL_DATE = s.INTERVAL_DATE
  197. ,t.LEAGUE = s.LEAGUE
  198. ,t.TROPHY_GROUP = s.TROPHY_GROUP
  199. ,t.payers_segment = s.payers_segment
  200. ,t.LTV_GROUP = s.LTV_GROUP
  201. ,t.LO_EVENT_TYPE = s.LO_EVENT_TYPE
  202. ,t.EVENT_TYPE = s.EVENT_TYPE
  203. ,t.IS_RECEIVED_RESOURCE = s.IS_RECEIVED_RESOURCE
  204. ,t.TRANSACTION_TYPE = s.TRANSACTION_TYPE
  205. ,t.TRANSACTION_SOURCE_LVL_0 = s.TRANSACTION_SOURCE_LVL_0
  206. ,t.TRANSACTION_SOURCE = s.TRANSACTION_SOURCE
  207. ,t.IS_BARGAIN = s.IS_BARGAIN
  208. ,t.MATCH_TYPE = s.MATCH_TYPE
  209. ,t.transaction_source_with_match_type = s.transaction_source_with_match_type
  210. ,t.MATCH_TYPE_ID = s.MATCH_TYPE_ID
  211. ,t.MATCH_MODE_TYPE = s.MATCH_MODE_TYPE
  212. ,t.RESOURCE_TYPE = s.RESOURCE_TYPE
  213. ,t.RESOURCE_SUB_TYPE = s.RESOURCE_SUB_TYPE
  214. ,t.RESOURCE_ID = s.RESOURCE_ID
  215. ,t.RESOURCE_VERSION = s.RESOURCE_VERSION
  216. ,t.STAKES_MULTIPLIER = s.STAKES_MULTIPLIER
  217. ,t.RESOURCE_CNT = s.RESOURCE_CNT
  218. ,t.RESOURCE_COIN_VALUE = s.RESOURCE_COIN_VALUE
  219. ,t.TOTAL_COINS_VALUE = s.TOTAL_COINS_VALUE
  220. ,t.LAST_UPDATED_DT = s.LAST_UPDATED_DT
  221. ,t.DW_INSERT_DT = s.DW_INSERT_DT
  222. WHEN NOT MATCHED THEN INSERT (
  223. INTERVAL_DATE
  224. ,LEAGUE
  225. ,TROPHY_GROUP
  226. ,payers_segment
  227. ,LTV_GROUP
  228. ,LO_EVENT_TYPE
  229. ,EVENT_TYPE
  230. ,IS_RECEIVED_RESOURCE
  231. ,TRANSACTION_TYPE
  232. ,TRANSACTION_SOURCE_LVL_0
  233. ,TRANSACTION_SOURCE
  234. ,IS_BARGAIN
  235. ,MATCH_TYPE
  236. ,transaction_source_with_match_type
  237. ,MATCH_TYPE_ID
  238. ,MATCH_MODE_TYPE
  239. ,RESOURCE_TYPE
  240. ,RESOURCE_SUB_TYPE
  241. ,RESOURCE_ID
  242. ,RESOURCE_VERSION
  243. ,STAKES_MULTIPLIER
  244. ,RESOURCE_CNT
  245. ,RESOURCE_COIN_VALUE
  246. ,TOTAL_COINS_VALUE
  247. ,LAST_UPDATED_DT
  248. ,DW_INSERT_DT
  249. )
  250. VALUES (
  251. s.INTERVAL_DATE
  252. ,s.LEAGUE
  253. ,s.TROPHY_GROUP
  254. ,s.payers_segment
  255. ,s.LTV_GROUP
  256. ,s.LO_EVENT_TYPE
  257. ,s.EVENT_TYPE
  258. ,s.IS_RECEIVED_RESOURCE
  259. ,s.TRANSACTION_TYPE
  260. ,s.TRANSACTION_SOURCE_LVL_0
  261. ,s.TRANSACTION_SOURCE
  262. ,s.IS_BARGAIN
  263. ,s.MATCH_TYPE
  264. ,s.transaction_source_with_match_type
  265. ,s.MATCH_TYPE_ID
  266. ,s.MATCH_MODE_TYPE
  267. ,s.RESOURCE_TYPE
  268. ,s.RESOURCE_SUB_TYPE
  269. ,s.RESOURCE_ID
  270. ,s.RESOURCE_VERSION
  271. ,s.STAKES_MULTIPLIER
  272. ,s.RESOURCE_CNT
  273. ,s.RESOURCE_COIN_VALUE
  274. ,s.TOTAL_COINS_VALUE
  275. ,s.LAST_UPDATED_DT
  276. ,s.DW_INSERT_DT
  277. )
  278. ;
  279. ''',
  280. '07_update_mng_table': '''
  281. INSERT INTO MATCH_MASTERS.MANAGE.MANAGEMENT_TABLE(
  282. TABLE_NAME
  283. ,DW_INSERT_DATE
  284. ,LAST_LOAD_DATE
  285. ,ROWS_LOADED
  286. ) VALUES (
  287. 'CANDIVORE.SEMANTIC_LAYER.T_EARN_SPENT_LIGHT'
  288. ,current_timestamp()
  289. ,(select max(LAST_UPDATED_DT) from MATCH_MASTERS.TRANSFORMATION.TRANS_EARN_SPENT_LIGHT)
  290. ,(select count(*) from MATCH_MASTERS.TRANSFORMATION.TRANS_EARN_SPENT_LIGHT)
  291. )
  292. '''
  293. }
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!