Hackerrank SQL Solutions | Ollivanders Inventory SQL Hackerrank Medium Problem

In this video we are solving "Ollivanders Inventory". This is a Medium SQL Problem on Hackerrank.
____________________________________________

SUBSCRIBE!
Do you want to understand how to solve SQL Problems in every detail? That's what my channel is about. When I was younger I thought I could never program because it looked way too difficult. The truth is that it takes time but with some patience anybody can do it! Follow me along and get better!
____________________________________________

SUPPORT MY CHANNEL

🙌The best way to support my channel right now is to give me a Super Thanks. You can do that by clicking thanks next to the title of the video. It is much appreciated!

____________________________________________

____________________________________________
0:00 Problem Description
0:55 Output Requirements
2:50 Code Age Relationship
4:38 Manual Solution
8:04 Start Query
10:27 Wands Table
11:08 Row Number Explanation
18:38 JOIN Wands and Wands_Property

Code:
WITH min_coins as (
SELECT
ROW_NUMBER() OVER(PARTITION BY code,power ORDER BY coins_needed asc) as RowNumber,
id,
code,
coins_needed,
power
FROM Wands
)

SELECT
id,
age,
coins_needed,
power
FROM Wands_Property wp
WHERE is_evil = 0
ORDER BY power desc, age desc

[sruthiroyal]

I have been struggling to understand the logic of the question and got exhusted by trying to understand from diff resources, this video helped a lot . thanks a, you explain it very clearly .

[suryprakashyadav]

Very well explained and summarized everything nicely too, Was so confused with this question while solving and I didn't understand the question initially but after watching this video solution each and every confusion has been resolved. Thank you :)

[anishchhabra]

Thank you so much man, this is by far the best explanation one can expect.

[_sahil_._]

Very beautifully explained sir . Thank you

[ashishdukare]

Thank you so much for this detailed explanation. Keep creating videos : )

Love from India ♥

[abinm.sharaf]

THANKS A LOT FOR THIS VIDEO HELPS A LOT

[stevenzambrano]

Great videos ! When will you release the Challenges & Symmetric pairs questions please!!
:)

[ahmetlekesiz]

Why did you ordered by (code, power, coins_needed) but not just coins_needed? Do we really need that in that question?

[gyanreddy]

Why do we put rownumber=1 in the query while we are joining it?

[DLAsmash]

Thanks for the explanation. This problem made absolutely no sense to me; why does the Wands_Property table even exist? Is it implying that all wands of a given age have the same evil status? And that as they get older, they change codes and switch willy-nilly from evil to non-evil and back?? This data seems relevant to each specific wand rather than a "code"; why not put everything in the same table? And it wouldn't have been so bad if the question itself wasn't worded horrendously on top of all of this. Usually not one to complain but this was an hour spent trying to figure it out that I really wish I could get back.

[bhaveshkumar]

Hi mentor, Please tell why this code showing error - with mc_table as (
select
row_number() over(partition by code, power order by code, power, coins_needed asc) as rownumber,
id,
code,
coins_needed,
power
from wands

)

SELECT mc.id, mc.age, mc.coins_needed, mc.power from wands_property wp
Join mc_table mc on mc.code = wp.code
where is_evil = 0 and rownumber < 2
order by mc.power desc, mc.age desc

[akankshmamidala]

Hi, can you please tell me the problem with the below code( mysql and it is showing error near partition by)
select w.id, w.power, w.coins_needed, ww.age
from wands w
join wands_property ww
on w.code = ww.code
where ww.is_evil=0
and w.coins_needed in
(select min(coins_needed) over (partition by code, power order by coins_needed)
from wands w)
order by w.power desc, w.age desc ;


Thanks...Have a good day☺