[SQL 코딩테스트] 해커랭크 Basic Join 정답

Population Census, African Cities, Average Population of Each Continent
⏰ 3:53

 

-- Population Census
SELECT SUM(C1.POPULATION)
FROM CITY C1
     JOIN COUNTRY C2
     ON C1.COUNTRYCODE = C2.CODE
WHERE C2.CONTINENT = 'Asia'

-- African Cities
SELECT C1.NAME
FROM CITY C1
     JOIN COUNTRY C2
     ON C1.COUNTRYCODE = C2.CODE
WHERE C2.CONTINENT = 'Africa'

-- Average Population of Each Continent
SELECT C2.CONTINENT, FLOOR(AVG(C1.POPULATION))
FROM CITY C1
     JOIN COUNTRY C2
     ON C1.COUNTRYCODE = C2.CODE
GROUP BY 1

 

The Report
⏰ 4:34

 

JOIN의 ON절에서 BETWEEN과 같은 다른 조건을 쓸 수 있다는 걸 다시 한 번 기억하게 해준 문제.

SELECT CASE WHEN G.GRADE < 8 THEN NULL ELSE S.NAME END AS NAME, 
        G.GRADE, S.MARKS
FROM STUDENTS S
     JOIN GRADES G
     ON S.MARKS BETWEEN G.MIN_MARK AND G.MAX_MARK
ORDER BY 2 DESC, 1, 3

 

Top Competitors
⏰ 34:48

 

와. 이건 각 표를 조인하는 순서 때문에 한참 헤맸다. 아직도 왜 틀렸는지 모르겠다!!!

 

여기서 다른 건 해커 아이디로 묶을 때 조인하는 테이블이 달랐다는 건데... 이것 때문에 무슨 차이가 발생하는 걸까? 

[정답] H.HACKER_ID = S.HACKER_ID

[오답] H.HACKER_ID = C.HACKER_ID

 

→ 알았다... 표 설명을 제대로 안 읽은 탓이었다. 

     C.HACKER_ID 는 그 챌린지를 '만든 사람'의 아이디였다...^^

     항상 각 DB와 칼럼 설명부터 꼼꼼히 읽자!!!!!!!!

 

[정답]

SELECT H.hacker_id, H.name
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 C.difficulty_level = D.difficulty_level AND D.SCORE = S.SCORE
GROUP BY H.hacker_id, H.name
HAVING COUNT(H.hacker_id) > 1
ORDER BY COUNT(H.hacker_id) DESC, H.hacker_id

 

[오답]

SELECT H.hacker_id, H.name
FROM Hackers H
     JOIN Challenges C
     ON H.hacker_id = C.hacker_id
     JOIN Difficulty D
     ON C.difficulty_level = D.difficulty_level 
     JOIN Submissions S
     ON C.challenge_id = S.challenge_id AND D.SCORE = S.SCORE
GROUP BY 1,2
HAVING COUNT(H.hacker_id) > 1
ORDER BY COUNT(H.hacker_id) DESC, H.hacker_id

 

Ollivander's Inventory
⏰ 01:35:32

 

헤매고 헤맨 이번 문제... 그래도 새로운 활용법도 알게 되고 재밌었다.

 

윈도우함수를 이용해 간단하게 풀어보려 했는데,

GROUP BY에 함께 활용되면 안되는 ID 열도 COINS_NEEDED 열과 함께 매칭되어 걸러져야 한다.

 

WITH구문으로 풀어보려고 기를 썼으나... 도저히 왜 틀렸는지 알 수 없게 계속 오류가 나서 게시판을 살펴보다가 MSSQL로 돌리면 문제없이 정답으로 인정된다는 말을 보고 해보니 정말 그랬다.

왜 MYSQL에서는 안되는지??? 다른 때 항상 써왔던 구문들 뿐이라 이건 문제 사이트의 오류라고밖에는 생각이 안 되는데... 이유가 있다면 누군가 이유를 알려줬으면 좋겠다.

 

[정답 1] WITH 구문 활용해 GROUP BY로 MIN(코인) 만 걸러준 후, 전체를 다시 JOIN → 세 값이 모두 일치하는 ID 출력

WITH CHEAPEST_WANDS AS (
SELECT P.AGE
       ,W.POWER
       ,MIN(COINS_NEEDED) AS MIN_COINS_NEEDED
FROM WANDS W 
     JOIN WANDS_PROPERTY P
     ON W.CODE = P.CODE 
WHERE P.IS_EVIL = 0
GROUP BY P.AGE, W.POWER
)

SELECT W.ID, P.AGE, C.MIN_COINS_NEEDED AS COINS_NEEDED, W.POWER
FROM WANDS W
     JOIN WANDS_PROPERTY P
     ON W.CODE = P.CODE 
     JOIN CHEAPEST_WANDS C
     ON P.AGE = C.AGE AND W.COIN_NEEDED = C.MIN_COINS_NEEDED AND W.POWER = C.POWER
WHERE P.IS_EVIL = 0
ORDER BY W.POWER DESC, WP.AGE DESC

 

그리고 게시판을 둘러보다가 발견한 서브쿼리 WHERE절의 새로운 사용법.

이건 혁신이야... 잘 익혀두면 좋을 것 같다. 너무 신기해서 챗GPT한테 구동 순서와 작동 원리도 더 캐물어봄.

 

+ 누군가 WHERE 대신 GROUP BY로 묶으면 왜 안되는지 묻자,

본쿼리의 WHERE절에서 일치 여부를 확인하기 위해서는 하나의 값만 출력되어야 하는데 여러 값이 출력되어 오류가 발생한다고 했다. 대신에 본쿼리의 값과 서브쿼리의 값이 같은지 확인해 하나씩 계속 출력할 수 있게 하는 건가보다!

(그럼 GROUP BY 한 다음에 본쿼리에서 WHERE ~ IN 을 사용하면 안되는걸까..? 해봤는데 역시 안된다. 다음에 IN 관련해서 더 깊이 익힐 기회가 있길!)

 

[정답 2] WHERE 절에서 서브쿼리 사용 후, 서브쿼리의 WHERE절에 본쿼리와 동일 값을 출력하는 조건문 넣어주기

select w.id, p.age, w.coins_needed, w.power 
from Wands as w 
	 join Wands_Property as p 
     on (w.code = p.code) 
where p.is_evil = 0 and 
	  w.coins_needed = (
      	select min(coins_needed) 
        from Wands as w1 
        	 join Wands_Property as p1 
             on (w1.code = p1.code) 
        where w1.power = w.power and p1.age = p.age
        )
order by w.power desc, p.age desc

올바르게 이해했다고 인정받음 (뿌듯)

 

[오답] ID도 COINS_NEEDED 열과 함께 매칭되어 걸러져야 하는데, 그러지 못한 답

SELECT ID, AGE
       , MIN(COINS_NEEDED) OVER (PARTITION BY AGE, POWER)
       , POWER
FROM WANDS W 
     LEFT JOIN WANDS_PROPERTY P
     ON W.CODE = P.CODE AND IS_EVIL = 0
GROUP BY 4,2
ORDER BY 4 DESC, 2 DESC

 

Challenges
⏰ 40:35

 

1위 외에는 걸러줄 수 있도록 WITH절에서 미리 RANK 열을 만들어준다.

 

*본쿼리 WHERE절의 서브쿼리에서, SELECT에서는 CNT만 뽑지만 GROUP BY에서 CNT와 RNK를 언급해 묶어주고 있는데, 이 때 여기서 언급해주지 않은 열은 HAVING 절에서 조건으로 활용할 수 없다.

이것 때문에 헤매다가 시간이 오래 걸렸음!

WITH CTE AS (
SELECT hacker_id, name, COUNT(CHALLENGE_ID) AS CNT
      ,dense_rank() over(order by count(CHALLENGE_ID) DESC) as RNK
FROM HACKERS H
     JOIN CHALLENGES C
     USING (HACKER_ID)
GROUP BY hacker_id, name
ORDER BY 3 DESC
    )
    
SELECT hacker_id, name, CNT
FROM CTE
WHERE CNT NOT IN (SELECT CNT FROM CTE
                  GROUP BY CNT, RNK
                  HAVING (COUNT(CNT) > 1 AND RNK <> 1))
ORDER BY 3 DESC, 1

 

Contest Leaderboard
⏰ 15:05

 

해커랭크는 이상하게 MYSQL에서 WITH구문을 쓰면 이유를 알 수 없는 오류가 뜰 때가 많다.

이럴 때 그냥 MSSQL SERVER로 바꿔서 넣어보면 정답인 경우가 많다... (내 시간 돌려줘~!)

 

근데 MSSQL에서는 아래와 같이 살짝 불편한 부분이 있어 함께 수정해줘야 한다.

 

1) GROUP BY나 ORDER BY 절에서 칼럼 번호를 부를 수가 없음

2) JOIN 시 USING 구문을 허용하지 않음

WITH CTE AS (
SELECT H.HACKER_ID, NAME, CHALLENGE_ID, MAX(SCORE) AS MAX_SCORE
      FROM HACKERS H
      JOIN SUBMISSIONS S
      ON H.HACKER_ID = S.HACKER_ID
      GROUP BY H.HACKER_ID, NAME, CHALLENGE_ID
      )
      
SELECT HACKER_ID, NAME, SUM(MAX_SCORE)
FROM CTE
GROUP BY HACKER_ID, NAME
HAVING SUM(MAX_SCORE) <> 0
ORDER BY SUM(MAX_SCORE) DESC, HACKER_ID

 

 

---

해커랭크는 전반적으로 이상한 걸로 자꾸 오류가 나서 시간을 많이 잡아먹게 하는 경우가 많았다.흑흑.

얼른 끝까지 뿌셔버리고 다시 돌아오지 않을테야. 

 

어느새 SQL 문제풀이를 매일 시작한지도 두 달이 다 되어간다. 꾸준함의 힘을 믿으며. 화이팅!