--方針:STEP1から5へ順々に正しいセッションIDリストを抽出していく

--計算し易いようにTIMESTAMPをRANKにPROCESSIDを数値に統一する
WITH BASE AS(
SELECT
SESSION_ID,
USER_ID,
RANK()
OVER(
PARTITION BY SESSION_ID,USER_ID
ORDER BY EX_TIMESTAMP) AS TIMERANK,
CAST(SUBSTRING(PROCESS_ID,5,1) AS INTEGER) AS P_ID
FROM PROCESS_LOG
)

--STEP1が正しいSESSION_IDリスト
,ST1 AS (
SELECT
SESSION_ID
FROM BASE
WHERE TIMERANK = 1 AND P_ID = 1
)

--STEP2が正しいSESSION_IDリスト
,ST2 AS(
SELECT
SESSION_ID
FROM BASE
WHERE SESSION_ID IN(SELECT * FROM ST1) AND TIMERANK = 2 AND P_ID = 2)

--STEP3が正しいSESSION_IDリスト
,ST3 AS(
SELECT
SESSION_ID
FROM BASE
WHERE SESSION_ID IN(SELECT * FROM ST2) AND TIMERANK = 3 AND P_ID = 3)

--STEP4が正しいSESSION_IDリスト
,ST4 AS(
SELECT
SESSION_ID
FROM BASE
WHERE SESSION_ID IN(SELECT * FROM ST3) AND TIMERANK = 4 AND P_ID = 4)

--STEP5が正しいSESSION_IDリスト
,ST5 AS(
SELECT
*,
SESSION_ID
FROM BASE
WHERE SESSION_ID IN(SELECT * FROM ST4) AND TIMERANK = 5 AND P_ID = 5)

--まとめ
SELECT
'STEP1' AS PROCESS,
COUNT(*) AS CNT
FROM ST1
UNION ALL
SELECT
'STEP2' AS PROCESS,
COUNT(*) AS CNT
FROM ST2
UNION ALL
SELECT
'STEP3' AS PROCESS,
COUNT(*) AS CNT
FROM ST3
UNION ALL
SELECT
'STEP4' AS PROCESS,
COUNT(*) AS CNT
FROM ST4
UNION ALL
SELECT
'STEP5' AS PROCESS,
COUNT(*) AS CNT
FROM ST5
;