API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
May 15th, 2023
169
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 2.89 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. ,COUNT(DISTINCT user_id) unique_users
  33. ,COUNT(DISTINCT match_id) total_matches
  34. ,SUM(match_score) match_score
  35. ,SUM(match_duration_sec) match_duration_sec
  36. ,SUM(ability_activated_cnt) ability_activated_cnt
  37. FROM
  38. (SELECT A.* ,B.AA max_prize_reached FROM (SELECT
  39. derived_tstamp
  40. ,user_id
  41. ,arena_group
  42. ,MATCH_ID
  43. ,seniority_bin
  44. ,ltv_group
  45. ,engagement_group
  46. ,match_mode
  47. ,modifier_1
  48. ,modifier_2
  49. ,calendar_entry_id
  50. ,lo_event_id
  51. ,CASE WHEN lo_event_id LIKE '%-mc-%' THEN 'MasterClub'
  52. WHEN lo_event_id LIKE '%cosmic%' THEN 'Cosmic'
  53. WHEN lo_event_id LIKE '%rally%' THEN 'Rally'
  54. WHEN lo_event_id LIKE '%heist%' THEN 'Heist'
  55. WHEN lo_event_id LIKE '%mutation%' THEN 'Mutations-Lab'
  56. ELSE 'no_entry_id(triggered)' END solo_type
  57. ,booster_tier
  58. ,resource_sub_type
  59. ,booster_name
  60. ,match_score
  61. ,match_duration_sec
  62. ,ability_activated_cnt
  63. ,max_prize_eligibility
  64. ,percentile_disc(0.25) WITHIN GROUP (ORDER BY match_score) OVER(partition BY booster_name, calendar_entry_id) P25
  65. ,percentile_disc(0.5) WITHIN GROUP (ORDER BY match_score) OVER(partition BY booster_name, calendar_entry_id) P50
  66. ,percentile_disc(0.75) WITHIN GROUP (ORDER BY match_score) OVER(partition BY booster_name, calendar_entry_id) P75
  67. ,percentile_disc(0.80) WITHIN GROUP (ORDER BY match_score) OVER(partition BY booster_name, calendar_entry_id) P80
  68. ,percentile_disc(0.85) WITHIN GROUP (ORDER BY match_score) OVER(partition BY booster_name, calendar_entry_id) P85
  69. ,percentile_disc(0.90) WITHIN GROUP (ORDER BY match_score) OVER(partition BY booster_name, calendar_entry_id) P90
  70. ,percentile_disc(0.95) WITHIN GROUP (ORDER BY match_score) OVER(partition BY booster_name, calendar_entry_id) P95
  71. ,MAX(max_prize_eligibility) OVER(partition BY user_id,calendar_entry_id) max_prize_reached_per_event_per_user
  72. ,max_prize_won
  73. ,levels
  74. FROM candivore.prod.f_user_match WHERE pvp_mode = 'Solo' AND DATE(derived_tstamp) >= DATEADD(DAY,-181,GETDATE())) A
  75. LEFT JOIN
  76. (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
  77. )
  78. 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!