Movie Rating - Practice Coding Problems

Movie Rating - Problem

Database Medium

You're building an analytics dashboard for a movie review platform! You have three interconnected database tables containing information about movies, users, and their ratings.

Your task is to solve two distinct problems:

Find the most active reviewer: Identify the user who has rated the greatest number of movies. If there's a tie, return the lexicographically smaller username.
Find February 2020's top movie: Identify the movie with the highest average rating specifically in February 2020. If there's a tie, return the lexicographically smaller movie title.

The database schema consists of:

Movies table: Contains movie_id and title
Users table: Contains user_id and name
MovieRating table: Contains movie_id, user_id, rating, and created_at

Return format: Your result should contain exactly two rows - the first row with the most active user's name, and the second row with the top-rated movie title from February 2020.

Input & Output

example_1.sql — Basic Case

$ Input: Movies: [(1,'Avatar'),(2,'Endgame'),(3,'Titanic')] Users: [(1,'Daniel'),(2,'Monica'),(3,'Maria')] MovieRating: [(1,1,3,'2020-02-05'),(1,2,4,'2020-02-11'),(1,3,2,'2020-02-17'),(2,1,5,'2020-02-20'),(2,2,3,'2020-02-15'),(2,3,4,'2020-02-10')]

› Output: Daniel Endgame

💡 Note: Daniel rated 3 movies (most active). In February 2020, Endgame has average rating 3.5, Avatar has 4.0, Titanic has 3.0. Avatar has the highest average rating.

example_2.sql — Tie Breaking

$ Input: Movies: [(1,'Avengers'),(2,'Batman')] Users: [(1,'Alice'),(2,'Bob')] MovieRating: [(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: Alice Avengers

💡 Note: Both Alice and Bob rated 2 movies each (tie), but Alice comes first lexicographically. Both movies have same average rating (4.0), but Avengers comes first lexicographically.

example_3.sql — Date Filtering Edge Case

$ Input: Movies: [(1,'Movie A'),(2,'Movie B')] Users: [(1,'User1'),(2,'User2')] MovieRating: [(1,1,5,'2020-01-31'),(1,2,3,'2020-02-01'),(2,1,4,'2020-02-29'),(2,2,2,'2020-03-01')]

› Output: User1 Movie A

💡 Note: User1 rated 2 movies vs User2's 2 movies, but User1 is lexicographically smaller. Only ratings from 2020-02-01 to 2020-02-29 count for February, so Movie A gets rating 3, Movie B gets rating 4. Movie B wins with higher average.

Visualization

Tap to expand

Understanding the Visualization

User Activity Analysis

Count how many movies each user has rated, then find the most active reviewer

Time-based Filtering

Filter ratings specifically for February 2020 using date conditions

Movie Rating Aggregation

Calculate average ratings for each movie within the time period

Tie-breaking Logic

Handle ties using lexicographical ordering (alphabetical sorting)

Result Combination

Combine both analytics results into a single response

Key Takeaway

🎯 Key Insight: Leverage SQL's built-in GROUP BY aggregation with proper indexing and ORDER BY clauses for both optimal performance and automatic tie-breaking. The database engine handles complex optimizations better than application-level processing.

Time & Space Complexity

Time Complexity

⏱️

O(R log R)

R ratings with efficient GROUP BY operations and sorting, leveraging database indexes

⚡ Linearithmic

Space Complexity

O(U + M)

Database manages memory efficiently, only storing unique users and movies temporarily

✓ Linear Space

Constraints

1 ≤ Number of movies, users ≤ 1000
1 ≤ Number of ratings ≤ 10⁴
1 ≤ rating ≤ 5
created_at is a valid date
Each (movie_id, user_id) pair appears at most once in MovieRating table
All user names and movie titles are unique
February 2020 has at least one rating

Asked in

N Netflix 45 A Amazon 38 G Google 32 f Meta 28 ⊞ Microsoft 25

The optimal approach uses SQL aggregation functions with GROUP BY clauses to efficiently count user ratings and calculate movie averages. The key is leveraging database optimizations with proper ORDER BY for tie-breaking and LIMIT 1 for performance. UNION ALL combines both results in a single query, minimizing database round trips.

Common Approaches

Approach	Time	Space	Notes
✓ Optimized SQL Aggregation (Optimal)	O(R log R)	O(U + M)	Use SQL GROUP BY, aggregation functions, and UNION ALL for optimal performance
Nested Loops Approach	O(U × R + M × R)	O(U + M + R)	Multiple separate queries with nested loops to count and calculate averages

Optimized SQL Aggregation (Optimal) — Algorithm Steps

Create first query: GROUP BY user to count ratings per user
Order by count DESC, name ASC to handle ties automatically
Use LIMIT 1 to get the top user efficiently
Create second query: Filter February 2020 and GROUP BY movie
Calculate AVG(rating) for each movie in the filtered period
Order by average DESC, title ASC for tie-breaking
Use LIMIT 1 to get the top movie efficiently
Combine both queries with UNION ALL

Visualization

Tap to expand

Step-by-Step Walkthrough

Index Scan

Database uses indexes to efficiently access relevant records

Hash Aggregation

GROUP BY uses hash tables for fast grouping and counting

Sort & Limit

Database sorts results and applies LIMIT for optimal performance

Date Filter

WHERE clause efficiently filters February 2020 records using date index

Average Calculation

AVG() function calculates averages during aggregation phase

Union Results

UNION ALL combines both query results efficiently

Code -

solution.c — C

// Optimal SQL approach in C
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <mysql/mysql.h>

typedef struct {
    char** results;
    int count;
} QueryResult;

QueryResult* solution(MYSQL* connection) {
    const char* query = 
        "("
        "    SELECT u.name as result"
        "    FROM MovieRating mr"
        "    JOIN Users u ON mr.user_id = u.user_id"
        "    GROUP BY mr.user_id, u.name"
        "    ORDER BY COUNT(*) DESC, u.name ASC"
        "    LIMIT 1"
        ")"
        "UNION ALL"
        "("
        "    SELECT m.title as result"
        "    FROM MovieRating mr"
        "    JOIN Movies m ON mr.movie_id = m.movie_id"
        "    WHERE mr.created_at >= '2020-02-01' "
        "      AND mr.created_at < '2020-03-01'"
        "    GROUP BY mr.movie_id, m.title"
        "    ORDER BY AVG(mr.rating) DESC, m.title ASC"
        "    LIMIT 1"
        ")";
    
    QueryResult* result = malloc(sizeof(QueryResult));
    result->results = malloc(2 * sizeof(char*));
    result->count = 0;
    
    if (mysql_query(connection, query)) {
        fprintf(stderr, "Query failed: %s\n", mysql_error(connection));
        return result;
    }
    
    MYSQL_RES* mysql_result = mysql_store_result(connection);
    if (mysql_result == NULL) {
        fprintf(stderr, "mysql_store_result failed: %s\n", mysql_error(connection));
        return result;
    }
    
    MYSQL_ROW row;
    while ((row = mysql_fetch_row(mysql_result)) && result->count < 2) {
        result->results[result->count] = malloc(strlen(row[0]) + 1);
        strcpy(result->results[result->count], row[0]);
        result->count++;
    }
    
    mysql_free_result(mysql_result);
    return result;
}

// Alternative implementation with prepared statements
QueryResult* solution_prepared(MYSQL* connection) {
    QueryResult* result = malloc(sizeof(QueryResult));
    result->results = malloc(2 * sizeof(char*));
    result->count = 0;
    
    // Prepare user query
    const char* user_query = 
        "SELECT u.name "
        "FROM MovieRating mr "
        "JOIN Users u ON mr.user_id = u.user_id "
        "GROUP BY mr.user_id, u.name "
        "ORDER BY COUNT(*) DESC, u.name ASC "
        "LIMIT 1";
    
    MYSQL_STMT* user_stmt = mysql_stmt_init(connection);
    if (mysql_stmt_prepare(user_stmt, user_query, strlen(user_query))) {
        fprintf(stderr, "User stmt prepare failed: %s\n", mysql_stmt_error(user_stmt));
        mysql_stmt_close(user_stmt);
        return result;
    }
    
    if (mysql_stmt_execute(user_stmt)) {
        fprintf(stderr, "User stmt execute failed: %s\n", mysql_stmt_error(user_stmt));
        mysql_stmt_close(user_stmt);
        return result;
    }
    
    // Bind result for user query
    char user_name[256];
    unsigned long user_length;
    my_bool user_is_null;
    
    MYSQL_BIND user_bind[1];
    memset(user_bind, 0, sizeof(user_bind));
    user_bind[0].buffer_type = MYSQL_TYPE_STRING;
    user_bind[0].buffer = user_name;
    user_bind[0].buffer_length = sizeof(user_name);
    user_bind[0].length = &user_length;
    user_bind[0].is_null = &user_is_null;
    
    mysql_stmt_bind_result(user_stmt, user_bind);
    
    if (mysql_stmt_fetch(user_stmt) == 0) {
        result->results[0] = malloc(user_length + 1);
        strcpy(result->results[0], user_name);
        result->count++;
    }
    
    mysql_stmt_close(user_stmt);
    
    // Similar process for movie query...
    const char* movie_query = 
        "SELECT m.title "
        "FROM MovieRating mr "
        "JOIN Movies m ON mr.movie_id = m.movie_id "
        "WHERE mr.created_at >= '2020-02-01' "
        "  AND mr.created_at < '2020-03-01' "
        "GROUP BY mr.movie_id, m.title "
        "ORDER BY AVG(mr.rating) DESC, m.title ASC "
        "LIMIT 1";
    
    // ... implement movie query similarly
    
    return result;
}

// Function to free the result
void free_query_result(QueryResult* result) {
    if (result) {
        for (int i = 0; i < result->count; i++) {
            free(result->results[i]);
        }
        free(result->results);
        free(result);
    }
}

Time & Space Complexity

Time Complexity

⏱️

O(R log R)

R ratings with efficient GROUP BY operations and sorting, leveraging database indexes

⚡ Linearithmic

Space Complexity

O(U + M)

Database manages memory efficiently, only storing unique users and movies temporarily

✓ Linear Space

Constraints

1 ≤ Number of movies, users ≤ 1000
1 ≤ Number of ratings ≤ 10⁴
1 ≤ rating ≤ 5
created_at is a valid date
Each (movie_id, user_id) pair appears at most once in MovieRating table
All user names and movie titles are unique
February 2020 has at least one rating

38.2K Views

High Frequency

~15 min Avg. Time

1.5K 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

Input & Output

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Optimized SQL Aggregation (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler