Ollivander's Inventory

Ollivander's Inventory
Harry Potter and his friends are at Ollivander's with Ron, finally replacing Charlie's old broken wand.
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.

• Wands table
  

• Wands_Property table
  

최소한의 금액(가장 싼)으로 살 수 있는 non-evil 지팡이를 찾는 문제다.

• 같은 code에서 power와 age가 크고 coins-neended가 가장 작은 걸 찾는다
  • code별, power별 최소 금액 찾기
• non-evil을 찾는다

• code, power에 따른 최소한의 금액 찾기

SELECT code, MIN(coins_needed), power
FROM wands
GROUP BY code, power

• 최종 결과가 id별로 나와야하는데 위의 GROUP BY에 id를 넣으면 나중에 age를 구하기 위해 다른 테이블과 JOIN할 때 문제가 발생한다
• 위의 부분을 서브쿼리로 넣는다

• 최종 코드

SELECT A.id, P.age, A.coins_needed, A.power
FROM
    (SELECT code, MIN(coins_needed) coins_needed, power -- coins_needed로 별칭을 주지 않으면 코드가 작동하지 않는다
    FROM wands
    GROUP BY code, power) W -- 여기에 id로 GROUP BY를 하면 id는 같지만 나머지가 다른 것들도 같이 가져오게 된다
    INNER JOIN wands_property P -- age를 가져오기 위해 wands_property 테이블과 JOIN
    ON W.code = P.code
    INNER JOIN wands A -- 위의 W에는 id가 없기 때문에 이너조인으로 wands 테이블의 id를 가져와야 한다
    ON W.code = A.code
    AND W.power = A.power
    AND W.coins_needed = A.coins_needed -- 모두가 같아야 id를 가져올 수 있다 (코드는 같은데 id와 power, coins-needed가 다른 게 있음)
WHERE P.is_evil = 0 -- non-evil
ORDER BY A.power DESC, P.age DESC

• 추가. ROW_NUMBER()를 사용하는 방법

-- 코드는 맞지만 해커랭크에서 MySQL버전이 안 맞아서 통과가 안됨
-- MS SQL server에서는 통과 됨
SELECT id, age, coins_needed, power
FROM (
  SELECT W.id,
  		P.age,
        W.coins_needed,
        W.power
        -- 윈도우 함수 사용 최소값 찾기
        ROW_NUMBER() OVER(PARTITION BY age, power ORDER BY coins_needed) rnk
  FROM wands W
  INNER JOIN wand_property P
  ON W.comd = P.code
  WHERE is_evil = 0
  ) A
WHERE rnk = 1 -- 최소값
ORDER BY power DESC, age DESC;

⚠️ 이 문제의 포인트

• GROUP BY 해준 테이블에서는 id를 찾기 힘들다
• 왜 id를 GROUP BY 하지 않는지 다시 생각해볼 것
• 원 테이블을 가져와서 id를 다시 찾아줘야 한다