API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Jun 27th, 2023 (edited)
160
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.13 KB | None | 0 0
raw download clone embed print report
  1. CREATE OR REPLACE PROCEDURE CANDIVORE.SEMANTIC_LAYER.T_COINS_SPENT()
  2. RETURNS VARCHAR(16777216)
  3. LANGUAGE JAVASCRIPT
  4. EXECUTE AS CALLER
  5. AS '
  6.  
  7. var my_sql_command = `
  8.  
  9. CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.T_Coins_Spent AS
  10. select * from (
  11.  
  12. (select
  13. date(derived_tstamp) interval_date
  14. ,ltv_group_static ltv_group
  15. ,league
  16. ,trophy_group
  17. ,trophy_group_2
  18. ,seniority_bin
  19. ,''boxes'' transaction_source
  20. ,to_varchar(prize_container[0][1]) resource_id
  21. ,coin_price
  22. ,case when prize_container[0][1] like ''%Big%'' then mode(resource_cnt)*6 when prize_container[0][1] like ''%Small%'' then mode(resource_cnt)*3 end resource_cnt
  23. ,case when prize_container[0][1] like ''%Big%'' then round(count(*)/6) when prize_container[0][1] like ''%Small%'' then round(count(*)/3) end occasions_bought
  24. ,case when prize_container[0][1] like ''%Big%'' then round(sum(coin_price)/6) when prize_container[0][1] like ''%Small%'' then round(sum(coin_price)/3) end coins_spent
  25. , sum(coin_price)/count(*) coins_per_item
  26. from (
  27. select A.*,B.ltv_group ltv_group_static,B.league,B.trophy_group,B.trophy_group_2,B.seniority_bin from (
  28. (select *, date(derived_tstamp) interval_date from CANDIVORE.PROD.F_USER_RESOURCE_TRANSACTION where date(derived_tstamp) > ''2023-01-01'' and is_coin_paid = 1 and is_received_resource = TRUE and is_chest = 1) A
  29. left join
  30. (select user_id, interval_date, ltv_group,CASE
  31. WHEN trophies_cnt<3800 THEN ''Studios''
  32. WHEN trophies_cnt<30000 THEN ''Master_League''
  33. WHEN trophies_cnt>=30000 THEN ''Legends_League''
  34. END as league
  35. , CASE
  36. WHEN trophies_cnt < 125 then ''0-125''
  37. WHEN trophies_cnt < 1400 then ''125-1400''
  38. WHEN trophies_cnt < 2000 then ''1400-2000''
  39. WHEN trophies_cnt < 3800 then ''2000-3800''
  40. WHEN trophies_cnt < 7000 then ''3800-7000''
  41. WHEN trophies_cnt < 16000 then ''7000-16000''
  42. WHEN trophies_cnt < 30000 then ''16000-30000''
  43. WHEN trophies_cnt >= 30000 then ''30000+''
  44. END as trophy_group
  45. , CASE
  46. WHEN trophies_cnt < 125 THEN ''0-125''
  47. WHEN trophies_cnt < 1400 THEN ''125-1400''
  48. WHEN trophies_cnt < 2000 THEN ''1400-2000''
  49. WHEN trophies_cnt < 3800 THEN ''2000-3800''
  50. WHEN trophies_cnt < 4500 THEN ''3800-4500''
  51. WHEN trophies_cnt < 5500 THEN ''4500-5500''
  52. WHEN trophies_cnt < 7000 THEN ''5500-7000''
  53. WHEN trophies_cnt < 10000 THEN ''7000-10000''
  54. WHEN trophies_cnt < 13000 THEN ''10000-13000''
  55. WHEN trophies_cnt < 16000 THEN ''13000-16000''
  56. WHEN trophies_cnt < 30000 THEN ''16000-30000''
  57. WHEN trophies_cnt >= 30000 THEN ''30000+''
  58. END AS trophy_group_2
  59. ,seniority_bin from candivore.prod.daily_users_from_params where interval_date > ''2023-01-01'') B
  60. on A.user_id = B.user_id and A.interval_date = B.interval_date
  61. ))
  62. where is_chest = 1 and is_coin_paid = 1 and date(derived_tstamp) > ''2023-01-01'' group by 1,2,3,4,5,6,7,8,9)
  63.  
  64. union all
  65.  
  66. (SELECT interval_date
  67. ,ltv_group_static ltv_group
  68. ,league
  69. ,trophy_group
  70. ,trophy_group_2
  71. ,seniority_bin
  72. ,transaction_source
  73. ,resource_id
  74. ,coin_price
  75. ,resource_cnt
  76. ,count(*) occasions_bought
  77. ,sum(coin_price) coins_spent
  78. ,sum(coin_price)/count(*) coins_per_item
  79. from (
  80. select A.*,B.ltv_group ltv_group_static,B.league,B.trophy_group_2,B.trophy_group,B.seniority_bin from (
  81. (select *, date(derived_tstamp) interval_date from CANDIVORE.PROD.F_USER_RESOURCE_TRANSACTION where date(derived_tstamp) > ''2023-01-01'' and is_coin_paid = 1 and is_received_resource = TRUE and is_chest = 0 and transaction_source != ''daily_deals'') A
  82. left join
  83. (select user_id, interval_date, ltv_group,CASE
  84. WHEN trophies_cnt<3800 THEN ''Studios''
  85. WHEN trophies_cnt<30000 THEN ''Master_League''
  86. WHEN trophies_cnt>=30000 THEN ''Legends_League''
  87. END as league
  88. , CASE
  89. WHEN trophies_cnt < 125 then ''0-125''
  90. WHEN trophies_cnt < 1400 then ''125-1400''
  91. WHEN trophies_cnt < 2000 then ''1400-2000''
  92. WHEN trophies_cnt < 3800 then ''2000-3800''
  93. WHEN trophies_cnt < 7000 then ''3800-7000''
  94. WHEN trophies_cnt < 16000 then ''7000-16000''
  95. WHEN trophies_cnt < 30000 then ''16000-30000''
  96. WHEN trophies_cnt >= 30000 then ''30000+''
  97. END as trophy_group
  98. , CASE
  99. WHEN trophies_cnt < 125 THEN ''0-125''
  100. WHEN trophies_cnt < 1400 THEN ''125-1400''
  101. WHEN trophies_cnt < 2000 THEN ''1400-2000''
  102. WHEN trophies_cnt < 3800 THEN ''2000-3800''
  103. WHEN trophies_cnt < 4500 THEN ''3800-4500''
  104. WHEN trophies_cnt < 5500 THEN ''4500-5500''
  105. WHEN trophies_cnt < 7000 THEN ''5500-7000''
  106. WHEN trophies_cnt < 10000 THEN ''7000-10000''
  107. WHEN trophies_cnt < 13000 THEN ''10000-13000''
  108. WHEN trophies_cnt < 16000 THEN ''13000-16000''
  109. WHEN trophies_cnt < 30000 THEN ''16000-30000''
  110. WHEN trophies_cnt >= 30000 THEN ''30000+''
  111. END AS trophy_group_2
  112. ,seniority_bin from candivore.prod.daily_users_from_params where interval_date > ''2023-01-01'') B
  113. on A.user_id = B.user_id and A.interval_date = B.interval_date
  114. )) group by 1,2,3,4,5,6,7,8,9,10 order by 1 desc, 2 , 3
  115. )
  116.  
  117. union all
  118.  
  119. (SELECT interval_date
  120. ,ltv_group_static ltv_group
  121. ,league
  122. ,trophy_group
  123. ,trophy_group_2
  124. ,seniority_bin
  125. ,transaction_source
  126. ,resource_id
  127. ,resource_cnt coin_price
  128. ,1 resource_cnt
  129. ,count(*) occasions_bought
  130. ,sum(resource_cnt) coins_spent
  131. ,resource_cnt coins_per_item
  132. from (
  133. select A.*,B.ltv_group ltv_group_static,B.league,B.trophy_group_2,B.trophy_group,B.seniority_bin from (
  134. (select *, date(derived_tstamp) interval_date from CANDIVORE.PROD.F_USER_RESOURCE_TRANSACTION where date(derived_tstamp) > ''2023-01-01'' and resource_id = ''Coin'' and is_received_resource = FALSE and transaction_source in (''tournament'',''match_end'',''create_team'')) A
  135. left join
  136. (select user_id, interval_date, ltv_group,CASE
  137. WHEN trophies_cnt<3800 THEN ''Studios''
  138. WHEN trophies_cnt<30000 THEN ''Master_League''
  139. WHEN trophies_cnt>=30000 THEN ''Legends_League''
  140. END as league
  141. , CASE
  142. WHEN trophies_cnt < 125 then ''0-125''
  143. WHEN trophies_cnt < 1400 then ''125-1400''
  144. WHEN trophies_cnt < 2000 then ''1400-2000''
  145. WHEN trophies_cnt < 3800 then ''2000-3800''
  146. WHEN trophies_cnt < 7000 then ''3800-7000''
  147. WHEN trophies_cnt < 16000 then ''7000-16000''
  148. WHEN trophies_cnt < 30000 then ''16000-30000''
  149. WHEN trophies_cnt >= 30000 then ''30000+''
  150. END as trophy_group
  151. , CASE
  152. WHEN trophies_cnt < 125 THEN ''0-125''
  153. WHEN trophies_cnt < 1400 THEN ''125-1400''
  154. WHEN trophies_cnt < 2000 THEN ''1400-2000''
  155. WHEN trophies_cnt < 3800 THEN ''2000-3800''
  156. WHEN trophies_cnt < 4500 THEN ''3800-4500''
  157. WHEN trophies_cnt < 5500 THEN ''4500-5500''
  158. WHEN trophies_cnt < 7000 THEN ''5500-7000''
  159. WHEN trophies_cnt < 10000 THEN ''7000-10000''
  160. WHEN trophies_cnt < 13000 THEN ''10000-13000''
  161. WHEN trophies_cnt < 16000 THEN ''13000-16000''
  162. WHEN trophies_cnt < 30000 THEN ''16000-30000''
  163. WHEN trophies_cnt >= 30000 THEN ''30000+''
  164. END AS trophy_group_2
  165. ,seniority_bin from candivore.prod.daily_users_from_params where interval_date > ''2023-01-01'') B
  166. on A.user_id = B.user_id and A.interval_date = B.interval_date
  167. )) group by 1,2,3,4,5,6,7,8,9,10 order by 1 desc, 2 , 3
  168. )
  169. )
  170.  
  171. union all
  172.  
  173. (SELECT interval_date
  174. ,ltv_group_static ltv_group
  175. ,league
  176. ,trophy_group
  177. ,trophy_group_2
  178. ,seniority_bin
  179. ,transaction_source
  180. ,offer_id resource_id
  181. ,coin_price
  182. ,1 resource_cnt
  183. ,count(*) occasions_bought
  184. ,sum(coin_price) coins_spent
  185. ,sum(coin_price)/count(*) coins_per_item
  186. from (
  187. select A.*,B.ltv_group ltv_group_static,B.league,B.trophy_group_2,B.trophy_group,B.seniority_bin from (
  188. (select *, date(derived_tstamp) interval_date from CANDIVORE.PROD.F_USER_RESOURCE_TRANSACTION where date(derived_tstamp) > ''2023-01-01'' and resource_id = ''Coin'' and is_received_resource = FALSE and transaction_source = ''daily_deals'') A
  189. left join
  190. (select user_id, interval_date, ltv_group,CASE
  191. WHEN trophies_cnt<3800 THEN ''Studios''
  192. WHEN trophies_cnt<30000 THEN ''Master_League''
  193. WHEN trophies_cnt>=30000 THEN ''Legends_League''
  194. END as league
  195. , CASE
  196. WHEN trophies_cnt < 125 then ''0-125''
  197. WHEN trophies_cnt < 1400 then ''125-1400''
  198. WHEN trophies_cnt < 2000 then ''1400-2000''
  199. WHEN trophies_cnt < 3800 then ''2000-3800''
  200. WHEN trophies_cnt < 7000 then ''3800-7000''
  201. WHEN trophies_cnt < 16000 then ''7000-16000''
  202. WHEN trophies_cnt < 30000 then ''16000-30000''
  203. WHEN trophies_cnt >= 30000 then ''30000+''
  204. END as trophy_group
  205. , CASE
  206. WHEN trophies_cnt < 125 THEN ''0-125''
  207. WHEN trophies_cnt < 1400 THEN ''125-1400''
  208. WHEN trophies_cnt < 2000 THEN ''1400-2000''
  209. WHEN trophies_cnt < 3800 THEN ''2000-3800''
  210. WHEN trophies_cnt < 4500 THEN ''3800-4500''
  211. WHEN trophies_cnt < 5500 THEN ''4500-5500''
  212. WHEN trophies_cnt < 7000 THEN ''5500-7000''
  213. WHEN trophies_cnt < 10000 THEN ''7000-10000''
  214. WHEN trophies_cnt < 13000 THEN ''10000-13000''
  215. WHEN trophies_cnt < 16000 THEN ''13000-16000''
  216. WHEN trophies_cnt < 30000 THEN ''16000-30000''
  217. WHEN trophies_cnt >= 30000 THEN ''30000+''
  218. END AS trophy_group_2
  219. ,seniority_bin from candivore.prod.daily_users_from_params where interval_date > ''2023-01-01'') B
  220. on A.user_id = B.user_id and A.interval_date = B.interval_date
  221. )) group by 1,2,3,4,5,6,7,8,9,10 order by 1 desc, 2 , 3
  222. )
  223.  
  224.  
  225.  
  226. `
  227. var statement = snowflake.createStatement({sqlText: my_sql_command});
  228. statement.execute();
  229. ';
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!