API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Jul 9th, 2023 (edited)
173
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.83 KB | None | 0 0
raw download clone embed print report
  1. CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.T_OFFERS_MIXMATCH AS
  2. SELECT AA.*, ifnull(BB.occasions_bought_per_user,0) occasions_bought_per_user, BB.unique_users_bought, BB.Coin_price, BB.IAP_price, BB.video_price, BB.rev, BB.dollars_rev, CC.true_price FROM
  3.  
  4. (SELECT
  5. offer_start_date
  6. ---
  7. , ifnull(ltv_group2,'n/a') ltv_group
  8. , ifnull(platform,'n/a') platform
  9. , ifnull(trophy_group2,'n/a') trophy_group
  10. , ifnull(trophy_group_2,'n/a') trophy_group_2
  11. , ifnull(league2,'n/a') league
  12. /*, ifnull(country,'n/a') country
  13. , ifnull(language,'n/a') language */
  14. ---
  15. , offer_id
  16. , template_id
  17. , offer_context
  18. ---
  19. , count(distinct user_id) unique_users_shown
  20. , count(*) occasions_shown
  21. FROM (
  22. SELECT A.*, B.ltv_group, B.platform , B.trophy_group , B.league, B.country, B.language,B.trophy_group_2
  23. ,first_value(B.ltv_group) over(partition by A.user_id,template_id order by B.interval_date) ltv_group2
  24. ,first_value(B.trophy_group) over(partition by A.user_id,template_id order by B.interval_date) trophy_group2
  25. ,first_value(B.league) over(partition by A.user_id,template_id order by B.interval_date) league2
  26. from(
  27. (select
  28. date(derived_tstamp) interval_date
  29. , uuid user_id
  30. , template_id
  31. , offer_context
  32. , CASE when length(offer_id) != 8 then offer_id else '0' end offer_id --
  33. , ifnull(coin_price,0) coin_price --
  34. , ifnull(video_price,0) video_price --
  35. , ifnull(iap_price,0) iap_price --
  36. , min(interval_date) over(partition by template_id) offer_start_date
  37. from candivore.prod.f_special_offer_shown where date(derived_tstamp) >= '2022-10-01' and template_id like '%pick-n-choose%' and is_developer = 0 or is_developer is null) A
  38.  
  39.  
  40. LEFT JOIN
  41.  
  42.  
  43. (select
  44. user_id
  45. , interval_date
  46. , country
  47. , language
  48. , platform
  49. , app_version
  50. , app_minor_version
  51. , ltv_group
  52. , date(first_install_dt) as install_date
  53. , CASE
  54. WHEN trophies_cnt < 125 then '0-125'
  55. WHEN trophies_cnt < 1400 then '125-1400'
  56. WHEN trophies_cnt < 2000 then '1400-2000'
  57. WHEN trophies_cnt < 3800 then '2000-3800'
  58. WHEN trophies_cnt < 7000 then '3800-7000'
  59. WHEN trophies_cnt < 16000 then '7000-16000'
  60. WHEN trophies_cnt < 30000 then '16000-30000'
  61. WHEN trophies_cnt >= 30000 then '30000+'
  62. END as trophy_group
  63. , CASE
  64. WHEN trophies_cnt < 125 THEN '0-125'
  65. WHEN trophies_cnt < 1400 THEN '125-1400'
  66. WHEN trophies_cnt < 2000 THEN '1400-2000'
  67. WHEN trophies_cnt < 3800 THEN '2000-3800'
  68. WHEN trophies_cnt < 4500 THEN '3800-4500'
  69. WHEN trophies_cnt < 5500 THEN '4500-5500'
  70. WHEN trophies_cnt < 7000 THEN '5500-7000'
  71. WHEN trophies_cnt < 10000 THEN '7000-10000'
  72. WHEN trophies_cnt < 13000 THEN '10000-13000'
  73. WHEN trophies_cnt < 16000 THEN '13000-16000'
  74. WHEN trophies_cnt < 20000 THEN '16000-20000'
  75. WHEN trophies_cnt < 30000 THEN '20000-30000'
  76. WHEN trophies_cnt >= 30000 THEN '30000+'
  77. END AS trophy_group_2
  78. ,CASE
  79. WHEN trophies_cnt < 800 THEN '800'
  80. WHEN trophies_cnt < 3800 THEN 'Studios'
  81. WHEN trophies_cnt < 30000 THEN 'Master_League'
  82. WHEN trophies_cnt >= 30000 THEN 'Legends_League'
  83. END league
  84. from candivore.prod.daily_users_from_params where interval_date >= '2022-10-01') B
  85.  
  86. on A.user_id = B.user_id and A.interval_date = B.interval_date
  87. )) GROUP BY 1,2,3,4,5,6,7,8,9) AA
  88.  
  89. left JOIN
  90.  
  91. (SELECT
  92. -- interval_date
  93. ---
  94. ltv_group
  95. , platform
  96. , trophy_group
  97. , trophy_group_2
  98. , league
  99. /*, ifnull(country,'n/a') country
  100. , ifnull(language,'n/a') language*/
  101. ---
  102. , offer_id -- offer_id
  103. , template_id
  104. ---
  105. --, purchase_type
  106. --, purchase_subtype
  107. , occasions_bought_per_user
  108.  
  109. , mode(coin_price) coin_price
  110. , mode(video_price) video_price
  111. , mode(iap_price) iap_price
  112. --, min(interval_date) over(partition by offer_id) offer_start_date
  113. , count(distinct user_id) unique_users_bought
  114. , sum(rev) rev
  115. , sum(dollars_rev) dollars_rev
  116.  
  117. FROM (
  118. SELECT
  119. -- interval_date
  120. ---
  121. user_id
  122. , ifnull(ltv_group2,'n/a') ltv_group
  123. , ifnull(platform,'n/a') platform
  124. , ifnull(trophy_group2,'n/a') trophy_group
  125. , ifnull(trophy_group_2,'n/a') trophy_group_2
  126. , ifnull(league2,'n/a') league
  127. /*, ifnull(country,'n/a') country
  128. , ifnull(language,'n/a') language*/
  129. ---
  130. , offer_id -- offer_id
  131. , template_id
  132. ---
  133. --, purchase_type
  134. --, purchase_subtype
  135. , coin_price
  136. , video_price
  137. , iap_price
  138. , sum(iap_price) rev
  139. , sum(dollar_price) dollars_rev
  140. --, min(interval_date) over(partition by offer_id) offer_start_date
  141. , count(*) occasions_bought_per_user
  142. /*, sum(case when level = 1 then 1 else 0 end) bought_lvl1
  143. , sum(case when level = 2 then 1 else 0 end) bought_lvl2
  144. , sum(case when level = 3 then 1 else 0 end) bought_lvl3
  145. , sum(case when level = 4 then 1 else 0 end) bought_lvl4
  146. , sum(case when level = 5 then 1 else 0 end) bought_lvl5*/
  147. FROM (
  148. SELECT A.*, B.ltv_group, B.platform, B.trophy_group, B.league, B.country, B.language,B.trophy_group_2
  149. ,first_value(B.ltv_group) over(partition by A.user_id,template_id order by B.interval_date) ltv_group2
  150. ,first_value(B.trophy_group) over(partition by A.user_id,template_id order by B.interval_date) trophy_group2
  151. ,first_value(B.league) over(partition by A.user_id,template_id order by B.interval_date) league2
  152. from (
  153. (select
  154. date(derived_tstamp) interval_date
  155. , uuid user_id
  156. , ifnull(offer_context,'0') offer_context
  157. , ifnull(purchase_type,'0') purchase_type
  158. , ifnull(purchase_subtype,'0') purchase_subtype
  159. , ifnull(prize_raw,'0') prize_raw
  160. , ifnull(offer_index,'0') offer_index
  161. , template_id --
  162. , level
  163. , CASE when length(shown_offer_id) != 8 then shown_offer_id else '0' end offer_id
  164. , ifnull(coin_price,0) coin_price
  165. , ifnull(video_price,0) video_price
  166. , ifnull(iap_price,0) iap_price
  167. , ifnull(dollar_price,0) dollar_price
  168. from candivore.prod.f_special_offer_bought) A
  169.  
  170. LEFT JOIN
  171.  
  172. (select
  173. user_id
  174. , interval_date
  175. , country
  176. , language
  177. , platform
  178. , app_version
  179. , app_minor_version
  180. , ltv_group
  181. , date(first_install_dt) as install_date
  182. , CASE
  183. WHEN trophies_cnt < 125 then '0-125'
  184. WHEN trophies_cnt < 1400 then '125-1400'
  185. WHEN trophies_cnt < 2000 then '1400-2000'
  186. WHEN trophies_cnt < 3800 then '2000-3800'
  187. WHEN trophies_cnt < 7000 then '3800-7000'
  188. WHEN trophies_cnt < 16000 then '7000-16000'
  189. WHEN trophies_cnt < 30000 then '16000-30000'
  190. WHEN trophies_cnt >= 30000 then '30000+'
  191. END as trophy_group
  192. , CASE
  193. WHEN trophies_cnt < 125 THEN '0-125'
  194. WHEN trophies_cnt < 1400 THEN '125-1400'
  195. WHEN trophies_cnt < 2000 THEN '1400-2000'
  196. WHEN trophies_cnt < 3800 THEN '2000-3800'
  197. WHEN trophies_cnt < 4500 THEN '3800-4500'
  198. WHEN trophies_cnt < 5500 THEN '4500-5500'
  199. WHEN trophies_cnt < 7000 THEN '5500-7000'
  200. WHEN trophies_cnt < 10000 THEN '7000-10000'
  201. WHEN trophies_cnt < 13000 THEN '10000-13000'
  202. WHEN trophies_cnt < 16000 THEN '13000-16000'
  203. WHEN trophies_cnt < 20000 THEN '16000-20000'
  204. WHEN trophies_cnt < 30000 THEN '20000-30000'
  205. WHEN trophies_cnt >= 30000 THEN '30000+'
  206. END AS trophy_group_2
  207. ,CASE
  208. WHEN trophies_cnt < 800 THEN '800'
  209. WHEN trophies_cnt < 3800 THEN 'Studios'
  210. WHEN trophies_cnt < 30000 THEN 'Master_League'
  211. WHEN trophies_cnt >= 30000 THEN 'Legends_League'
  212. END league
  213. from candivore.prod.daily_users_from_params where interval_date >= '2022-10-01') B
  214.  
  215. on A.user_id = B.user_id and A.interval_date = B.interval_date
  216. )) GROUP BY 1,2,3,4,5,6,7,8,9,10,11) GROUP BY 1,2,3,4,5,6,7,8) BB
  217.  
  218. ON /*AA.offer_start_date = BB.offer_start_date and*/ AA.ltv_group = BB.ltv_group and AA.trophy_group = BB.trophy_group and AA.platform = BB.platform and AA.league = BB.league /*and AA.country = BB.country
  219. and AA.language = BB.language*/ and AA.offer_id = BB.offer_id and AA.template_id = BB.template_id and AA.trophy_group_2 = BB.trophy_group_2
  220.  
  221. LEFT JOIN
  222.  
  223. (SELECT
  224. -- interval_date
  225. ifnull(ltv_group,'n/a') ltv_group
  226. , ifnull(platform,'n/a') platform
  227. , ifnull(trophy_group,'n/a') trophy_group
  228. , ifnull(trophy_group_2,'n/a') trophy_group_2
  229. , ifnull(league,'n/a') league
  230. ---
  231. , special_offer_id offer_id
  232. ---
  233. , sum(true_price) true_price
  234. FROM (
  235. SELECT A.*, B.ltv_group, B.platform, B.trophy_group, B.league, B.country, B.language,B.trophy_group_2
  236. from (
  237. (select date(derived_tstamp) interval_date,user_id, special_offer_id,iap_price true_price from candivore.prod.f_in_app_purchase where interval_date >= '2022-10-01') A
  238.  
  239. LEFT JOIN
  240.  
  241. (select
  242. user_id
  243. , interval_date
  244. , country
  245. , language
  246. , platform
  247. , app_version
  248. , app_minor_version
  249. , ltv_group
  250. , date(first_install_dt) as install_date
  251. , CASE
  252. WHEN trophies_cnt < 125 then '0-125'
  253. WHEN trophies_cnt < 1400 then '125-1400'
  254. WHEN trophies_cnt < 2000 then '1400-2000'
  255. WHEN trophies_cnt < 3800 then '2000-3800'
  256. WHEN trophies_cnt < 7000 then '3800-7000'
  257. WHEN trophies_cnt < 16000 then '7000-16000'
  258. WHEN trophies_cnt < 30000 then '16000-30000'
  259. WHEN trophies_cnt >= 30000 then '30000+'
  260. END as trophy_group
  261. , CASE
  262. WHEN trophies_cnt < 125 THEN '0-125'
  263. WHEN trophies_cnt < 1400 THEN '125-1400'
  264. WHEN trophies_cnt < 2000 THEN '1400-2000'
  265. WHEN trophies_cnt < 3800 THEN '2000-3800'
  266. WHEN trophies_cnt < 4500 THEN '3800-4500'
  267. WHEN trophies_cnt < 5500 THEN '4500-5500'
  268. WHEN trophies_cnt < 7000 THEN '5500-7000'
  269. WHEN trophies_cnt < 10000 THEN '7000-10000'
  270. WHEN trophies_cnt < 13000 THEN '10000-13000'
  271. WHEN trophies_cnt < 16000 THEN '13000-16000'
  272. WHEN trophies_cnt < 20000 THEN '16000-20000'
  273. WHEN trophies_cnt < 30000 THEN '20000-30000'
  274. WHEN trophies_cnt >= 30000 THEN '30000+'
  275. END AS trophy_group_2
  276. ,CASE
  277. WHEN trophies_cnt < 800 THEN '800'
  278. WHEN trophies_cnt < 3800 THEN 'Studios'
  279. WHEN trophies_cnt < 30000 THEN 'Master_League'
  280. WHEN trophies_cnt >= 30000 THEN 'Legends_League'
  281. END league
  282. from candivore.prod.daily_users_from_params where interval_date >= '2022-10-01') B
  283.  
  284. on A.user_id = B.user_id and A.interval_date = B.interval_date
  285. )) GROUP BY 1,2,3,4,5,6) CC
  286.  
  287. ON AA.ltv_group = CC.ltv_group and AA.trophy_group = CC.trophy_group and AA.platform = CC.platform and AA.league = CC.league and AA.offer_id = CC.offer_id and AA.trophy_group_2 = CC.trophy_group_2
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!