API tools faq
paste
Advertisement
YuvalGai

Untitled

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