#113. [LeetCode / MySQL] 1341. Movie Rating

문제

 

 

입력 코드

1. 

(SELECT name AS results
FROM MovieRating JOIN Users USING(user_id)
GROUP BY name
ORDER BY COUNT(*) DESC, name
LIMIT 1)

UNION ALL

(SELECT title AS results
FROM MovieRating JOIN Movies USING(movie_id)
WHERE EXTRACT(YEAR_MONTH FROM created_at) = 202002
GROUP BY title
ORDER BY AVG(rating) DESC, title
LIMIT 1);

 

 

2. 

WITH 
TheMostActiveUser AS (
    SELECT name
    FROM 
        Users
        NATURAL JOIN MovieRating
    GROUP BY user_id
    ORDER BY COUNT(*) DESC, name
    LIMIT 1
),
TheBestMovieFebruary AS (
    SELECT title
    FROM
        Movies
        NATURAL JOIN MovieRating
    WHERE created_at BETWEEN '2020-02-01' AND '2020-02-29'
    GROUP BY movie_id
    ORDER BY AVG(rating) DESC, title
    LIMIT 1
)

SELECT name AS results
FROM TheMostActiveUser
UNION ALL
SELECT title
FROM TheBestMovieFebruary

 

 

코드 설명

#SELECT #JOIN #USING #GROUP BY #ORDER BY #COUNT #LIMIT #UNION #UNION ALL #EXTRACT #AVG

 

 

EXTRACT(unit FROM date) : 날짜에서 년, 월, 일, 시간 등을 추출하는 함수

• unit으로 사용할 수 있는 것들

MICROSECOND	SECOND	MINUTE	HOUR
DAY	WEEK	MONTH	QUARTER
YEAR	SECOND_MICROSECOND	MINUTE_MICROSECOND	MINUTE_SECOND
HOUR_MICROSECOND	HOUR_SECOND	HOUR_MINUTE	DAY_MICROSECOND
DAY_SECOND	DAY_MINUTE	DAY_HOUR	YEAR_MONTH

 

 

문제 출처

https://leetcode.com/problems/movie-rating/description/?envType=study-plan-v2&envId=top-sql-50