API tools faq
paste
Advertisement
YuvalGai

Untitled

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