with seiki as (    --ちゃんと出るべきやつ
    select a.*
    from process_log as a
    inner join process_log as b
    on a.session_id = b.session_id
    and a.ex_timestamp  > b.ex_timestamp
    and cast(substr(a.process_id,5,1) as int) -1  = cast(substr(b.process_id,5,1) as int)
    )


,pettern2 as (--除外対象
    select a.*
    from process_log as a
    left outer join seiki
    on a.session_id = seiki.session_id
    and a.process_id = seiki.process_id
    and a.user_id = seiki.user_id
    and a.ex_timestamp = seiki.ex_timestamp
    where cast(substr(a.process_id,5,1) as int) > 1
    and seiki.process_id is null
    )
,pettern3 as (--除外対象より前のステップかつ処理があと
    select a.*
    from process_log as a
    inner join pettern2
    on  a.session_id = pettern2.session_id
    and cast(substr(a.process_id,5,1) as int) < cast(substr(pettern2.process_id,5,1) as int) 
    and a.ex_timestamp > pettern2.ex_timestamp
    where cast(substr(a.process_id,5,1) as int) > 1
    )

,pettern4 as (--除外対象より後のステップ
    select a.*
    from process_log as a
    inner join 
        (select *
        from pettern2
        union all 
        select *
        from pettern3
        ) as b
    on  a.session_id = b.session_id
    and cast(substr(a.process_id,5,1) as int) > cast(substr(b.process_id,5,1) as int) 
    )

,exclusion as(
    select *
    from pettern2
    union 
    select *
    from pettern3
    union 
    select *
    from pettern4)

select a.process_id as PROCESS
        ,count(a.process_id) as CNT
from process_log as a
left outer join exclusion
on a.session_id = exclusion.session_id
and a.process_id = exclusion.process_id
and a.user_id = exclusion.user_id
and a.ex_timestamp = exclusion.ex_timestamp
where exclusion.session_id is null
group by a.process_id
order by a.PROCESS_ID