with t as (
select SESSION_ID as s,
PROCESS_ID as p,
rank() over(
partition by session_id
order by EX_TIMESTAMP) as r
from PROCESS_LOG as x
group by session_id, process_id
), t1 as (
select *
from t
where p='STEP1' and r=1
), t2 as (
select *
from t1 inner join t
on t.s=t1.s
where t.p='STEP2' and t.r=2
), t3 as (
select *
from t2 inner join t
on t.s=t2.s
where t.p='STEP3' and t.r=3
), t4 as (
select *
from t3 inner join t
on t.s=t3.s
where t.p='STEP4' and t.r=4
), t5 as (
select *
from t4 inner join t
on t.s=t4.s
where t.p='STEP5' and t.r=5
)
select 'STEP1' as 'PROCESS',
count(*) as 'CNT'
from t1
union all
select 'STEP2' as 'PROCESS',
count(*) as 'CNT'
from t2
union all
select 'STEP3' as 'PROCESS',
count(*) as 'CNT'
from t3
union all
select 'STEP4' as 'PROCESS',
count(*) as 'CNT'
from t4
union all
select 'STEP5' as 'PROCESS',
count(*) as 'CNT'
from t5