API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Jun 18th, 2023
153
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 10.53 KB | None | 0 0
raw download clone embed print report
  1. CREATE OR REPLACE PROCEDURE CANDIVORE.PROD.USER_BASE_STATS()
  2. RETURNS VARCHAR(16777216)
  3. LANGUAGE JAVASCRIPT
  4. EXECUTE AS CALLER
  5. AS '
  6.  
  7. //2. Get the last day most updates user stats
  8. var my_sql_command2 = `
  9. INSERT INTO CANDIVORE.PROD.USER_DAILY_PARAMS
  10. select distinct
  11. derived_tstamp
  12. ,date(derived_tstamp) as interval_date
  13. ,session_id
  14. ,user_id
  15. ,user_name
  16. ,country
  17. ,language
  18. ,device_language
  19. ,test_name
  20. ,test_group_name
  21. ,fb_user_ID
  22. ,client_IP
  23. ,is_testing_user
  24. ,is_developer
  25. ,team_ID
  26. ,device_ID
  27. ,device_model
  28. ,device_os
  29. ,platform
  30. ,media_source
  31. ,app_version
  32. ,app_minor_version
  33. ,first_install_DT
  34. ,advertiser_ID
  35. ,appsflyer_id
  36. ,ad_name
  37. ,campaign_name
  38. ,subscription_tier
  39. ,total_IAP_amt
  40. ,stars_cnt
  41. ,trophies_cnt
  42. ,LT_matches_played_cnt
  43. ,LT_matches_won_cnt
  44. ,LT_purchases_amt
  45. ,current_arena_index
  46. ,event_source
  47. ,ad_id
  48. ,adset
  49. ,adset_id
  50. ,campaign_id
  51. ,influencer_name
  52. ,af_keywords
  53. ,site_id
  54. ,team_name
  55.  
  56. from (
  57. select *, RANK () OVER (PARTITION BY user_id, date(derived_tstamp) ORDER BY derived_tstamp DESC)
  58. as rank_row
  59. from(
  60. select distinct
  61. derived_tstamp
  62. ,session_id
  63. , user_id
  64. ,user_name
  65. ,country
  66. ,language
  67. ,device_language
  68. ,test_name
  69. ,test_group_name
  70. ,fb_user_ID
  71. ,client_IP
  72. ,is_testing_user
  73. ,is_developer
  74. ,team_ID
  75. ,device_ID
  76. ,device_model
  77. ,device_os
  78. ,platform
  79. ,media_source
  80. ,app_version
  81. ,app_minor_version
  82. ,to_timestamp_ntz(first_install_DT) as first_install_DT
  83. ,advertiser_ID
  84. ,appsflyer_id
  85. ,ad_name
  86. ,campaign_name
  87. ,subscription_tier
  88. ,total_IAP_amt
  89. ,stars_cnt
  90. ,trophies_cnt
  91. ,LT_matches_played_cnt
  92. ,LT_matches_won_cnt
  93. ,LT_purchases_amt
  94. ,current_arena_index
  95. ,event_source
  96. ,ad_id
  97. ,adset
  98. ,adset_id
  99. ,campaign_id
  100. ,influencer_name
  101. ,af_keywords
  102. ,site_id
  103. ,team_name
  104. from snowplow.derived.video_watched
  105. where date(derived_tstamp) = dateadd(day, -1,date(current_date()))
  106.  
  107. UNION ALL
  108.  
  109. select distinct
  110. derived_tstamp
  111. ,session_id
  112. , user_id
  113. ,user_name
  114. ,country
  115. ,language
  116. ,device_language
  117. ,test_name
  118. ,test_group_name
  119. ,fb_user_ID
  120. ,client_IP
  121. ,is_testing_user
  122. ,is_developer
  123. ,team_ID
  124. ,device_ID
  125. ,device_model
  126. ,device_os
  127. ,platform
  128. ,media_source
  129. ,app_version
  130. ,app_minor_version
  131. ,to_timestamp_ntz(first_install_DT) as first_install_DT
  132. ,advertiser_ID
  133. ,appsflyer_id
  134. ,ad_name
  135. ,campaign_name
  136. ,subscription_tier
  137. ,total_IAP_amt
  138. ,stars_cnt
  139. ,trophies_cnt
  140. ,LT_matches_played_cnt
  141. ,LT_matches_won_cnt
  142. ,LT_purchases_amt
  143. ,current_arena_index
  144. ,event_source
  145. ,ad_id
  146. ,adset
  147. ,adset_id
  148. ,campaign_id
  149. ,influencer_name
  150. ,af_keywords
  151. ,site_id
  152. ,team_name
  153. from snowplow.derived.in_app_purchase
  154. where date(derived_tstamp) = dateadd(day, -1,date(current_date()))
  155.  
  156. UNION ALL
  157.  
  158. select distinct
  159. derived_tstamp
  160. ,session_id
  161. , user_id
  162. ,user_name
  163. ,country
  164. ,language
  165. ,device_language
  166. ,test_name
  167. ,test_group_name
  168. ,fb_user_ID
  169. ,client_IP
  170. ,is_testing_user
  171. ,is_developer
  172. ,team_ID
  173. ,device_ID
  174. ,device_model
  175. ,device_os
  176. ,platform
  177. ,media_source
  178. ,app_version
  179. ,app_minor_version
  180. ,to_timestamp_ntz(first_install_DT) as first_install_DT
  181. ,advertiser_ID
  182. ,appsflyer_id
  183. ,ad_name
  184. ,campaign_name
  185. ,subscription_tier
  186. ,total_IAP_amt
  187. ,stars_cnt
  188. ,trophies_cnt
  189. ,LT_matches_played_cnt
  190. ,LT_matches_won_cnt
  191. ,LT_purchases_amt
  192. ,current_arena_index
  193. ,event_source
  194. ,ad_id
  195. ,adset
  196. ,adset_id
  197. ,campaign_id
  198. ,influencer_name
  199. ,af_keywords
  200. ,site_id
  201. ,team_name
  202. from snowplow.derived.notification_opened
  203. where date(derived_tstamp) = dateadd(day, -1,date(current_date()))
  204.  
  205. UNION ALL
  206.  
  207. select distinct
  208. derived_tstamp
  209. ,session_id
  210. , user_id
  211. ,user_name
  212. ,country
  213. ,language
  214. ,device_language
  215. ,test_name
  216. ,test_group_name
  217. ,fb_user_ID
  218. ,client_IP
  219. ,is_testing_user
  220. ,is_developer
  221. ,team_ID
  222. ,device_ID
  223. ,device_model
  224. ,device_os
  225. ,platform
  226. ,media_source
  227. ,app_version
  228. ,app_minor_version
  229. ,to_timestamp_ntz(first_install_DT) as first_install_DT
  230. ,advertiser_ID
  231. ,appsflyer_id
  232. ,ad_name
  233. ,campaign_name
  234. ,subscription_tier
  235. ,total_IAP_amt
  236. ,stars_cnt
  237. ,trophies_cnt
  238. ,LT_matches_played_cnt
  239. ,LT_matches_won_cnt
  240. ,LT_purchases_amt
  241. ,current_arena_index
  242. ,event_source
  243. ,ad_id
  244. ,adset
  245. ,adset_id
  246. ,campaign_id
  247. ,influencer_name
  248. ,af_keywords
  249. ,site_id
  250. ,team_name
  251. from snowplow.derived.live_event_progression
  252. where date(derived_tstamp) = dateadd(day, -1,date(current_date()))
  253.  
  254. UNION ALL
  255.  
  256. select distinct
  257. derived_tstamp
  258. ,session_id
  259. , user_id
  260. ,user_name
  261. ,country
  262. ,language
  263. ,device_language
  264. ,test_name
  265. ,test_group_name
  266. ,fb_user_ID
  267. ,client_IP
  268. ,is_testing_user
  269. ,is_developer
  270. ,team_ID
  271. ,device_ID
  272. ,device_model
  273. ,device_os
  274. ,platform
  275. ,media_source
  276. ,app_version
  277. ,app_minor_version
  278. ,to_timestamp_ntz(first_install_DT) as first_install_DT
  279. ,advertiser_ID
  280. ,appsflyer_id
  281. ,ad_name
  282. ,campaign_name
  283. ,subscription_tier
  284. ,total_IAP_amt
  285. ,stars_cnt
  286. ,trophies_cnt
  287. ,LT_matches_played_cnt
  288. ,LT_matches_won_cnt
  289. ,LT_purchases_amt
  290. ,current_arena_index
  291. ,event_source
  292. ,ad_id
  293. ,adset
  294. ,adset_id
  295. ,campaign_id
  296. ,influencer_name
  297. ,af_keywords
  298. ,site_id
  299. ,team_name
  300. from snowplow.derived.client_restore_user
  301. where date(derived_tstamp) = dateadd(day, -1,date(current_date()))
  302.  
  303.  
  304. UNION ALL
  305.  
  306. select distinct
  307. derived_tstamp
  308. ,session_id
  309. , user_id
  310. ,user_name
  311. ,country
  312. ,language
  313. ,device_language
  314. ,test_name
  315. ,test_group_name
  316. ,fb_user_ID
  317. ,client_IP
  318. ,is_testing_user
  319. ,is_developer
  320. ,team_ID
  321. ,device_ID
  322. ,device_model
  323. ,device_os
  324. ,platform
  325. ,media_source
  326. ,app_version
  327. ,app_minor_version
  328. ,to_timestamp_ntz(first_install_DT) as first_install_DT
  329. ,advertiser_ID
  330. ,appsflyer_id
  331. ,ad_name
  332. ,campaign_name
  333. ,subscription_tier
  334. ,total_IAP_amt
  335. ,stars_cnt
  336. ,trophies_cnt
  337. ,LT_matches_played_cnt
  338. ,LT_matches_won_cnt
  339. ,LT_purchases_amt
  340. ,current_arena_index
  341. ,event_source
  342. ,ad_id
  343. ,adset
  344. ,adset_id
  345. ,campaign_id
  346. ,influencer_name
  347. ,af_keywords
  348. ,site_id
  349. ,team_name
  350. from snowplow.derived.client_user_login
  351. where action != ''reconnected'' and date(derived_tstamp) = dateadd(day, -1,date(current_date()))
  352.  
  353. UNION ALL
  354.  
  355. /*
  356. select distinct
  357. derived_tstamp
  358. ,session_id
  359. , user_id
  360. ,user_name
  361. ,country
  362. ,language
  363. ,device_language
  364. ,test_name
  365. ,test_group_name
  366. ,fb_user_ID
  367. ,client_IP
  368. ,is_testing_user
  369. ,is_developer
  370. ,team_ID
  371. ,device_ID
  372. ,device_model
  373. ,device_os
  374. ,platform
  375. ,media_source
  376. ,app_version
  377. ,app_minor_version
  378. ,to_timestamp_ntz(first_install_DT) as first_install_DT
  379. ,advertiser_ID
  380. ,appsflyer_id
  381. ,ad_name
  382. ,campaign_name
  383. ,subscription_tier
  384. ,total_IAP_amt
  385. ,stars_cnt
  386. ,trophies_cnt
  387. ,LT_matches_played_cnt
  388. ,LT_matches_won_cnt
  389. ,LT_purchases_amt
  390. ,current_arena_index
  391. ,event_source
  392. ,ad_id
  393. ,adset
  394. ,adset_id
  395. ,campaign_id
  396. ,influencer_name
  397. ,af_keywords
  398. ,site_id
  399. ,team_name
  400. from snowplow.derived.user_loading
  401. where date(derived_tstamp) = dateadd(day, -1,date(current_date()))
  402.  
  403. UNION ALL */
  404.  
  405. select distinct
  406. derived_tstamp
  407. ,session_id
  408. , user_id
  409. ,user_name
  410. ,country
  411. ,language
  412. ,device_language
  413. ,test_name
  414. ,test_group_name
  415. ,fb_user_ID
  416. ,client_IP
  417. ,is_testing_user
  418. ,is_developer
  419. ,team_ID
  420. ,device_ID
  421. ,device_model
  422. ,device_os
  423. ,platform
  424. ,media_source
  425. ,app_version
  426. ,app_minor_version
  427. ,to_timestamp_ntz(first_install_DT) as first_install_DT
  428. ,advertiser_ID
  429. ,appsflyer_id
  430. ,ad_name
  431. ,campaign_name
  432. ,subscription_tier
  433. ,total_IAP_amt
  434. ,stars_cnt
  435. ,trophies_cnt
  436. ,LT_matches_played_cnt
  437. ,LT_matches_won_cnt
  438. ,LT_purchases_amt
  439. ,current_arena_index
  440. ,event_source
  441. ,ad_id
  442. ,adset
  443. ,adset_id
  444. ,campaign_id
  445. ,influencer_name
  446. ,af_keywords
  447. ,site_id
  448. ,team_name
  449. from snowplow.derived.user_progression
  450. where date(derived_tstamp) = dateadd(day, -1,date(current_date()))
  451.  
  452.  
  453. UNION ALL
  454.  
  455. select distinct
  456. derived_tstamp
  457. ,session_id
  458. , user_id
  459. ,user_name
  460. ,country
  461. ,language
  462. ,device_language
  463. ,test_name
  464. ,test_group_name
  465. ,fb_user_ID
  466. ,client_IP
  467. ,is_testing_user
  468. ,is_developer
  469. ,team_ID
  470. ,device_ID
  471. ,device_model
  472. ,device_os
  473. ,platform
  474. ,media_source
  475. ,app_version
  476. ,app_minor_version
  477. ,to_timestamp_ntz(first_install_DT) as first_install_DT
  478. ,advertiser_ID
  479. ,appsflyer_id
  480. ,ad_name
  481. ,campaign_name
  482. ,subscription_tier
  483. ,total_IAP_amt
  484. ,stars_cnt
  485. ,trophies_cnt
  486. ,LT_matches_played_cnt
  487. ,LT_matches_won_cnt
  488. ,LT_purchases_amt
  489. ,current_arena_index
  490. ,event_source
  491. ,ad_id
  492. ,adset
  493. ,adset_id
  494. ,campaign_id
  495. ,influencer_name
  496. ,af_keywords
  497. ,site_id
  498. ,team_name
  499. from snowplow.derived.core_user_resource_transaction
  500. where date(derived_tstamp) = dateadd(day, -1,date(current_date()))
  501.  
  502.  
  503. UNION ALL
  504.  
  505. select distinct
  506. derived_tstamp
  507. ,match_session_id as session_id
  508. , user_id
  509. ,user_name
  510. ,country
  511. ,language
  512. ,device_language
  513. ,test_name
  514. ,test_group_name
  515. ,fb_user_ID
  516. ,client_IP
  517. ,is_testing_user
  518. ,is_developer
  519. ,team_ID
  520. ,device_ID
  521. ,device_model
  522. ,device_os
  523. ,platform
  524. ,media_source
  525. ,app_version
  526. ,app_minor_version
  527. ,to_timestamp_ntz(first_install_DT) as first_install_DT
  528. ,advertiser_ID
  529. ,appsflyer_id
  530. ,ad_name
  531. ,campaign_name
  532. ,subscription_tier
  533. ,total_IAP_amt
  534. ,stars_cnt
  535. ,trophies_cnt
  536. ,LT_matches_played_cnt
  537. ,LT_matches_won_cnt
  538. ,LT_purchases_amt
  539. ,current_arena_index
  540. ,event_source
  541. ,ad_id
  542. ,adset
  543. ,adset_id
  544. ,campaign_id
  545. ,influencer_name
  546. ,af_keywords
  547. ,site_id
  548. ,team_name
  549. from snowplow.derived.user_match
  550. where date(derived_tstamp) = dateadd(day, -1,date(current_date()))
  551.  
  552.  
  553. UNION ALL
  554.  
  555. select distinct
  556. derived_tstamp
  557. ,session_id
  558. , user_id
  559. ,user_name
  560. ,country
  561. ,language
  562. ,device_language
  563. ,test_name
  564. ,test_group_name
  565. ,fb_user_ID
  566. ,USER_IPADDRESS
  567. ,is_testing_user
  568. ,is_developer
  569. ,team_ID
  570. ,device_ID
  571. ,device_model
  572. ,device_os
  573. ,platform
  574. ,media_source
  575. ,app_version
  576. ,app_minor_version
  577. ,first_install_DT
  578. ,advertiser_ID
  579. ,appsflyer_id
  580. ,ad_name
  581. ,campaign_name
  582. ,subscription_tier_id
  583. ,total_IAP_amt
  584. ,stars_cnt
  585. ,trophies_cnt
  586. ,LT_matches_played_cnt
  587. ,LT_matches_won_cnt
  588. ,LT_purchases_amt
  589. ,current_arena_index
  590. ,event_source
  591. ,ad_id
  592. ,adset
  593. ,adset_id
  594. ,campaign_id
  595. ,influencer_name
  596. ,af_keywords
  597. ,site_id
  598. ,team_name
  599. from snowplow.derived.user_session_start
  600. where date(derived_tstamp) = dateadd(day, -1,date(current_date()))
  601.  
  602. )
  603. ) where rank_row = 1
  604.  
  605. `
  606.  
  607.  
  608. var statement2 = snowflake.createStatement({sqlText: my_sql_command2});
  609. statement2.execute();
  610.  
  611.  
  612. var my_sql_command99 = "COMMIT;";
  613. var statement99 = snowflake.createStatement({sqlText: my_sql_command99});
  614. statement99.execute();
  615. ';
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!