API tools faq
paste
Advertisement
YuvalGai

Untitled

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