ソースコード
with tmp0 as (
    select
        session_id,
        process_id,
        lag(process_id, 1) over (
            partition by session_id
            order by ex_timestamp asc
        ) as prev_process_id,
        ex_timestamp
    from process_log
),
tmp1 as (
    select
        session_id,
        process_id,
        case
            when process_id = 'STEP1' and prev_process_id is null then 1
            when process_id = 'STEP2' and prev_process_id = 'STEP1' then 1
            when process_id = 'STEP3' and prev_process_id = 'STEP2' then 1
            when process_id = 'STEP4' and prev_process_id = 'STEP3' then 1
            when process_id = 'STEP5' and prev_process_id = 'STEP4' then 1
            else 0
        end as valid_process,
        ex_timestamp
    from tmp0
),
tmp2 as (
    select
        session_id,
        process_id,
        min(valid_process) over (
            partition by session_id
            order by ex_timestamp asc
        ) as min_valid_process,
        ex_timestamp
    from tmp1
)
select
    process_id as PROCESS,
    count(session_id) as CNT
from tmp2
where min_valid_process = 1
group by process_id
提出情報
提出日時2023/12/17 22:31:06
コンテスト第10回 SQLコンテスト
問題顧客行動分析
受験者toshikish
状態 (詳細)WA
(Wrong Answer: 誤答)
メモリ使用量92 MB
メッセージ
テストケース(通過数/総数)
3/4
状態
メモリ使用量
データパターン1
AC
84 MB
データパターン2
AC
89 MB
データパターン3
AC
86 MB
データパターン4
WA
92 MB