API tools faq
paste
Advertisement
YuvalGai

Untitled

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