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

Поделиться
HTML-код
  • Опубликовано: 10 дек 2024

Комментарии • 15

  • @vaastavv
    @vaastavv Год назад +4

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

  • @chandrachurmukherjeejucse5816
    @chandrachurmukherjeejucse5816 8 месяцев назад

    Great explanation.

  • @ShubhamAgarwal-k1s
    @ShubhamAgarwal-k1s 5 месяцев назад

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

  • @clouddrive6833
    @clouddrive6833 Год назад

    nice explantion sir

  • @tatagovind12
    @tatagovind12 5 месяцев назад +1

    use "union all" pls

    • @g4uravrawat663
      @g4uravrawat663 Месяц назад

      thanks this saved my time, that 14th test case was such a pain

  • @devgarg7761
    @devgarg7761 Год назад +2

    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
      @rupdeepthey Год назад +2

      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
      @lucasplacido7134 Год назад +2

      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
      @kaushlendrakumar1571 Год назад

      @@lucasplacido7134 yeah by union all it is working

  • @sauravchandra10
    @sauravchandra10 Год назад

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