MySQL

SELECT w.id, cheapest_wands.age, cheapest_wands.coins_needed, cheapest_wands.power
FROM Wands w
JOIN Wands_Property wp
    ON w.code = wp.code
JOIN 
    (SELECT 
        wp.age,
        w.power,
        MIN(w.coins_needed) as coins_needed
    FROM Wands w
    JOIN Wands_Property wp
        ON w.code = wp.code
    WHERE wp.is_evil = 0
    GROUP BY wp.age, w.power) AS cheapest_wands
    ON wp.age = cheapest_wands.age AND w.coins_needed = cheapest_wands.coins_needed AND w.power = cheapest_wands.power
ORDER BY cheapest_wands.power DESC, cheapest_wands.age DESC

IN MS SQL Server, i think output of HackerRank is missing some value that have same coins, same age, same power but different ID

with part1 as ( select wp.age , min(w.coins_needed) as min_coin , w.power from wands as w left join wands_property as wp on w.code = wp.code where wp.is_evil = 0 group by w.power, wp.age ) -- select * from part1 -- order by power desc, age desc select w.id , p.age , p.min_coin , p.power from part1 as p left join wands as w on p.min_coin = w.coins_needed and p.power = w.power order by p.power desc, p.age desc;

Since we can't use CTE's in this version. Posting the alternate way.

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

SELECT w.id, wp.age, w.coins_needed, w.power FROM Wands w JOIN Wands_Property wp ON w.code = wp.code WHERE wp.is_evil = 0 AND w.coins_needed = ( SELECT MIN(w2.coins_needed) FROM Wands w2 JOIN Wands_Property wp2 ON w2.code = wp2.code WHERE wp2.is_evil = 0 AND wp2.age = wp.age AND w2.power = w.power ) ORDER BY w.power DESC, wp.age DESC;

For sql server: with bestchoice as ( select w.id, wp.age, w.coins_needed, w.power, row_number() over (patition by w.power, wp.age order by w.coins_needed asc) as rn from Wands w join Wands_Property wp on w.code = wp.code where wp.is_evil = 0 ) select id, age, coins_needed, power from bestchoice where rn =1 order by power desc, age desc;