1341. Movie Rating

Tables

erDiagram
	Movies {
		movie_id int PK
		title varchar
	}
	Users {
		user_id int PK
		name varchar
	}
	MovieRating {
		movie_id int PK
		user_id int PK
		rating int
		created_at date
	}
	MovieRating }o -- || Users : "user info"
	MovieRating }o -- || Movies : "movie info"
Mermaid
복사

Question

•

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.

DataFlow

MovieRating -> groupby movie_id -> avg rating -> movie rank UNION MovieRating -> groupby user_id -> count rating -> user rank

Answer

SELECT
	title AS results
FROM (
	SELECT
		a.movie_id,
		b.title,
		a.rating,
		ROW_NUMBER() OVER(ORDER BY a.rating DESC, b.title ASC) AS rn
	FROM (
		SELECT
			movie_id,
			AVG(rating) AS rating
		FROM MovieRating
		WHERE DATE_FORMAT(created_at, "%Y-%m") = "2020-02"
		GROUP BY movie_id
		) a
	LEFT JOIN Movies b
		ON a.movie_id = b.movie_id
) movie_title
WHERE rn = 1

UNION ALL

SELECT
	name AS results
FROM (
	SELECT
		a.user_id,
		b.name,
		a.cnt,
		ROW_NUMBER() OVER(ORDER BY a.cnt DESC, b.name ASC) AS rn
	FROM (
		SELECT
			user_id,
			COUNT(rating) AS cnt
		FROM MovieRating
		GROUP BY user_id
		) a
	LEFT JOIN Users b
		ON a.user_id = b.user_id
) user_cnt
WHERE rn = 1;
SQL
복사