Solving a Tricky Harry Potter Themed SQL Problem from HackerRank

In this short TH-cam tutorial, I break down and solve a medium difficulty SQL problem on HackerRank. This is made slightly more complex because the question isn’t phrased in the most understandable format.
We’ll likely need to use joins, derived tables, window functions and touch on syntactic sugar.
Enjoy!
🤝 Connect with me on LinkedIn:
/ ja376
🤝 View my Website:
www.matadorsoftware.me
🖥📝 I also have a great value 2 hour data career consultation service to get you on the right track. Covering resumes, ATS, data technology, soft skills, roadmaps and a take home document pack at great value. Check out the Fiverr gig below:
www.fiverr.com/share/z3x07g

[@flexyourdata]

Just ran into this exact problem on hackerrank. Nice touch of the challenge creator that the language used to describe the problem is reflective of real-world ambiguity. Really makes you feel immersed in the business problem.
And by "nice", I mean...

[@MatadorSoftware]

Yeah, if it wasn’t for that classic ‘end user terminology’ then it’s straightforward…

[@xiaozhenyuan2624]

Thank you.
it can not work in mysql

[@amirakotbmassoud3172]

why i select from second table not from the first table and when i selectt all attribute in first query all of this in second table i cant understand can you explain for me pleaze?

[@MatadorSoftware]

There are many methods you could use to tackle this, but for readability and accessing elements, it’s best to use subqueries/ CTEs/ Temp Tables etc.

[@ceejay1353]

I tried this exaclty but it's not working for me for some reason.

[@memenadekhanh3992]

do you have this error message: ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(PARTITION BY Wands.power, Wands_Property.age ORDER BY Wands.coins_needed)
F' at line 8