create or replace table candivore.semantic_layer.t_onboarding_funnel as
SELECT date(first_install_DT) as install_cohort
    , test_name
    , test_group_name
    , platform
    , truest_media_source
    , CASE when action = 'skip_training' then pre_skipped_msg
    WHEN message = 'EventFirstEntryWelcome' THEN 'Tutorial Begin'
    when action = 'start_match' and match_index = 0 then 'Match_1 Begin'
    when message = 'InGameMatch3' then 'Match_1 Intro'
    when action = 'show_hint' and match_index = 0 then 'Match_1 Hint'
    when message = 'InGameExtraMove' then 'Match_1 Extra Move'
    when message = 'FirstGameGoodJob' then 'Match_1 End'
    when action = 'start_match' and match_index = 1 then 'Match_2 Begin'
    when action = 'show_hint' and match_index = 1 then 'Match_2 Hint'
    when message = 'InGameBoosterUnlocked' then 'Match_2 InGame Booster Unlocked'
    when message = 'InGameBoosterBarFull' then 'Match_2 InGame Booster Activate'
    when message = 'InGameCreateSpecial' then 'Match_2 Create Special'
    when action = 'start_match' and match_index = 2 then 'Match_3 Begin'
    when action = 'show_hint' and match_index = 2 then 'Match_3 Hint'
    when message = 'InGamePerkHammer' then 'Match3_Hammer_Perk_Intro'
    when message = 'HammerTutorial' then 'Match3_Hammer_Perk_Tutorial'
    when message = 'InGamePerkShuffler' then 'Match3_Shuffle_Perk_Intro'
    when message = 'ShufflerTutorial' then 'Match3_Shuffle_Perk_Tutorial'
    when action = 'start_match' and match_index = 3 then 'Match_4 Begin'
    when action = 'show_hint' and match_index = 3 then 'Match_4 Hint'
    when message = 'EventDresserChooseOutfit' then 'Match_4_Choose_Outfit'
    when message = 'EventDresserEnterName' then 'Match_4_User_Name'
    when message = 'FirstBoosterSelect' then 'Match_4_Booster_Select'
    when message = 'FinalGameOneMoveLeft' then 'Match_4_Move_Left'
    when message = 'FinalGameTerrificGame' then 'Match_4_Final_Step'
    -- when action = 'skip_training' then 'skip_training'

----------------------- END ONBOARDING -----------------------

/*when message = 'FinalGameWolfIllGetYou' then 1 else 0 end as tutorialMatch4End
when message = 'HomescreenTutorialTrophies' then 1 else 0 end as tutorialTrophyReward --****
when message = 'HomescreenTutorialPlay' then 1 else 0 end as tutorialHomeScreenPlay --****
when message = 'SoloEventsScreenTutorial' then 1 else 0 end as tutorialSolo
when message = 'SoloInGameTutorial' then 1 else 0 end as tutorialSolo_inGame
when message = 'StickerAlbumsUnlocked' then 1 else 0 end as tutorialStickersUnlocked
when message = 'FirstStickerPlaced' then 1 else 0 end as tutorialStickers
when message = 'HomescreenTutorialTrophies' then seniority end as tutorialEnd --****
when message = 'NewBoosterUnlocked' then seniority end as tutorialNewBoosterUnlocked --****
when message = 'RumbleTutorialWolf' then 1 else 0 end as tutorialRumbleIntro
when message = 'RumbleHomeScreenTutorial' then 1 else 0 end as tutorialRumbleSteps
when message = 'RumbleEventUnlocked1' then 1 else 0 end as tutorialRumbleStep1
when message = 'RumbleEventUnlocked2' then 1 else 0 end as tutorialRumbleStep2
when message = 'OutOfSelectedBooster' then 1 else 0 end as tutorialOutOfSelectedBooster
when message = 'OutOfBoosters' then 1 else 0 end as tutorialOutOfBoosters
when message = 'FirstBoosterLost' then 1 else 0 end as tutorialFirstBoosterLost
when message = 'SoloEventUnlocked1' then 1 else 0 end as tutorialSoloEventUnlocked1
when message = 'RumbleInGameTutorial' then 1 else 0 end as tutorialRumbleInGameTutorial
when message = 'PerksUnlocked' then 1 else 0 end as tutorialPerksUnlocked*/
END tutorial_step
    , CASE
WHEN tutorial_step = 'Tutorial Begin' THEN 1
when tutorial_step = 'Match_1 Begin' then 2
when tutorial_step = 'Match_1 Intro' then 3
when tutorial_step = 'Match_1 Hint' then 4
when tutorial_step = 'Match_1 Extra Move' then 5
when tutorial_step = 'Match_1 End' then 6
when tutorial_step = 'Match_2 Begin' then 7
when tutorial_step = 'Match_2 Hint' then 8
when tutorial_step = 'Match_2 InGame Booster Unlocked' then 9
when tutorial_step = 'Match_2 InGame Booster Activate' then 10
when tutorial_step = 'Match_2 Create Special' then 11
when tutorial_step = 'Match_3 Begin' then 12
when tutorial_step = 'Match_3 Hint' then 13
when tutorial_step = 'Match3_Hammer_Perk_Intro' then 16
when tutorial_step = 'Match3_Hammer_Perk_Tutorial' then 17
when tutorial_step = 'Match3_Shuffle_Perk_Intro' then 14
when tutorial_step = 'Match3_Shuffle_Perk_Tutorial' then 15
when tutorial_step = 'Match_4 Begin' then 22
when tutorial_step = 'Match_4 Hint' then 18
when tutorial_step = 'Match_4_Choose_Outfit' then 19
when tutorial_step = 'Match_4_User_Name' then 20
when tutorial_step = 'Match_4_Booster_Select' then 21
when tutorial_step = 'Match_4_Move_Left' then 23
when tutorial_step = 'Match_4_Final_Step' then 24
end tutorial_step_index

    , CASE
WHEN message = 'EventFirstEntryWelcome' THEN 1
when action = 'start_match' and match_index = 0 then 1
when message = 'InGameMatch3' then 1
when action = 'show_hint' and match_index = 0 then 1
when message = 'InGameExtraMove' then 0
when message = 'FirstGameGoodJob' then 1
when action = 'start_match' and match_index = 1 then 1
when action = 'show_hint' and match_index = 1 then 0
when message = 'InGameBoosterUnlocked' then 1
when message = 'InGameBoosterBarFull' then 1
when message = 'InGameCreateSpecial' then 0
when action = 'start_match' and match_index = 2 then 1
when action = 'show_hint' and match_index = 2 then 0
when message = 'InGamePerkHammer' then 1
when message = 'HammerTutorial' then 0
when message = 'InGamePerkShuffler' then 1
when message = 'ShufflerTutorial' then 0
when action = 'start_match' and match_index = 3 then 1
when action = 'show_hint' and match_index = 3 then 0
when message = 'EventDresserChooseOutfit' then 1
when message = 'EventDresserEnterName' then 1
when message = 'FirstBoosterSelect' then 1
when message = 'FinalGameOneMoveLeft' then 1
when message = 'FinalGameTerrificGame' then 1
when action = 'skip_training' then 2
end mandatory_step

    --- measures ---
    , count(distinct user_id) unique_users
    , count(distinct case when action != 'skip_training' then user_id end) unique_users_unskipped
    , count(distinct case when action = 'skip_training' and skipped = FALSE then user_id end) unique_users_skipped

    FROM (select *,case WHEN message = 'EventFirstEntryWelcome' THEN 'Tutorial Begin'
when message = 'InGameMatch3' then 'Match_1 Intro'
when message = 'InGameExtraMove' then 'Match_1 Extra Move'
when message = 'FirstGameGoodJob' then 'Match_1 End'
when message = 'InGameBoosterUnlocked' then 'Match_2 InGame Booster Unlocked'
when message = 'InGameBoosterBarFull' then 'Match_2 InGame Booster Activate'
when message = 'InGameCreateSpecial' then 'Match_2 Create Special'
when message = 'InGamePerkHammer' then 'Match3_Hammer_Perk_Intro'
when message = 'HammerTutorial' then 'Match3_Hammer_Perk_Tutorial'
when message = 'InGamePerkShuffler' then 'Match3_Shuffle_Perk_Intro'
when message = 'ShufflerTutorial' then 'Match3_Shuffle_Perk_Tutorial'
when message = 'EventDresserChooseOutfit' then 'Match_4_Choose_Outfit'
when message = 'EventDresserEnterName' then 'Match_4_User_Name'
when message = 'FirstBoosterSelect' then 'Match_4_Booster_Select'
when message = 'FinalGameOneMoveLeft' then 'Match_4_Move_Left'
when message = 'FinalGameTerrificGame' then 'Match_4_Final_Step'
end message2
,lag(message2,1)ignore nulls over(partition by user_id order by derived_tstamp) pre_skipped_msg
,last_value(media_source) over(partition by user_id order by derived_tstamp) truest_media_source
,first_value(app_version) over(partition by user_id order by derived_tstamp) first_app_version
from CANDIVORE.PROD.F_USER_ONBOARDING
WHERE date(first_install_DT) >= '2023-06-18' and user_id not in (select distinct user_id from candivore.prod.f_client_user_login where action = 'reconnected' and date(derived_tstamp) >= '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'))
group by 1,2,3,4,5,6,7,8