[HackerRank] Olivander's Inventory

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

Ollivander's Inventory | HackerRank

 

조건1) non-evil 지팡이 (is_evil = 0)

조건2) 높은 power와 age

조건3) 최소한의 돈

조건4) id, age, coins_needed, power 출력

조건5) power 기준 내림차순, age 기준 내림차순 정렬

 

--> 돈이 최소로 드는 지팡이 중

힘이 센 순서대로, 연식이 오래된 순서대로 보여주는 코드

 

 

select id
    , age
    , coins_needed
    , w.power
from wands w
    left join wands_property p
    on w.code = p.code
where p.is_evil = 0 
    and w.coins_needed = (
        select min(w2.coins_needed)
        from wands w2 
            left join wands_property p2
            on w2.code = p2.code
        where w2.power = w.power
            and p2.age = p.age
    )
order by w.power desc, age desc

 

 

 

 

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