with tmp_tbl_2 as 
(
select * 
, lag(process_id, 1) over(partition by session_id order by ex_timestamp) as previous_process
from process_log 
)
,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 
) --step1以外のstepは1つ前のstepが実行されている
,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
)
select 
  process_id as PROCESS 
  ,count(case when cum_bad = 0 then session_id end) as CNT
from tmp_tbl_4
group by 1 
order by 1 
;