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