Solving Ollivander’s Inventory (Hacker Rank)

There is a really cool challenge on Hacker Rank called ‘Ollivander’s Inventory’. It is of medium difficulty and you can use various languages to solve the problem. I chose to solve this problem in language I’m very familiar with, SQL (Oracle).

The Objective is Hermione decides the best way to choose a wand for Ron Weasly is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age.

Write a query to print the id, age, coins_needed, and power of the wands that Ron’s interested in, sorted in order of descending power. If more than one wand has the same power, sort the result in order of descending age.

The following Tables are given:

Wands (F: id, code, coins_needed, power)

Wands_Property (F: code, age, is_evil)

The primary key in the Wands table is id and the foreign_key is code. The primary key for the Wands_Property table is the wands code. Let’s get started.

I created a basic select statement, joining my tables as seen below.

Select 
 wp.age, 
 w.code, 
 w.coins_needed, 
 w.power 
from wands_property wp, 
wands w where w.code = wp.code 
and wp.is_evil = ‘0’.

In the above query I assign both tables an alias, so when the query runs I won’t get a column ambiguously undefined. This error means the query can’t identify what table column or field is coming from. I assign the table wands (w) and wands_property (wp). Last I put a condition to only return non evil wands or return data where is_evil is equal to zero.

One objective down! Woohoo!

Remember, we need to get a wand with the minimum number of gold galleons needed to buy each non-evil wand of high power and age.

Lets add onto our query.

Select wp.age, 
 w.code, 
 minimum(w.coins_needed) min_coins_needed , 
 w.power 
from wands_property wp, 
 wands w 
where w.code = wp.code 
and wp.is_evil = ‘0’ 
group by power, wp.age, w.code

In the query above, I added an minimum analytic function to get the minimum coins_needed and gave it a name of min_coins_needed. Next, I grouped by power, age, and code.

Grouping is a requirement when using this analytic or you will get an oracle error, not a group by expression (Important!).

Let’s continue, we still need return the wands id and to order by power and age in descending order.

select w.id, age, min_coins_needed, w.power 
from wands w,
(Select wp.age, 
 w.code, 
 minimum(w.coins_needed) min_coins_needed , 
 w.power 
from wands_property wp, 
 wands w 
where w.code = wp.code 
and wp.is_evil = ‘0’ 
group by power, wp.age, w.code) x
where w.code = x.code 
and w.coins_needed = x.min_coins_needed
order by power desc, age desc

This is the final query, we change our query to an inline view or basically a subquery in the from clause. We select the wands table again. This time we join the wands table to our subquery using code and the minimum coins needed. This allows us to filter our code to the wands who meets our requirement and return all the data the objective required which is id, age, coins_needed (minimum_coins_needed), and power. We add an order by first power and then age in descending order. This solves our problem!!

Thank you so much for reading my blog. I hope this helps you and I’d love to hear how you solved this or a similar challenge. Til’ next time..