PCSQL 모의고사 for Guest (4)

 

https://programmers.co.kr/app/with_setting/tests/120812/challenges/databases/166

 

 

하핫,, 정답을 맞추긴 했지만

혼자 쉬엄쉬엄 타닥거려서 그런지,,,

변수명 설정, 테이블명 설정, 가독성 좋게 하는 연습을 해야겠다

싶네요

 

with tab1 as(
    SELECT user_id, 
            s.problem_id,
            submitted,
            timestamp,
            if(submitted=correct_answer, 1, 0) as tada
    from submissions s join problems p
        on s.problem_id = p.problem_id
),
ordercnt as (
    select *,
        count(*) over (partition by user_id, problem_id order by timestamp) 'cnt'
    from tab1
)

select cnt as nth_submission,
    round(sum(tada) / count(*), 2) * 100 as correct_rate
from ordercnt
group by cnt
order by 1