The Problem
Harry Potter and his friends find themselves at Ollivander's, where Ron is looking to replace his old, broken wand. Hermione suggests that the best wand to choose would be one that is not only of high power and age but also requires the least number of gold galleons to purchase. Your task is to write a SQL query that returns the id, age, coins_needed, and power of the wands that meet these criteria, sorted in descending order of power. If multiple wands share the same power level, sort these by age in descending order.
The Input
The data for this problem is contained in two tables:
-
Wands
: This table contains the id of the wand, the code of the wand, the total number of gold galleons needed to buy the wand (coins_needed
), and the power of the wand (power
). A higherpower
value indicates a better quality wand. -
Wands_Property
: This table contains thecode
of the wand, theage
of the wand, and a boolean flagis_evil
which indicates whether the wand is good for dark arts. Ifis_evil
is 0, it means that the wand is not evil. Thecode
andage
have a one-to-one relationship.
Sample wands
and wands_prop
data:
The Output
The output of the query should return the id
, age
, coins_needed
, and power
of the wands that meet the criteria, sorted in descending order of power. If more than one wand has the same power, those wands should be sorted in descending order of age
.
Sample Output:
9 45 1647 10
12 17 9897 10
1 20 3688 8
15 40 6018 7
19 20 7651 6
11 40 7587 5
10 20 504 5
18 40 3312 3
20 17 5689 3
5 45 6020 2
14 40 5408 1
Explanation
The output reflects the wands that have the lowest cost in galleons for each combination of age
and power
. For example, for wands of age
20 and power
5, 6, and 8, the wands with the lowest coins_needed
are selected.
The Solution
Two distinct SQL solutions are presented below. Each has its own strengths and drawbacks.
Source Code 1
The first solution uses a subquery within the WHERE clause to select the wands with the minimum coins_needed
for each combination of age
and power
.
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(coins_needed)
FROM Wands
JOIN Wands_Property ON Wands.code = Wands_Property.code
WHERE Wands.power = W.power AND Wands_Property.age = WP.age
)
ORDER BY W.power DESC, WP.age DESC;
While this solution is straightforward, it may not be the most efficient due to the subquery operation which needs to be executed for each row in the result set.
Source Code 2
The second solution uses a Common Table Expression (CTE) with a window function to rank the wands based on their coins_needed
for each combination of age
and power
. This way, the wand with the lowest cost (i.e., rank 1) can be selected directly.
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;
This solution is generally more efficient than the first one, especially for larger data sets, since it avoids the use of a correlated subquery.
For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.
Top comments (0)