Ollivander's Inventory (HackerRank)

Ollivander's Inventory | HackerRank

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.

풀이

제출한 답

with sub as (
select 
    w.id, 
    w.coins_needed, 
    wp.age, 
    w.power, 
    row_number() over (partition by wp.age, w.power order by w.coins_needed) rnk
from wands as w join wands_property as wp on w.code = wp.code
where wp.is_evil = 0)

select 
    id, age, coins_needed, power
from sub
where rnk = 1
order by power desc, age desc

-> power 와 age가 같으면(partition by) 저렴한(order by coins_needed asc, rnk = 1)

2022.11.05