API tools faq
paste
Advertisement
YuvalGai

Untitled

YuvalGai
Jul 4th, 2023
163
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 7.63 KB | None | 0 0
raw download clone embed print report
  1. SELECT date(first_install_DT) as install_cohort
  2. , test_name
  3. , test_group_name
  4. , platform
  5. , CASE when action = 'skip_training' then pre_skipped_msg
  6. WHEN message = 'EventFirstEntryWelcome' THEN 'Tutorial Begin'
  7. when action = 'start_match' and match_index = 0 then 'Match_1 Begin'
  8. when message = 'InGameMatch3' then 'Match_1 Intro'
  9. when action = 'show_hint' and match_index = 0 then 'Match_1 Hint'
  10. when message = 'InGameExtraMove' then 'Match_1 Extra Move'
  11. when message = 'FirstGameGoodJob' then 'Match_1 End'
  12. when action = 'start_match' and match_index = 1 then 'Match_2 Begin'
  13. when action = 'show_hint' and match_index = 1 then 'Match_2 Hint'
  14. when message = 'InGameBoosterUnlocked' then 'Match_2 InGame Booster Unlocked'
  15. when message = 'InGameBoosterBarFull' then 'Match_2 InGame Booster Activate'
  16. when message = 'InGameCreateSpecial' then 'Match_2 Create Special'
  17. when action = 'start_match' and match_index = 2 then 'Match_3 Begin'
  18. when action = 'show_hint' and match_index = 2 then 'Match_3 Hint'
  19. when message = 'InGamePerkHammer' then 'Match3_Hammer_Perk_Intro'
  20. when message = 'HammerTutorial' then 'Match3_Hammer_Perk_Tutorial'
  21. when message = 'InGamePerkShuffler' then 'Match3_Shuffle_Perk_Intro'
  22. when message = 'ShufflerTutorial' then 'Match3_Shuffle_Perk_Tutorial'
  23. when action = 'start_match' and match_index = 3 then 'Match_4 Begin'
  24. when action = 'show_hint' and match_index = 3 then 'Match_4 Hint'
  25. when message = 'EventDresserChooseOutfit' then 'Match_4_Choose_Outfit'
  26. when message = 'EventDresserEnterName' then 'Match_4_User_Name'
  27. when message = 'FirstBoosterSelect' then 'Match_4_Booster_Select'
  28. when message = 'FinalGameOneMoveLeft' then 'Match_4_Move_Left'
  29. when message = 'FinalGameTerrificGame' then 'Match_4_Final_Step'
  30. -- when action = 'skip_training' then 'skip_training'
  31.  
  32. ----------------------- END ONBOARDING -----------------------
  33.  
  34. /*when message = 'FinalGameWolfIllGetYou' then 1 else 0 end as tutorialMatch4End
  35. when message = 'HomescreenTutorialTrophies' then 1 else 0 end as tutorialTrophyReward --****
  36. when message = 'HomescreenTutorialPlay' then 1 else 0 end as tutorialHomeScreenPlay --****
  37. when message = 'SoloEventsScreenTutorial' then 1 else 0 end as tutorialSolo
  38. when message = 'SoloInGameTutorial' then 1 else 0 end as tutorialSolo_inGame
  39. when message = 'StickerAlbumsUnlocked' then 1 else 0 end as tutorialStickersUnlocked
  40. when message = 'FirstStickerPlaced' then 1 else 0 end as tutorialStickers
  41. when message = 'HomescreenTutorialTrophies' then seniority end as tutorialEnd --****
  42. when message = 'NewBoosterUnlocked' then seniority end as tutorialNewBoosterUnlocked --****
  43. when message = 'RumbleTutorialWolf' then 1 else 0 end as tutorialRumbleIntro
  44. when message = 'RumbleHomeScreenTutorial' then 1 else 0 end as tutorialRumbleSteps
  45. when message = 'RumbleEventUnlocked1' then 1 else 0 end as tutorialRumbleStep1
  46. when message = 'RumbleEventUnlocked2' then 1 else 0 end as tutorialRumbleStep2
  47. when message = 'OutOfSelectedBooster' then 1 else 0 end as tutorialOutOfSelectedBooster
  48. when message = 'OutOfBoosters' then 1 else 0 end as tutorialOutOfBoosters
  49. when message = 'FirstBoosterLost' then 1 else 0 end as tutorialFirstBoosterLost
  50. when message = 'SoloEventUnlocked1' then 1 else 0 end as tutorialSoloEventUnlocked1
  51. when message = 'RumbleInGameTutorial' then 1 else 0 end as tutorialRumbleInGameTutorial
  52. when message = 'PerksUnlocked' then 1 else 0 end as tutorialPerksUnlocked*/
  53. END tutorial_step
  54. , CASE
  55. WHEN tutorial_step = 'Tutorial Begin' THEN 1
  56. when tutorial_step = 'Match_1 Begin' then 2
  57. when tutorial_step = 'Match_1 Intro' then 3
  58. when tutorial_step = 'Match_1 Hint' then 4
  59. when tutorial_step = 'Match_1 Extra Move' then 5
  60. when tutorial_step = 'Match_1 End' then 6
  61. when tutorial_step = 'Match_2 Begin' then 7
  62. when tutorial_step = 'Match_2 Hint' then 8
  63. when tutorial_step = 'Match_2 InGame Booster Unlocked' then 9
  64. when tutorial_step = 'Match_2 InGame Booster Activate' then 10
  65. when tutorial_step = 'Match_2 Create Special' then 11
  66. when tutorial_step = 'Match_3 Begin' then 12
  67. when tutorial_step = 'Match_3 Hint' then 13
  68. when tutorial_step = 'Match3_Hammer_Perk_Intro' then 16
  69. when tutorial_step = 'Match3_Hammer_Perk_Tutorial' then 17
  70. when tutorial_step = 'Match3_Shuffle_Perk_Intro' then 14
  71. when tutorial_step = 'Match3_Shuffle_Perk_Tutorial' then 15
  72. when tutorial_step = 'Match_4 Begin' then 22
  73. when tutorial_step = 'Match_4 Hint' then 18
  74. when tutorial_step = 'Match_4_Choose_Outfit' then 19
  75. when tutorial_step = 'Match_4_User_Name' then 20
  76. when tutorial_step = 'Match_4_Booster_Select' then 21
  77. when tutorial_step = 'Match_4_Move_Left' then 23
  78. when tutorial_step = 'Match_4_Final_Step' then 24
  79. end tutorial_step_index
  80.  
  81. , CASE
  82. WHEN message = 'EventFirstEntryWelcome' THEN 1
  83. when action = 'start_match' and match_index = 0 then 1
  84. when message = 'InGameMatch3' then 1
  85. when action = 'show_hint' and match_index = 0 then 1
  86. when message = 'InGameExtraMove' then 0
  87. when message = 'FirstGameGoodJob' then 1
  88. when action = 'start_match' and match_index = 1 then 1
  89. when action = 'show_hint' and match_index = 1 then 0
  90. when message = 'InGameBoosterUnlocked' then 1
  91. when message = 'InGameBoosterBarFull' then 1
  92. when message = 'InGameCreateSpecial' then 0
  93. when action = 'start_match' and match_index = 2 then 1
  94. when action = 'show_hint' and match_index = 2 then 0
  95. when message = 'InGamePerkHammer' then 1
  96. when message = 'HammerTutorial' then 0
  97. when message = 'InGamePerkShuffler' then 1
  98. when message = 'ShufflerTutorial' then 0
  99. when action = 'start_match' and match_index = 3 then 1
  100. when action = 'show_hint' and match_index = 3 then 0
  101. when message = 'EventDresserChooseOutfit' then 1
  102. when message = 'EventDresserEnterName' then 1
  103. when message = 'FirstBoosterSelect' then 1
  104. when message = 'FinalGameOneMoveLeft' then 1
  105. when message = 'FinalGameTerrificGame' then 1
  106. when action = 'skip_training' then 2
  107. end mandatory_step
  108.  
  109. --- measures ---
  110. , count(distinct user_id) unique_users
  111. , count(distinct case when action != 'skip_training' then user_id end) unique_users_unskipped
  112. , count(distinct case when action = 'skip_training' and skipped = FALSE then user_id end) unique_users_skipped
  113.  
  114. FROM (select *,case WHEN message = 'EventFirstEntryWelcome' THEN 'Tutorial Begin'
  115. when message = 'InGameMatch3' then 'Match_1 Intro'
  116. when message = 'InGameExtraMove' then 'Match_1 Extra Move'
  117. when message = 'FirstGameGoodJob' then 'Match_1 End'
  118. when message = 'InGameBoosterUnlocked' then 'Match_2 InGame Booster Unlocked'
  119. when message = 'InGameBoosterBarFull' then 'Match_2 InGame Booster Activate'
  120. when message = 'InGameCreateSpecial' then 'Match_2 Create Special'
  121. when message = 'InGamePerkHammer' then 'Match3_Hammer_Perk_Intro'
  122. when message = 'HammerTutorial' then 'Match3_Hammer_Perk_Tutorial'
  123. when message = 'InGamePerkShuffler' then 'Match3_Shuffle_Perk_Intro'
  124. when message = 'ShufflerTutorial' then 'Match3_Shuffle_Perk_Tutorial'
  125. when message = 'EventDresserChooseOutfit' then 'Match_4_Choose_Outfit'
  126. when message = 'EventDresserEnterName' then 'Match_4_User_Name'
  127. when message = 'FirstBoosterSelect' then 'Match_4_Booster_Select'
  128. when message = 'FinalGameOneMoveLeft' then 'Match_4_Move_Left'
  129. when message = 'FinalGameTerrificGame' then 'Match_4_Final_Step'
  130. end message2,lag(message2,1)ignore nulls over(partition by user_id order by derived_tstamp) pre_skipped_msg from CANDIVORE.PROD.F_USER_ONBOARDING
  131. WHERE date(first_install_DT) >= '2023-06-18' and user_id not in (select distinct user_id from candivore.prod.f_client_restore_user where date(derived_tstamp) >= '2023-06-18'))
  132. group by 1,2,3,4,5,6,7
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!