ソースコード
            with history AS
            (
            select
            		session_id 
            	,lag(process_id) OVER (partition by session_id order by ex_timestamp) bef
            	,process_id 
            	,lead(process_id, 1) OVER (partition by session_id order by ex_timestamp, session_id) P2
            	,lead(process_id, 2) OVER (partition by session_id order by ex_timestamp, session_id) P3
            	,lead(process_id, 3) OVER (partition by session_id order by ex_timestamp, session_id) P4
            	,lead(process_id, 4) OVER (partition by session_id order by ex_timestamp, session_id) P5
            from
            	process_log
            group by 
            	session_id, process_id
            ), result as (
            select process_id  PROCESS, count(session_id) CNT FROM history where bef is null AND  process_id = 'STEP1'
            UNION ALL
            select P2 PROCESS, count(session_id) CNT FROM history where bef is null AND process_id = 'STEP1' AND P2 = 'STEP2'
            UNION ALL
            select P3 PROCESS, count(session_id) CNT FROM history where bef is null AND process_id = 'STEP1' AND P2 = 'STEP2' AND P3 = 'STEP3'
            UNION ALL
            select P4 PROCESS, count(session_id) CNT FROM history where bef is null AND process_id = 'STEP1' AND P2 = 'STEP2' AND P3 = 'STEP3' AND P4 = 'STEP4'
            UNION ALL
            select P5 PROCESS, count(session_id) CNT FROM history where bef is null AND process_id = 'STEP1' AND P2 = 'STEP2' AND P3 = 'STEP3' AND P4 = 'STEP4' AND P5 = 'STEP5'
            ) 
            select PROCESS, NULLIF(CNT, 0) CNT FROM result  where process is not null order by 1
            ;
提出情報
提出日時2023/12/17 16:35:59
コンテスト第10回 SQLコンテスト
問題顧客行動分析
受験者Kojiron
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量100 MB
メッセージ
テストケース(通過数/総数)
3/4
状態
メモリ使用量
データパターン1
AC
89 MB
データパターン2
AC
87 MB
データパターン3
AC
96 MB
データパターン4
WA
100 MB