API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
May 21st, 2023
192
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 13.13 KB | None | 0 0
raw download clone embed print report
  1. SELECT * FROM candivore.prod.daily_users_from_params LIMIT 10
  2. CREATE OR REPLACE TABLE CANDIVORE.SEMANTIC_LAYER.T_OVERVIEW_DASHBOARD AS
  3.  
  4. WITH staging AS (SELECT DISTINCT
  5. daily_summary.USER_ID
  6. , daily_summary.DATE
  7. , daily_summary.LOGIN_ARENA
  8. , daily_summary.LEAGUE
  9. , daily_summary.seniority_bin
  10. , daily_summary.engagement_group
  11. , daily_summary.LEAGUE_CHANGE
  12. , daily_summary.COUNTRY
  13. , daily_summary.platform
  14. , daily_summary.LTV_GROUP
  15. , daily_summary.TEAM_NAME
  16. , daily_summary.TEAM_ID
  17. , daily_summary.MATCH_CNT
  18. , daily_summary.SESSIONS_CNT
  19. , daily_summary.MATCH_SESSIONS_CNT
  20. , daily_summary.DAILY_IAP
  21. , daily_summary.LT_PURCHASES_AMT
  22. , daily_summary.ON_FIRE_GAMES
  23. -- , daily_summary.FRIENDS_FROM_LINK_CNT
  24. , daily_summary.TOTAL_COMPLETED_ALBUMS
  25. , daily_summary.IS_SUBSCRIBER
  26. , daily_summary.SUBSCRIPTION_TIER
  27. , daily_summary.FIRST_PURCHASE_TS
  28. , daily_summary.PURCHASES_CNT
  29. , daily_summary.NEW_USER
  30. , daily_summary.daily_ad_value
  31. , daily_summary.lt_ad_value
  32. , daily_summary.iap_7d
  33. , daily_summary.iap_30d
  34. , daily_summary.is_ftd
  35. , daily_summary.is_otd
  36. , daily_summary.trophy_group
  37. , daily_summary.TOTAL_MATCH_DURATION_MIN
  38. , daily_summary.fb_user_id
  39. , daily_summary.google_user_id
  40. , daily_summary.apple_user_id
  41. , daily_summary.New_invited_user
  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 (login_arena <= 22 AND finish_arena >= 23) THEN 'moved_to_LL'
  84.     WHEN (login_arena <= 13 AND finish_arena >= 14) THEN 'moved_to_ML'
  85.     ELSE 'no change'
  86.  END AS LEAGUE_CHANGE
  87. ,country
  88. ,ltv_group
  89. ,seniority_bin
  90. ,engagement_group
  91. ,team_name
  92. ,team_id
  93. ,daily_match_cnt match_cnt
  94. ,daily_sessions_cnt sessions_cnt
  95. ,daily_match_sessions_cnt match_sessions_cnt
  96. ,DAILY_IAP
  97. ,platform
  98. ,on_fire_games
  99. ,friends_from_link_cnt
  100. ,total_completed_albums
  101. ,is_subscriber
  102. ,subscription_tier
  103. ,first_purchase_ts
  104. ,purchases_cnt
  105. ,iap_7d
  106. ,iap_30d
  107. ,lt_purchases_amt
  108. ,is_ftd
  109. ,is_otd
  110. ,daily_ad_value
  111. ,lt_ad_value
  112. ,TOTAL_MATCH_DURATION_MIN
  113. , fb_user_id
  114. , google_user_id
  115. , apple_user_id
  116. ,CASE WHEN is_new_user = 1 AND is_restored = 0 THEN 1 ELSE 0 END AS New_user
  117. ,CASE WHEN is_new_user = 1 AND invited_by_uuid IS NOT NULL THEN 1 ELSE 0 END AS New_invited_user
  118. FROM CANDIVORE.PROD.DAILY_USERS_FROM_PARAMS WHERE is_active = 1
  119. -- and interval_date= 2022-08-30
  120. ) AS daily_summary        
  121.        
  122. LEFT JOIN //F_USER_RESOURCE_TRANSACTION
  123.  
  124. (SELECT user_id, TRANSACTION_DATE
  125. ,SUM(CASE WHEN transaction_source = 'stickers_trading' THEN 1 ELSE 0 END) AS stickers_traded
  126. ,SUM(CASE WHEN transaction_source = 'team_request' THEN 1 ELSE 0 END) AS team_requests
  127. FROM CANDIVORE.PROD.F_USER_RESOURCE_TRANSACTION
  128. GROUP BY user_id, TRANSACTION_DATE) AS RESOURCE_TRANSACTION
  129.  
  130. ON daily_summary.user_id = RESOURCE_TRANSACTION.user_id AND daily_summary.DATE = RESOURCE_TRANSACTION.TRANSACTION_DATE
  131.  
  132. LEFT JOIN //F_IN_APP_PURCHASE
  133.  
  134. (SELECT
  135. user_id
  136. ,DATE(derived_tstamp) AS DATE
  137. ,mode(CASE
  138.     WHEN package LIKE 'subscription_20%' THEN 'subscription_20'
  139.     WHEN package LIKE 'subscription_10%' THEN 'subscription_10'
  140.     WHEN package LIKE 'subscription_5%' THEN 'subscription_5'
  141.     ELSE NULL
  142.  END) AS subscription_package
  143. ,mode(SUBSCRIPTION_ACTION) AS SUBSCRIPTION_ACTION
  144. FROM CANDIVORE.PROD.F_IN_APP_PURCHASE WHERE PACKAGE LIKE '%subscription%'
  145. GROUP BY USER_ID, DATE(derived_tstamp)) AS IN_APP_PURCHASE
  146.  
  147. ON daily_summary.user_id = IN_APP_PURCHASE.user_id AND daily_summary.DATE = IN_APP_PURCHASE.DATE
  148.  
  149. LEFT JOIN //F_NOTIFICATION_OPENED
  150.  
  151. (SELECT
  152. user_id,
  153. DATE(derived_tstamp) AS DATE,
  154. COUNT(user_id) AS open_notif,
  155. COUNT(DISTINCT user_id) AS players_open_notif
  156. FROM CANDIVORE.PROD.F_NOTIFICATION_OPENED
  157. GROUP BY user_id, DATE(derived_tstamp)) AS NOTIFICATION_OPENED
  158.  
  159. ON daily_summary.user_id = NOTIFICATION_OPENED.user_id AND daily_summary.DATE = NOTIFICATION_OPENED.DATE
  160.  
  161. LEFT JOIN //F_USER_MATCH
  162.  
  163. (SELECT
  164. user_id,
  165. match_end_date AS DATE,
  166. COUNT(CASE WHEN match_type = 'Solo' THEN 1 END) AS solo_matches,
  167. COUNT(CASE WHEN match_type = 'Solo' AND is_won = 1 THEN 1 END) AS solo_wins,
  168. CASE WHEN solo_matches > 0 THEN solo_wins/solo_matches ELSE NULL END AS user_daily_winrate_solo,
  169. COUNT(CASE WHEN booster_tier = 'Legendary & Special Editions' AND match_type = 'Solo' THEN 1 ELSE NULL END) AS solo_legendary_matches,
  170. COUNT(CASE WHEN booster_tier = 'Legendary & Special Editions' AND match_type = 'Solo' AND is_won = 1 THEN 1 ELSE NULL END) AS solo_legendary_wins,
  171. CASE WHEN solo_legendary_matches > 0 THEN solo_legendary_wins/solo_legendary_matches ELSE NULL END AS user_daily_winrate_solo_legendary,
  172.  
  173. COUNT(CASE WHEN match_type = 'Rumble' THEN 1 END) AS rumble_matches,
  174. COUNT(CASE WHEN match_type = 'Rumble' AND is_won = 1 THEN 1 END) AS rumble_wins,
  175. CASE WHEN rumble_matches > 0 THEN rumble_wins/rumble_matches ELSE NULL END AS user_daily_winrate_rumble,
  176. COUNT(CASE WHEN booster_tier = 'Legendary & Special Editions' AND match_type = 'Rumble' THEN 1 ELSE NULL END) AS rumble_legendary_matches,
  177. COUNT(CASE WHEN booster_tier = 'Legendary & Special Editions' AND match_type = 'Rumble' AND is_won = 1 THEN 1 ELSE NULL END) AS rumble_legendary_wins,
  178. CASE WHEN rumble_legendary_matches > 0 THEN rumble_legendary_wins/rumble_legendary_matches ELSE NULL END AS user_daily_winrate_rumble_legendary
  179.  
  180. FROM CANDIVORE.PROD.F_USER_MATCH
  181. GROUP BY user_id, match_end_date) AS USER_MATCH
  182.  
  183. ON daily_summary.user_id = USER_MATCH.user_id AND daily_summary.DATE = USER_MATCH.DATE
  184. )
  185.  
  186. --------------------------
  187. --- end with statement ---
  188. --------------------------
  189.  
  190. SELECT
  191. A.interval_date
  192. ,A.country
  193. ,A.ltv_group
  194. ,A.LOGIN_ARENA
  195. ,A.league
  196. ,A.trophy_group
  197. ,A.seniority_bin
  198. ,A.engagement_group
  199. ,A.platform
  200. ,A.ACTIVE_USERS
  201. ,A.active_paying_users
  202. ,A.NEW_USERS
  203. ,A.New_invited_users
  204. ,A.daily_matches
  205. ,A.daily_onfire_matches
  206. ,A.daily_sessions
  207. ,A.daily_active_sessions
  208. ,A.DAILY_REVENUE
  209. ,A.users_payed_today
  210. ,A.daily_purchases
  211. ,A.FTDs
  212. ,A.OTDs
  213. ,A.Users_moved_to_ML
  214. ,A.FRIENDS_FROM_LINK_CNT
  215. ,A.Users_moved_to_LL
  216. ,A.Active_subscribers
  217. ,A.solo_matches
  218. ,A.solo_wins
  219. ,A.average_user_daily_winrate_solo
  220. ,A.median_user_daily_winrate_solo
  221. ,A.solo_legendary_matches
  222. ,A.solo_legendary_wins
  223. ,A.average_user_daily_winrate_solo_legendary
  224. ,A.median_user_daily_winrate_solo_legendary
  225. ,A.rumble_matches
  226. ,A.rumble_wins
  227. ,A.average_user_daily_winrate_rumble
  228. ,A.median_user_daily_winrate_rumble
  229. ,A.rumble_legendary_matches
  230. ,A.rumble_legendary_wins
  231. ,A.average_user_daily_winrate_rumble_legendary
  232. ,A.median_user_daily_winrate_rumble_legendary
  233. ,A.total_daily_team_request
  234. ,A.total_daily_stickers_traded
  235. ,A.active_users_in_teams
  236. ,A.open_notif
  237. ,A.players_open_notif
  238. ,A.daily_ad_value
  239. ,A.lt_ad_value
  240. ,A.is_iap_7d
  241. ,A.is_iap_30d
  242. ,A.TOTAL_MATCH_DURATION_MIN
  243. ,A.fb_connected_users
  244. ,A.google_connected_users
  245. ,A.apple_connected_users
  246. ,A.unconnected_users
  247. ,B.fake_users
  248. ,B.restored_users
  249. ,B.fake_restored_users
  250. FROM (
  251. (SELECT
  252. DATE AS interval_date
  253. ,country
  254. ,ltv_group
  255. ,LOGIN_ARENA
  256. ,league
  257. ,trophy_group
  258. ,seniority_bin
  259. ,engagement_group
  260. ,platform
  261. ,COUNT(DISTINCT user_id) AS ACTIVE_USERS
  262. ,COUNT(DISTINCT(CASE WHEN LT_PURCHASES_AMT>0 THEN user_id END)) AS active_paying_users
  263. ,SUM(New_invited_user) New_invited_users
  264. ,SUM(new_user) AS NEW_USERS
  265. ,SUM(match_cnt) AS daily_matches
  266. ,SUM(on_fire_games) AS daily_onfire_matches
  267. ,SUM(sessions_cnt) AS daily_sessions
  268. ,SUM(match_sessions_cnt) AS daily_active_sessions
  269. ,SUM(DAILY_IAP) AS DAILY_REVENUE
  270. ,COUNT(DISTINCT(CASE WHEN DAILY_IAP IS NOT NULL THEN user_id END)) AS users_payed_today
  271. ,SUM(purchases_cnt) AS daily_purchases
  272. ,SUM(CASE WHEN is_ftd = TRUE THEN 1 END) AS FTDs
  273. ,SUM(CASE WHEN is_otd = TRUE THEN 1 END) AS OTDs
  274. ,SUM(CASE WHEN LEAGUE_CHANGE = 'moved_to_ML' THEN 1 ELSE 0 END) AS Users_moved_to_ML
  275. ,SUM(CASE WHEN LEAGUE_CHANGE = 'moved_to_LL' THEN 1 ELSE 0 END) AS Users_moved_to_LL
  276. ,SUM(CASE WHEN is_subscriber = TRUE THEN 1 ELSE 0 END) AS Active_subscribers
  277. ,SUM(solo_matches) AS solo_matches
  278. ,SUM(solo_wins) AS solo_wins
  279. ,avg(user_daily_winrate_solo) AS average_user_daily_winrate_solo
  280. ,median(user_daily_winrate_solo) AS median_user_daily_winrate_solo
  281. ,SUM(solo_legendary_matches) AS solo_legendary_matches
  282. ,SUM(solo_legendary_wins) AS solo_legendary_wins
  283. ,avg(user_daily_winrate_solo_legendary) AS average_user_daily_winrate_solo_legendary
  284. ,median(user_daily_winrate_solo_legendary) AS median_user_daily_winrate_solo_legendary
  285. ,SUM(rumble_matches) AS rumble_matches
  286. ,SUM(rumble_wins) AS rumble_wins
  287. ,avg(user_daily_winrate_rumble) AS average_user_daily_winrate_rumble
  288. ,median(user_daily_winrate_rumble) AS median_user_daily_winrate_rumble
  289. ,SUM(rumble_legendary_matches) AS rumble_legendary_matches
  290. ,SUM(rumble_legendary_wins) AS rumble_legendary_wins
  291. ,avg(user_daily_winrate_rumble_legendary) AS average_user_daily_winrate_rumble_legendary
  292. ,median(user_daily_winrate_rumble_legendary) AS median_user_daily_winrate_rumble_legendary
  293. ,SUM(team_requests) AS total_daily_team_request
  294. ,SUM(stickers_traded) AS total_daily_stickers_traded
  295. ,COUNT(team_name) AS active_users_in_teams
  296. ,SUM(open_notif) AS open_notif
  297. ,SUM(players_open_notif) AS players_open_notif
  298. ,SUM(daily_ad_value) AS daily_ad_value
  299. ,SUM(lt_ad_value) AS lt_ad_value
  300. ,SUM(TOTAL_MATCH_DURATION_MIN) TOTAL_MATCH_DURATION_MIN
  301. ,SUM(CASE WHEN iap_7d > 0 THEN 1 ELSE 0 END) AS is_iap_7d
  302. ,SUM(CASE WHEN iap_30d > 0 THEN 1 ELSE 0 END) AS is_iap_30d
  303. ,SUM(CASE WHEN fb_user_id IS NOT NULL THEN 1 ELSE 0 END) fb_connected_users
  304. ,SUM(CASE WHEN google_user_id IS NOT NULL THEN 1 ELSE 0 END) google_connected_users
  305. ,SUM(CASE WHEN apple_user_id IS NOT NULL THEN 1 ELSE 0 END) apple_connected_users
  306. ,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
  307. FROM staging
  308. GROUP BY interval_date, country, league,trophy_group, LOGIN_ARENA, ltv_group, seniority_bin, engagement_group, platform
  309. ) A
  310. LEFT JOIN
  311. (SELECT
  312. interval_date
  313. ,country
  314. ,ltv_group
  315. ,LOGIN_ARENA
  316. ,CASE
  317.     WHEN trophies_cnt<3800 THEN 'Studios'
  318.     WHEN trophies_cnt<30000 THEN 'Master_League'
  319.     WHEN trophies_cnt>=30000 THEN 'Legends_League'
  320.  END AS league
  321. , CASE
  322.     WHEN trophies_cnt < 125 THEN '0-125'
  323.     WHEN trophies_cnt < 1400 THEN '125-1400'
  324.     WHEN trophies_cnt < 2000 THEN '1400-2000'
  325.     WHEN trophies_cnt < 3800 THEN '2000-3800'
  326.     WHEN trophies_cnt < 7000 THEN '3800-7000'
  327.     WHEN trophies_cnt < 16000 THEN '7000-16000'
  328.     WHEN trophies_cnt < 30000 THEN '16000-30000'
  329.     WHEN trophies_cnt >= 30000 THEN '30000+'
  330.   END AS trophy_group
  331. ,seniority_bin
  332. ,engagement_group
  333. ,platform
  334. ,SUM(is_fake) AS fake_users
  335. ,SUM(is_restored) AS restored_users
  336. ,SUM(CASE WHEN is_restored = 1 AND is_fake = 1 THEN 1 END) AS fake_restored_users
  337. FROM CANDIVORE.PROD.USER_DAILY_SUMMARY
  338. GROUP BY interval_date, country, league,trophy_group, LOGIN_ARENA, ltv_group, seniority_bin, engagement_group, platform
  339. ) B
  340. 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
  341. )
  342.  
  343.  
  344.  
  345. ;
  346. SELECT * ,CASE WHEN lo_event_id LIKE '%-mc-%' THEN 'MasterClub'
  347. WHEN lo_event_id LIKE '%cosmic%' THEN 'Cosmic'
  348. WHEN lo_event_id LIKE '%rally%' THEN 'Rally'
  349. WHEN lo_event_id LIKE '%heist%' THEN 'Heist'
  350. WHEN lo_event_id LIKE '%mutation%' THEN 'Mutations-Lab'
  351. ELSE 'check'
  352. END Solo_type FROM CANDIVORE.PROD.F_USER_MATCH
  353. WHERE match_end_date >= '2023-04-15' AND pvp_mode = 'Solo' AND Solo_type ='check' AND trophies_cnt >= 30000 LIMIT 10
  354.  
  355. ;
  356. SELECT match_end_date,CASE WHEN lo_event_id LIKE '%-mc-%' THEN 'MasterClub'
  357. WHEN lo_event_id LIKE '%cosmic%' THEN 'Cosmic'
  358. WHEN lo_event_id LIKE '%rally%' THEN 'Rally'
  359. WHEN lo_event_id LIKE '%heist%' THEN 'Heist'
  360. WHEN lo_event_id LIKE '%mutation%' THEN 'Mutations-Lab'
  361. ELSE 'check'
  362. END Solo_type , COUNT(DISTINCT match_id) AS solo_matches FROM CANDIVORE.PROD.F_USER_MATCH
  363. WHERE match_end_date >= '2023-04-15' AND pvp_mode = 'Solo' AND trophies_cnt >= 30000 GROUP BY 1,2 ORDER BY 2, 1 DESC
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!