프로그래머스 PCSQL 모의고사(샘플 개인 풀이)

sql쿼리 각 절의 우선 순위

from >>> where >>> select >>> order by >>> group by >>> having >>> limit(이절은 order by 이후 실행)

 

sql을 잘다루지 못해 쿼리가 다소 복잡하게 보이실수도 있는점 양해부탁드립니다... ㅎ.. ㅎ....ㅎㅎ

 

 

문제 1

OLD_POSTS 테이블 : 게시글 정보

 

NEW_POSTS 테이블 : 새로운 게시글 정보

 

문제 

 

나의 쿼리

SELECT *, 0 as LIKES FROM OLD_POSTS # 좋아요 0 붙여주기
UNION # 합집합
SELECT * FROM NEW_POSTS
ORDER BY ID desc # id 기준 내림차순

 

 

문제 2

PROBLEMS 테이블 : 알고리즘 기출문제 정보

 

문제 

 

나의 쿼리

SELECT ALGORITHM_TYPE
FROM PROBLEMS
GROUP BY ALGORITHM_TYPE
ORDER BY COUNT(*) desc, ALGORITHM_TYPE # 가장많이 출시된순, 알고리즘 사전순 정렬
LIMIT 1 # 맨 첫번째 행 가져오기

 

 

문제 3

RESERVATION 테이블 : 회의실 예약 요청 정보

 

문제 

 

나의 쿼리

SELECT id, start_time, end_time
FROM RESERVATION
WHERE id not in 
(
SELECT a.id # 예약이 불가능한 아이디 뽑아내기
FROM RESERVATION a
JOIN RESERVATION b
ON a.id>b.id
GROUP BY id
HAVING sum(
CASE
    WHEN b.start_time>=a.end_time or b.end_time<=a.start_time THEN 0
    ELSE 1
END)>0
)
ORDER BY start_time

 

 

문제 4

SUMBMISSIONS 테이블 : 유저 제출 정보

 

PROBLEMS 테이블 : 정답 점수

 

문제

 

나의 쿼리

SELECT c.problem as NTH_SUBMISSION, round((sum(c.ck='ok')/count(*))*100,0) as CORRECT_RATE
FROM
(
SELECT a.problem, 
CASE
    WHEN a.submitted=b.correct_answer THEN 'ok'
    ELSE 'no'
END AS ck
FROM
(SELECT problem_id, submitted,
ROW_NUMBER() OVER(PARTITION BY USER_ID, PROBLEM_ID ORDER BY TIMESTAMP) AS problem
FROM SUBMISSIONS) a
JOIN 
PROBLEMS b
ON a.problem_id=b.problem_id
) c
GROUP BY c.problem
ORDER BY NTH_SUBMISSION

 

 

문제 5

SUMBMISSIONS 테이블 : 유저 제출 정보

 

PROBLEMS 테이블 : 정답 점수

 

문제

 

나의 쿼리

SELECT r.user_id, sum(r.score) as TOTAL_SCORE, 
CASE
    WHEN (max(timestamp)+sum(pk)) is null THEN 0
    ELSE (max(timestamp)+sum(pk))
END as TIME_TAKEN
FROM
(
SELECT q.user_id, q.problem_id, w.timestamp,
CASE
	WHEN w.timestamp is null THEN 0
	ELSE q.score
END AS score,
CASE
	WHEN w.timestamp is not null THEN sum(ck='no')*300
	ELSE 0
END AS pk
FROM
(
SELECT a.user_id, a.problem_id, a.timestamp, b.score,
CASE
    WHEN a.submitted=b.correct_answer THEN 'ok'
    ELSE 'no'
END AS ck
FROM SUBMISSIONS a
JOIN PROBLEMS b
ON a.problem_id=b.problem_id
) q
LEFT JOIN
(
SELECT c.user_id, c.problem_id, min(c.timestamp) as timestamp
FROM
(SELECT a.user_id, a.problem_id, a.timestamp, b.score,
CASE
    WHEN a.submitted=b.correct_answer THEN 'ok'
    ELSE 'no'
END AS ck
FROM SUBMISSIONS a
JOIN PROBLEMS b
ON a.problem_id=b.problem_id) c
WHERE c.ck='ok'
GROUP BY c.user_id, c.problem_id
) w
ON q.user_id=w.user_id and q.problem_id=w.problem_id
WHERE w.timestamp is null or q.timestamp<=w.timestamp
GROUP BY user_id, problem_id
) r
GROUP BY user_id
ORDER BY TOTAL_SCORE desc, TIME_TAKEN, user_id