API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Sep 8th, 2024
48
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 17.04 KB | None | 0 0
raw download clone embed print report
  1. WITH staging AS (SELECT DISTINCT
  2. daily_summary.USER_ID
  3. , daily_summary.DATE
  4. , daily_summary.LOGIN_ARENA
  5. , daily_summary.LEAGUE
  6. , daily_summary.seniority_bin
  7. , daily_summary.engagement_group
  8. , daily_summary.LEAGUE_CHANGE
  9. , daily_summary.COUNTRY
  10. , daily_summary.platform
  11. , daily_summary.LTV_GROUP
  12. , daily_summary.TEAM_NAME
  13. , daily_summary.TEAM_ID
  14. , daily_summary.MATCH_CNT
  15. , daily_summary.SESSIONS_CNT
  16. , daily_summary.MATCH_SESSIONS_CNT
  17. , daily_summary.DAILY_IAP
  18. , daily_summary.LT_PURCHASES_AMT
  19. , daily_summary.ON_FIRE_GAMES
  20. , daily_summary.TOTAL_COMPLETED_ALBUMS
  21. , daily_summary.IS_SUBSCRIBER
  22. , daily_summary.SUBSCRIPTION_TIER
  23. , daily_summary.FIRST_PURCHASE_TS
  24. , daily_summary.PURCHASES_CNT
  25. , daily_summary.NEW_USER
  26. , daily_summary.New_organic_User
  27. , daily_summary.daily_ad_value
  28. , daily_summary.lt_ad_value
  29. , daily_summary.sum7_iap
  30. , daily_summary.sum30_iap
  31. , daily_summary.is_ftd
  32. , daily_summary.is_otd
  33. , daily_summary.trophy_group
  34. , daily_summary.premium_pass_active
  35. , daily_summary.TOTAL_MATCH_DURATION_MIN
  36. , daily_summary.fb_user_id
  37. , daily_summary.google_user_id
  38. , daily_summary.apple_user_id
  39. , daily_summary.New_invited_user
  40. , daily_summary.trophy_group_2
  41. , daily_summary.trophy_group_3
  42. , RESOURCE_TRANSACTION.STICKERS_TRADED
  43. , RESOURCE_TRANSACTION.TEAM_REQUESTS
  44. , IN_APP_PURCHASE.SUBSCRIPTION_PACKAGE
  45. , IN_APP_PURCHASE.SUBSCRIPTION_ACTION
  46. , NOTIFICATION_OPENED.OPEN_NOTIF
  47. , NOTIFICATION_OPENED.PLAYERS_OPEN_NOTIF
  48. , nvl(USER_MATCH.SOLO_MATCHES, 0) as SOLO_MATCHES
  49. , nvl(USER_MATCH.SOLO_WINS, 0) as SOLO_WINS
  50. , nvl(USER_MATCH.USER_DAILY_WINRATE_SOLO, 0) as USER_DAILY_WINRATE_SOLO
  51. , nvl(USER_MATCH.SOLO_LEGENDARY_MATCHES, 0) as SOLO_LEGENDARY_MATCHES
  52. , nvl(USER_MATCH.SOLO_LEGENDARY_WINS, 0) as SOLO_LEGENDARY_WINS
  53. , nvl(USER_MATCH.USER_DAILY_WINRATE_SOLO_LEGENDARY, 0) as USER_DAILY_WINRATE_SOLO_LEGENDARY
  54. , nvl(USER_MATCH.RUMBLE_MATCHES, 0) as RUMBLE_MATCHES
  55. , nvl(USER_MATCH.RUMBLE_WINS, 0) as RUMBLE_WINS
  56. , nvl(USER_MATCH.USER_DAILY_WINRATE_RUMBLE, 0) as USER_DAILY_WINRATE_RUMBLE
  57. , nvl(USER_MATCH.RUMBLE_LEGENDARY_MATCHES, 0) as RUMBLE_LEGENDARY_MATCHES
  58. , nvl(USER_MATCH.RUMBLE_LEGENDARY_WINS, 0) as RUMBLE_LEGENDARY_WINS
  59. , nvl(USER_MATCH.USER_DAILY_WINRATE_RUMBLE_LEGENDARY, 0) as USER_DAILY_WINRATE_RUMBLE_LEGENDARY
  60. , nvl(USER_MATCH.pvp_matches , 0 ) as pvp_matches
  61. , nvl(USER_MATCH.showdown_matches , 0 ) as showdown_matches
  62. , nvl(USER_MATCH.tournament_matches , 0 ) as tournament_matches
  63. , nvl(USER_MATCH.PVP_WINS , 0 ) as PVP_WINS
  64. , nvl(USER_MATCH.PVP_LEGENDARY_MATCHES , 0 ) as PVP_LEGENDARY_MATCHES
  65. , nvl(USER_MATCH.PVP_LEGENDARY_WINS , 0 ) as PVP_LEGENDARY_WINS
  66.  
  67. FROM
  68. (SELECT
  69. user_id
  70. ,interval_date AS DATE
  71. ,LOGIN_ARENA
  72. ,CASE
  73. WHEN trophies_cnt<3800 THEN 'Studios'
  74. WHEN trophies_cnt<30000 THEN 'Master_League'
  75. WHEN trophies_cnt>=30000 THEN 'Legends_League'
  76. END AS league
  77. , CASE
  78. WHEN trophies_cnt < 1500 THEN '0-1500'
  79. WHEN trophies_cnt < 3800 THEN '1500-3800'
  80. WHEN trophies_cnt < 8000 THEN '3800-8000'
  81. WHEN trophies_cnt < 17000 THEN '8000-17000'
  82. WHEN trophies_cnt < 30000 THEN '17000-30000'
  83. ELSE '30000+'
  84. END AS trophy_group
  85. , CASE
  86. WHEN trophies_cnt < 1000 THEN '0-999'
  87. WHEN trophies_cnt < 2000 THEN '1000-1999'
  88. WHEN trophies_cnt < 3800 THEN '2000-3800'
  89. WHEN trophies_cnt < 7000 THEN '3800-7000'
  90. WHEN trophies_cnt < 16000 THEN '7000-16000'
  91. WHEN trophies_cnt < 30000 THEN '16000-30000'
  92. WHEN trophies_cnt >= 30000 THEN '30000+'
  93. END AS trophy_group_3
  94. , CASE
  95. WHEN trophies_cnt < 125 THEN '0-125'
  96. WHEN trophies_cnt < 1400 THEN '125-1400'
  97. WHEN trophies_cnt < 2000 THEN '1400-2000'
  98. WHEN trophies_cnt < 3800 THEN '2000-3800'
  99. WHEN trophies_cnt < 4500 THEN '3800-4500'
  100. WHEN trophies_cnt < 5500 THEN '4500-5500'
  101. WHEN trophies_cnt < 7000 THEN '5500-7000'
  102. WHEN trophies_cnt < 10000 THEN '7000-10000'
  103. WHEN trophies_cnt < 13000 THEN '10000-13000'
  104. WHEN trophies_cnt < 16000 THEN '13000-16000'
  105. WHEN trophies_cnt < 30000 THEN '16000-30000'
  106. WHEN trophies_cnt >= 30000 THEN '30000+'
  107. END AS trophy_group_2
  108. ,CASE
  109. WHEN (login_arena <= 22 AND finish_arena >= 23) THEN 'moved_to_LL'
  110. WHEN (login_arena <= 13 AND finish_arena >= 14) THEN 'moved_to_ML'
  111. ELSE 'no CHANGE'
  112. END AS LEAGUE_CHANGE
  113. ,country
  114. ,ltv_group
  115. ,seniority_bin
  116. ,engagement_group
  117. ,team_name
  118. ,team_id
  119. ,daily_match_cnt match_cnt
  120. ,daily_sessions_cnt sessions_cnt
  121. ,daily_match_sessions_cnt match_sessions_cnt
  122. ,DAILY_IAP
  123. ,platform
  124. ,on_fire_games
  125. ,friends_from_link_cnt
  126. ,total_completed_albums
  127. ,is_subscriber
  128. ,subscription_tier
  129. ,first_purchase_ts
  130. ,purchases_cnt
  131. ,sum7_iap
  132. ,sum30_iap
  133. ,lt_purchases_amt
  134. ,is_ftd
  135. ,is_otd
  136. ,daily_ad_value
  137. ,lt_ad_value
  138. ,TOTAL_MATCH_DURATION_MIN
  139. , fb_user_id
  140. , google_user_id
  141. , apple_user_id
  142. , premium_pass_active
  143. ,CASE WHEN is_new_user = 1 AND is_restored = 0 and user_id not in (select distinct user_id from MATCH_MASTERS.prod.f_client_user_login where action = 'reconnected' and date(derived_tstamp) >= DATEADD(day, -3, $T_OVERVIEW_DASHBOARD_UPDATED_DT_MNG)) THEN 1 ELSE 0 END AS New_user
  144. ,CASE WHEN is_new_user = 1 and is_restored = 0 and user_id not in (select distinct user_id from MATCH_MASTERS.prod.f_client_user_login where action = 'reconnected' and date(derived_tstamp) >= DATEADD(day, -3, $T_OVERVIEW_DASHBOARD_UPDATED_DT_MNG)) and (media_source='organic' OR media_source='branch_int' OR media_source='challenge_link' OR media_source='friend_link' OR media_source='sticker_sharing') then 1 else 0 end as New_organic_User
  145. ,CASE WHEN is_restored=0 and is_new_user=1 AND (invited_by_uuid <> NULL or media_source='friend_link') THEN 1 ELSE 0 END AS New_invited_user
  146. FROM MATCH_MASTERS.PROD.DAILY_USERS_FROM_PARAMS WHERE is_active = 1 and interval_date >= current_date - 121
  147. ) AS daily_summary
  148.  
  149. LEFT JOIN //F_USER_RESOURCE_TRANSACTION
  150.  
  151. (SELECT user_id, TRANSACTION_DATE
  152. ,SUM(CASE WHEN transaction_source = 'stickers_trading' THEN 1 ELSE 0 END) AS stickers_traded
  153. ,SUM(CASE WHEN transaction_source = 'team_request' THEN 1 ELSE 0 END) AS team_requests
  154. FROM MATCH_MASTERS.PROD.F_USER_RESOURCE_TRANSACTION where date(derived_tstamp) >= current_date - 121
  155. GROUP BY user_id, TRANSACTION_DATE) AS RESOURCE_TRANSACTION
  156.  
  157. ON daily_summary.user_id = RESOURCE_TRANSACTION.user_id AND daily_summary.DATE = RESOURCE_TRANSACTION.TRANSACTION_DATE
  158.  
  159. LEFT JOIN //F_IN_APP_PURCHASE
  160.  
  161. (SELECT
  162. user_id
  163. ,DATE(derived_tstamp) AS DATE
  164. ,mode(CASE
  165. WHEN package LIKE 'subscription_20%' THEN 'subscription_20'
  166. WHEN package LIKE 'subscription_10%' THEN 'subscription_10'
  167. WHEN package LIKE 'subscription_5%' THEN 'subscription_5'
  168. ELSE NULL
  169. END) AS subscription_package
  170. ,mode(SUBSCRIPTION_ACTION) AS SUBSCRIPTION_ACTION
  171. FROM MATCH_MASTERS.PROD.F_IN_APP_PURCHASE WHERE PACKAGE LIKE '%subscription%'
  172. GROUP BY USER_ID, DATE(derived_tstamp)) AS IN_APP_PURCHASE
  173.  
  174. ON daily_summary.user_id = IN_APP_PURCHASE.user_id AND daily_summary.DATE = IN_APP_PURCHASE.DATE
  175.  
  176. LEFT JOIN //F_NOTIFICATION_OPENED
  177.  
  178. (SELECT
  179. user_id,
  180. DATE(derived_tstamp) AS DATE,
  181. COUNT(user_id) AS open_notif,
  182. COUNT(DISTINCT user_id) AS players_open_notif
  183. FROM MATCH_MASTERS.PROD.F_NOTIFICATION_OPENED where date(derived_tstamp) >= current_date - 121
  184. GROUP BY user_id, DATE(derived_tstamp)) AS NOTIFICATION_OPENED
  185.  
  186. ON daily_summary.user_id = NOTIFICATION_OPENED.user_id AND daily_summary.DATE = NOTIFICATION_OPENED.DATE
  187.  
  188. LEFT JOIN //F_USER_MATCH
  189.  
  190. (SELECT
  191. user_id,
  192. match_end_date AS DATE,
  193. COUNT(CASE WHEN pvp_mode = 'Solo' THEN 1 END) AS solo_matches,
  194. COUNT(CASE WHEN pvp_mode = 'Solo' AND is_won = 1 THEN 1 END) AS solo_wins,
  195. CASE WHEN solo_matches > 0 THEN solo_wins/solo_matches ELSE NULL END AS user_daily_winrate_solo,
  196. COUNT(CASE WHEN booster_tier = 'Legendary & Special Editions' AND pvp_mode = 'Solo' THEN 1 ELSE NULL END) AS solo_legendary_matches,
  197. COUNT(CASE WHEN booster_tier = 'Legendary & Special Editions' AND pvp_mode = 'Solo' AND is_won = 1 THEN 1 ELSE NULL END) AS solo_legendary_wins,
  198. CASE WHEN solo_legendary_matches > 0 THEN solo_legendary_wins/solo_legendary_matches ELSE NULL END AS user_daily_winrate_solo_legendary,
  199.  
  200.  
  201. COUNT(CASE WHEN pvp_mode = 'Rumble' THEN 1 END) AS rumble_matches,
  202. COUNT(CASE WHEN pvp_mode = 'Rumble' AND is_won = 1 THEN 1 END) AS rumble_wins,
  203. CASE WHEN rumble_matches > 0 THEN rumble_wins/rumble_matches ELSE NULL END AS user_daily_winrate_rumble,
  204. COUNT(CASE WHEN booster_tier = 'Legendary & Special Editions' AND pvp_mode = 'Rumble' THEN 1 ELSE NULL END) AS rumble_legendary_matches,
  205. COUNT(CASE WHEN booster_tier = 'Legendary & Special Editions' AND pvp_mode = 'Rumble' AND is_won = 1 THEN 1 ELSE NULL END) AS rumble_legendary_wins,
  206. CASE WHEN rumble_legendary_matches > 0 THEN rumble_legendary_wins/rumble_legendary_matches ELSE NULL END AS user_daily_winrate_rumble_legendary,
  207.  
  208. COUNT(CASE WHEN pvp_mode = 'PvP' THEN 1 else null END) AS pvp_matches,
  209. COUNT(CASE WHEN pvp_mode = 'Showdown' THEN 1 else null END) AS showdown_matches,
  210. COUNT(CASE WHEN pvp_mode = 'PvP' AND is_won = 1 THEN 1 END) AS pvp_wins,
  211. COUNT(case when match_type='Tournament' then 1 else null end ) AS tournament_matches,
  212. COUNT(CASE WHEN booster_tier = 'Legendary & Special Editions' AND pvp_mode = 'PvP' THEN 1 ELSE NULL END) AS pvp_legendary_matches,
  213. COUNT(CASE WHEN booster_tier = 'Legendary & Special Editions' AND pvp_mode = 'PvP' AND is_won = 1 THEN 1 ELSE NULL END) AS pvp_legendary_wins
  214.  
  215. FROM MATCH_MASTERS.PROD.F_USER_MATCH where match_end_date >= current_date - 121
  216. GROUP BY user_id, match_end_date) AS USER_MATCH
  217.  
  218. ON daily_summary.user_id = USER_MATCH.user_id AND daily_summary.DATE = USER_MATCH.DATE
  219. )
  220.  
  221. --------------------------
  222. --- end with statement ---
  223. --------------------------
  224.  
  225. SELECT
  226. A.interval_date
  227. ,A.country
  228. ,A.ltv_group
  229. ,A.LOGIN_ARENA
  230. ,A.league
  231. ,A.trophy_group
  232. ,A.trophy_group_2
  233. ,A.trophy_group_3
  234. ,A.seniority_bin
  235. ,A.engagement_group
  236. ,A.platform
  237. ,A.ACTIVE_USERS
  238. ,A.active_paying_users
  239. ,A.NEW_USERS
  240. ,A.New_organic_Users
  241. ,A.New_invited_users
  242. ,A.daily_matches
  243. ,A.daily_onfire_matches
  244. ,A.daily_sessions
  245. ,A.daily_active_sessions
  246. ,A.DAILY_REVENUE
  247. ,A.users_payed_today
  248. ,A.daily_purchases
  249. ,A.FTDs
  250. ,A.OTDs
  251. ,A.Users_moved_to_ML
  252. ,A.Users_moved_to_LL
  253. ,A.Active_subscribers
  254. ,A.solo_matches
  255. ,A.solo_wins
  256. ,A.average_user_daily_winrate_solo
  257. ,A.median_user_daily_winrate_solo
  258. ,A.solo_legendary_matches
  259. ,A.solo_legendary_wins
  260. ,A.average_user_daily_winrate_solo_legendary
  261. ,A.median_user_daily_winrate_solo_legendary
  262. ,A.rumble_matches
  263. ,A.rumble_wins
  264. ,A.average_user_daily_winrate_rumble
  265. ,A.median_user_daily_winrate_rumble
  266. ,A.rumble_legendary_matches
  267. ,A.rumble_legendary_wins
  268. ,A.average_user_daily_winrate_rumble_legendary
  269. ,A.median_user_daily_winrate_rumble_legendary
  270. ,A.total_daily_team_request
  271. ,A.total_daily_stickers_traded
  272. ,A.active_users_in_teams
  273. ,A.open_notif
  274. ,A.players_open_notif
  275. ,A.daily_ad_value
  276. ,A.lt_ad_value
  277. ,A.is_iap_7d
  278. ,A.is_iap_30d
  279. ,A.TOTAL_MATCH_DURATION_MIN
  280. ,A.fb_connected_users
  281. ,A.google_connected_users
  282. ,A.apple_connected_users
  283. ,A.unconnected_users
  284. ,A.PVP_DAU
  285. ,A.Showdown_DAU
  286. ,A.Rumble_DAU
  287. ,A.Solo_DAU
  288. ,A.Tournament_DAU
  289. ,B.fake_users
  290. ,B.restored_users
  291. ,B.fake_restored_users
  292. ,A.pvp_matches
  293. ,A.showdown_matches
  294. ,A.pvp_wins
  295. ,A.pvp_legendary_matches
  296. ,A.pvp_legendary_wins
  297. ,A.pp_users
  298. ,A.interval_date as LAST_UPDATED_DT
  299. ,current_timestamp() as DW_INSERT_DT
  300.  
  301. FROM (
  302. (SELECT
  303. DATE AS interval_date
  304. ,country
  305. ,ltv_group
  306. ,LOGIN_ARENA
  307. ,league
  308. ,trophy_group
  309. ,trophy_group_2
  310. ,trophy_group_3
  311. ,seniority_bin
  312. ,engagement_group
  313. ,platform
  314. ,count(distinct case when premium_pass_active=true then user_id end) pp_users
  315. ,COUNT(DISTINCT user_id) AS ACTIVE_USERS
  316. ,COUNT(DISTINCT(CASE WHEN LT_PURCHASES_AMT>0 THEN user_id END)) AS active_paying_users
  317. ,SUM(New_invited_user) New_invited_users
  318. ,sum(New_organic_User) AS New_organic_Users
  319. ,SUM(new_user) AS NEW_USERS
  320. ,SUM(match_cnt) AS daily_matches
  321. ,SUM(on_fire_games) AS daily_onfire_matches
  322. ,SUM(sessions_cnt) AS daily_sessions
  323. ,SUM(match_sessions_cnt) AS daily_active_sessions
  324. ,SUM(DAILY_IAP) AS DAILY_REVENUE
  325. ,COUNT(DISTINCT(CASE WHEN DAILY_IAP IS NOT NULL THEN user_id END)) AS users_payed_today
  326. ,SUM(purchases_cnt) AS daily_purchases
  327. ,SUM(CASE WHEN is_ftd = TRUE THEN 1 END) AS FTDs
  328. ,SUM(CASE WHEN is_otd = TRUE THEN 1 END) AS OTDs
  329. ,SUM(CASE WHEN LEAGUE_CHANGE = 'moved_to_ML' THEN 1 ELSE 0 END) AS Users_moved_to_ML
  330. ,SUM(CASE WHEN LEAGUE_CHANGE = 'moved_to_LL' THEN 1 ELSE 0 END) AS Users_moved_to_LL
  331. ,SUM(CASE WHEN is_subscriber = TRUE THEN 1 ELSE 0 END) AS Active_subscribers
  332. ,SUM(solo_matches) AS solo_matches
  333. ,SUM(solo_wins) AS solo_wins
  334. ,avg(user_daily_winrate_solo) AS average_user_daily_winrate_solo
  335. ,median(user_daily_winrate_solo) AS median_user_daily_winrate_solo
  336. ,SUM(solo_legendary_matches) AS solo_legendary_matches
  337. ,SUM(solo_legendary_wins) AS solo_legendary_wins
  338. ,avg(user_daily_winrate_solo_legendary) AS average_user_daily_winrate_solo_legendary
  339. ,median(user_daily_winrate_solo_legendary) AS median_user_daily_winrate_solo_legendary
  340. ,SUM(rumble_matches) AS rumble_matches
  341. ,SUM(rumble_wins) AS rumble_wins
  342. ,avg(user_daily_winrate_rumble) AS average_user_daily_winrate_rumble
  343. ,median(user_daily_winrate_rumble) AS median_user_daily_winrate_rumble
  344. ,SUM(rumble_legendary_matches) AS rumble_legendary_matches
  345. ,SUM(rumble_legendary_wins) AS rumble_legendary_wins
  346. ,avg(user_daily_winrate_rumble_legendary) AS average_user_daily_winrate_rumble_legendary
  347. ,median(user_daily_winrate_rumble_legendary) AS median_user_daily_winrate_rumble_legendary
  348. ,SUM(team_requests) AS total_daily_team_request
  349. ,SUM(stickers_traded) AS total_daily_stickers_traded
  350. ,COUNT(team_name) AS active_users_in_teams
  351. ,SUM(open_notif) AS open_notif
  352. ,SUM(players_open_notif) AS players_open_notif
  353. ,SUM(daily_ad_value) AS daily_ad_value
  354. ,SUM(lt_ad_value) AS lt_ad_value
  355. ,SUM(TOTAL_MATCH_DURATION_MIN) TOTAL_MATCH_DURATION_MIN
  356. ,SUM(CASE WHEN sum7_iap > 0 THEN 1 ELSE 0 END) AS is_iap_7d
  357. ,SUM(CASE WHEN sum30_iap > 0 THEN 1 ELSE 0 END) AS is_iap_30d
  358. ,SUM(CASE WHEN fb_user_id IS NOT NULL THEN 1 ELSE 0 END) fb_connected_users
  359. ,SUM(CASE WHEN google_user_id IS NOT NULL THEN 1 ELSE 0 END) google_connected_users
  360. ,SUM(CASE WHEN apple_user_id IS NOT NULL THEN 1 ELSE 0 END) apple_connected_users
  361. ,SUM(CASE WHEN apple_user_id IS NULL AND google_user_id IS NULL AND fb_user_id IS NULL THEN 1 ELSE 0 END) unconnected_users
  362. ,sum(case when pvp_matches>0 then 1 else 0 end)PVP_DAU
  363. ,sum(case when showdown_matches>0 then 1 else 0 end)Showdown_DAU
  364. ,sum(case when rumble_matches>0 then 1 else 0 end)Rumble_DAU
  365. ,sum(case when solo_matches>0 then 1 else 0 end)Solo_DAU
  366. ,sum(case when tournament_matches>0 then 1 else 0 end)Tournament_DAU
  367. ,sum(pvp_matches) as pvp_matches
  368. ,sum(showdown_matches) as showdown_matches
  369. ,sum(pvp_wins) as pvp_wins
  370. ,sum(pvp_legendary_matches) as pvp_legendary_matches
  371. ,sum(pvp_legendary_wins) as pvp_legendary_wins
  372.  
  373. FROM staging
  374. GROUP BY interval_date, country, league,trophy_group, LOGIN_ARENA, ltv_group, seniority_bin, engagement_group, platform, trophy_group_2, trophy_group_3
  375. ) A
  376. LEFT JOIN
  377. (SELECT
  378. interval_date
  379. ,country
  380. ,ltv_group
  381. ,LOGIN_ARENA
  382. ,CASE
  383. WHEN trophies_cnt<3800 THEN 'Studios'
  384. WHEN trophies_cnt<30000 THEN 'Master_League'
  385. WHEN trophies_cnt>=30000 THEN 'Legends_League'
  386. END AS league
  387. , CASE
  388. WHEN trophies_cnt < 1500 THEN '0-1500'
  389. WHEN trophies_cnt < 3800 THEN '1500-3800'
  390. WHEN trophies_cnt < 8000 THEN '3800-8000'
  391. WHEN trophies_cnt < 17000 THEN '8000-17000'
  392. WHEN trophies_cnt < 30000 THEN '17000-30000'
  393. ELSE '30000+'
  394. END AS trophy_group
  395. , CASE
  396. WHEN trophies_cnt < 125 THEN '0-125'
  397. WHEN trophies_cnt < 1400 THEN '125-1400'
  398. WHEN trophies_cnt < 2000 THEN '1400-2000'
  399. WHEN trophies_cnt < 3800 THEN '2000-3800'
  400. WHEN trophies_cnt < 4500 THEN '3800-4500'
  401. WHEN trophies_cnt < 5500 THEN '4500-5500'
  402. WHEN trophies_cnt < 7000 THEN '5500-7000'
  403. WHEN trophies_cnt < 10000 THEN '7000-10000'
  404. WHEN trophies_cnt < 13000 THEN '10000-13000'
  405. WHEN trophies_cnt < 16000 THEN '13000-16000'
  406. WHEN trophies_cnt < 30000 THEN '16000-30000'
  407. WHEN trophies_cnt >= 30000 THEN '30000+'
  408. END AS trophy_group_2
  409. , CASE
  410. WHEN trophies_cnt < 1000 THEN '0-999'
  411. WHEN trophies_cnt < 2000 THEN '1000-1999'
  412. WHEN trophies_cnt < 3800 THEN '2000-3800'
  413. WHEN trophies_cnt < 7000 THEN '3800-7000'
  414. WHEN trophies_cnt < 16000 THEN '7000-16000'
  415. WHEN trophies_cnt < 30000 THEN '16000-30000'
  416. WHEN trophies_cnt >= 30000 THEN '30000+'
  417. END AS trophy_group_3
  418. ,seniority_bin
  419. ,engagement_group
  420. ,platform
  421. ,SUM(is_fake) AS fake_users
  422. ,SUM(is_restored) AS restored_users
  423. ,SUM(CASE WHEN is_restored = 1 AND is_fake = 1 THEN 1 END) AS fake_restored_users
  424. FROM MATCH_MASTERS.PROD.daily_users_from_params
  425. GROUP BY interval_date, country, league,trophy_group, LOGIN_ARENA, ltv_group, seniority_bin, engagement_group, platform, trophy_group_2, trophy_group_3
  426. ) B
  427. ON A.interval_date = B.interval_date
  428. AND A.country = B.country
  429. AND A.ltv_group = B.ltv_group
  430. AND A.LOGIN_ARENA = B.LOGIN_ARENA
  431. AND A.league = B.league
  432. AND A.trophy_group = B.trophy_group
  433. AND A.seniority_bin = B.seniority_bin
  434. AND A.engagement_group = B.engagement_group
  435. AND A.platform = B.platform
  436. and A.trophy_group_2 = B.trophy_group_2
  437. and A.trophy_group_3 = B.trophy_group_3
  438. )
  439. WHERE 1=1
  440. AND A.interval_date >= current_date - 121
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!