[공부로그] SQL HackerRank 문제 풀이 (Basic Join)

Q. Population Census

SELECT SUM(CITY.POPULATION)
FROM CITY JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE CONTINENT = 'ASIA'

Q. African Cities

SELECT CITY.NAME
FROM CITY JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE CONTINENT = 'AFRICA'

Q. Average Population of Each Continent

SELECT COUNTRY.CONTINENT, FLOOR(AVG(CITY.POPULATION))
FROM CITY JOIN COUNTRY ON CITY.COUNTRYCODE = COUNTRY.CODE
GROUP BY COUNTRY.CONTINENT

올림 CEILING 내림 FLOOR 반올림 ROUND( ,자릿수) 1이면 소숫점 첫째자리까지 -1이면 10까지

Q. The Report

SELECT S.NAME, G.GRADE, S.MARKS
FROM STUDENTS S JOIN GRADES G ON S.MARKS BETWEEN G.MIN_MARK AND G.MAX_MARK
WHERE G.GRADE >= 8
ORDER BY G.GRADE DESC, S.NAME;

SELECT NULL, G.GRADE, S.MARKS
FROM STUDENTS S JOIN GRADES G ON S.MARKS BETWEEN G.MIN_MARK AND G.MAX_MARK
WHERE G.GRADE < 8
ORDER BY G.GRADE DESC, S.MARKS

JOIN을 =이 아닌 BETWEEN으로도 할 수 있다!!

Q. Top Competitors

1번 이상 최고점을 맞은 애들 뽑기

SELECT H.HACKER_ID, H.NAME
FROM SUBMISSIONS S JOIN HACKERS H ON S.HACKER_ID = H.HACKER_ID
JOIN CHALLENGES C ON S.CHALLENGE_ID = C.CHALLENGE_ID
JOIN DIFFICULTY D ON C.DIFFICULTY_LEVEL = D.DIFFICULTY_LEVEL
GROUP BY H.HACKER_ID, H.NAME
HAVING COUNT(S.SCORE = D.SCORE) > 1
ORDER BY COUNT(S.SCORE = D.SCORE) DESC, H.HACKER_ID

첨에 이렇게 했는데 틀렸다 ㅠㅠㅠㅠ

SELECT H.HACKER_ID, H.NAME
FROM SUBMISSIONS S JOIN HACKERS H ON S.HACKER_ID = H.HACKER_ID
JOIN CHALLENGES C ON S.CHALLENGE_ID = C.CHALLENGE_ID
JOIN DIFFICULTY D ON C.DIFFICULTY_LEVEL = D.DIFFICULTY_LEVEL
WHERE S.SCORE = D.SCORE
GROUP BY H.HACKER_ID, H.NAME
HAVING COUNT(S.CHALLENGE_ID) > 1
ORDER BY COUNT(S.CHALLENGE_ID) DESC, H.HACKER_ID

Q. Ollivander's Inventory

Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that Ron's interested in, sorted in order of descending power. If more than one wand has same power, sort the result in order of descending age.
1. 악한 것 제외
2. 나이, 파워가 중복되는 애들 중에 가장 값이 싼 거 고르기
3. 순서대로~~

우선 나이, 파워가 같고 악한 것을 제외하고 가장 싼 가격을 골라보자.

SELECT P1.AGE AGE, W1.POWER POWER, MIN(COINS_NEEDED) MINCOIN
FROM WANDS W1 JOIN WANDS_PROPERTY P1 ON W1.CODE = P1.CODE
WHERE P1.IS_EVIL = 0
GROUP BY P1.AGE, W1.POWER

그리고 얘를 테이블로 넣는다.

SELECT W.ID, P.AGE, W.COINS_NEEDED, W.POWER
FROM WANDS W JOIN WANDS_PROPERTY P ON W.CODE = P.CODE
	JOIN (SELECT P1.AGE AGE, W1.POWER POWER, MIN(COINS_NEEDED) MINCOIN
		FROM WANDS W1 JOIN WANDS_PROPERTY P1 ON W1.CODE = P1.CODE
		WHERE P1.IS_EVIL = 0
		GROUP BY P1.AGE, W1.POWER) AS M 
 	ON M.AGE = P.AGE AND M.POWER = W.POWER
WHERE COINS_NEEDED = M.MINCOIN

그리고 ORDER BY 절을 만족해준다.

SELECT W.ID, P.AGE, W.COINS_NEEDED, W.POWER
FROM WANDS W JOIN WANDS_PROPERTY P ON W.CODE = P.CODE
	JOIN (SELECT P1.AGE AGE, W1.POWER POWER, MIN(COINS_NEEDED) MINCOIN
		FROM WANDS W1 JOIN WANDS_PROPERTY P1 ON W1.CODE = P1.CODE
		WHERE P1.IS_EVIL = 0
		GROUP BY P1.AGE, W1.POWER) AS M 
 	ON M.AGE = P.AGE AND M.POWER = W.POWER
WHERE COINS_NEEDED = M.MINCOIN
ORDER BY W.POWER DESC, P.AGE DESC

그룹바이할때 항상 그 기준이 되는 것은 그냥 적어도 되지만 나머지는 집계 함수 안에 넣어야 하는 것! 그리고 서브쿼리를 테이블로 만들어서 SELECT할 수 있는 것!!

SELECT W.id, P.age, W.coins_needed, W.power
FROM Wands W
 INNER JOIN Wands_Property P ON W.code = P.code
WHERE P.is_evil = 0
AND W.coins_needed = (SELECT MIN(W1.coins_needed)
                      FROM Wands W1
                       INNER JOIN Wands_Property P1 ON W1.code = P1.code
                      WHERE P1.is_evil = 0 
                      AND W1.power = W.power
                      AND P1.age = P.age)
ORDER BY W.power DESC, P.age DESC

https://techblog-history-younghunjo1.tistory.com/165
이런식으로 바깥의 컬럼을 서브쿼리 안으로 불러와서 푸신 분도 있어서 신기했다.

Q. Challenges

Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.
1. hacker_id, name, and the total number of challenges created by each student. >> 학생별로 그룹바이 해야할듯
2. total number of challenges in descending order. sort the result by hacker_id. >> ORDER BY로 해결!
3. 같은 개수의 챌린지를 만들었는데 이 수가 최대의 수가 아니면 제외 >> HAVING절

우선 학생별 챌린지 개수를 뽑자

SELECT H.HACKER_ID ID, COUNT(C.CHALLENGE_ID) CNUM 
FROM HACKERS H JOIN CHALLENGES C ON H.HACKER_ID = C.HACKER_ID 
GROUP BY H.HACKER_ID

중복이 아닌 것만 남기자

SELECT CNUM
FROM
(SELECT H.HACKER_ID, ID COUNT(C.CHALLENGE_ID) CNUM 
FROM HACKERS H JOIN CHALLENGES C ON H.HACKER_ID = C.HACKER_ID 
GROUP BY H.HACKER_ID)
GROUP BY CNUM 
HAVING COUNT(*) = 1

최대값도 찾아주자

SELECT MAX(CNUM)
FROM
(SELECT H.HACKER_ID, ID COUNT(C.CHALLENGE_ID) CNUM 
FROM HACKERS H JOIN CHALLENGES C ON H.HACKER_ID = C.HACKER_ID 
GROUP BY H.HACKER_ID)

다음에 이것들을 HAVING절에 넣어준다.

SELECT H.HACKER_ID, H.NAME, COUNT(C.CHALLENGE_ID) CHALLENGES_CREATED
FROM HACKERS H JOIN CHALLENGES C ON H.HACKER_ID = C.HACKER_ID
GROUP BY H.HACKER_ID, H.NAME
HAVING CHALLENGES_CREATED IN (
        SELECT CNUM
        FROM (
            SELECT H.HACKER_ID ID, COUNT(C.CHALLENGE_ID) CNUM 
            FROM HACKERS H JOIN CHALLENGES C ON H.HACKER_ID = C.HACKER_ID 
            GROUP BY H.HACKER_ID
            ) A
        GROUP BY CNUM 
        HAVING COUNT(*) = 1
        )
    OR CHALLENGES_CREATED = (
        SELECT MAX(CNUM)
        FROM (
            SELECT H.HACKER_ID ID, COUNT(C.CHALLENGE_ID) CNUM 
            FROM HACKERS H JOIN CHALLENGES C ON H.HACKER_ID = C.HACKER_ID 
            GROUP BY H.HACKER_ID
            ) B
        )

ORDER BY로 정렬

SELECT H.HACKER_ID, H.NAME, COUNT(C.CHALLENGE_ID) CHALLENGES_CREATED
FROM HACKERS H JOIN CHALLENGES C ON H.HACKER_ID = C.HACKER_ID
GROUP BY H.HACKER_ID, H.NAME
HAVING CHALLENGES_CREATED IN (
        SELECT CNUM
        FROM (
            SELECT H.HACKER_ID ID, COUNT(C.CHALLENGE_ID) CNUM 
            FROM HACKERS H JOIN CHALLENGES C ON H.HACKER_ID = C.HACKER_ID 
            GROUP BY H.HACKER_ID
            ) A
        GROUP BY CNUM 
        HAVING COUNT(*) = 1
        )
    OR CHALLENGES_CREATED = (
        SELECT MAX(CNUM)
        FROM (
            SELECT H.HACKER_ID ID, COUNT(C.CHALLENGE_ID) CNUM 
            FROM HACKERS H JOIN CHALLENGES C ON H.HACKER_ID = C.HACKER_ID 
            GROUP BY H.HACKER_ID
            ) B
        )
ORDER BY CHALLENGES_CREATED DESC, HACKER_ID

Q. Contest Leaderboard

The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of 0 from your result.
1. print the hacker_id, name, and total score of the hackers >> GROUP BY
2. ordered by the descending score, ascending hacker_id >> ORDER BY
3. 전체 점수가 0인애들 제외 >> HAVING

그런데 큰 문제는 그냥 SUM을 쓰면 안된다는 것이다. 한 사람이 같은 문제를 여러번 풀기 때문에 같은 문제 같은 사람 일 때 최대값만 합쳐주어야 한다.

사람과 문제로 그룹바이하여 최대 점수만 남기는 테이블을 만든다.

SELECT HACKER_ID , CHALLENGE_ID, MAX(SCORE) MAXSCORE
FROM SUBMISSIONS 
GROUP BY HACKER_ID , CHALLENGE_ID

해당 테이블을 사용하여서 SUM을 계산한다.

SELECT SUB.HACKER_ID, H.NAME, SUM(SUB.MAXSCORE)
FROM (
    SELECT HACKER_ID , CHALLENGE_ID, MAX(SCORE) MAXSCORE
    FROM SUBMISSIONS 
    GROUP BY HACKER_ID , CHALLENGE_ID
    ) SUB 
    JOIN HACKERS H ON SUB.HACKER_ID = H.HACKER_ID
GROUP BY SUB.HACKER_ID, H.NAME
HAVING SUM(SUB.MAXSCORE) != 0
ORDER BY SUM(SUB.MAXSCORE) DESC, SUB.HACKER_ID