WITH stepbase AS (
	SELECT
		SESSION_ID
		,PROCESS_ID
		,USER_ID
		,EX_TIMESTAMP
		,ROW_NUMBER() OVER (ORDER BY SESSION_ID, PROCESS_ID) AS RENBAN0
	FROM PROCESS_LOG
)
,timebase AS (
	SELECT
		SESSION_ID
		,PROCESS_ID
		,USER_ID
		,EX_TIMESTAMP
		,ROW_NUMBER() OVER (ORDER BY SESSION_ID, EX_TIMESTAMP) AS RENBAN1
	FROM PROCESS_LOG
)
,stepokdata AS (
	SELECT *
	FROM stepbase AS t0
	INNER JOIN timebase AS t1
		ON t0.SESSION_ID = t1.SESSION_ID
			AND t0.PROCESS_ID = t1.PROCESS_ID
			AND RENBAN0 = RENBAN1
)
,mintimetable AS (
	SELECT
		SESSION_ID
		,MIN(EX_TIMESTAMP) AS mintime
	FROM stepokdata
)
,step1ok AS (
SELECT
	SESSION_ID
	,PROCESS_ID
	,t0.EX_TIMESTAMP
FROM stepokdata AS t0
WHERE EXISTS(
	SELECT 1
	FROM mintimetable AS t1
	WHERE t0.EX_TIMESTAMP = t1.mintime
	)
	AND t0.PROCESS_ID = 'STEP1'
)
,kokomade AS(
	SELECT *
	FROM stepokdata AS t0
	WHERE EXISTS (
		SELECT 1
		FROM step1ok AS t1
		WHERE t0.SESSION_ID = t1.SESSION_ID
		)
)
,junduke AS (
	SELECT
		SESSION_ID
		,PROCESS_ID
		,USER_ID
		,ROW_NUMBER() OVER (PARTITION BY SESSION_ID ORDER BY PROCESS_ID) AS RENBAN
	FROM kokomade
)
,allokdata AS (
	SELECT *
	FROM junduke
	WHERE SUBSTR(PROCESS_ID,-1) = CAST(RENBAN AS verchar)
)
SELECT
	PROCESS_ID AS PROCESS
	,COUNT(SESSION_ID) AS CNT
FROM allokdata
GROUP BY 1
ORDER BY PROCESS_ID