-- select *, rank() over(partition by SESSION_ID order by EX_TIMESTAMP) as RANK
-- from PROCESS_LOG



with ta as (
select * 
from (select SESSION_ID, EX_TIMESTAMP as T1 from PROCESS_LOG where PROCESS_ID = "STEP1") 
left join (select SESSION_ID, EX_TIMESTAMP as T2 from PROCESS_LOG where PROCESS_ID = "STEP2") using (SESSION_ID)
left join (select SESSION_ID, EX_TIMESTAMP as T3 from PROCESS_LOG where PROCESS_ID = "STEP3") using (SESSION_ID)
left join (select SESSION_ID, EX_TIMESTAMP as T4 from PROCESS_LOG where PROCESS_ID = "STEP4") using (SESSION_ID)
left join (select SESSION_ID, EX_TIMESTAMP as T5 from PROCESS_LOG where PROCESS_ID = "STEP5") using (SESSION_ID)
)

, tb as (
select 
case when T1 < T2 and T2 < T3 and T3 < T4 and T4 < T5 then 1 else 0 end as "STEP5",
case when T1 < T2 and T2 < T3 and T3 < T4 and not (T5 is not null and T5 < T4) then 1 else 0 end as "STEP4",
case when T1 < T2 and T2 < T3 and not (T4 is not null and T4 < T3) and not (T5 is not null and T5 < T3) then 1 else 0 end as "STEP3",
case when T1 < T2 and not (T4 is not null and T4 < T2) and not (T5 is not null and T5 < T2) and not (T3 is not null and T3 < T2)  then 1 else 0 end as "STEP2",
case when T1 then 1 else 0 end as "STEP1"
from ta)

, tc as (
select 1 as "id0",sum(STEP1) as "STEP1", sum(STEP2) as "STEP2", sum(STEP3) as "STEP3", sum(STEP4) as "STEP4",  sum(STEP5) as "STEP5"
from tb)

select key as PROCESS, value as CNT
from (
SELECT id0, 'STEP1' AS key, STEP1 AS value FROM tc
UNION ALL
SELECT id0, 'STEP2' AS key, STEP2 AS value FROM tc
UNION ALL
SELECT id0, 'STEP3' AS key, STEP3 AS value FROM tc
UNION ALL
SELECT id0, 'STEP4' AS key, STEP4 AS value FROM tc
UNION ALL
SELECT id0, 'STEP5' AS key, STEP5 AS value FROM tc
)
order by PROCESS