[SQL] HackerRank - Ollivander's Inventory(M)

  Ollivander's Inventory  

문제에 대한 자세한 내용은 다음 링크에서 확인하실 수 있습니다

Ollivander's Inventory | HackerRank

 

---

# Problem

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.

 

Input Format

The following tables contain data on the wands in Ollivander's inventory:

 

* Wands: The id is the id of the wand, code is the code of the wand, coins_needed is the total number of gold galleons needed to buy the wand, and power denotes the quality of the wand (the higher the power, the better the wand is). 

 

 

* Wands_Property: The code is the code of the wand, age is the age of the wand, and is_evil denotes whether the wand is good for the dark arts. If the value of is_evil is 0, it means that the wand is not evil. The mapping between code and age is one-one, meaning that if there are two pairs, (code1, age1) and (code2, age2), then code1 != code2 and age1 != age2.

 

---

# Answer

1) id, age, coins_needed, power of the wands 출력하기
2) age와 power가 같은 것이 여러개라면 그 중 coins_needed 값이 가장 작은 것을 출력하기
3) is_evil 값이 0이어야 함
3) power순으로 내림차순, power가 같다면 age순으로 내림차순 하기

 

먼저, age와 power별 coins_needed의 최솟값을 찾기 위한 쿼리는 다음과 같다

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

• code가 1이고 power가 1, 2, 6일때 coins_needed의 최솟값은 각각 2701, 4361, 3754이다.

 

(1) 위의 쿼리를 FROM절에 넣고 wands, wands_property 테이블과 LEFT JOIN해주었다.

( 우리는 wands 테이블의 전체는 필요없고 위의 테이블에 해당하는 값만 필요하므로 LEFT JOIN을 해주었다. )

 

(2) is_evil 값이 0이라는 조건과 power, age 순으로 내림차순 정렬하는 조건을 각각 WHERE, ORDER BY 절에 넣어주었다.

 

(3) 마지막으로 id, age, coins_needed, power of the wands를 출력하기 위해 SELECT절에 지정해주었다.

SELECT w.id
     , wp.age
     , a.min_coins
     , a.power   -- (3)
  FROM (SELECT code, power, MIN(coins_needed) AS min_coins
          FROM wands
         GROUP BY code, power) AS a
  LEFT JOIN wands AS w
         ON a.min_coins = w.coins_needed AND a.code = w.code
  LEFT JOIN wands_property AS wp
         ON a.code = wp.code   -- (1)
 WHERE wp.is_evil = 0
 ORDER BY a.power DESC, wp.age DESC;   -- (2)