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 OverviewInput: 3 TablesMoviesUsersMovieRatingJOIN & GROUP BYAnalysisMost Active User: Daniel (3 ratings)Best Feb Movie: Frozen 2 (3.5 avg)UNION ALLFinal ResultDanielFrozen 2Key Operations:1. COUNT(*) ratings per user2. AVG(rating) per movie in Feb 20203. Lexicographical tie-breaking
Understanding the Visualization
1
Input Tables
Three related tables with ratings data
2
Dual Analysis
Count user ratings and average movie ratings
3
Combined Result
UNION ALL to get both answers
Key Takeaway
🎯 Key Insight: Use UNION ALL to combine results from two different analytical queries with proper ordering for tie-breaking

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