API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Jul 9th, 2023
159
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.13 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.FRIENDS_FROM_LINK_CNT
  21. , daily_summary.TOTAL_COMPLETED_ALBUMS
  22. , daily_summary.IS_SUBSCRIBER
  23. , daily_summary.SUBSCRIPTION_TIER
  24. , daily_summary.FIRST_PURCHASE_TS
  25. , daily_summary.PURCHASES_CNT
  26. , daily_summary.NEW_USER
  27. , daily_summary.New_organic_User
  28. , daily_summary.New_organic_User_2
  29. , daily_summary.daily_ad_value
  30. , daily_summary.lt_ad_value
  31. , daily_summary.iap_7d
  32. , daily_summary.iap_30d
  33. , daily_summary.is_ftd
  34. , daily_summary.is_otd
  35. , daily_summary.trophy_group
  36. , daily_summary.TOTAL_MATCH_DURATION_MIN
  37. , daily_summary.fb_user_id
  38. , daily_summary.google_user_id
  39. , daily_summary.apple_user_id
  40. , daily_summary.New_invited_user
  41. , daily_summary.trophy_group_2
  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. , USER_MATCH.SOLO_MATCHES
  49. , USER_MATCH.SOLO_WINS
  50. , USER_MATCH.USER_DAILY_WINRATE_SOLO
  51. , USER_MATCH.SOLO_LEGENDARY_MATCHES
  52. , USER_MATCH.SOLO_LEGENDARY_WINS
  53. , USER_MATCH.USER_DAILY_WINRATE_SOLO_LEGENDARY
  54. , USER_MATCH.RUMBLE_MATCHES
  55. , USER_MATCH.RUMBLE_WINS
  56. , USER_MATCH.USER_DAILY_WINRATE_RUMBLE
  57. , USER_MATCH.RUMBLE_LEGENDARY_MATCHES
  58. , USER_MATCH.RUMBLE_LEGENDARY_WINS
  59. , USER_MATCH.USER_DAILY_WINRATE_RUMBLE_LEGENDARY
  60.  
  61.  
  62. FROM
  63. (SELECT
  64. user_id
  65. ,interval_date AS DATE
  66. ,LOGIN_ARENA
  67. ,CASE
  68. WHEN trophies_cnt<3800 THEN 'Studios'
  69. WHEN trophies_cnt<30000 THEN 'Master_League'
  70. WHEN trophies_cnt>=30000 THEN 'Legends_League'
  71. END AS league
  72. , CASE
  73. WHEN trophies_cnt < 125 THEN '0-125'
  74. WHEN trophies_cnt < 1400 THEN '125-1400'
  75. WHEN trophies_cnt < 2000 THEN '1400-2000'
  76. WHEN trophies_cnt < 3800 THEN '2000-3800'
  77. WHEN trophies_cnt < 7000 THEN '3800-7000'
  78. WHEN trophies_cnt < 16000 THEN '7000-16000'
  79. WHEN trophies_cnt < 30000 THEN '16000-30000'
  80. WHEN trophies_cnt >= 30000 THEN '30000+'
  81. END AS trophy_group
  82. , CASE
  83. WHEN trophies_cnt < 1000 THEN '0-999'
  84. WHEN trophies_cnt < 2000 THEN '1000-1999'
  85. WHEN trophies_cnt < 3800 THEN '2000-3800'
  86. WHEN trophies_cnt < 7000 THEN '3800-7000'
  87. WHEN trophies_cnt < 16000 THEN '7000-16000'
  88. WHEN trophies_cnt < 30000 THEN '16000-30000'
  89. WHEN trophies_cnt >= 30000 THEN '30000+'
  90. END AS trophy_group_2
  91. ,CASE
  92. WHEN (login_arena <= 22 AND finish_arena >= 23) THEN 'moved_to_LL'
  93. WHEN (login_arena <= 13 AND finish_arena >= 14) THEN 'moved_to_ML'
  94. ELSE 'no change'
  95. END AS LEAGUE_CHANGE
  96. ,country
  97. ,ltv_group
  98. ,seniority_bin
  99. ,engagement_group
  100. ,team_name
  101. ,team_id
  102. ,daily_match_cnt match_cnt
  103. ,daily_sessions_cnt sessions_cnt
  104. ,daily_match_sessions_cnt match_sessions_cnt
  105. ,DAILY_IAP
  106. ,platform
  107. ,on_fire_games
  108. ,friends_from_link_cnt
  109. ,total_completed_albums
  110. ,is_subscriber
  111. ,subscription_tier
  112. ,first_purchase_ts
  113. ,purchases_cnt
  114. ,iap_7d
  115. ,iap_30d
  116. ,lt_purchases_amt
  117. ,is_ftd
  118. ,is_otd
  119. ,daily_ad_value
  120. ,lt_ad_value
  121. ,TOTAL_MATCH_DURATION_MIN
  122. , fb_user_id
  123. , google_user_id
  124. , apple_user_id
  125. ,CASE WHEN is_new_user = 1 AND is_restored = 0 THEN 1 ELSE 0 END AS New_user
  126. ,CASE WHEN is_new_user 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
  127. ,CASE WHEN is_new_user and (media_source='organic' OR media_source='branch_int' OR media_source='challenge_link' OR media_source='friend_link' OR media_source='sticker_sharing') and is_restored = 0 then 1 else 0 end as New_organic_User_2
  128. ,CASE WHEN is_new_user = 1 AND invited_by_uuid is not null THEN 1 ELSE 0 END AS New_invited_user
  129. FROM CANDIVORE.PROD.DAILY_USERS_FROM_PARAMS WHERE is_active = 1
  130. -- and interval_date= 2022-08-30
  131. ) AS daily_summary
  132.  
  133. LEFT JOIN //F_USER_RESOURCE_TRANSACTION
  134.  
  135. (SELECT user_id, TRANSACTION_DATE
  136. ,SUM(CASE WHEN transaction_source = 'stickers_trading' THEN 1 ELSE 0 END) AS stickers_traded
  137. ,SUM(CASE WHEN transaction_source = 'team_request' THEN 1 ELSE 0 END) AS team_requests
  138. FROM CANDIVORE.PROD.F_USER_RESOURCE_TRANSACTION
  139. GROUP BY user_id, TRANSACTION_DATE) AS RESOURCE_TRANSACTION
  140.  
  141. ON daily_summary.user_id = RESOURCE_TRANSACTION.user_id AND daily_summary.DATE = RESOURCE_TRANSACTION.TRANSACTION_DATE
  142.  
  143. LEFT JOIN //F_IN_APP_PURCHASE
  144.  
  145. (SELECT
  146. user_id
  147. ,DATE(derived_tstamp) AS DATE
  148. ,mode(CASE
  149. WHEN package LIKE 'subscription_20%' THEN 'subscription_20'
  150. WHEN package LIKE 'subscription_10%' THEN 'subscription_10'
  151. WHEN package LIKE 'subscription_5%' THEN 'subscription_5'
  152. ELSE NULL
  153. END) AS subscription_package
  154. ,mode(SUBSCRIPTION_ACTION) AS SUBSCRIPTION_ACTION
  155. FROM CANDIVORE.PROD.F_IN_APP_PURCHASE WHERE PACKAGE LIKE '%subscription%'
  156. GROUP BY USER_ID, DATE(derived_tstamp)) AS IN_APP_PURCHASE
  157.  
  158. ON daily_summary.user_id = IN_APP_PURCHASE.user_id AND daily_summary.DATE = IN_APP_PURCHASE.DATE
  159.  
  160. LEFT JOIN //F_NOTIFICATION_OPENED
  161.  
  162. (SELECT
  163. user_id,
  164. DATE(derived_tstamp) AS DATE,
  165. COUNT(user_id) AS open_notif,
  166. COUNT(DISTINCT user_id) AS players_open_notif
  167. FROM CANDIVORE.PROD.F_NOTIFICATION_OPENED
  168. GROUP BY user_id, DATE(derived_tstamp)) AS NOTIFICATION_OPENED
  169.  
  170. ON daily_summary.user_id = NOTIFICATION_OPENED.user_id AND daily_summary.DATE = NOTIFICATION_OPENED.DATE
  171.  
  172. LEFT JOIN //F_USER_MATCH
  173.  
  174. (SELECT
  175. user_id,
  176. match_end_date AS DATE,
  177. COUNT(CASE WHEN PVP_MODE = 'Solo' THEN 1 END) AS solo_matches,
  178. COUNT(CASE WHEN PVP_MODE = 'Solo' AND is_won = 1 THEN 1 END) AS solo_wins,
  179. CASE WHEN solo_matches > 0 THEN solo_wins/solo_matches ELSE NULL END AS user_daily_winrate_solo,
  180. COUNT(CASE WHEN booster_tier = 'Legendary & Special Editions' AND PVP_MODE = 'Solo' THEN 1 ELSE NULL END) AS solo_legendary_matches,
  181. 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,
  182. CASE WHEN solo_legendary_matches > 0 THEN solo_legendary_wins/solo_legendary_matches ELSE NULL END AS user_daily_winrate_solo_legendary,
  183.  
  184. COUNT(CASE WHEN PVP_MODE = 'Rumble' THEN 1 END) AS rumble_matches,
  185. COUNT(CASE WHEN PVP_MODE = 'Rumble' AND is_won = 1 THEN 1 END) AS rumble_wins,
  186. CASE WHEN rumble_matches > 0 THEN rumble_wins/rumble_matches ELSE NULL END AS user_daily_winrate_rumble,
  187. COUNT(CASE WHEN booster_tier = 'Legendary & Special Editions' AND PVP_MODE = 'Rumble' THEN 1 ELSE NULL END) AS rumble_legendary_matches,
  188. 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,
  189. CASE WHEN rumble_legendary_matches > 0 THEN rumble_legendary_wins/rumble_legendary_matches ELSE NULL END AS user_daily_winrate_rumble_legendary
  190.  
  191. FROM CANDIVORE.PROD.F_USER_MATCH
  192. GROUP BY user_id, match_end_date) AS USER_MATCH
  193.  
  194. ON daily_summary.user_id = USER_MATCH.user_id AND daily_summary.DATE = USER_MATCH.DATE
  195. )
  196.  
  197. --------------------------
  198. --- end with statement ---
  199. --------------------------
  200.  
  201. SELECT
  202. A.interval_date
  203. ,A.country
  204. ,A.ltv_group
  205. ,A.LOGIN_ARENA
  206. ,A.league
  207. ,A.trophy_group
  208. ,A.trophy_group_2
  209. ,A.seniority_bin
  210. ,A.engagement_group
  211. ,A.platform
  212. ,A.ACTIVE_USERS
  213. ,A.active_paying_users
  214. ,A.NEW_USERS
  215. ,A.New_invited_users
  216. ,A.New_organic_Users
  217. ,A.New_organic_Users_2
  218. ,A.daily_matches
  219. ,A.daily_onfire_matches
  220. ,A.daily_sessions
  221. ,A.daily_active_sessions
  222. ,A.DAILY_REVENUE
  223. ,A.users_payed_today
  224. ,A.daily_purchases
  225. ,A.FTDs
  226. ,A.OTDs
  227. ,A.Users_moved_to_ML
  228. ,A.FRIENDS_FROM_LINK_CNT
  229. ,A.Users_moved_to_LL
  230. ,A.Active_subscribers
  231. ,A.solo_matches
  232. ,A.solo_wins
  233. ,A.average_user_daily_winrate_solo
  234. ,A.median_user_daily_winrate_solo
  235. ,A.solo_legendary_matches
  236. ,A.solo_legendary_wins
  237. ,A.average_user_daily_winrate_solo_legendary
  238. ,A.median_user_daily_winrate_solo_legendary
  239. ,A.rumble_matches
  240. ,A.rumble_wins
  241. ,A.average_user_daily_winrate_rumble
  242. ,A.median_user_daily_winrate_rumble
  243. ,A.rumble_legendary_matches
  244. ,A.rumble_legendary_wins
  245. ,A.average_user_daily_winrate_rumble_legendary
  246. ,A.median_user_daily_winrate_rumble_legendary
  247. ,A.total_daily_team_request
  248. ,A.total_daily_stickers_traded
  249. ,A.active_users_in_teams
  250. ,A.open_notif
  251. ,A.players_open_notif
  252. ,A.daily_ad_value
  253. ,A.lt_ad_value
  254. ,A.is_iap_7d
  255. ,A.is_iap_30d
  256. ,A.TOTAL_MATCH_DURATION_MIN
  257. ,A.fb_connected_users
  258. ,A.google_connected_users
  259. ,A.apple_connected_users
  260. ,A.unconnected_users
  261. ,B.fake_users
  262. ,B.restored_users
  263. ,B.fake_restored_users
  264. FROM (
  265. (SELECT
  266. DATE AS interval_date
  267. ,country
  268. ,ltv_group
  269. ,LOGIN_ARENA
  270. ,league
  271. ,trophy_group
  272. ,trophy_group_2
  273. ,seniority_bin
  274. ,engagement_group
  275. ,platform
  276. ,COUNT(DISTINCT user_id) AS ACTIVE_USERS
  277. ,COUNT(DISTINCT(CASE WHEN LT_PURCHASES_AMT>0 THEN user_id END)) AS active_paying_users
  278. ,sum(New_invited_user) New_invited_users
  279. ,sum(New_organic_User) AS New_organic_Users
  280. ,sum(New_organic_User_2) AS New_organic_Users_2
  281. ,SUM(new_user) AS NEW_USERS
  282. ,SUM(match_cnt) AS daily_matches
  283. ,SUM(on_fire_games) AS daily_onfire_matches
  284. ,SUM(sessions_cnt) AS daily_sessions
  285. ,SUM(match_sessions_cnt) AS daily_active_sessions
  286. ,SUM(DAILY_IAP) AS DAILY_REVENUE
  287. ,COUNT(DISTINCT(CASE WHEN DAILY_IAP IS NOT NULL THEN user_id END)) AS users_payed_today
  288. ,SUM(purchases_cnt) AS daily_purchases
  289. ,SUM(CASE WHEN is_ftd = TRUE THEN 1 END) AS FTDs
  290. ,SUM(CASE WHEN is_otd = TRUE THEN 1 END) AS OTDs
  291. ,SUM(CASE WHEN LEAGUE_CHANGE = 'moved_to_ML' THEN 1 ELSE 0 END) AS Users_moved_to_ML
  292. ,SUM(CASE WHEN LEAGUE_CHANGE = 'moved_to_LL' THEN 1 ELSE 0 END) AS Users_moved_to_LL
  293. ,SUM(CASE WHEN is_subscriber = TRUE THEN 1 ELSE 0 END) AS Active_subscribers
  294. ,SUM(solo_matches) AS solo_matches
  295. ,SUM(solo_wins) AS solo_wins
  296. ,avg(user_daily_winrate_solo) AS average_user_daily_winrate_solo
  297. ,median(user_daily_winrate_solo) AS median_user_daily_winrate_solo
  298. ,SUM(solo_legendary_matches) AS solo_legendary_matches
  299. ,SUM(solo_legendary_wins) AS solo_legendary_wins
  300. ,avg(user_daily_winrate_solo_legendary) AS average_user_daily_winrate_solo_legendary
  301. ,median(user_daily_winrate_solo_legendary) AS median_user_daily_winrate_solo_legendary
  302. ,SUM(rumble_matches) AS rumble_matches
  303. ,SUM(rumble_wins) AS rumble_wins
  304. ,avg(user_daily_winrate_rumble) AS average_user_daily_winrate_rumble
  305. ,median(user_daily_winrate_rumble) AS median_user_daily_winrate_rumble
  306. ,SUM(rumble_legendary_matches) AS rumble_legendary_matches
  307. ,SUM(rumble_legendary_wins) AS rumble_legendary_wins
  308. ,avg(user_daily_winrate_rumble_legendary) AS average_user_daily_winrate_rumble_legendary
  309. ,median(user_daily_winrate_rumble_legendary) AS median_user_daily_winrate_rumble_legendary
  310. ,SUM(team_requests) AS total_daily_team_request
  311. ,SUM(stickers_traded) AS total_daily_stickers_traded
  312. ,COUNT(team_name) AS active_users_in_teams
  313. ,SUM(open_notif) AS open_notif
  314. ,SUM(players_open_notif) AS players_open_notif
  315. ,SUM(daily_ad_value) AS daily_ad_value
  316. ,SUM(lt_ad_value) AS lt_ad_value
  317. ,SUM(TOTAL_MATCH_DURATION_MIN) TOTAL_MATCH_DURATION_MIN
  318. ,SUM(CASE WHEN iap_7d > 0 THEN 1 ELSE 0 END) AS is_iap_7d
  319. ,SUM(CASE WHEN iap_30d > 0 THEN 1 ELSE 0 END) AS is_iap_30d
  320. ,SUM(CASE WHEN fb_user_id IS NOT NULL THEN 1 ELSE 0 END) fb_connected_users
  321. ,SUM(CASE WHEN google_user_id IS NOT NULL THEN 1 ELSE 0 END) google_connected_users
  322. ,SUM(CASE WHEN apple_user_id IS NOT NULL THEN 1 ELSE 0 END) apple_connected_users
  323. ,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
  324. FROM staging
  325. GROUP BY interval_date, country, league,trophy_group, LOGIN_ARENA, ltv_group, seniority_bin, engagement_group, platform, trophy_group_2
  326. ) A
  327. LEFT JOIN
  328. (SELECT
  329. interval_date
  330. ,country
  331. ,ltv_group
  332. ,LOGIN_ARENA
  333. ,CASE
  334. WHEN trophies_cnt<3800 THEN 'Studios'
  335. WHEN trophies_cnt<30000 THEN 'Master_League'
  336. WHEN trophies_cnt>=30000 THEN 'Legends_League'
  337. END AS league
  338. , CASE
  339. WHEN trophies_cnt < 125 THEN '0-125'
  340. WHEN trophies_cnt < 1400 THEN '125-1400'
  341. WHEN trophies_cnt < 2000 THEN '1400-2000'
  342. WHEN trophies_cnt < 3800 THEN '2000-3800'
  343. WHEN trophies_cnt < 7000 THEN '3800-7000'
  344. WHEN trophies_cnt < 16000 THEN '7000-16000'
  345. WHEN trophies_cnt < 30000 THEN '16000-30000'
  346. WHEN trophies_cnt >= 30000 THEN '30000+'
  347. END AS trophy_group
  348. ,seniority_bin
  349. ,engagement_group
  350. ,platform
  351. ,SUM(is_fake) AS fake_users
  352. ,SUM(is_restored) AS restored_users
  353. ,SUM(CASE WHEN is_restored = 1 AND is_fake = 1 THEN 1 END) AS fake_restored_users
  354. FROM CANDIVORE.PROD.USER_DAILY_SUMMARY
  355. GROUP BY interval_date, country, league,trophy_group, LOGIN_ARENA, ltv_group, seniority_bin, engagement_group, platform
  356. ) B
  357. ON A.interval_date = B.interval_date AND A.country = B.country AND A.ltv_group = B.ltv_group AND A.LOGIN_ARENA = B.LOGIN_ARENA AND A.league = B.league AND A.trophy_group = B.trophy_group AND A.seniority_bin = B.seniority_bin AND A.engagement_group = B.engagement_group AND A.platform = B.platform
  358. )
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!