API tools faq
paste
Advertisement
YuvalGai

Untitled

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