해커랭크 SQL - 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.

Ollivander's Inventory | HackerRank

 

 

대충 요약하자면, 최소의 돈으로 high age와 high power 를 가진 지팡이를 골라야 한다.

 

 

문제풀이

SELECT W.id, P.age, W.coins_needed, W.power
FROM Wands W
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
                        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 power DESC, age DESC;

 

 

해커랭크 처음으로 풀어보았는데 괜찮은 것 같다. (영어인 것 빼고는....)

SQL 연습은 해커랭크에서 해봐야겠다..!!