문제 이해

↑Challenges table

문제 이해 포인트

• 최종 출력 컬럼: 1개 초과의 풀 스코어를 달성한 해커만 해커 아이디와 해커 이름을 출력
  (풀 스코어 : 챌린지 id의 어려움 난이도에 맞는 점수와 실제 받은 점수가 일치하는 경우)
• 정렬 방법:
  - 1. 총 풀 스코어를 받은 챌린지의 갯수의 내림차순 정렬
  2. 풀 스코어의 갯수가 동일 할 경우를 대비하여 해커 이름으로 오름차순 정렬

---

생각 정리
1.Submission 테이블에서 해커 이름 붙이기
2. submission 테이블의 challenge_id에 맞는 score 가져오기
ㄴ difficulty_table 사용
3. 1,2에서 합친 테이블에서 s.score와 c.score가 동일한 rows 가져오기 / challenge 수 카운팅 하기
4. 3번의 테이블을 해커아이디, 이름으로 그룹핑 + 정렬

---

첫 오답
WITH main AS
(SELECT S.hacker_id as hacker_id,
S.score as score_01,
S.challenge_id as challenge_id,
H.name as name,
C.difficulty_level as difficulty_name,
D.score as score_02
FROM Submissions S
JOIN Hackers H ON H.hacker_id = S.hacker_id
JOIN Challenges C ON C.challenge_id = S.challenge_id
JOIN Difficulty D ON D.difficulty_level = C.difficulty_level)
SELECT hacker_id,
name,
count(challenge_id)
FROM main
WHERE score_01 = score_02
GROUP BY 1,2
ORDER BY count(challenge_id) DESC, 1

• 1개 초과의 challenge id를 세는 조건을 만들지 않음(having 조건 부재)

---

정답

WITH main AS (
SELECT
S.hacker_id AS hacker_id,
S.score AS score_01,
S.challenge_id AS challenge_id,
H.name AS name,
C.difficulty_level AS difficulty_name,
D.score AS score_02
FROM Submissions S
JOIN Hackers H ON H.hacker_id = S.hacker_id
JOIN Challenges C ON C.challenge_id = S.challenge_id
JOIN Difficulty D ON D.difficulty_level = C.difficulty_level
), main02 AS (
SELECT
hacker_id,
name,
COUNT(challenge_id) AS challenge_count
FROM main
WHERE score_01 = score_02 --- 조건 1 (전체 테이블)
GROUP BY hacker_id, name
HAVING COUNT(challenge_id) > 1 -- 조건 2 (그룹핑 된 후의 조건)
)
SELECT
hacker_id,
name
FROM main02
ORDER BY
challenge_count DESC,-- 정렬 조건 1
hacker_id -- 정렬 조건 2

---

궁금 한 점

1. as 별칭을 사용하지 않을때 with 서브 쿼리 다음의 쿼리에서는 컬럼을 어떻게 정의 해야 하는지
   ㄴ with 서브쿼리절 테이블의 이름으로 쓰면 됨
   (다만, score 처럼 동일한 이름을 사용하는 경우는 as 별칭을 적어줘야 중복을 피하고, 원하는 컬럼을 정확히 가져 올 수 있음)
   

2. 최종 쿼리에서 order by를 하는 구체적인 사유가 궁금

• 최종 결과를 정렬
• 성능 최적화(cte 절에서 사용시 불필요한 정렬이 추가 되어 성능에 영향)
• 구조 단순화