API tools faq
paste
Advertisement
YuvalGai

Untitled

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