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.

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.

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.

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

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Danielle Torres

Highly organized & motivated Software Engineer with an application analyst/data analyst background.