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.
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
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.
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.
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
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
( 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
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.
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
Great explanation.
Sir, please explain the date format function in SQL Server.
nice explantion sir
Glad that you found the video useful 😊
use "union all" pls
thanks this saved my time, that 14th test case was such a pain
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.
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.
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
@@lucasplacido7134 yeah by union all it is working
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
( 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