-- https://topsic-contest.jp/contests/contest010/submissions/22996
WITH RECURSIVE PROCESS(PROCESS_NO) AS (
    SELECT              1 AS PROCESS_NO
    UNION ALL
    SELECT PROCESS_NO + 1 AS PROCESS_NO FROM PROCESS WHERE PROCESS_NO < 5
), PROCESS_LOG1 AS (
    SELECT
        SESSION_ID,
        PROCESS_ID,
        RANK() OVER (
            PARTITION BY SESSION_ID
            ORDER BY COALESCE(EX_TIMESTAMP, '2000-01-01 00:00:00') ASC
        ) AS TIMETAMP_NO
    FROM
        PROCESS_LOG
), PROCESS_LOG2 AS (
    SELECT
        PROCESS_LOG1.*,
        RANK() OVER (
            PARTITION BY SESSION_ID
            ORDER BY PROCESS_ID ASC
        ) AS PROCESS_NO
    FROM
        PROCESS_LOG1
    WHERE
        PROCESS_LOG1.PROCESS_ID = 'STEP' || TIMETAMP_NO
)
SELECT
    'STEP' || PROCESS.PROCESS_NO AS PROCESS,
    COALESCE(LOG.CNT, 0) AS CNT
FROM
    PROCESS
LEFT JOIN (
    SELECT
        PROCESS_NO,
        COUNT(*) AS CNT
    FROM
        PROCESS_LOG2
    WHERE
        PROCESS_LOG2.PROCESS_NO = PROCESS_LOG2.TIMETAMP_NO
    GROUP BY
        PROCESS_LOG2.PROCESS_ID
    ORDER BY
        PROCESS_LOG2.PROCESS_ID
) AS
    LOG ON
    LOG.PROCESS_NO = PROCESS.PROCESS_NO;