PCSQL 모의고사 for Guest (5)

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

 

 

with correct_table as (
    select user_id,
        s.problem_id,
        timestamp,
        score,
        if(submitted=correct_answer, 1, 0) as correct
    from submissions s join problems p
        on s.problem_id = p.problem_id
)
, score_table1 as (
    select user_id,
           sum(score) as total_score
    from (select distinct user_id, problem_id, score
          from correct_table
          where correct = 1) a
    group by user_id
)
, score_table2 as (
    select u.user_id,
        ifnull(total_score, 0) total_score
    from (
        select distinct user_id
        from submissions) u left join score_table1 s
        on u.user_id = s.user_id
)
, time_table1 as (
    select user_id, problem_id,
        min(timestamp) as min_time
    from correct_table
    where correct = 1
    group by user_id, problem_id
)
, time_table2 as (
    select user_id, 
        max(min_time) as time
    from time_table1
    group by user_id 
)
, penalty_table1 as (
    select c.user_id,
        c.problem_id,
        timestamp,
        correct
    from correct_table c join time_table1 t1
        on c.user_id = t1.user_id 
        and c.problem_id = t1.problem_id 
    where correct = 0 
        and timestamp < min_time
)
, penalty_table2 as (
    select user_id,
        count(*) * 300 as penalty
    from penalty_table1
    group by user_id
)

select s2.user_id,
    total_score,
    ifnull(time, 0) + ifnull(penalty, 0) as time_taken
from score_table2 s2 left join time_table2 t2
    on s2.user_id = t2.user_id
    left join penalty_table2 p2
    on s2.user_id = p2.user_id
order by 2 desc, 3, 1