API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
May 7th, 2023
154
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 18.47 KB | None | 0 0
raw download clone embed print report
  1. CREATE OR REPLACE TABLE candivore.semantic_layer.T_MODES_CONTROL AS
  2.   SELECT A.*
  3. , G.match_type2
  4. , G.match_type_id
  5. , G.match_mode
  6. , G.modifier_1
  7. , G.modifier_2
  8. , G.is_random_mixer
  9. , G.rounds_cnt
  10. , G.total_matches
  11. , G.unique_users_from_match_end
  12. , G.event_start_date
  13. , G.event_start_ts
  14. , G.total_seconds_played_in_segment
  15. , G.total_booster_activations_in_segment
  16. , G.total_match_score_in_segment
  17. , G.games_with_legendary_boosters
  18. , G.games_with_SE_boosters
  19. , G.diamond_losses
  20. , G.diamond3_losses
  21. , G.legendary_losses
  22. , G.SE_losses
  23. , G.total_losses
  24. , G.total_wins
  25. , G.legendary_wins
  26. , G.SE_wins
  27. , G.min_time
  28. , G.max_time
  29. , G.unique_users
  30. , G.unique_users_per_solo_prize
  31. , G.unique_users_with_LESE_boosters
  32. , G.total_coin_value
  33. , G.total_match_ids
  34. , G.rumble_size
  35. , G.tournament_size
  36. , G.total_bots
  37. , G.total_on_fire
  38. , G.conceded_matches
  39. , G.total_perk_activations
  40. , G.max_max_prize_eligibility
  41. , D.points_earned LE_points_earned
  42. , D.value_earned LE_value_earned
  43. , C.lo_event_id
  44. , C.lo_event_min_trophies
  45. , C.lo_event_max_trophies
  46. , C.calendar_order
  47. , C.hours
  48. , C.lo_entry_tickets_required
  49. , C.lo_visible_on_homescreen
  50. , C.lo_entry_min_booster_rarity
  51. , C.lo_entry_max_booster_rarity
  52. , C.lo_event_solo_moves_per_turn
  53. , C.lo_event_turn_amt elimination_round
  54. , C.lo_event_turn_duration_sec
  55. , C.lo_entry_is_private
  56. FROM (SELECT
  57. DATE(derived_tstamp) interval_date
  58. , CASE
  59.     WHEN trophies_cnt < 800 THEN '800'
  60.     WHEN CURRENT_ARENA_INDEX<=13 THEN 'Studios'
  61.     WHEN CURRENT_ARENA_INDEX<=22 THEN 'Master_League'
  62.     WHEN CURRENT_ARENA_INDEX>=23 THEN 'Legends_League'
  63.  END arena_group
  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. , LTV_group
  75. , lo_entry_id
  76. , lo_event_type
  77. , lo_event_config_id
  78. , CASE
  79.     WHEN lo_event_id LIKE '%mutation%' THEN 'solo_mutation'
  80.     WHEN lo_event_id LIKE 'Solo%' OR lo_event_id LIKE 'solo%' THEN 'solo'
  81.     WHEN lo_event_type = 'Rumble' THEN 'rumble'
  82.     WHEN lo_event_type = 'AdventureMode' THEN 'adventure'
  83.     WHEN lo_event_type LIKE 'Leaderboard%' THEN 'leaderboard'
  84.     WHEN lo_event_type LIKE 'BoostersClash' THEN 'boosters_clash'
  85.     WHEN match_type_id = 4 OR transaction_source LIKE 'tournament%' THEN 'tournament'
  86.     WHEN match_type_id = 5 THEN 'daily'
  87.     WHEN match_type_id = 2 THEN 'classic'
  88.     ELSE 'other'
  89. END AS match_type
  90. , COUNT(DISTINCT match_id) transaction_matches
  91. , COUNT(DISTINCT user_id) AS unique_users_from_transaction
  92. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_received_resource = 0 THEN resource_cnt END) AS legendary_boosters_spent
  93. , SUM(CASE WHEN resource_sub_type = 'Special Edition' AND is_received_resource = 0 THEN resource_cnt END) AS se_boosters_spent
  94. , SUM(CASE WHEN resource_sub_type = 'Diamond 1' AND is_received_resource = 0 THEN resource_cnt END) AS diamond1_boosters_spent
  95. , SUM(CASE WHEN resource_sub_type = 'Diamond 2' AND is_received_resource = 0 THEN resource_cnt END) AS diamond2_boosters_spent
  96. , SUM(CASE WHEN resource_sub_type = 'Diamond 3' AND is_received_resource = 0 THEN resource_cnt END) AS diamond3_boosters_spent
  97. , SUM(CASE WHEN resource_sub_type = 'Gold' AND is_received_resource = 0 THEN resource_cnt END) AS gold_boosters_spent
  98. , SUM(CASE WHEN resource_sub_type = 'Silver' AND is_received_resource = 0 THEN resource_cnt END) AS silver_boosters_spent
  99. , SUM(CASE WHEN resource_sub_type = 'Bronze' AND is_received_resource = 0 THEN resource_cnt END) AS bronze_boosters_spent
  100. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_received_resource = resource_cnt THEN 1 END) AS legendary_boosters_earned
  101. , SUM(CASE WHEN resource_sub_type = 'Special Edition' AND is_received_resource = resource_cnt THEN 1 END) AS se_boosters_earned
  102. , SUM(CASE WHEN resource_sub_type = 'Diamond 1' AND is_received_resource = 1 THEN resource_cnt END) AS diamond1_boosters_earned
  103. , SUM(CASE WHEN resource_sub_type = 'Diamond 2' AND is_received_resource = 1 THEN resource_cnt END) AS diamond2_boosters_earned
  104. , SUM(CASE WHEN resource_sub_type = 'Diamond 3' AND is_received_resource = 1 THEN resource_cnt END) AS diamond3_boosters_earned
  105. , SUM(CASE WHEN resource_sub_type = 'Gold' AND is_received_resource = 1 THEN resource_cnt END) AS gold_boosters_earned
  106. , SUM(CASE WHEN resource_sub_type = 'Silver' AND is_received_resource = 1 THEN resource_cnt END) AS silver_boosters_earned
  107. , SUM(CASE WHEN resource_sub_type = 'Bronze' AND is_received_resource = 1 THEN resource_cnt END) AS bronze_boosters_earned
  108. , SUM(CASE WHEN resource_type = 'Shield' AND is_received_resource = 1 THEN resource_cnt END) AS shields_earned
  109. , SUM(CASE WHEN resource_type = 'Shield' AND is_received_resource = 0 THEN resource_cnt END) AS shields_spent
  110. , SUM(CASE WHEN resource_type = 'Perk' AND is_received_resource = 1 THEN resource_cnt END) AS perks_earned
  111. , SUM(CASE WHEN resource_type = 'Perk' AND is_received_resource = 0 THEN resource_cnt END) AS perks_spent
  112. , SUM(CASE WHEN resource_type = 'Ticket' AND is_received_resource = 1 THEN resource_cnt END) AS tickets_earned
  113. , SUM(CASE WHEN resource_type = 'Ticket' AND is_received_resource = 0 THEN resource_cnt END) AS tickets_spent
  114. , SUM(CASE WHEN resource_type = 'Trophy' AND is_received_resource = 1 THEN resource_cnt END) AS trophy_earned
  115. , SUM(CASE WHEN resource_type = 'Trophy' AND is_received_resource = 0 THEN resource_cnt END) AS trophy_spent
  116. , SUM(CASE WHEN resource_id = 'Coin' AND is_received_resource = 1 THEN resource_cnt END) AS coins_earned
  117. , SUM(CASE WHEN resource_id = 'Coin' AND is_received_resource = 0 THEN resource_cnt END) AS coins_spent
  118. ---
  119. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS legendary_boosters_spent_value
  120. , SUM(CASE WHEN resource_sub_type = 'Special Edition' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS se_boosters_spent_value
  121. , SUM(CASE WHEN resource_sub_type = 'Diamond 1' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS diamond1_boosters_spent_value
  122. , SUM(CASE WHEN resource_sub_type = 'Diamond 2' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS diamond2_boosters_spent_value
  123. , SUM(CASE WHEN resource_sub_type = 'Diamond 3' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS diamond3_boosters_spent_value
  124. , SUM(CASE WHEN resource_sub_type = 'Gold' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS gold_boosters_spent_value
  125. , SUM(CASE WHEN resource_sub_type = 'Silver' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS silver_boosters_spent_value
  126. , SUM(CASE WHEN resource_sub_type = 'Bronze' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS bronze_boosters_spent_value
  127. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS legendary_boosters_earned_value
  128. , SUM(CASE WHEN resource_sub_type = 'Special Edition' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS se_boosters_earned_value
  129. , SUM(CASE WHEN resource_sub_type = 'Diamond 1' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS diamond1_boosters_earned_value
  130. , SUM(CASE WHEN resource_sub_type = 'Diamond 2' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS diamond2_boosters_earned_value
  131. , SUM(CASE WHEN resource_sub_type = 'Diamond 3' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS diamond3_boosters_earned_value
  132. , SUM(CASE WHEN resource_sub_type = 'Gold' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS gold_boosters_earned_value
  133. , SUM(CASE WHEN resource_sub_type = 'Silver' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS silver_boosters_earned_value
  134. , SUM(CASE WHEN resource_sub_type = 'Bronze' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS bronze_boosters_earned_value
  135. , SUM(CASE WHEN resource_type = 'Shield' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS shields_earned_value
  136. , SUM(CASE WHEN resource_type = 'Shield' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS shields_spent_value
  137. , SUM(CASE WHEN resource_type = 'Perk' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS perks_earned_value
  138. , SUM(CASE WHEN resource_type = 'Perk' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS perks_spent_value
  139. , SUM(CASE WHEN resource_type = 'Ticket' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS tickets_earned_value
  140. , SUM(CASE WHEN resource_type = 'Ticket' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS tickets_spent_value
  141. , SUM(CASE WHEN resource_type = 'Trophy' AND is_received_resource = 1 THEN resource_cnt*resource_coin_value END) AS trophy_earned_value
  142. , SUM(CASE WHEN resource_type = 'Trophy' AND is_received_resource = 0 THEN resource_cnt*resource_coin_value END) AS trophy_spent_value
  143. FROM (SELECT temp1.*, temp2.resource_coin_value, temp1.resource_cnt*temp2.resource_coin_value AS total_coins_value FROM (SELECT * FROM CANDIVORE.PROD.F_USER_RESOURCE_TRANSACTION WHERE (transaction_source LIKE 'live_event' OR transaction_source LIKE 'match_end' OR transaction_source LIKE 'tournament%' OR transaction_source LIKE 'disconnection_refund') AND DATE(derived_tstamp) > DATEADD(DAY,-121,GETDATE())) temp1 LEFT JOIN
  144. (SELECT resource_name, resource_version, resource_sub_type, resource_coin_value  FROM (SELECT * FROM
  145. (SELECT *, ROW_NUMBER() OVER (partition BY resource_name, resource_version ORDER BY db_create_date DESC, resource_id DESC) AS resource_rank
  146.   FROM candivore.prod.DIM_RESOURCE_VERSION)
  147.   WHERE resource_rank = 1)) temp2
  148. ON temp1.resource_id = temp2.resource_name AND temp1.resource_version = temp2.resource_version AND temp1.resource_sub_type = temp2.resource_sub_type)
  149. GROUP BY 1,2,3,4,5,6,7,8) A
  150.  
  151. LEFT JOIN
  152. (SELECT
  153. match_end_date interval_date
  154. ,CASE
  155.     WHEN trophies_cnt < 800 THEN '800'
  156.     WHEN CURRENT_ARENA_INDEX<=13 THEN 'Studios'
  157.     WHEN CURRENT_ARENA_INDEX<=22 THEN 'Master_League'
  158.     WHEN CURRENT_ARENA_INDEX>=23 THEN 'Legends_League'
  159.  END arena_group
  160. , CASE
  161.     WHEN trophies_cnt < 125 THEN '0-125'
  162.     WHEN trophies_cnt < 1400 THEN '125-1400'
  163.     WHEN trophies_cnt < 2000 THEN '1400-2000'
  164.     WHEN trophies_cnt < 3800 THEN '2000-3800'
  165.     WHEN trophies_cnt < 7000 THEN '3800-7000'
  166.     WHEN trophies_cnt < 16000 THEN '7000-16000'
  167.     WHEN trophies_cnt < 30000 THEN '16000-30000'
  168.     WHEN trophies_cnt >= 30000 THEN '30000+'
  169.   END AS trophy_group
  170. , LTV_group
  171. , calendar_entry_id lo_entry_id
  172. , unique_users
  173. , unique_users_with_LESE_boosters
  174. , event_start_date
  175. , event_start_ts
  176. , CASE
  177.     WHEN lo_event_id LIKE '%mutation%' THEN 'solo_mutation'
  178.     WHEN lo_event_id LIKE 'Solo%' OR lo_event_id LIKE 'solo%' THEN 'solo'
  179.     WHEN lo_event_type = 'Rumble' THEN 'rumble'
  180.     WHEN lo_event_type = 'AdventureMode' THEN 'adventure'
  181.     WHEN lo_event_type LIKE 'Leaderboard%' THEN 'leaderboard'
  182.     WHEN lo_event_type LIKE 'BoostersClash' THEN 'boosters_clash'
  183.     WHEN match_type_id = 4 THEN 'tournament'
  184.     WHEN match_type_id = 5 THEN 'daily'
  185.     WHEN match_type_id = 2 THEN 'classic'
  186.     ELSE match_type
  187. END AS match_type2
  188. , match_type_id
  189. , CASE WHEN is_random_mixer = TRUE THEN 'random' ELSE match_mode END match_mode
  190. , is_random_mixer
  191. , rounds_cnt
  192. , tournament_size
  193. , modifier_1
  194. , modifier_2
  195. , min_time
  196. , max_time
  197. , rumble_size
  198. , max_max_prize_eligibility
  199. , mode(unique_users_per_solo_prize) unique_users_per_solo_prize
  200. , COUNT(*) AS total_matches
  201. , SUM(CASE WHEN is_conceded = 0 THEN 1 ELSE 0 END) unconceded_matches
  202. , COUNT(DISTINCT match_id) AS total_match_ids
  203. , COUNT(DISTINCT user_id) AS unique_users_from_match_end
  204. , SUM(MATCH_DURATION_SEC) AS total_seconds_played_in_segment
  205. , SUM(CASE WHEN is_conceded = 0 THEN MATCH_DURATION_SEC ELSE 0 END) AS total_seconds_played_in_segment_unconceded
  206. , SUM(ability_activated_cnt) AS total_booster_activations_in_segment
  207. , SUM(CASE WHEN is_conceded = 0 THEN ability_activated_cnt ELSE 0 END) AS total_booster_activations_in_segment_unconceded
  208. , SUM(match_score) AS total_match_score_in_segment
  209. , SUM(CASE WHEN is_conceded = 0 THEN match_score ELSE 0 END) AS total_match_score_in_segment_unconceded
  210. , SUM(CASE WHEN resource_sub_type = 'Legendary' THEN 1 END) AS games_with_legendary_boosters
  211. , SUM(CASE WHEN resource_type = 'Booster' AND resource_sub_type = 'Special Edition' THEN 1 END) AS games_with_SE_boosters
  212. , SUM(CASE WHEN booster_tier = 'Diamond' AND is_won = 0 THEN 1 END) AS diamond_losses
  213. , SUM(CASE WHEN resource_sub_type = 'Diamond 3' AND is_won = 0 THEN 1 END) AS diamond3_losses
  214. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_won = 0 THEN 1 END) AS legendary_losses
  215. , SUM(CASE WHEN resource_sub_type = 'Special Edition' AND is_won = 0 THEN 1 END) AS SE_losses
  216. , SUM(CASE WHEN is_won = 0 THEN 1 END) AS total_losses
  217. , SUM(CASE WHEN is_won = 1 THEN 1 END) AS total_wins
  218. , SUM(CASE WHEN resource_sub_type = 'Legendary' AND is_won = 1 THEN 1 END) legendary_wins
  219. , SUM(CASE WHEN resource_type = 'Booster' AND resource_sub_type = 'Special Edition' AND is_won = 1 THEN 1 END) SE_wins
  220. , SUM(CASE WHEN resource_type = 'Booster' THEN resource_coin_value END) AS total_coin_value
  221. , SUM(is_on_fire_game) total_on_fire
  222. , SUM(CASE WHEN match_type = 'Rumble' THEN ((rumble_size-unique_users_in_match)/unique_users_in_match) ELSE is_rival_bot END) total_bots
  223. , SUM(board_ability1_uses + board_ability2_uses) AS total_perk_activations
  224. , SUM(CASE WHEN is_conceded = 1 AND is_won = 0 THEN 1 ELSE 0 END) conceded_matches
  225. FROM (SELECT *, MIN(derived_tstamp) OVER(partition BY calendar_entry_id) min_time
  226. , MAX(derived_tstamp) OVER(partition BY calendar_entry_id) max_time
  227. , COUNT(DISTINCT user_id) OVER(partition BY calendar_entry_id) unique_users
  228. , COUNT(DISTINCT user_id) OVER(partition BY calendar_entry_id,max_max_prize_eligibility) unique_users_per_solo_prize
  229. , COUNT(DISTINCT user_id) OVER(partition BY match_id) unique_users_in_match
  230. , MIN(DATE(derived_tstamp)) OVER(partition BY calendar_entry_id) event_start_date
  231. , MIN(date_trunc('hour', derived_tstamp)) OVER(partition BY calendar_entry_id) event_start_ts
  232. , COUNT(DISTINCT CASE WHEN resource_sub_type = 'Legendary' OR resource_sub_type = 'Special Edition' THEN user_id END) OVER(partition BY calendar_entry_id) unique_users_with_LESE_boosters
  233. FROM (SELECT *, MAX(max_prize_eligibility) OVER(partition BY calendar_entry_id,user_id) max_max_prize_eligibility FROM CANDIVORE.PROD.F_USER_MATCH LEFT JOIN
  234. (SELECT resource_id, resource_coin_value  FROM CANDIVORE.PROD.DIM_RESOURCE_VERSION) temp2
  235. ON CANDIVORE.PROD.F_USER_MATCH.resource_id = temp2.resource_id WHERE is_bot = 0 AND DATE(CANDIVORE.PROD.F_USER_MATCH.derived_tstamp) > DATEADD(DAY,-121,GETDATE())))
  236. GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21) G
  237. ON A.interval_date = G.interval_date AND A.arena_group = G.arena_group AND A.trophy_group = G.trophy_group AND A.LTV_group = G.LTV_group AND A.lo_entry_id = G.lo_entry_id AND A.match_type = G.match_type2
  238.  
  239. LEFT JOIN
  240.  
  241. (SELECT A.LO_ENTRY_ID, B.lo_event_id, A.interval_date, A.lo_entry_name, A.calendar_order, A.lo_visible_on_homescreen,
  242. CASE WHEN min0 IS NULL THEN min1 ELSE min0 END lo_event_min_trophies,  
  243. CASE WHEN max0 IS NULL THEN max1 ELSE max0 END lo_event_max_trophies,
  244. hours, lo_entry_tickets_required, lo_entry_min_booster_rarity, lo_entry_max_booster_rarity,lo_event_solo_moves_per_turn, lo_event_turn_amt, lo_event_turn_duration_sec, lo_entry_is_private
  245. FROM
  246. ((SELECT CASE WHEN lo_original_entry_id IS NULL THEN LO_ENTRY_ID ELSE LO_original_ENTRY_ID END LO_ENTRY_ID,DATE(derived_tstamp) interval_date, LO_ENTRY_NAME, calendar_order, lo_visible_on_homescreen, lo_entry_min_trophies min0, lo_entry_max_trophies max0, round((lo_entry_end_ts-lo_entry_start_ts)/60/60,0) hours, lo_entry_tickets_required,lo_entry_min_booster_rarity, lo_entry_max_booster_rarity, lo_entry_is_private FROM (SELECT * FROM (SELECT *,CASE WHEN lo_entry_ts IS NULL THEN lo_entry_update_ts ELSE lo_entry_ts END lo_entry_ts2,ROW_NUMBER() OVER (partition BY lo_entry_id ORDER BY lo_entry_ts2 DESC) rn FROM CANDIVORE.PROD.F_LIVEOPS_CALENDAR) WHERE rn = 1)) A
  247. LEFT JOIN
  248. (SELECT DATE(derived_tstamp) interval_date, lo_event_id, lo_event_min_trophies min1, lo_event_max_trophies max1, lo_event_solo_moves_per_turn, lo_event_turn_amt, lo_event_turn_duration_sec  FROM CANDIVORE.PROD.DIM_EVENT_CONFIG) B
  249. ON A.LO_ENTRY_NAME = B.LO_EVENT_ID))
  250. C
  251. ON A.lo_entry_id = C.lo_entry_id
  252.  
  253. FULL OUTER JOIN
  254. (SELECT table1.source_calendar_entry_id, table1.interval_date interval_date, table2.arena_group, table2.trophy_group, table2.LTV_group, SUM(table1.points_earned) points_earned, SUM(table1.points_earned*table1.event_point_value) value_earned FROM  (SELECT *, DATE(derived_tstamp) AS interval_date FROM CANDIVORE.PROD.F_LIVE_EVENT_PROGRESSION WHERE action = 'points_earned' AND event_slot = 'Main' AND DATE(derived_tstamp) > DATEADD(DAY,-121,GETDATE())) AS table1 LEFT JOIN (SELECT
  255. user_id
  256. , interval_date
  257. , CASE
  258.     WHEN trophies_cnt < 800 THEN '800'
  259.     WHEN CURRENT_ARENA_INDEX<=13 THEN 'Studios'
  260.     WHEN CURRENT_ARENA_INDEX<=22 THEN 'Master_League'
  261.     WHEN CURRENT_ARENA_INDEX>=23 THEN 'Legends_League'
  262.  END ARENA_GROUP
  263. , CASE
  264.     WHEN trophies_cnt < 125 THEN '0-125'
  265.     WHEN trophies_cnt < 1400 THEN '125-1400'
  266.     WHEN trophies_cnt < 2000 THEN '1400-2000'
  267.     WHEN trophies_cnt < 3800 THEN '2000-3800'
  268.     WHEN trophies_cnt < 7000 THEN '3800-7000'
  269.     WHEN trophies_cnt < 16000 THEN '7000-16000'
  270.     WHEN trophies_cnt < 30000 THEN '16000-30000'
  271.     WHEN trophies_cnt >= 30000 THEN '30000+'
  272.   END AS trophy_group
  273.  
  274. , CASE WHEN total_iap_amt = 0 THEN '0'
  275.            WHEN total_iap_amt <= 10 THEN 'Low'
  276.            WHEN total_iap_amt <= 100 THEN 'Med'
  277.            WHEN total_iap_amt <= 299 THEN 'High'
  278.            WHEN total_iap_amt <= 999 THEN 'Very High'
  279.            WHEN total_iap_amt >= 1000 THEN 'VIP'
  280.            ELSE NULL
  281. END AS LTV_group
  282.  FROM CANDIVORE.PROD.USER_DAILY_PARAMS) table2 ON table1.user_id = table2.user_id AND table1.interval_date = table2.interval_date  GROUP BY 1,2,3,4,5) D
  283. ON A.lo_entry_id = D.source_calendar_entry_id AND A.interval_date = D.interval_date AND A.LTV_group = D.LTV_group AND A.trophy_group = D.trophy_group AND A.ARENA_GROUP = D.ARENA_GROUP
  284.  
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!