Hackerrank SQL Solutions | Ollivanders Inventory SQL Hackerrank Medium Problem

[@sruthiroyal6425]

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 lottttt , you explain it very clearly .

[@anishchhabra5313]

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

[@suryprakashyadav6657]

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 :)

[@ashishdukare1313]

Thank you so much for this detailed explanation. Keep creating videos : ) Love from India ♥

[@thecodingmentor7701]

Thanks Ashish, that means a lot! 🙏

[@_sahil_.11_]

Very beautifully explained sir . Thank you

[@kalpanarallapalli2241]

Great explanation !

[@abinm.sharaf7014]

THANKS A LOT FOR THIS VIDEO HELPS A LOT

[@thecodingmentor7701]

You are Welcome Abin!

[@tirthpatel20]

Best explanation!

[@charan9330]

Thank you for the great explanation

[@joshdee7639]

dude your amazing

[@thecodingmentor7701]

Thanks Josh, you too are amazing 😀

[@gyanreddy2696]

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

[@thecodingmentor7701]

Hi gyan, thanks for the question! That is a key point of solving this problem. I tried to explain it from 11:08 (see Problem description). You can look at this section again and you will understand. Basically the rows that we are interested in have rownumber = 1 because they are the wands with the minimum coins needed (while having the same power). So in the join we dont want all rows but only the rows with rownumber = 1. You could also make a join without the restriction and write RowNumber = 1 in the where clause. Gives the same result. Hope that helps :)

[@ahmetlekesiz9948]

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

[@thecodingmentor7701]

Hi Ahmet, yes you are right! order by coins_needed is enough. Thanks!

[@stevenzambrano6628]

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

[@thecodingmentor7701]

Hey steven! Just between us, challenges are gonna come this weekend ;)

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

[@karthikdnd]

Ain't working in mYSQL server

[@thecodingmentor7701]

Yes true, this is for MSSQL Server. If you want to have it in MySQL you have to adapt the code

[@karthikdnd]

This code is pretty much the same in MySQL and MSSQL can you tell me what's wrong with this? WITH t AS ( SELECT id, code, coins_needed, power, ROW_NUMBER() OVER (PARTITION BY code, power ORDER BY code ASC, power DESC, coins_needed ASC) AS rnk FROM wands ) SELECT * FROM t; This code is pretty much the same in MySQL and MSSQL can you tell me what's wrong with this?

[@TheTechInsiderBySaurabh]

this query is giving error in my sql

[@thecodingmentor7701]

Hi Saurabh, the Query is made for MSSQL. If you use it in MySQL, you habe to adapt it

[@TheTechInsiderBySaurabh]

@@thecodingmentor7701 thank you so much for reply i am new in sql and i am from completely different background so if you have time could you please elaborate it by the way learning a lot from your videos thanks man

[@thecodingmentor7701]

Hi Saurabh, Sure, you have different versions of SQL. They are the same language but different dialects. MSSQL is the commercial Implementation from Microsoft. MySQL is often used for open Source projects. Thats why they are different. Try to google "MSSQL to MySQL" and you will find ways to translate from one dialect into the other

[@bhaveshkumar7320]

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

[@thecodingmentor7701]

Hi Bhavesh, you practically solved it! You wrote "mc.age" but is "age" really coming from the table mc (mc_table)?

[@bhaveshkumar7320]

@@thecodingmentor7701 of course Not, can you re-write the code in correct syntax: SELECT mc.id, 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, age desc

[@akankshmamidala597]

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☺

[@thecodingmentor7701]

Hi Akanksh, Thanks for your request! Hackerrank is running on MySQL Version 5.27. You can check this with following command: SELECT VERSION() Unfortunately the window functions (which you are using with OVER()) were only introduced in MySQL Version 8.0. Therefore your statement is not working.