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 SESSION_ID
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,
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),
--ステップ番号と実行順を比較
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 *
--FROM D
--GROUP BY SESSION_ID;
SELECT PROCESS_ID AS PROCESS,COUNT(SESSION_ID) AS CNT
FROM D
WHERE SNUM = NUM
GROUP BY PROCESS_ID;
--SELECT *
--FROM D;