[해커랭크/MSSQL] 40. Ollivander's Inventory

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

Ollivander's Inventory | HackerRank

 

---

🤔 문제 분석

1. 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.

2. 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.

3. If more than one wand has same power, sort the result in order of descending age.

 

💡 풀이

SELECT id, age, coins_needed, power
FROM(
    SELECT W.id, WP.age, W.coins_needed, W.power,
           (ROW_NUMBER() OVER (PARTITION BY WP.age, W.power ORDER BY W.coins_needed)) AS rn
    FROM Wands AS W
        INNER JOIN Wands_Property AS WP ON W.code = WP.code
    WHERE is_evil = 0
) AS sub
WHERE rn = 1
ORDER BY power DESC, age DESC;

 

의문

왜 윈도우함수 MIN을 사용하는 것은 정답처리가 안되는가

SELECT id, age, coins_needed, power, min_coin
FROM(
    SELECT W.id, WP.age, W.coins_needed, W.power,
           (MIN(W.coins_needed) OVER (PARTITION BY WP.age)) AS min_coin
    FROM Wands AS W
        INNER JOIN Wands_Property AS WP ON W.code = WP.code
    WHERE is_evil = 0
) AS sub
WHERE coins_needed = min_coin
ORDER BY power DESC, age DESC;