[해커랭크] [sql] Ollivander's Inventory

오늘은 medium 문제 중 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.

 

 

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,  and , then  and .
• 

---

Sample Input

Wands

Table:

Wands_Property

Table:

Sample Output

9 45 1647 10 12 17 9897 10 1 20 3688 8 15 40 6018 7 19 20 7651 6 11 40 7587 5 10 20 504 5 18 40 3312 3 20 17 5689 3 5 45 6020 2 14 40 5408 1

Explanation

The data for wands of

age 45

(code 1):

• The minimum number of galleons needed for
• The minimum number of galleons needed for

The data for wands of

age 40

(code 2):

• The minimum number of galleons needed for
• The minimum number of galleons needed for
• The minimum number of galleons needed for
• The minimum number of galleons needed for

The data for wands of

age 20

(code 4):

• The minimum number of galleons needed for
• The minimum number of galleons needed for
• The minimum number of galleons needed for

The data for wands of

age 17

(code 5):

• The minimum number of galleons needed for
• The minimum number of galleons needed for

 

문제 해석 ! 
1. 각각 power와 age의 최소 gold_galleons(coin_needed)를 출력해라
2. id, age, coins_needed, power 순서로 컬럼을 출력해라
3. power를 내림차순으로 정렬하고, power가 같은경우는 age 를 내림차순으로 정렬해라

 

내가 푼 정답

SELECT w.id, t.age, t.coin, t.power
FROM Wands w INNER JOIN (
                SELECT wp1.code, wp1.age, MIN(w1.coins_needed) as "coin", w1.power 
                FROM  Wands w1 INNER JOIN Wands_Property wp1  ON w1.code=wp1.code 
                WHERE wp1.is_evil=0
                GROUP BY wp1.code, wp1.age, w1.power) t ON w.code=t.code AND w.power=t.power AND w.coins_needed=t.coin
ORDER BY t.power DESC ,t.age DESC -- order by

똑같은 쿼리를 with 절로 하면 에러남.. 

id 도 출력해야 하는데 group by로 id를 나누면 안되기 때문에 서브쿼리를 이용해야 하는 번거로움이 있었다.

 

푸는데 오래걸림