LeetCode Medium 1341 "Movie Rating" SAP Interview SQL Question with Explanation

No video

LeetCode Medium 1341 "Movie Rating" SAP Interview SQL Question with Explanation

Рет қаралды 3,871

Күн бұрын

Question: leetcode.com/p...
In this video I solve and explain a medium difficulty leetcode SQL question using MySQL query. This question has been asked in Apple, Facebook, Amazon, Google, Adobe, Microsoft, Adobe interviews or what we popularly call FAANG interviews.
I explain the related concept as well. This question includes points to keep in mind to develop SQL queries.
LeetCode is the best platform to help you enhance your skills, expand your knowledge and prepare for technical interviews.
If you found this helpful, Like and Subscribe to the channel for more content.
#LeetCodeSQL #FAANG #SQLinterviewQuestions

Пікірлер: 14

@vaastavv Жыл бұрын

Great explanation. Just one issue with the query, when you group by name it might happen that there are multiple users with the same name so it will be better to group it by user_id to pass all possible test cases.

@lucasplacido7134 Жыл бұрын

Great video. I was following this same logic but i didn't know that you could group by the title using the aggregation function in the order by clause. Thank you

@ShubhamAgarwal-k1s Ай бұрын

Sir, please explain the date format function in SQL Server.

@chandrachurmukherjeejucse5816 5 ай бұрын

Great explanation.

@clouddrive6833 Жыл бұрын

nice explantion sir

@EverydayDataScience Жыл бұрын

Glad that you found the video useful 😊

@tatagovind12 2 ай бұрын

use "union all" pls

@devgarg7761 Жыл бұрын

Hello, sir you have most of the questions of sql leetcode posted but your solution doesnt work most of the time. This time it 4th question i tried your solution but didn't work with all test cases and dont know how come your gets submitted. Please check. Note - every single line is same.

@rupdeepthey Жыл бұрын

He solved all these almost a year ago and Leetcode keeps adding 1-2 new test cases. I don't know why you are complaining. If you understood what he explained then you can handle minor changes. Don't just copy paste the code. See expected output and find out what is going wrong. It is not his responsibility to handle these minor changes. If you still face issues then go to the solutions sections and figure out what are the necessary changes. For this question use UNION ALL instead of UNION because just in case movie name and person name is same then UNION will overlap while UNION ALL will print both as expected from Leetcode. And his solutions always worked except for one question where it was a bug from Leetcode's side and this one is just a minor change. Even if it failed 4 times for you then you cannot say it failed most of the times lol. 4 is a very small number.

@lucasplacido7134 Жыл бұрын

It's probably the 17 test case. You need to use UNION ALL to pass it. They added a test case in which the movie name is the same as the user name, then the union removes the duplicated row. Your output has to have two rows, one with the movie name and one with the user name

@kaushlendrakumar1571 Жыл бұрын

@@lucasplacido7134 yeah by union all it is working

@sauravchandra10 9 ай бұрын

With enought trying SQL questions can be solved, but your approach makes the code more organised, that is why I keep coming back to your videos even when I have solve the question on my own. Thanks a lot for that. Here is my accepted code: WITH cte AS( SELECT mr.*, m.title, u.name FROM MovieRating mr LEFT JOIN Movies m ON mr.movie_id=m.movie_id LEFT JOIN Users u ON mr.user_id=u.user_id ), cte2 AS( SELECT name, count(*) as no_of_ratings FROM cte GROUP BY user_id ORDER BY no_of_ratings DESC, name ASC LIMIT 1 ), cte3 AS( SELECT title, AVG(rating) AS avg_rating FROM cte WHERE created_at BETWEEN '2020-02-01' AND '2020-02-29' GROUP BY movie_id ORDER BY avg_rating DESC, title ASC LIMIT 1 ) SELECT (SELECT name FROM cte2) AS results UNION ALL SELECT (SELECT title FROM cte3) AS results

@ujjwalvarshney3188 Жыл бұрын

( select name from MovieRating a join Users b on a.user_id = b.user_id group by 1 order by count(a.user_id) desc ,name limit 1 ) union all (select title as name from MovieRating a join Movies b on a.movie_id = b.movie_id where date_trunc('month' ,created_at) = '2020-02-01' group by 1 order by sum(rating):: numeric(38,2)/ count(*) desc , title limit 1) order by 1 - in Redshift