Hackerrank ollivander's inventory

Question

I am practicing in hacker website.
I searched the solution for problem in following link

https://www.hackerrank.com/challenges/harry-potter-and-wands/problem

I found the following solution

======
SELECT
W.id,
WP.age,
W.coins_needed,
W.power
FROM Wands W
INNER JOIN Wands_Property WP ON W.code = WP.code
WHERE WP.is_evil != 1
AND W.coins_needed = (
SELECT
MIN(W2.coins_needed)
FROM Wands W2
INNER JOIN Wands_Property WP2 ON W2.code = WP.code
WHERE WP2.age = WP.age
AND W2.power = W.power)
ORDER BY
W.power DESC,
WP.age DESC

======.

But i don't know how the subquery works.pls explain me.

Answer 1

Subquery works in a same way as outer query works.

In this query you want to get that row which has minimum coins_needed value. So what you can do for that? First you can get minimum value of the column coins_needed then you can compare that minimum value in the same column coins_needed to get particular row.

Here you are getting minimum value of coins_needed column from the table Wands and storing that value to a outer query in the same column coin_needed to get the row.