API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Mar 15th, 2023 (edited)
137
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 6.29 KB | None | 0 0
raw download clone embed print report
  1. CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.T_Coins_Spent AS
  2. SELECT * FROM (
  3.  
  4. (SELECT
  5. DATE(derived_tstamp) interval_date
  6. ,ltv_group_static ltv_group
  7. ,league
  8. ,trophy_group
  9. ,seniority_bin
  10. ,"boxes" transaction_source
  11. ,to_varchar(prize_container[0][1]) resource_id
  12. ,coin_price
  13. ,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
  14. ,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
  15. ,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
  16. , SUM(coin_price)/COUNT(*) coins_per_item
  17. FROM (
  18. SELECT A.*,B.ltv_group ltv_group_static,B.league,B.trophy_group,B.seniority_bin FROM (
  19. (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
  20. LEFT JOIN
  21. (SELECT user_id, interval_date, ltv_group,CASE
  22.     WHEN trophies_cnt<3800 THEN "Studios"
  23.     WHEN trophies_cnt<30000 THEN "Master_League"
  24.     WHEN trophies_cnt>=30000 THEN "Legends_League"
  25.  END AS league
  26. , CASE
  27.     WHEN trophies_cnt < 125 THEN "0-125"
  28.     WHEN trophies_cnt < 1400 THEN "125-1400"
  29.     WHEN trophies_cnt < 2000 THEN "1400-2000"
  30.     WHEN trophies_cnt < 3800 THEN "2000-3800"
  31.     WHEN trophies_cnt < 7000 THEN "3800-7000"
  32.     WHEN trophies_cnt < 16000 THEN "7000-16000"
  33.     WHEN trophies_cnt < 30000 THEN "16000-30000"
  34.     WHEN trophies_cnt >= 30000 THEN "30000+"
  35.   END AS trophy_group
  36. ,seniority_bin FROM candivore.prod.daily_users_from_params WHERE interval_date > "2023-01-01") B
  37. ON A.user_id = B.user_id AND A.interval_date = B.interval_date
  38. ))
  39. 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 ORDER BY 1 DESC, 2)
  40.  
  41. UNION ALL
  42.  
  43. (SELECT interval_date
  44. ,ltv_group_static ltv_group
  45. ,league
  46. ,trophy_group
  47. ,seniority_bin
  48. ,transaction_source
  49. ,resource_id
  50. ,coin_price
  51. ,resource_cnt
  52. ,COUNT(*) occasions_bought
  53. ,SUM(coin_price) coins_spent
  54. ,SUM(coin_price)/COUNT(*) coins_per_item
  55. FROM (
  56. SELECT A.*,B.ltv_group ltv_group_static,B.league,B.trophy_group,B.seniority_bin FROM (
  57. (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
  58. LEFT JOIN
  59. (SELECT user_id, interval_date, ltv_group,CASE
  60.     WHEN trophies_cnt<3800 THEN "Studios"
  61.     WHEN trophies_cnt<30000 THEN "Master_League"
  62.     WHEN trophies_cnt>=30000 THEN "Legends_League"
  63.  END AS league
  64. , CASE
  65.     WHEN trophies_cnt < 125 THEN "0-125"
  66.     WHEN trophies_cnt < 1400 THEN "125-1400"
  67.     WHEN trophies_cnt < 2000 THEN "1400-2000"
  68.     WHEN trophies_cnt < 3800 THEN "2000-3800"
  69.     WHEN trophies_cnt < 7000 THEN "3800-7000"
  70.     WHEN trophies_cnt < 16000 THEN "7000-16000"
  71.     WHEN trophies_cnt < 30000 THEN "16000-30000"
  72.     WHEN trophies_cnt >= 30000 THEN "30000+"
  73.   END AS trophy_group
  74. ,seniority_bin FROM candivore.prod.daily_users_from_params WHERE interval_date > "2023-01-01") B
  75. ON A.user_id = B.user_id AND A.interval_date = B.interval_date
  76. )) GROUP BY 1,2,3,4,5,6,7,8,9 ORDER BY 1 DESC, 2 , 3
  77. )
  78.  
  79. UNION ALL
  80.  
  81. (SELECT interval_date
  82. ,ltv_group_static ltv_group
  83. ,league
  84. ,trophy_group
  85. ,seniority_bin
  86. ,transaction_source
  87. ,resource_id
  88. ,resource_cnt coin_price
  89. ,1 resource_cnt
  90. ,COUNT(*) occasions_bought
  91. ,SUM(resource_cnt) coins_spent
  92. ,resource_cnt coins_per_item
  93. FROM (
  94. SELECT A.*,B.ltv_group ltv_group_static,B.league,B.trophy_group,B.seniority_bin FROM (
  95. (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
  96. LEFT JOIN
  97. (SELECT user_id, interval_date, ltv_group,CASE
  98.     WHEN trophies_cnt<3800 THEN "Studios"
  99.     WHEN trophies_cnt<30000 THEN "Master_League"
  100.     WHEN trophies_cnt>=30000 THEN "Legends_League"
  101.  END AS league
  102. , CASE
  103.     WHEN trophies_cnt < 125 THEN "0-125"
  104.     WHEN trophies_cnt < 1400 THEN "125-1400"
  105.     WHEN trophies_cnt < 2000 THEN "1400-2000"
  106.     WHEN trophies_cnt < 3800 THEN "2000-3800"
  107.     WHEN trophies_cnt < 7000 THEN "3800-7000"
  108.     WHEN trophies_cnt < 16000 THEN "7000-16000"
  109.     WHEN trophies_cnt < 30000 THEN "16000-30000"
  110.     WHEN trophies_cnt >= 30000 THEN "30000+"
  111.   END AS trophy_group
  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 ORDER BY 1 DESC, 2 , 3
  115. )
  116. )
  117.  
  118. UNION ALL
  119.  
  120. (SELECT interval_date
  121. ,ltv_group_static ltv_group
  122. ,league
  123. ,trophy_group
  124. ,seniority_bin
  125. ,transaction_source
  126. ,offer_id resource_id
  127. ,coin_price
  128. ,1 resource_cnt
  129. ,COUNT(*) occasions_bought
  130. ,SUM(coin_price) coins_spent
  131. ,SUM(coin_price)/COUNT(*) coins_per_item
  132. FROM (
  133. SELECT A.*,B.ltv_group ltv_group_static,B.league,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 = "daily_deals") 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. ,seniority_bin FROM candivore.prod.daily_users_from_params WHERE interval_date > "2023-01-01") B
  152. ON A.user_id = B.user_id AND A.interval_date = B.interval_date
  153. )) GROUP BY 1,2,3,4,5,6,7,8,9 ORDER BY 1 DESC, 2 , 3
  154. )
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!