PCSQL 모의고사 5번 문제 풀이

사용 개념

• 윈도우 함수
  • 누적 합계 sum()
• 임시 테이블을 만들어 결합(JOIN)하는 테크닉
• 복잡한 CTE 설계
• 엣지 테스트 케이스 설계

문제 접근 및 정답

걸린 시간의 조건이 더 복잡하기 때문에 걸린 시간을 중점적으로 해결하려고 했음.

 

해당 문제가 정답인지 체크 -> 이전에 정답이었는지 누적합 방식 사용 -> 패널티 시간 계산

한 번도 맞춘적이 없는 문제에 대해서는 연산을 하지 않아야 하므로 맞춘 적이 있는 문제만 가져와서 패널티 시간과 점수를 연산한다.

-- SELECT * FROM submissions -- 유저의 제출 정보
-- SELECT * FROM problems -- 정답/점수

-- 과거에 정답이었는지 체크 , 이전에 맞춘 적이 있는지 체크
with is_prev_tbl as
(
    SELECT s.user_id, s.problem_id, s.submitted, s.timestamp, p.correct_answer, p.score,
        if(s.submitted = p.correct_answer, 1, 0) as is_correct, -- 해당 문제에 대해 정답이면 1
        sum(if(s.submitted = p.correct_answer, 1, 0)) OVER (PARTITION BY s.user_id, s.problem_id ORDER BY s.timestamp) as is_prev_correct -- 이전에 정답이었으면 1 이상
    FROM submissions s LEFT JOIN problems p ON s.problem_id = p.problem_id
),
--  + user_id, problem_id 별 패널티 시간 계산
add_time_tbl as
(
    SELECT *, 
        sum(if(submitted != correct_answer and is_prev_correct=0, 300, 0)) OVER (PARTITION BY user_id, problem_id ORDER BY timestamp) as add_time -- 오답이면서 이전에 맞춘 적이 없는 경우 추가시간 +300초
    FROM is_prev_tbl
),
-- 맞춘 적이 있는 문제만 조회
is_correct_true_tbl as
(
    SELECT user_id, problem_id
    FROM is_prev_tbl
    GROUP BY user_id, problem_id
    HAVING sum(is_correct) >= 1
),
-- 유저 별 맞춘 문제에 대해서 최초 정답 시간, 패널티 시간, 얻은 점수 표시
correct_answer_info as
(
    SELECT a.user_id, a.problem_id, min(timestamp) as 'submit_time', 
    		max(add_time) as 'penalty', max(score) as 'get_score'
    FROM add_time_tbl a INNER JOIN is_correct_true_tbl c ON a.user_id = c.user_id and a.problem_id = c.problem_id
    WHERE a.is_correct = 1
    GROUP BY a.user_id, a.problem_id
),
-- 제출은 했지만 정답을 맞추지 못한 사람은 점수 0점, 시간 0 (전체 유저 아이디 임시 테이블)
all_user_id as
(
    SELECT distinct user_id
    FROM submissions
)

-- 답안 제출한 적이 한 번이라도 있는 전체 유저 아이디는 모두 살려서 LEFT JOIN 한다.
-- 시간은 패널티 시간 + 각 유저가 문제 당 처음으로 문제를 맞춘 시간을 연산한다. 
-- 점수는 맞춘 문제의 score을 모두 합한다.

SELECT a.user_id, 
    if(sum(c.get_score) is null, 0, sum(c.get_score)) as 'TOTAL_SCORE', 
    if((max(c.submit_time)+sum(c.penalty)) is null, 0, (max(c.submit_time)+sum(c.penalty))) as 'TIME_TAKEN'
FROM all_user_id a LEFT JOIN correct_answer_info c ON a.user_id = c.user_id
GROUP BY a.user_id
ORDER BY 2 desc, 3 asc, 1 asc

 

엣지 케이스

만약 채점 결과 테스트 1~4는 통과가 되고 5~8은 통과가 되지 않는다면,

같은 유저가 이미 맞춘 문제에 또 맞췄을 경우 시간이 어떻게 되는지 고려해야 한다.

Ex.

USER_ID가 101이고, PROBLEM_ID가 1인 문제에 1456 시간에 정답을 맞췄고,

USER_ID가 101이고, PROBLEM_ID가 1인 문제에 1458 시간에 또 제출을 하여 정답을 맞췄다면,

 

최초로 정답을 맞춘 시각인 1456 에 패널티 시간을 더해주어야 한다.

max()와 min()을 조심하자.