Movie Rating - Problem

You are given three tables: Movies, Users, and MovieRating.

Write a solution to find:

  • Part 1: The name of the user who has rated the greatest number of movies. In case of a tie, return the lexicographically smaller user name.
  • Part 2: The movie name with the highest average rating in February 2020. In case of a tie, return the lexicographically smaller movie name.

The result should contain exactly two rows - one for each query result.

Table Schema

Movies
Column Name Type Description
movie_id PK int Primary key, unique movie identifier
title varchar Movie name (unique)
Primary Key: movie_id
Users
Column Name Type Description
user_id PK int Primary key, unique user identifier
name varchar User name (unique)
Primary Key: user_id
MovieRating
Column Name Type Description
movie_id PK int Foreign key to Movies table
user_id PK int Foreign key to Users table
rating int Movie rating given by user
created_at date Date when rating was created
Primary Key: (movie_id, user_id)

Input & Output

Example 1 — Most Active User and Best February Movie
Input Tables:
Movies
movie_id title
1 Avengers
2 Frozen 2
3 Joker
Users
user_id name
1 Daniel
2 Monica
3 Maria
4 James
MovieRating
movie_id user_id rating created_at
1 1 3 2020-01-12
1 2 4 2020-02-11
1 3 2 2020-02-12
1 4 1 2020-01-01
2 1 5 2020-02-17
2 2 2 2020-02-01
2 3 2 2020-03-01
3 1 3 2020-02-22
3 4 4 2020-02-13
Output:
results
Daniel
Frozen 2
💡 Note:

Daniel rated 3 movies (most active user). In February 2020, Frozen 2 had the highest average rating of 3.5 ((5+2)/2), while Avengers had 3.0 ((4+2)/2) and Joker had 3.5 ((3+4)/2), but Frozen 2 comes first lexicographically.
Example 2 — Tie Breaking with Lexicographical Order
Input Tables:
Movies
movie_id title
1 Avatar
2 Beetlejuice
Users
user_id name
1 Alice
2 Bob
MovieRating
movie_id user_id rating created_at
1 1 4 2020-02-10
1 2 4 2020-02-15
2 1 4 2020-02-20
2 2 4 2020-02-25
Output:
results
Alice
Avatar
💡 Note:

Both users rated 2 movies each, so we pick Alice (lexicographically first). Both movies have the same average rating (4.0), so we pick Avatar (lexicographically first).

Constraints

  • 1 ≤ movie_id, user_id ≤ 10000
  • 1 ≤ rating ≤ 5
  • created_at is a valid date
  • Movie titles and user names are unique

Visualization

Tap to expand
Movie Rating Problem INPUT MovieRating Table user_id movie_id rating 1 (Daniel) 1 3 1 (Daniel) 2 4 2 (Monica) 1 5 2 (Monica) 2 5 Users Table 1: Daniel 2: Monica Movies Table 1: Avengers 2: Frozen 2 Target: Feb 2020 created_at LIKE '2020-02%' Task 1: Most ratings by user (lex smallest tie) Task 2: Highest avg rating in Feb (lex smallest tie) ALGORITHM STEPS 1 JOIN Tables Link ratings with user/movie names 2 Query 1: Count Ratings GROUP BY user, COUNT(*) 3 Query 2: Avg Rating Filter Feb 2020, GROUP BY movie 4 ORDER BY + LIMIT Sort by count/avg DESC, name ASC SQL Logic SELECT name FROM MovieRating JOIN Users GROUP BY user_id ORDER BY COUNT DESC, name SELECT title FROM MovieRating JOIN Movies WHERE created_at LIKE '2020-02%' GROUP BY movie_id ORDER BY AVG DESC, title FINAL RESULT Result 1: Top Reviewer Daniel 2 movie ratings Lex smallest among ties Result 2: Best Movie (Feb) Frozen 2 Avg: 5.0 stars Highest avg in Feb 2020 Output Format: results = ["Daniel", "Frozen 2"] Key Insight: Use UNION ALL to combine two independent queries: one finds the user with maximum rating count, the other finds the movie with highest average in Feb 2020. Both use ORDER BY with LIMIT 1 and sort by name/title ASC as secondary key to handle ties lexicographically. TutorialsPoint - Movie Rating | Optimal Solution

Related Problems

Asked in
Facebook 28 Amazon 22 Google 15
32.0K Views
High Frequency
~18 min Avg. Time
890 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen