with tmp_tbl_1 as 
(
select 
  session_id 
  ,process_id 
  ,user_id 
  ,ex_timestamp
  ,row_number() over(partition by session_id, user_id order by ex_timestamp) as first_step 
from process_log 
)
-- select *
-- from tmp_tbl_1 
-- where first_step = 1 and process_id = 'STEP1'
,target_sessions as 
(
  select session_id 
  from tmp_tbl_1 
  where first_step = 1 and process_id = 'STEP1'
)
,tmp_tbl_2 as 
(
select * 
, lag(process_id, 1) over(partition by session_id order by ex_timestamp) as previous_process
from process_log 
where session_id in (select session_id from target_sessions)
-- order by session_id, ex_timestamp
)
,tmp_tbl_3 as 
(
select *
, case when (process_id = 'STEP1' and previous_process is null 
          or process_id = 'STEP2' and previous_process = 'STEP1'
          or process_id = 'STEP3' and previous_process = 'STEP2'
          or process_id = 'STEP4' and previous_process = 'STEP3'
          or process_id = 'STEP5' and previous_process = 'STEP4'
          ) then 'ok' 
          else 'bad' 
        end as check_
from tmp_tbl_2 
-- order by session_id, ex_timestamp
)
,tmp_tbl_4 as (
select *
  ,sum(case when check_ = 'bad' then 1 else 0 end) over(partition by session_id order by ex_timestamp) as cum_bad
from tmp_tbl_3
-- order by session_id, ex_timestamp
)
,tmp_tbl_5 as (
select * 
from tmp_tbl_4 
where cum_bad = 0
-- order by session_id, ex_timestamp
)
select 
  process_id as PROCESS 
  ,count(0) as CNT
from tmp_tbl_5
group by 1 
order by process
;
;