ソースコード


with step1 as (
  select PROCESS_ID, SESSION_ID,EX_TIMESTAMP
  from PROCESS_LOG main
  where PROCESS_ID = 'STEP1'
  and   not exists (select * from  PROCESS_LOG where SESSION_ID = main.SESSION_ID and EX_TIMESTAMP < main.EX_TIMESTAMP)
),
step2 as (
  select PROCESS_ID, SESSION_ID,EX_TIMESTAMP
  from PROCESS_LOG main
  where PROCESS_ID = 'STEP2'
  and   exists ( select * from step1 where SESSION_ID = main.SESSION_ID and EX_TIMESTAMP < main.EX_TIMESTAMP)
  and   not exists ( select * from PROCESS_LOG where SESSION_ID = main.SESSION_ID and EX_TIMESTAMP < main.EX_TIMESTAMP and PROCESS_ID in ('STEP3','STEP4','STEP5'))
),
step3 as (
  select PROCESS_ID, SESSION_ID,EX_TIMESTAMP
  from PROCESS_LOG main
  where PROCESS_ID = 'STEP3'
  and   exists ( select * from step2 where SESSION_ID = main.SESSION_ID and EX_TIMESTAMP < main.EX_TIMESTAMP)
  and   not exists ( select * from PROCESS_LOG where SESSION_ID = main.SESSION_ID and EX_TIMESTAMP < main.EX_TIMESTAMP and PROCESS_ID in ('STEP4','STEP5'))
),
step4 as (
  select PROCESS_ID, SESSION_ID,EX_TIMESTAMP
  from PROCESS_LOG main
  where PROCESS_ID = 'STEP4'
  and   exists ( select * from step3 where SESSION_ID = main.SESSION_ID and EX_TIMESTAMP < main.EX_TIMESTAMP)
  and   not exists ( select * from PROCESS_LOG where SESSION_ID = main.SESSION_ID and EX_TIMESTAMP < main.EX_TIMESTAMP and PROCESS_ID in ('STEP5'))
),
step5 as (
  select PROCESS_ID, SESSION_ID,EX_TIMESTAMP
  from PROCESS_LOG main
  where PROCESS_ID = 'STEP5'
  and   exists ( select * from step4 where SESSION_ID = main.SESSION_ID and EX_TIMESTAMP < main.EX_TIMESTAMP)
)
select 'STEP1', count(1) from step1
union all
select 'STEP2', count(1) from step2
union all
select 'STEP3', count(1) from step3
union all
select 'STEP4', count(1) from step4
union all
select 'STEP5', count(1) from step5
;



提出情報
提出日時2024/04/28 18:13:26
コンテスト第10回 SQLコンテスト
問題顧客行動分析
受験者yunyun8686
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量84 MB
メッセージ
テストケース(通過数/総数)
0/4
状態
メモリ使用量
データパターン1
WA
84 MB
データパターン2
WA
84 MB
データパターン3
WA
84 MB
データパターン4
WA
84 MB