SQL, HackerRank, Ollivander's Inventory 풀이

💡 문제 링크
https://www.hackerrank.com/challenges/harry-potter-and-wands/problem?isFullScreen=true

요약하면

• id, age, coins_needed, power 필드 출력
• power와 age별 최소 coins_needed에 해당하는 행만 출력
• is_evil = 0인 행만 출력
• code and age is one-one. (code가 다르면 age도 다름)

1. 풀이(1)

SELECT D.ID, AB.AGE, D.COINS_NEEDED, AB.POWER
FROM(
    SELECT A.POWER, B.AGE, MIN(A.COINS_NEEDED) MIN_COINS
    FROM WANDS A
    JOIN WANDS_PROPERTY B ON A.CODE=B.CODE
    GROUP BY 1,2) AB
LEFT JOIN WANDS_PROPERTY C ON AB.AGE = C.AGE
LEFT JOIN WANDS D ON C.CODE=D.CODE
WHERE D.COINS_NEEDED = AB.MIN_COINS
AND C.IS_EVIL=0
ORDER BY 4 DESC, 2 DESC
;

• 우선 power, age 필드 그룹별 최소 coins_needed를 구하고 이 테이블에
• WANDS 테이블과 WANDS_PROPERTY 테이블을 left join함.

1.1. 풀이 과정 - "group by 이후 left join?"

SELECT A.POWER, B.AGE, MIN(A.COINS_NEEDED) MIN_COINS
FROM WANDS A
JOIN WANDS_PROPERTY B ON A.CODE=B.CODE
GROUP BY 1,2

여기까지는 power, age별 최소 coins_needed 값이 잘 출력된다.
문제에서 age와 code가 one-one 관계라고 주어졌으니 left join을 차례대로 했는데 결과로 power, age 별 중복 행이 생겼다.

-- 실패한 답안
SELECT D.ID, AB.AGE, AB.MIN_COINS, AB.POWER
FROM(
    SELECT A.POWER, B.AGE, MIN(A.COINS_NEEDED) MIN_COINS
    FROM WANDS A
    JOIN WANDS_PROPERTY B ON A.CODE=B.CODE
    GROUP BY 1,2) AB
LEFT JOIN WANDS_PROPERTY C ON AB.AGE = C.AGE
LEFT JOIN WANDS D ON C.CODE=D.CODE
WHERE C.IS_EVIL=0
ORDER BY 4 DESC, 2 DESC
;

결국 SELECT 문에서 AB.MIN_COINS 대신 D.COINS_NEEDED로 수정하고 조건문으로 WHERE D.COINS_NEEDED = AB.MIN_COINS 를 추가하니 제대로 돌아갔다.

1.2 무엇이 잘못됐나

LEFT JOIN을 잘못 이해한 풀이였다. LEFT JOIN은
1) 왼쪽 테이블의 모든 레코드를 리턴하고
2) 오른쪽의 레코드 중 왼쪽 레코드와 일치하는 경우만 채워진 상태로 리턴된다.
여기선 왼쪽 레코드가 (POWER, AGE) 그룹별 최소 coin 레코드가 1개이지만 오른쪽 테이블들에는 (POWER, AGE)에 해당하는 레코드가 여러개이다. 따라서 중복행이 생기는 것이다.

대신 조건문을 이용하여 (POWER, AGE)별 최소 COIN 개수를 찾는 방법으로 더 깔끔한 풀이를 찾았다.

2. 풀이(2)

출처 : https://techblog-history-younghunjo1.tistory.com/165

SELECT A.ID, B.AGE, A.COINS_NEEDED, A.POWER
FROM WANDS A
JOIN WANDS_PROPERTY B ON A.CODE = B.CODE
WHERE B.IS_EVIL = 0
AND A.COINS_NEEDED = (
    SELECT MIN(COINS_NEEDED)
    FROM WANDS W1
    INNER JOIN WANDS_PROPERTY P1 ON W1.CODE = P1.CODE
    WHERE P1.IS_EVIL = 0
    AND W1.POWER = A.POWER
    AND P1.AGE = B.AGE
)
ORDER BY 4 DESC, 2 DESC
;

서브 쿼리의 WHERE 구문에서 메인 쿼리의 칼럼을 이용하는 예시는 처음 봤는데 훨씬 깔끔한 것 같다.

• WHERE A.COINS_NEEDED = (서브쿼리) 형식
• 서브쿼리 조건문에 메인쿼리의 칼럼 이용. (power, age) 별 최소 coin값에 해당하는 행 뽑기.