Movie Rating

Movie Rating

What is this problem about?

The Movie Rating problem is a SQL challenge where you need to find: (1) the user who has rated the most movies (alphabetical tiebreaker), and (2) the movie with the highest average rating in February 2020 (alphabetical tiebreaker). The result combines these two into a single output using UNION. This Movie Rating coding problem tests complex SQL aggregation with multiple tiebreaker conditions.

Why is this asked in interviews?

Microsoft, Meta, Amazon, Google, Bloomberg, and Snap ask this because it tests the ability to write and UNION two independent complex queries, each with its own aggregation, grouping, and tiebreaking. The database interview pattern requires fluency with GROUP BY, ORDER BY with multiple keys, LIMIT, and UNION ALL.

Algorithmic pattern used

Two separate queries + UNION ALL. Query 1: SELECT user_name, COUNT(*) as cnt FROM ratings GROUP BY user_id ORDER BY cnt DESC, user_name ASC LIMIT 1. Query 2: SELECT movie_name, AVG(rating) as avg_r FROM ratings WHERE created_at LIKE '2020-02%' GROUP BY movie_id ORDER BY avg_r DESC, movie_name ASC LIMIT 1. UNION ALL the results (single-column output: name).

Example explanation

Users: Alice rated 3 movies, Bob rated 3 movies (tie). Alphabetical: Alice. Query 1 → "Alice". Movies in Feb 2020: "Frozen 2" avg=3.5, "Joker" avg=3.5 (tie). Alphabetical: "Frozen 2". Query 2 → "Frozen 2". UNION result: ["Alice", "Frozen 2"].

Common mistakes candidates make

• Using UNION instead of UNION ALL (UNION removes duplicates, could drop a result).
• Filtering on the wrong date format (use LIKE '2020-02%' or BETWEEN).
• Applying alphabetical tiebreaker incorrectly (must be ASC for user_name, ASC for movie_name).
• Joining to the users/movies table before understanding the base query structure.

Interview preparation tip

SQL problems requiring two independent rankings combined into one output use UNION ALL. Practice writing each sub-query independently first, verify the tiebreaker ORDER BY logic, then combine. The pattern ORDER BY count DESC, name ASC LIMIT 1 for "most X with alphabetical tiebreak" is standard — memorize it. UNION ALL is preferred over UNION when you want all rows (including potential duplicates across the two sub-queries).