--一つ前が存在しない
with cur as (
select *
        ,max(ex_timestamp) over (partition by session_id,user_id) as max_time
        ,min(ex_timestamp) over (partition by session_id,user_id) as min_time
        ,cast(substr(process_id,5,1) as int) step
        ,rank() over (partition by session_id,user_id order by ex_timestamp) as row
from process_log
)

,cur2 as(

select cur.* 
    ,(case when step <> row 
        then 0
        when step = 1 and cur.min_time = cur.ex_timestamp 
        then 1
        when after.session_id is null and cur.max_time = cur.ex_timestamp 
        then 1
        when cur.ex_timestamp > before.ex_timestamp
        then 1
        when cur.ex_timestamp < after.ex_timestamp
        then 1
        else 0 end)  as exclude
from cur    
left outer join process_log as before
on cur.session_id = before.session_id
and cast(substr(cur.process_id,5,1) as int) - 1 = cast(substr(before.process_id,5,1) as int)
left outer join process_log as after
on cur.session_id = after.session_id
and cast(substr(cur.process_id,5,1) as int) + 1 = cast(substr(after.process_id,5,1) as int)
)
select 
        cur2.process_id as PROCESS
        ,count(cur2.process_id) as CNT
from cur2
left outer join cur2 as before
on cur2.session_id = before.session_id
and cast(substr(cur2.process_id,5,1) as int) - 1 = cast(substr(before.process_id,5,1) as int)
left outer join cur2 as before2
on cur2.session_id = before2.session_id
and cast(substr(cur2.process_id,5,1) as int) - 2 = cast(substr(before2.process_id,5,1) as int)
left outer join cur2 as before3
on cur2.session_id = before3.session_id
and cast(substr(cur2.process_id,5,1) as int) - 3 = cast(substr(before3.process_id,5,1) as int)

where  cur2.exclude = 1 
and (before.exclude = 1 or cur2.process_id = 'STEP1')
and (before2.exclude = 1 or cur2.process_id in ('STEP1','STEP2'))
and (before3.exclude = 1 or cur2.process_id in ('STEP1','STEP2','STEP3'))
group by cur2.process_id
order by PROCESS 
/*
select cur2.process_id as PROCESS
        ,count(cur2.process_id) as CNT
from cur2
group by cur2.process_id
order by PROCESS    
  */