API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
May 21st, 2023
194
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 4.94 KB | None | 0 0
raw download clone embed print report
  1. CREATE OR REPLACE TABLE candivore.semantic_layer.T_SOLO_DASHBOARD AS
  2.  
  3. SELECT
  4. DATE(derived_tstamp) interval_date
  5. ,calendar_entry_id
  6. ,lo_event_id
  7. ,CASE WHEN lo_event_id LIKE '%-mc-%' THEN 'MasterClub'
  8. WHEN lo_event_id LIKE '%cosmic%' THEN 'Cosmic'
  9. WHEN lo_event_id LIKE '%rally%' THEN 'Rally'
  10. WHEN lo_event_id LIKE '%heist%' THEN 'Heist'
  11. WHEN lo_event_id LIKE '%mutation%' THEN 'Mutations-Lab'
  12. ELSE 'no_entry_id(triggered)' END solo_type
  13. ,arena_group
  14. ,seniority_bin
  15. ,ltv_group
  16. ,engagement_group
  17. ,match_mode
  18. ,modifier_1
  19. ,modifier_2
  20. ,booster_tier
  21. ,resource_sub_type
  22. ,booster_name
  23. ,max_prize_won
  24. ,max_prize_reached
  25. ,mode(P25) P25
  26. ,mode(P50) P50
  27. ,mode(P75) P75
  28. ,mode(P80) P80
  29. ,mode(P85) P85
  30. ,mode(P90) P90
  31. ,mode(P95) P95
  32. ,mode(unique_users_in_event) unique_users_in_event
  33. ,mode(unique_users_reached_0_spot_prize_all_event) unique_users_reached_0_spot_prize_all_event
  34. ,mode(unique_users_reached_1_spot_prize_all_event) unique_users_reached_1_spot_prize_all_event
  35. ,mode(unique_users_reached_2_spot_prize_all_event) unique_users_reached_2_spot_prize_all_event
  36. ,mode(unique_users_reached_3_spot_prize_all_event) unique_users_reached_3_spot_prize_all_event
  37. ,mode(unique_users_reached_4_spot_prize_all_event) unique_users_reached_4_spot_prize_all_event
  38. ,COUNT(DISTINCT CASE WHEN max_prize_won = 0 THEN user_id END) unique_users_reached_0_spot_prize_solo_run_with_boosters
  39. ,COUNT(DISTINCT CASE WHEN max_prize_won = 1 THEN user_id END) unique_users_reached_1_spot_prize_solo_run_with_boosters
  40. ,COUNT(DISTINCT CASE WHEN max_prize_won = 2 THEN user_id END) unique_users_reached_2_spot_prize_solo_run_with_boosters
  41. ,COUNT(DISTINCT CASE WHEN max_prize_won = 3 THEN user_id END) unique_users_reached_3_spot_prize_solo_run_with_boosters
  42. ,COUNT(DISTINCT CASE WHEN max_prize_won = 4 THEN user_id END) unique_users_reached_4_spot_prize_solo_run_with_boosters
  43. ,COUNT(DISTINCT CASE WHEN max_prize_won = 5 THEN user_id END) unique_users_reached_5_spot_prize_solo_run_with_boosters
  44. ,COUNT(DISTINCT user_id) unique_users
  45. ,COUNT(DISTINCT match_id) total_matches
  46. ,SUM(match_score) match_score
  47. ,SUM(match_duration_sec) match_duration_sec
  48. ,SUM(ability_activated_cnt) ability_activated_cnt
  49. FROM
  50. (SELECT A.* ,B.AA max_prize_reached, COUNT(DISTINCT CASE WHEN max_prize_reached = 0 THEN A.user_id END) OVER(partition BY A.calendar_entry_id) unique_users_reached_0_spot_prize_all_event
  51. ,COUNT(DISTINCT CASE WHEN max_prize_reached = 1 THEN A.user_id END) OVER(partition BY A.calendar_entry_id) unique_users_reached_1_spot_prize_all_event
  52. ,COUNT(DISTINCT CASE WHEN max_prize_reached = 2 THEN A.user_id END) OVER(partition BY A.calendar_entry_id) unique_users_reached_2_spot_prize_all_event
  53. ,COUNT(DISTINCT CASE WHEN max_prize_reached = 3 THEN A.user_id END) OVER(partition BY A.calendar_entry_id) unique_users_reached_3_spot_prize_all_event
  54. ,COUNT(DISTINCT CASE WHEN max_prize_reached = 4 THEN A.user_id END) OVER(partition BY A.calendar_entry_id) unique_users_reached_4_spot_prize_all_event
  55. FROM (SELECT
  56. derived_tstamp
  57. ,user_id
  58. ,arena_group
  59. ,MATCH_ID
  60. ,seniority_bin
  61. ,ltv_group
  62. ,engagement_group
  63. ,match_mode
  64. ,modifier_1
  65. ,modifier_2
  66. ,calendar_entry_id
  67. ,lo_event_id
  68. ,CASE WHEN lo_event_id LIKE '%-mc-%' THEN 'MasterClub'
  69. WHEN lo_event_id LIKE '%cosmic%' THEN 'Cosmic'
  70. WHEN lo_event_id LIKE '%rally%' THEN 'Rally'
  71. WHEN lo_event_id LIKE '%heist%' THEN 'Heist'
  72. WHEN lo_event_id LIKE '%mutation%' THEN 'Mutations-Lab'
  73. ELSE 'no_entry_id(triggered)' END solo_type
  74. ,booster_tier
  75. ,resource_sub_type
  76. ,booster_name
  77. ,match_score
  78. ,match_duration_sec
  79. ,ability_activated_cnt
  80. ,max_prize_eligibility
  81. ,COUNT(DISTINCT user_id) OVER(partition BY calendar_entry_id) unique_users_in_event
  82. ,percentile_disc(0.25) WITHIN GROUP (ORDER BY match_score) OVER(partition BY booster_name, calendar_entry_id) P25
  83. ,percentile_disc(0.5) WITHIN GROUP (ORDER BY match_score) OVER(partition BY booster_name, calendar_entry_id) P50
  84. ,percentile_disc(0.75) WITHIN GROUP (ORDER BY match_score) OVER(partition BY booster_name, calendar_entry_id) P75
  85. ,percentile_disc(0.80) WITHIN GROUP (ORDER BY match_score) OVER(partition BY booster_name, calendar_entry_id) P80
  86. ,percentile_disc(0.85) WITHIN GROUP (ORDER BY match_score) OVER(partition BY booster_name, calendar_entry_id) P85
  87. ,percentile_disc(0.90) WITHIN GROUP (ORDER BY match_score) OVER(partition BY booster_name, calendar_entry_id) P90
  88. ,percentile_disc(0.95) WITHIN GROUP (ORDER BY match_score) OVER(partition BY booster_name, calendar_entry_id) P95
  89. ,MAX(max_prize_eligibility) OVER(partition BY user_id,calendar_entry_id) max_prize_reached_per_event_per_user
  90. ,max_prize_won
  91. ,levels
  92. FROM candivore.prod.f_user_match WHERE pvp_mode = 'Solo' AND DATE(derived_tstamp) >= DATEADD(DAY,-91,GETDATE())) A
  93. LEFT JOIN
  94. (SELECT calendar_entry_id, user_id, MAX(event_level) AA FROM candivore.prod.f_live_event_progression WHERE lo_event_id LIKE '%mutation%' GROUP BY 1,2) B ON A.user_id = B.user_id AND A.calendar_entry_id = B.calendar_entry_id
  95. )
  96. GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16
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!