Hackerrank / Basic Join / Ollivander's Inventory / MySQL

📚문제

출처 : hackerrank(https://www.hackerrank.com/challenges/harry-potter-and-wands/problem?isFullScreen=true)

https://www.hackerrank.com/challenges/harry-potter-and-wands/problem?isFullScreen=true

Ollivander's Inventory | HackerRank

 

📝풀이

SELECT w.id
     , p.age
     , w.coins_needed
     , w.power
FROM Wands w
INNER JOIN Wands_Property p ON w.code = p.code
WHERE (p.age,w.power,w.coins_needed) IN (SELECT p.age AS age
     , w.power AS power
     , MIN(w.coins_needed) AS min_coin
FROM Wands w
INNER JOIN Wands_Property p ON w.code = p.code
WHERE p.is_evil = 0
GROUP BY p.age, w.power)
ORDER BY w.power DESC , p.age DESC

WHERE 절 서브쿼리를 활용