-- 集計対象のSTEP1を取得
with target_base as (
    select 
        SESSION_ID
        ,PROCESS_ID
        ,EX_TIMESTAMP
        ,MIN(EX_TIMESTAMP) OVER(PARTITION BY SESSION_ID) "MIN_TS"
    from PROCESS_LOG
    ),
    target_step1 as (
    select
        SESSION_ID
        ,PROCESS_ID
        ,EX_TIMESTAMP
    from target_base
    where EX_TIMESTAMP = MIN_TS
    and PROCESS_ID = 'STEP1'
    ),
    target_session as (
    select
        p.SESSION_ID
        ,p.PROCESS_ID
        ,p.EX_TIMESTAMP
        ,lag(p.EX_TIMESTAMP,1) over(order by p.session_id,p.process_id) "pre_ts"
    from
        PROCESS_LOG p
    inner join target_step1 t
    on p.SESSION_ID = t.SESSION_ID
    ),
    target_session2 as (
    select SESSION_ID
        ,PROCESS_ID
        ,EX_TIMESTAMP
    from target_session
    where EX_TIMESTAMP > pre_ts
    or PROCESS_ID = 'STEP1'
    ),
    pro_master(process_id,num) as (
    values('STEP1',1),('STEP2',2),('STEP3',3),('STEP4',4),('STEP5',5)
    ),
    target_session3 as (
    select SESSION_ID
        ,t2.PROCESS_ID
        ,t2.EX_TIMESTAMP
        ,p.num
    from target_session2 t2
    inner join pro_master p
    on t2.process_id = p.process_id
    ),
    temp as (
    select SESSION_ID
        ,PROCESS_ID
        ,EX_TIMESTAMP
        ,num
        ,lag(num,1) over(order by session_id, ex_timestamp) "pre_num"
    from target_session3
    ),
    temp2 as (
    select SESSION_ID
            ,PROCESS_ID
            ,EX_TIMESTAMP
            ,num - pre_num
    from temp
    where num - pre_num = 1
    or process_id = 'STEP1'
    )
select process_id "PROCESS"
    ,COUNT(process_id)
from temp2
group by process_id
order by PROCESS asc
;

-- いったいどこで間違えたというんだ。。。