Ollivander's Inventory | HackerRank | MSSQL
Tags: hackerrank, mssql, codenewbie, beginners
Published: 2023-05-12 05:05:00

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

    ![wands](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gws1cphpvxbe4of58dci.png)

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.

    ![wands prop](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/w9nva5pjjrj0xbkxcj21.png)

Sample `wands` and `wands_prop` data:

![sample wands](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5gpmwc7wtg2eknflm4bd.png)
![sample wands prop](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gqz0txc9t37mdvbb9sou.png)

## 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`.

```sql
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.

```sql
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](https://www.hackerrank.com/challenges/harry-potter-and-wands/problem?isFullScreen=true)

For more insightful solutions and tech-related content, feel free to connect with me on my [Beacons page](https://beacons.ai/ranggakd).

{% embed https://beacons.ai/ranggakd %}