WITH A AS(
SELECT *
FROM PROCESS_LOG
GROUP BY USER_ID,SESSION_ID,PROCESS_ID
ORDER BY SESSION_ID,EX_TIMESTAMP),

--STEP1が存在するSESSION_ID
B AS(
SELECT *
FROM PROCESS_LOG
WHERE PROCESS_ID = "STEP1"
GROUP BY USER_ID,SESSION_ID,PROCESS_ID
ORDER BY SESSION_ID),

--STEP1 → STEP2 → STEP3 → STEP4 → STEP5 の順番に実行
C AS(
SELECT A.SESSION_ID,A.PROCESS_ID,A.EX_TIMESTAMP,
--ROW_NUMBER() 
--OVER(PARTITION BY A.SESSION_ID ORDER BY A.SESSION_ID ASC,A.PROCESS_ID ASC) SNUM2,
CAST(SUBSTR(A.PROCESS_ID, 5, 1) AS INTEGER) AS SNUM,
ROW_NUMBER() 
OVER(PARTITION BY A.SESSION_ID ORDER BY A.SESSION_ID ASC,A.EX_TIMESTAMP ASC) TNUM
FROM A
JOIN B
ON A.SESSION_ID = B.SESSION_ID),

--STEP1 → STEP2 → STEP3 → STEP4 → STEP5 の順番に実行
D AS(
SELECT *,
ROW_NUMBER() 
OVER(PARTITION BY SESSION_ID ORDER BY SESSION_ID ASC,EX_TIMESTAMP ASC) NUM
FROM C
WHERE SNUM = TNUM)

SELECT PROCESS_ID AS PROCESS,COUNT(*) AS CNT
FROM D
WHERE SNUM = NUM
GROUP BY PROCESS_ID;

--SELECT PROCESS_ID AS PROCESS,COUNT(*) AS CNT
--FROM D
--WHERE SNUM = NUM
--GROUP BY PROCESS_ID
--ORDER BY PROCESS_ID ASC;

--SELECT *
--FROM D;