Retiago Drago

Posted on

# Ollivander's Inventory | HackerRank | MSSQL

## 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:

1. `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 higher `power` value indicates a better quality wand.

2. `Wands_Property`: This table contains the `code` of the wand, the `age` of the wand, and a boolean flag `is_evil` which indicates whether the wand is good for dark arts. If `is_evil` is 0, it means that the wand is not evil. The `code` and `age` 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.

Original Source

For more insightful solutions and tech-related content, feel free to connect with me on my Beacons page.

## ranggakd - Link in Bio & Creator Tools | Beacons

@ranggakd | center details summary summary Oh hello there I m a an Programmer AI Tech Writer Data Practitioner Statistics Math Addict Open Source Contributor Quantum Computing Enthusiast details center.

beacons.ai