HackerRank: SQL 풀이(41~45)

SeongGyun Hong·2024년 12월 16일

SQL

목록 보기

17/51

41. Population Census

MS SQL의 JOIN 요약

INNER JOIN: 두 테이블에서 조건에 맞는 행만 결합.

SELECT A.컬럼, B.컬럼
FROM 테이블A A
INNER JOIN 테이블B B ON A.키 = B.키;

LEFT JOIN: 왼쪽 테이블의 모든 행 + 조건에 맞는 오른쪽 테이블 행.

SELECT A.컬럼, B.컬럼
FROM 테이블A A
LEFT JOIN 테이블B B ON A.키 = B.키;

RIGHT JOIN: 오른쪽 테이블의 모든 행 + 조건에 맞는 왼쪽 테이블 행.

SELECT A.컬럼, B.컬럼
FROM 테이블A A
RIGHT JOIN 테이블B B ON A.키 = B.키;

FULL OUTER JOIN: 두 테이블의 모든 행(조건 불일치 포함).

SELECT A.컬럼, B.컬럼
FROM 테이블A A
FULL OUTER JOIN 테이블B B ON A.키 = B.키;

CROSS JOIN: 두 테이블의 모든 행의 데카르트 곱.

SELECT A.컬럼, B.컬럼
FROM 테이블A A
CROSS JOIN 테이블B B;

SELF JOIN: 같은 테이블끼리 JOIN.

SELECT A.컬럼, B.컬럼
FROM 테이블A A
INNER JOIN 테이블A B ON A.키 = B.다른키;

정답

SELECT SUM(A.POPULATION)
FROM CITY A
INNER JOIN COUNTRY B
ON A.COUNTRYCODE = B.CODE
WHERE B.CONTINENT = 'Asia';

42. African Cities

SELECT A.NAME
FROM CITY A
INNER JOIN COUNTRY B
ON A.CountryCode = B.Code
WHERE B.CONTINENT = 'Africa';

43. Average Population of Each Continent

SELECT B.Continent, AVG(A.Population)
FROM CITY A
INNER JOIN COUNTRY B
ON A.CountryCode = B.Code
GROUP BY B.Continent;

44. Top Competitors

SELECT h.hacker_id, h.name
FROM Hackers h
JOIN Submissions s ON h.hacker_id = s.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(DISTINCT c.challenge_id) > 1
ORDER BY COUNT(DISTINCT c.challenge_id) DESC, h.hacker_id ASC;

으어;; 이건 좀 너무 긴거 아니요 ;;;
최적화 하는 것도 신경써야할듯...

45. Ollivander's Inventory

처음에 실패한 내 쿼리

WITH CTE AS (
    SELECT W.id AS ID, WP.age AS AGE, W.coins_needed AS NEED, W.power AS PWR
    FROM Wands W 
    INNER JOIN Wands_Property WP ON W.code = WP.code
    WHERE WP.is_evil = 0
)
SELECT CTE.ID, CTE.AGE, CTE.NEED, CTE.PWR
FROM CTE
WHERE CTE.NEED = (SELECT MIN(NEED)
                  FROM CTE temp
                  WHERE temp.NEED = CTE.NEED
                  )
ORDER BY CTE.PWR DESC, CTE.AGE DESC;

이렇게 하면 PWR와 AGE 별로 NEED 최소를 알 수가 없음...

그래서 다시 고치면 아래와 같음

WITH CTE AS (
    SELECT W.id AS ID, WP.age AS AGE, W.coins_needed AS NEED, W.power AS PWR
    FROM Wands W 
    INNER JOIN Wands_Property WP ON W.code = WP.code
    WHERE WP.is_evil = 0
)
SELECT CTE.ID, CTE.AGE, CTE.NEED, CTE.PWR
FROM CTE
WHERE CTE.NEED = (SELECT MIN(NEED)
                  FROM CTE temp
                  WHERE temp.PWR = CTE.PWR
                  AND temp.AGE = CTE.AGE
                  )
ORDER BY CTE.PWR DESC, CTE.AGE DESC;

이걸 더 최적화 하면 아래와 같음

WITH MinCoins AS (
    SELECT 
        WP.age, 
        W.power, 
        MIN(W.coins_needed) AS min_coins
    FROM Wands W
    INNER JOIN Wands_Property WP
        ON W.code = WP.code
    WHERE WP.is_evil = 0
    GROUP BY WP.age, W.power
)
SELECT 
    W.id, 
    WP.age, 
    W.coins_needed, 
    W.power
FROM Wands W
INNER JOIN Wands_Property WP
    ON W.code = WP.code
INNER JOIN MinCoins MC
    ON WP.age = MC.age 
    AND W.power = MC.power
    AND W.coins_needed = MC.min_coins
WHERE WP.is_evil = 0
ORDER BY W.power DESC, WP.age DESC;