with r as (
select *
, rank() over(partition by session_id order by EX_TIMESTAMP) as ra
-- , (select count(1) from process_log p2 where process_log.SESSION_ID = p2.SESSION_ID and p2.EX_TIMESTAMP < process_log.EX_TIMESTAMP) as pcnt
from process_log
)
, r2 as (
select * from r where ra = cast(substr(process_id, 5) as integer)
)
, r3 as (
select *, rank() over(partition by session_id order by EX_TIMESTAMP) as ra2
from r2
)
, r4 as (
select process_id, count(1) as CNT from r3 where ra = ra2 group by process_id
)
, ss as (
select 'STEP1' as PROCESS union all
select 'STEP2' union all
select 'STEP3' union all
select 'STEP4' union all
select 'STEP5'
)
select 
ss.PROCESS as PROCESS
, ifnull(r4.CNT, 0) as CNT
from ss left join r4 on ss.process = r4.process_id
order by PROCESS

-- , r2 as (
-- select * from r
-- where ra == cast(substr(process_id, 5) as integer)
-- )
-- , r3 as (select * 
-- , (select count(1) from r2 b where b.ra < a.ra and b.session_id = a.session_id) as cnt
-- from r2 as a
-- )
-- , ss as (
-- select 'STEP1' as PROCESS union all
-- select 'STEP2' union all
-- select 'STEP3' union all
-- select 'STEP4' union all
-- select 'STEP5'
-- )
-- select 
-- ss.PROCESS as PROCESS
-- , ifnull(count(1), 0) as CNT
-- from ss left join r3 on ss.PROCESS = r3.process_id
-- where ra - 1 = cnt
-- group by process_id
-- order by PROCESS