API tools faq
paste
YuvalGai

Untitled

YuvalGai
Jun 26th, 2023 (edited)
177
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.73 KB | None | 0 0
raw download clone embed print report
  1. SELECT
  2. match_end_date AS INTERVAL_DATE,
  3. arena_group,
  4. current_arena_index,
  5. CASE
  6. WHEN trophies_cnt < 125 THEN '0-125'
  7. WHEN trophies_cnt < 1400 THEN '125-1400'
  8. WHEN trophies_cnt < 2000 THEN '1400-2000'
  9. WHEN trophies_cnt < 3800 THEN '2000-3800'
  10. WHEN trophies_cnt < 7000 THEN '3800-7000'
  11. WHEN trophies_cnt < 16000 THEN '7000-16000'
  12. WHEN trophies_cnt < 30000 THEN '16000-30000'
  13. WHEN trophies_cnt >= 30000 THEN '30000+' END trophy_group,
  14. CASE
  15. WHEN trophies_cnt < 125 THEN '0-125'
  16. WHEN trophies_cnt < 1400 THEN '125-1400'
  17. WHEN trophies_cnt < 2000 THEN '1400-2000'
  18. WHEN trophies_cnt < 3800 THEN '2000-3800'
  19. WHEN trophies_cnt < 4500 THEN '3800-4500'
  20. WHEN trophies_cnt < 5500 THEN '4500-5500'
  21. WHEN trophies_cnt < 7000 THEN '5500-7000'
  22. WHEN trophies_cnt < 10000 THEN '7000-10000'
  23. WHEN trophies_cnt < 13000 THEN '10000-13000'
  24. WHEN trophies_cnt < 16000 THEN '13000-16000'
  25. WHEN trophies_cnt < 20000 THEN '16000-20000'
  26. WHEN trophies_cnt < 30000 THEN '20000-30000'
  27. WHEN trophies_cnt >= 30000 THEN '30000+'
  28. END AS trophy_group_2,
  29. ltv_group,
  30. is_payer,
  31. lo_entry_trophy_mode,
  32. lo_entry_trophy_gain,
  33. lo_entry_trophy_loss,
  34. pvp_mode,
  35. MATCH_TYPE_ID,
  36. CASE WHEN is_random_mixer = TRUE THEN 'Daily_Random_Mixer' ELSE match_type END AS MATCH_TYPE,
  37. MATCH_MODE,
  38. MODIFIER_1,
  39. MODIFIER_2,
  40. match_modifier_3,
  41. concat(
  42. MATCH_SUB_TYPE,
  43. '-',
  44. MATCH_MODE,
  45. '+',
  46. MODIFIER_1,
  47. '-',
  48. MODIFIER_2,
  49. '-',
  50. match_modifier_3
  51. ) AS match_sub_type,
  52. RESOURCE_SUB_TYPE,
  53. BOOSTER_NAME,
  54. RIVAL_RESOURCE_SUB_TYPE,
  55. RUMBLE_SIZE,
  56. CASE WHEN PVP_MODE NOT LIKE 'PvP' THEN 'No Rival(Solo/Rumble)' ELSE RIVAL_BOOSTER_NAME END RIVAL_BOOSTER_NAME,
  57. rounds_cnt,
  58. is_private,
  59. is_mc_match,
  60. is_rival_bot,
  61. turn_index,
  62. app_version,
  63. CASE when LO_EVENT_ID like '%-mc-%' then 'MC'
  64. when LO_EVENT_ID like '%cosmic%' then 'cosmic'
  65. when LO_EVENT_ID like '%rally%' then 'rally'
  66. when LO_EVENT_ID like '%heist%' then 'heist'
  67. end Solo_Type,
  68. SUM(ROUNDS_CNT) AS ROUNDS,
  69. SUM(MATCH_SCORE) AS MATCH_SCORE,
  70. SUM(MATCH_DURATION_SEC) / 60 AS MATCH_DURATION_MINS,
  71. SUM(ABILITY_ACTIVATED_CNT) AS BOOSTER_ACTIVATIONS,
  72. SUM(ifnull(CS_RESYNCS, 0)) AS RESYNCS,
  73. COUNT(
  74. DISTINCT CASE WHEN CS_RESYNCS > 0 THEN concat(match_id, user_id, rematch_cnt) END
  75. ) AS RESYNC_MATCHES,
  76. SUM(ifnull(ERROR_RESYNCS, 0)) AS ERROR_RESYNCS,
  77. COUNT(
  78. DISTINCT CASE WHEN ERROR_RESYNCS > 0 THEN concat(match_id, user_id, rematch_cnt) END
  79. ) AS ERROR_RESYNC_MATCHES,
  80. SUM(
  81. CASE WHEN is_conceded = 1
  82. AND is_won = 0
  83. AND match_duration_sec = 0 THEN 1 END
  84. ) AS CONCEDED_MATCHES,
  85. SUM(
  86. CASE WHEN is_conceded = 1
  87. AND is_won = 0
  88. AND match_duration_sec BETWEEN 1
  89. AND 10 THEN 1 END
  90. ) AS CONCEDED_MATCHES_1_10,
  91. SUM(
  92. CASE WHEN is_conceded = 1
  93. AND is_won = 0
  94. AND match_duration_sec BETWEEN 10
  95. AND 60 THEN 1 END
  96. ) AS CONCEDED_MATCHES_10_60,
  97. SUM(
  98. CASE WHEN is_conceded = 1
  99. AND is_won = 0
  100. AND match_duration_sec > 60 THEN 1 END
  101. ) AS CONCEDED_MATCHES_AFTER_60sec,
  102. SUM(ifnull(rival_played_out_of_time, 0)) AS OOT,
  103. COUNT(
  104. DISTINCT CASE WHEN rival_played_out_of_time > 0 THEN concat(match_id, user_id, rematch_cnt) END
  105. ) AS OOT_MATCHES,
  106. COUNT(DISTINCT concat(match_id, user_id, rematch_cnt)) AS MATCHES,
  107. SUM(is_won) AS wins,
  108. AVG(MATCH_RANK) AS AVG_MATCH_RANK,
  109. sum(is_rival_bot) AS agianst_bot_MATCHES,
  110. sum(MATCH_DURATION_SEC) as total_seconds_played_in_segment,
  111. sum(ability_activated_cnt) as total_booster_activations_in_segment,
  112. sum(four_matched_cnt) as total_fours,
  113. sum(five_matched_cnt) as total_fives,
  114. sum(match_making_found_time) as match_making_found_time
  115. FROM
  116. CANDIVORE.PROD.F_USER_MATCH a left join (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) b on a.calendar_entry_id=b.lo_entry_id
  117. WHERE
  118. is_bot = FALSE
  119. AND MATCH_END_DATE >= DATEADD(DAY, -91, GETDATE())
  120. GROUP BY
  121. 1,
  122. 2,
  123. 3,
  124. 4,
  125. 5,
  126. 6,
  127. 7,
  128. 8,
  129. 9,
  130. 10,
  131. 11,
  132. 12,
  133. 13,
  134. 14,
  135. 15,
  136. 16,
  137. 17,
  138. 18,
  139. 19,
  140. 20,
  141. 21,
  142. 22,
  143. 23,
  144. 24,
  145. 25,
  146. 26,
  147. 27,
  148. 28,
  149. 29,
  150. 30
Add Comment
Please, Sign In to add comment
Public Pastes
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!