[Programmers] PCSQL 샘플문제 4번 (MYSQL)

-- 코드를 입력하세요
-- user별 점수, 시간 저장한 테이블.
WITH CORRECT AS (SELECT DISTINCT A.USER_ID,
IFNULL(MAX(CASE WHEN B.SCORE > 0 THEN A.TIMESTAMP END),0) AS TIME, IFNULL(SUM(B.SCORE), 0) AS SCORE
FROM SUBMISSIONS A LEFT OUTER JOIN PROBLEMS B ON A.PROBLEM_ID = B.PROBLEM_ID AND A.SUBMITTED = B.CORRECT_ANSWER
GROUP BY A.USER_ID)

-- 정답만 따로 정리한 테이블
, TIMES AS (SELECT DISTINCT A.USER_ID, A.PROBLEM_ID, A.TIMESTAMP
FROM SUBMISSIONS A JOIN PROBLEMS B ON A.PROBLEM_ID = B.PROBLEM_ID AND A.SUBMITTED = B.CORRECT_ANSWER)

-- 틀린 횟수 카운트할 테이블
, WRONG AS (SELECT A.USER_ID, COUNT(A.USER_ID) AS CNT
FROM SUBMISSIONS A JOIN TIMES B ON A.USER_ID = B.USER_ID AND A.PROBLEM_ID = B.PROBLEM_ID
WHERE A.TIMESTAMP < B.TIMESTAMP
GROUP BY USER_ID)

-- 출력할 테이블
SELECT A.USER_ID, A.SCORE AS TOTAL_SCORE, SUM(A.TIME + IFNULL(B.CNT, 0)*300) AS TIME_TAKEN
FROM CORRECT A LEFT OUTER JOIN WRONG B ON A.USER_ID = B.USER_ID
GROUP BY A.USER_ID
ORDER BY TOTAL_SCORE DESC, TIME_TAKEN, USER_ID;

 

 

예제는 성공했고, 

테스트케이스 1번 3번 빼고 다 실패 .. !

어디가 문제인지 조금 더 찾아봐야 할 것 같다 ㅜㅜ

정답 제공해주세요 ....