SQL Interview Mastery: Movie Rating Analysis (LeetCode 1341)

An in-depth guide to solving multi-table relational aggregation problems, handling lexical (dictionary) tie-breaking, temporal data filtering (MONTH/YEAR), and optimizing table union strategies in SQL.

1. Problem Statement & Overview

In this chapter, we tackle LeetCode Problem 1341 (Movie Rating), a classic intermediate-to-advanced SQL interview question. The challenge tests your ability to query across three distinct relational tables, perform multi-column grouping, compute aggregate functions (like COUNT and AVG), filter dates, handle tie-breaking using lexicographical (alphabetical) sorting, and efficiently combine disparate result sets using UNION ALL.

The Relational Schema

We are provided with three primary tables:

1. Movies: Contains movie metadata.
   • movie_id (INT): Primary Key.
   • title (VARCHAR): Name of the movie.
2. Users: Contains user profile data.
   • user_id (INT): Primary Key.
   • name (VARCHAR): Name of the user.
3. MovieRating: Contains the review transactions linking users and movies.
   • movie_id (INT): Foreign Key referencing Movies.
   • user_id (INT): Foreign Key referencing Users.
   • rating (INT): The numerical score given by the user.
   • created_at (DATE): The timestamp when the rating was submitted.
   • Composite Primary Key: (movie_id, user_id).

Core Objectives

We must return a single result column named results containing two specific rows:

1. Top User: The name of the user who has rated the greatest number of movies. In the event of a tie, return the lexicographically smaller user name (alphabetical order).
2. Top Movie: The title of the movie with the highest average rating in February 2020. In the event of a tie, return the lexicographically smaller movie title.

2. Core Mental Model & Real-Life Analogy

Real-Life Analogy: The Film Festival Awards Committee

Imagine organizing a massive annual film festival where thousands of critics and moviegoers submit reviews.

• Task 1 (Finding the Super Reviewer): You look at the logbook (MovieRating) and count how many entries bear each reviewer's ID (user_id). You match those IDs back to the badge registry (Users) to read their actual names. If two critics reviewed an identical, maximum number of movies (e.g., 3 reviews each), you line up their name tags alphabetically and pick the one that comes first in the dictionary.
• Task 2 (Finding the February Hit): You filter the logbook strictly for pages stamped with the date February 2020 (02-2020). For each movie, you calculate the mathematical average of all its Feb 2020 scores. If two movies tie for the highest average score (e.g., both score 3.5), you compare their titles alphabetically and pick the lexicographically smaller title.

3. Step-by-Step Example Walkthrough

Let's trace a concrete dataset to see how the logic evaluates step-by-step.

Sample Data

• Users: (1, 'Daniel'), (2, 'Monica'), (3, 'Maria'), (4, 'James')
• Movies: (1, 'Avengers'), (2, 'Frozen 2'), (3, 'Joker')
• MovieRating:
  • (1, 1, 4, '2020-01-12') (Daniel rated Avengers 4)
  • (1, 1, 2, '2020-02-01') (Daniel rated Avengers 2)
  • (1, 1, 5, '2020-02-15') (Daniel rated Avengers 5 - Wait, multiple per user/movie allowed in schema log)
  • Let's look at total review counts per user:
    • Daniel (ID 1): 3 ratings total.
    • Monica (ID 2): 3 ratings total.
    • Maria (ID 3): 2 ratings total.
    • James (ID 4): 1 rating total.

Part 1 Resolution (Top User by Review Count)

• Max Ratings: Both Daniel and Monica have 3 ratings.
• Tie-breaker: Lexicographical comparison between 'Daniel' and 'Monica'.
• 'Daniel' comes before 'Monica' alphabetically (D < M).
• Result Row 1: Daniel.

Part 2 Resolution (Top Average Movie Rating in Feb 2020)

Filtering MovieRating where created_at falls in Feb 2020 (MONTH = 02, YEAR = 2020):

• Avengers (Movie ID 1): Ratings in Feb = [4, 2] $\rightarrow$ Average = $\frac{4 + 2}{2} = 3.0$
• Frozen 2 (Movie ID 2): Ratings in Feb = [5, 2] $\rightarrow$ Average = $\frac{5 + 2}{2} = 3.5$
• Joker (Movie ID 3): Ratings in Feb = [3, 4] $\rightarrow$ Average = $\frac{3 + 4}{2} = 3.5$
• Max Average: 3.5 (shared by Frozen 2 and Joker).
• Tie-breaker: Lexicographical comparison between 'Frozen 2' and 'Joker'.
• 'Frozen 2' comes before 'Joker' alphabetically (F < J).
• Result Row 2: Frozen 2.

4. Part 1: Querying the User with the Most Ratings

To find the user who rated the greatest number of movies, we must join the Users table with the MovieRating table, aggregate by user, count their reviews, sort them in descending order, and extract the top record.

SQL Implementation (Part 1)

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

Key Query Mechanics

• USING (user_id): A cleaner alternative to ON Users.user_id = MovieRating.user_id. It automatically joins the tables on the shared column name without requiring table aliases.
• GROUP BY user_id: Groups all ratings submitted by the same individual user together.
• ORDER BY COUNT(rating) DESC, name ASC:
  • First, sorts by total review count in descending order (highest review count first).
  • Second, handles ties by sorting the user name in ascending alphabetical order (A -> Z), ensuring the lexicographically smaller name bubbles to the top row.
• LIMIT 1: Restricts the output to only the top-ranked row.

5. Part 2: Querying the Movie with Highest Average Rating in Feb 2020

Next, we need to locate movies reviewed in February 2020, calculate their average rating, handle ties alphabetically, and extract the top result.

SQL Implementation (Part 2)

SELECT title AS results
FROM Movies
INNER JOIN MovieRating USING (movie_id)
WHERE MONTH(created_at) = 2 AND YEAR(created_at) = 2020
GROUP BY movie_id
ORDER BY AVG(rating) DESC, title ASC
LIMIT 1;

Key Query Mechanics

• WHERE MONTH(created_at) = 2 AND YEAR(created_at) = 2020: Extracts date parts to isolate reviews created strictly in February 2020.
• GROUP BY movie_id: Groups review scores by individual movies.
• ORDER BY AVG(rating) DESC, title ASC:
  • Sorts by the mathematical average rating (AVG(rating)) in descending order.
  • In the event of a tie (e.g., both movies average 3.5), sorts the title in ascending alphabetical order (A -> Z).
• LIMIT 1: Extracts the single top movie.

6. Combining Results: UNION vs. UNION ALL

To combine our two independent query outputs into a single cohesive result set with two rows, we use SQL set operators.

Why UNION ALL over UNION?

• UNION: Automatically removes duplicate rows across the combined query results. To do this, the database engine must perform a distinct sort pass, making it slower.
• UNION ALL: Appends the result sets together raw and unfiltered, without checking for duplicates. It is significantly faster and more performant.
• Application Context: Even if a user's name happened to match a movie title (e.g., a user named "Frozen 2"), we would want both records returned independently. Therefore, UNION ALL is both logically safer and computationally faster.

7. Complete Production-Ready SQL Query

Below is the complete, integrated solution wrapped with UNION ALL and parentheses as required for multi-query structuring in SQL:

(
    SELECT name AS results
    FROM Users
    INNER JOIN MovieRating USING (user_id)
    GROUP BY user_id
    ORDER BY COUNT(rating) DESC, name ASC
    LIMIT 1
)
UNION ALL
(
    SELECT title AS results
    FROM Movies
    INNER JOIN MovieRating USING (movie_id)
    WHERE MONTH(created_at) = 02 AND YEAR(created_at) = 2020
    GROUP BY movie_id
    ORDER BY AVG(rating) DESC, title ASC
    LIMIT 1
);

8. Summary and Key Takeaways

• Multi-Table Relational Mapping: Complex reporting problems often require bridging entity tables (Users, Movies) through a transactional mapping table (MovieRating).
• The USING Clause: Use USING (column_name) as a concise shorthand for equijoin conditions when primary and foreign key column names match across tables.
• Robust Tie-Breaking (ORDER BY Multi-Sorting): Always provide a secondary sort column (e.g., ORDER BY metric DESC, name ASC) to guarantee deterministic results during data ties.
• Lexicographical Ordering: Alphabetical ordering (ASC) satisfies dictionary-order requirements when choosing between tied entities.
• Date Filtering Functions: Built-in temporal functions like MONTH() and YEAR() allow precise filtering on date/timestamp attributes.
• Performance Optimization with UNION ALL: Prefer UNION ALL over UNION when combining independent subquery result sets to bypass unnecessary duplicate-elimination overhead.
• Row Restriction via LIMIT: Combine ORDER BY with LIMIT 1 to isolate top-ranked items efficiently.