API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Jun 12th, 2023
146
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 6.08 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.*, BB.occasions_bought_per_user, BB.unique_users_bought, BB.Coin_price, BB.IAP_price, BB.video_price, BB.rev, BB.dollars_rev 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(league2,''n/a'') league
  11. /*, ifnull(country,''n/a'') country
  12. , ifnull(language,''n/a'') language */
  13. ---
  14. , offer_id
  15. , template_id
  16. , offer_context
  17. ---
  18. , count(distinct user_id) unique_users_shown
  19. , count(*) occasions_shown
  20. FROM (
  21. SELECT A.*, B.ltv_group, B.platform , B.trophy_group , B.league, B.country, B.language
  22. ,first_value(B.ltv_group) over(partition by A.user_id,template_id order by B.interval_date) ltv_group2
  23. ,first_value(B.trophy_group) over(partition by A.user_id,template_id order by B.interval_date) trophy_group2
  24. ,first_value(B.league) over(partition by A.user_id,template_id order by B.interval_date) league2
  25. from(
  26. (select
  27. date(derived_tstamp) interval_date
  28. , uuid user_id
  29. , template_id
  30. , offer_context
  31. , CASE when length(offer_id) != 8 then offer_id else ''0'' end offer_id --
  32. , ifnull(coin_price,0) coin_price --
  33. , ifnull(video_price,0) video_price --
  34. , ifnull(iap_price,0) iap_price --
  35. , min(interval_date) over(partition by template_id) offer_start_date
  36. from candivore.prod.f_special_offer_shown where date(derived_tstamp) >= ''2022-10-01'' and template_id like ''%pick-n-choose%'') A
  37.  
  38. LEFT JOIN
  39.  
  40. (select
  41. user_id
  42. , interval_date
  43. , country
  44. , language
  45. , platform
  46. , app_version
  47. , app_minor_version
  48. , ltv_group
  49. , date(first_install_dt) as install_date
  50. , CASE
  51. WHEN trophies_cnt < 125 then ''0-125''
  52. WHEN trophies_cnt < 1400 then ''125-1400''
  53. WHEN trophies_cnt < 2000 then ''1400-2000''
  54. WHEN trophies_cnt < 3800 then ''2000-3800''
  55. WHEN trophies_cnt < 7000 then ''3800-7000''
  56. WHEN trophies_cnt < 16000 then ''7000-16000''
  57. WHEN trophies_cnt < 30000 then ''16000-30000''
  58. WHEN trophies_cnt >= 30000 then ''30000+''
  59. END as trophy_group
  60. ,CASE
  61. WHEN trophies_cnt < 800 THEN ''800''
  62. WHEN trophies_cnt < 3800 THEN ''Studios''
  63. WHEN trophies_cnt < 30000 THEN ''Master_League''
  64. WHEN trophies_cnt >= 30000 THEN ''Legends_League''
  65. END league
  66. from candivore.prod.daily_users_from_params where interval_date >= ''2022-10-01'') B
  67.  
  68. on A.user_id = B.user_id and A.interval_date = B.interval_date
  69. )) GROUP BY 1,2,3,4,5,6,7,8) AA
  70.  
  71. left JOIN
  72.  
  73. (SELECT
  74. -- interval_date
  75. ---
  76. ltv_group
  77. , platform
  78. , trophy_group
  79. , league
  80. /*, ifnull(country,''n/a'') country
  81. , ifnull(language,''n/a'') language*/
  82. ---
  83. , offer_id -- offer_id
  84. , template_id
  85. ---
  86. --, purchase_type
  87. --, purchase_subtype
  88. , coin_price
  89. , video_price
  90. , iap_price
  91. , occasions_bought_per_user
  92. --, min(interval_date) over(partition by offer_id) offer_start_date
  93. , count(distinct user_id) unique_users_bought
  94. , sum(rev) rev
  95. , sum(dollars_rev) dollars_rev
  96.  
  97. FROM (
  98. SELECT
  99. -- interval_date
  100. ---
  101. user_id
  102. , ifnull(ltv_group2,''n/a'') ltv_group
  103. , ifnull(platform,''n/a'') platform
  104. , ifnull(trophy_group2,''n/a'') trophy_group
  105. , ifnull(league2,''n/a'') league
  106. /*, ifnull(country,''n/a'') country
  107. , ifnull(language,''n/a'') language*/
  108. ---
  109. , offer_id -- offer_id
  110. , template_id
  111. ---
  112. --, purchase_type
  113. --, purchase_subtype
  114. , coin_price
  115. , video_price
  116. , iap_price
  117. , sum(iap_price) rev
  118. , sum(dollar_price) dollars_rev
  119. --, min(interval_date) over(partition by offer_id) offer_start_date
  120. , count(*) occasions_bought_per_user
  121. /*, sum(case when level = 1 then 1 else 0 end) bought_lvl1
  122. , sum(case when level = 2 then 1 else 0 end) bought_lvl2
  123. , sum(case when level = 3 then 1 else 0 end) bought_lvl3
  124. , sum(case when level = 4 then 1 else 0 end) bought_lvl4
  125. , sum(case when level = 5 then 1 else 0 end) bought_lvl5*/
  126. FROM (
  127. SELECT A.*, B.ltv_group, B.platform, B.trophy_group, B.league, B.country, B.language
  128. ,first_value(B.ltv_group) over(partition by A.user_id,template_id order by B.interval_date) ltv_group2
  129. ,first_value(B.trophy_group) over(partition by A.user_id,template_id order by B.interval_date) trophy_group2
  130. ,first_value(B.league) over(partition by A.user_id,template_id order by B.interval_date) league2
  131. from (
  132. (select
  133. date(derived_tstamp) interval_date
  134. , uuid user_id
  135. , ifnull(offer_context,''0'') offer_context
  136. , ifnull(purchase_type,''0'') purchase_type
  137. , ifnull(purchase_subtype,''0'') purchase_subtype
  138. , ifnull(prize_raw,''0'') prize_raw
  139. , ifnull(offer_index,''0'') offer_index
  140. , template_id --
  141. , level
  142. , CASE when length(shown_offer_id) != 8 then shown_offer_id else ''0'' end offer_id
  143. , ifnull(coin_price,0) coin_price
  144. , ifnull(video_price,0) video_price
  145. , ifnull(iap_price,0) iap_price
  146. , ifnull(dollar_price,0) dollar_price
  147. from candivore.prod.f_special_offer_bought) A
  148.  
  149. LEFT JOIN
  150.  
  151. (select
  152. user_id
  153. , interval_date
  154. , country
  155. , language
  156. , platform
  157. , app_version
  158. , app_minor_version
  159. , ltv_group
  160. , date(first_install_dt) as install_date
  161. , CASE
  162. WHEN trophies_cnt < 125 then ''0-125''
  163. WHEN trophies_cnt < 1400 then ''125-1400''
  164. WHEN trophies_cnt < 2000 then ''1400-2000''
  165. WHEN trophies_cnt < 3800 then ''2000-3800''
  166. WHEN trophies_cnt < 7000 then ''3800-7000''
  167. WHEN trophies_cnt < 16000 then ''7000-16000''
  168. WHEN trophies_cnt < 30000 then ''16000-30000''
  169. WHEN trophies_cnt >= 30000 then ''30000+''
  170. END as trophy_group
  171. ,CASE
  172. WHEN trophies_cnt < 800 THEN ''800''
  173. WHEN trophies_cnt < 3800 THEN ''Studios''
  174. WHEN trophies_cnt < 30000 THEN ''Master_League''
  175. WHEN trophies_cnt >= 30000 THEN ''Legends_League''
  176. END league
  177. from candivore.prod.daily_users_from_params where interval_date >= ''2022-10-01'') B
  178.  
  179. on A.user_id = B.user_id and A.interval_date = B.interval_date
  180. )) GROUP BY 1,2,3,4,5,6,7,8,9,10) GROUP BY 1,2,3,4,5,6,7,8,9,10) BB
  181.  
  182. 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
  183. and AA.language = BB.language*/ and AA.offer_id = BB.offer_id and AA.template_id = BB.template_id
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!