115. Movie Rating

[문제]

https://leetcode.com/problems/movie-rating/description/

 

[풀이]

1. 영화 평점 테이블에서 각 유저별로, 영화별로, 평가수와 평균점수를 구하고 각각 새로운 테이블을 생성

2. 평가수 테이블과 유저 테이블을 조인해서 유저의 이름을 구하고

3. 평균점수 테이블과 영화 테이블을 조인해서 영화 이름을 구한 뒤

4. 두 데이터로 테이블을 만들어서 반환

 

Pandas

import pandas as pd

def movie_rating(movies: pd.DataFrame, users: pd.DataFrame, movie_rating: pd.DataFrame) -> pd.DataFrame:
	
    user_rating_counts = (
    	movie_rating
        .groupby('user_id')
        .size()
        .reset_index(name='cnt')
    )
    
    user_name = (
    	user_rating_counts
        .merge(users, on='user_id')
        .sort_values(['cnt', 'name'], ascending=[False, True])
        .iloc[0]['name']
    )
    
    movie_avg_ratings = (
    	movie_rating[
        	movie_rating.created_at.dt.to_period('M') == '2020-02'
        ]
        .groupby('movie_id')['rating']
        .mean()
        .reset_index()
    )
    
    movie_title = (
    	movie_avg_ratings
        .merge(movies, on='movie_id')
        .sort_values(['rating', 'title'], ascending=[False, True])
        .iloc[0]['title']
    )
    
    return pd.DataFrame({'results: [user_name, movie_title]})

 

SQL

WITH UserRatingCounts AS (
    SELECT user_id, COUNT(*) AS cnt
    FROM MovieRating
    GROUP BY user_id
),
MovieAvgRatings AS (
    SELECT movie_id, AVG(rating) AS avg_rating
    FROM MovieRating
    WHERE created_at BETWEEN '2020-02-01' AND '2020-02-29'
    GROUP BY movie_id
)

(SELECT name as results
FROM Users 
JOIN UserRatingCounts USING (user_id)
ORDER BY cnt DESC, name
LIMIT 1)

UNION ALL

(SELECT title as results
FROM Movies
JOIN MovieAvgRatings USING (movie_id)
ORDER BY avg_rating DESC, title
LIMIT 1)