1341. Movie Rating / LeetCode, SQL, MS SQL Server

문제

source: LeetCode

Write an SQL query to:

- Find the name of the user who has rated the greatest number of movies. In case of a tie, return the lexicographically smaller user name.
- Find the movie name with the highest average rating in February 2020. In case of a tie, return the lexicographically smaller movie name.

 

 

 

 

제출답안(MS SQL Server)

WITH
    rating_count_by_user
        AS (SELECT user_id, COUNT(rating) AS count_rating
            FROM movierating
            GROUP BY user_id),


    ls_id_most_rating
        AS (SELECT user_id
            FROM rating_count_by_user
            WHERE count_rating = (SELECT MAX(count_rating) FROM rating_count_by_user)),

    answer1
        AS (SELECT TOP 1 name AS results
            FROM ls_id_most_rating AS cnt
            JOIN users AS u ON cnt.user_id = u.user_id
            ORDER BY name),

    rating_avg_by_movie_feb2020
        AS (SELECT movie_id, AVG(CONVERT(FLOAT, rating)) AS average_rating
            FROM movierating
            WHERE YEAR(created_at) = 2020
                AND MONTH(created_at) = 2
            GROUP BY movie_id),

    ls_movie_highest_avgrating_feb2020
        AS (SELECT movie_id
            FROM rating_avg_by_movie_feb2020
            WHERE average_rating = (SELECT MAX(average_rating) FROM rating_avg_by_movie_feb2020)
                ),

    answer2
        AS (SELECT TOP 1 title AS results
            FROM ls_movie_highest_avgrating_feb2020 AS rating
            JOIN movies AS mv ON rating.movie_id = mv.movie_id
            ORDER BY title)


SELECT * FROM answer1
UNION ALL
SELECT * FROM answer2

 

 

 

풀이(MS SQL Server)

CTE1: user_id별 rating 횟

WITH
    rating_count_by_user
        AS (SELECT user_id, COUNT(rating) AS count_rating
            FROM movierating
            GROUP BY user_id),

 

CTE2: rating을 가장 많이 한 user_id 목

 

    ls_id_most_rating
        AS (SELECT user_id
            FROM rating_count_by_user
            WHERE count_rating = (SELECT MAX(count_rating) FROM rating_count_by_user)),

 

CTE3: user_id와 name을 결합 (CTE2 JOIN users)

    answer1
        AS (SELECT TOP 1 name AS results
            FROM ls_id_most_rating AS cnt
            JOIN users AS u ON cnt.user_id = u.user_id
            ORDER BY name),

>> 두 개의 쿼리결과를 합쳐야하므로 UNION을 사용해야함

>> UNION을 위해 칼럼이름은 results로 명명

>> the lexicographically smaller user name을 문제에서 요구하므로 이름기준 정렬 후 TOP1 을 넣었

 

CTE4: 2020년 2월 기준 영화별 평균평점

    rating_avg_by_movie_feb2020
        AS (SELECT movie_id, AVG(CONVERT(FLOAT, rating)) AS average_rating
            FROM movierating
            WHERE YEAR(created_at) = 2020
                AND MONTH(created_at) = 2
            GROUP BY movie_id),

>> rating이 INT타입이므로 정확한 평균이 계산되지 않음

>> 따라서 FLOAT 값으로 변환 후 평균을 계산하였음

>> 2020년 2월 데이터를 필터하기 위해 FORMAT(created_at, 'yyyy-MM') = '2020-02'를 했으나 런타임이 두 배 길어짐. 따라서 YEAR, MONTH로 각각 비교하였음

 

CTE5: 2020년 2월 기준 평균평점이 가장 높은 영화 id 목록

    ls_movie_highest_avgrating_feb2020
        AS (SELECT movie_id
            FROM rating_avg_by_movie_feb2020
            WHERE average_rating = (SELECT MAX(average_rating) FROM rating_avg_by_movie_feb2020)
                ),

 

CTE6:

    answer2
        AS (SELECT TOP 1 title AS results
            FROM ls_movie_highest_avgrating_feb2020 AS rating
            JOIN movies AS mv ON rating.movie_id = mv.movie_id
            ORDER BY title)

>> 두 개의 쿼리결과를 합쳐야하므로 UNION을 사용해야함 (CTE3)

>> UNION을 위해 칼럼이름은 results로 명명

>>the lexicographically smaller movie name을 문제에서 요구하므로 타이틀 기준 정렬 후 TOP 1

 

메인쿼리

SELECT * FROM answer1
UNION ALL
SELECT * FROM answer2

 

 

 

문제 바로가기(MS SQL Server)