I think that in a way the RANK() version is a tiny bit better just because it scales well in case you're asked to "implement a tie-breaker" if you end up with two or more employees with the same highest salary. You could tell the interviewer that if in the future they decided that they wanted the employee with highest salary and longest tenure from each department, you could just add the hiring date to the ORDER BY clause and you'd resolve the ties (even though we don't have hiring date on this table, but you get my point!) Awesome video! Thanks as always.
@stratascratch
3 жыл бұрын
I really like that explanation! Those small nuances add a lot of value to interviewers since it proves that you know what you're talking about. Thanks for your input!
@ashutoshsingh5568
3 жыл бұрын
Your way of teaching is simple and easy to understand. I’m learning new things from you. Thanks for the teachings. Keep it up.
@stratascratch
3 жыл бұрын
Thanks for watching! Really appreciate it.
@Luke_L
2 жыл бұрын
In the last solution, why can't you use the WHERE clause in the subquery by itself: SELECT department, first_name, salary, RANK() OVER(PARTITION BY DEPARTMENT ORDER BY salary DESC) AS salary_rank FROM employee WHERE salary_rank = 1; I know it gives an error, but I don't know why. Thanks!
@flipcase
2 жыл бұрын
Wow Nate didn't know you were from Cornell! Can see it on the wall behind! And as always, really awesome video. My favorite is rank!
@stratascratch
2 жыл бұрын
That's impressive! Didn't know you can see the diploma all the way back there! Rank is the best one for this question!
@sauravkumar9454
3 жыл бұрын
Loved all four solutions and the differences among them. My personal fav would be self-join.
@techiewithcamera
Жыл бұрын
My Solution: SELECT department AS department, first_name AS employee_name, salary FROM employee WHERE (department , salary) IN (SELECT department, MAX(salary) FROM employee GROUP BY department );
@stratascratch
Жыл бұрын
Thanks for sharing!
@cleversachin
3 жыл бұрын
Looks like solution 3 will have edge cases if there are matching salary within multiple depts. I wll also join with deptno
@ayoajayi280
2 жыл бұрын
what if we want to find the employees with the lowest salary. will the windows function work?
@krishnaKumar-fx6eu
2 жыл бұрын
Hi Nate, I really enjoy your video and the depth of explanation that you provide for every video. I am currently looking to solve medium level of questions and I request you to please cover more questions of medium level.. ! Looking forward to your response. Thank you so much...!!
@stratascratch
2 жыл бұрын
Will do! It's a 2022 goal of mine to cover more questions and make more videos.
@krishnaKumar-fx6eu
2 жыл бұрын
@@stratascratch Yup, That sounds great..!!!! Completed most of the videos.. waiting for more.
@elfridhasman4181
2 жыл бұрын
Thanks man, you Help Me alot
@17_gouravgupta87
3 жыл бұрын
Please make a series on SQL tutorial s it will be great help
@Firatayrilik
2 жыл бұрын
Wonderful!
@user-mo2xn3ni2b
3 жыл бұрын
I think you could also use row_number too, and another approach would be to use cross apply with order by salary desc in it, right ?
@stratascratch
3 жыл бұрын
Yup that's right on both approaches! None of them are wrong to use on an interview or on your day job.
@adibzainal3991
3 жыл бұрын
This is very insightful, subscribed!
@shikharsaxena8984
3 жыл бұрын
Your explanation is very good sir, appreciate your work 👏👏
@hiovanycubillosgomez5901
3 жыл бұрын
Hey man that's great, thank you so much to share your knowledge.
@joaopedroreissilva7075
2 жыл бұрын
Thank you so much, Nate! Your didatic is awesome!
@stratascratch
2 жыл бұрын
Thanks for watching! Really appreciate it!
@tejasphirke3436
3 жыл бұрын
Very clear explanation..
@maarif1869
3 жыл бұрын
Great!!
@ruima721
2 жыл бұрын
Hey Nate, thanks for your tutorial and it really helps me. Could I ask in the third solution, is it necessary to put an "a" in the subquery just after ")". I tried the same sql without "a" and there comes an error.
@stratascratch
2 жыл бұрын
Yes, typically you need to alias subqueries. That's probably why you're getting the error.
@harshitsati
2 жыл бұрын
Hey Nate, why doesn't stratascratch support mysql ? I don't really know postgresql at the moment so I was looking for mysql but could not find it as an option Or do you recommend dropping mySql over postgresql?
@stratascratch
2 жыл бұрын
MySQL will be out soon -- probably sometime in November/Dec. We're currently building this functionality. MySQL and Postgres are 90% the same so I just chose an engine I was more familiar with and one that is better for analytics which is postgres. I wouldn't drop mysql over postres, both are great to know. If you know MySQL more then stick with it. We'll release it on the platform soon!
@harshitsati
2 жыл бұрын
@@stratascratch thanks for the answer nate!
@dipanjan_chaudhury
3 жыл бұрын
Hello I am from India. Do you provide any discount coupon for premium subscription?
@stratascratch
3 жыл бұрын
Yes, we have a 15% off any plan if you use ss15 discount code at checkout!
Пікірлер: 36