Movie Rating solution and explanation

WITH UserRatings AS (SELECT user_id, COUNT(*) AS rating_count FROM MovieRating GROUP BY user_id), MaxUserRatings AS (SELECT user_id FROM UserRatings WHERE rating_count = (SELECT MAX(rating_count) FROM UserRatings)), AvgMovieRatings AS (SELECT m.movie_id, AVG(r.rating) AS avg_rating FROM MovieRating r JOIN Movies m ON r.movie_id = m.movie_id WHERE r.created_at BETWEEN '2020-02-01' AND '2020-02-29' GROUP BY m.movie_id), MaxAvgMovieRatings AS (SELECT movie_id FROM AvgMovieRatings WHERE avg_rating = (SELECT MAX(avg_rating) FROM AvgMovieRatings)) SELECT (SELECT name FROM Users WHERE user_id IN (SELECT user_id FROM MaxUserRatings) ORDER BY name LIMIT 1) AS results UNION SELECT (SELECT title FROM Movies WHERE movie_id IN (SELECT movie_id FROM MaxAvgMovieRatings) ORDER BY title LIMIT 1);

from collections import defaultdict

# Users and Movies data
the_users = {1: 'Daniel', 2: 'Monica', 3: 'Maria', 4: 'James'}
the_movies = {1: 'Avengers', 2: 'Frozen 2', 3: 'Joker'}

# MovieRating data
movie_ratings = [
    {'movie_id': 1, 'user_id': 1, 'rating': 3, 'created_at': '2020-01-12'},
    {'movie_id': 1, 'user_id': 2, 'rating': 4, 'created_at': '2020-02-11'},
    {'movie_id': 1, 'user_id': 3, 'rating': 2, 'created_at': '2020-02-12'},
    {'movie_id': 1, 'user_id': 4, 'rating': 1, 'created_at': '2020-01-01'},
    {'movie_id': 2, 'user_id': 1, 'rating': 5, 'created_at': '2020-02-17'},
    {'movie_id': 2, 'user_id': 2, 'rating': 2, 'created_at': '2020-02-01'},
    {'movie_id': 2, 'user_id': 3, 'rating': 2, 'created_at': '2020-03-01'},
    {'movie_id': 3, 'user_id': 1, 'rating': 3, 'created_at': '2020-02-22'},
    {'movie_id': 3, 'user_id': 2, 'rating': 4, 'created_at': '2020-02-25'},
]

# Count the number of ratings each user gave
user_rating_count = defaultdict(int)
for rating in movie_ratings:
    user_rating_count[rating['user_id']] += 1

# Find the user with the maximum ratings
max_user_count = max(user_rating_count.values())
max_users = [the_users[user_id] for user_id, count in user_rating_count.items() if count == max_user_count]
user_name = min(max_users)  # Lexicographically smallest

# Calculate average ratings for movies in February 2020
movie_rating_sums = defaultdict(int)
movie_rating_counts = defaultdict(int)
for rating in movie_ratings:
    if rating['created_at'][:7] == '2020-02':  # Check if in February 2020
        movie_rating_sums[rating['movie_id']] += rating['rating']
        movie_rating_counts[rating['movie_id']] += 1

# Determine highest average rating
max_avg_rating = 0
avg_movie_titles = []
for movie_id, total_rating in movie_rating_sums.items():
    count = movie_rating_counts[movie_id]
    avg_rating = total_rating / count
    if avg_rating > max_avg_rating:
        max_avg_rating = avg_rating
        avg_movie_titles = [the_movies[movie_id]]
    elif avg_rating == max_avg_rating:
        avg_movie_titles.append(the_movies[movie_id])

movie_name = min(avg_movie_titles)  # Lexicographically smallest

# Result
result = [user_name, movie_name]
print(result)

import sqlite3

def main():
    # Establish connection to database
    conn = sqlite3.connect('movies.db')
    cursor = conn.cursor()

    # Query 1: Find User with most ratings
    query1 = """
    SELECT name FROM Users U JOIN (
        SELECT user_id, COUNT(*) AS count
        FROM MovieRating
        GROUP BY user_id
        ORDER BY count DESC, user_id
        LIMIT 1
    ) MR ON U.user_id = MR.user_id
    """

    # Query 2: Film with highest average rating in February 2020
    query2 = """
    SELECT M.title FROM Movies M JOIN (
        SELECT movie_id, AVG(rating) AS avg_rating
        FROM MovieRating
        WHERE created_at BETWEEN '2020-02-01' AND '2020-02-28'
        GROUP BY movie_id
    ) MR ON M.movie_id = MR.movie_id
    ORDER BY avg_rating DESC, M.title
    LIMIT 1
    """

    # Execute query1
    cursor.execute(query1)
    result1 = cursor.fetchone()
    print(result1[0]) if result1 else print('No results for Query 1')

    # Execute query2
    cursor.execute(query2)
    result2 = cursor.fetchone()
    print(result2[0]) if result2 else print('No results for Query 2')

    # Clean up
    conn.close()

if __name__ == '__main__':
    main()

import java.sql.*;

public class MovieRatingSolution {
    public static void main(String[] args) {
        // Establish database connection

        // Query 1: Find User with most ratings
        String query1 = "SELECT name FROM Users U JOIN (SELECT user_id, COUNT(*) AS count FROM MovieRating GROUP BY user_id ORDER BY count DESC, user_id LIMIT 1) MR ON U.user_id = MR.user_id";

        // Query 2: Film with highest average rating in February 2020
        String query2 = "SELECT M.title FROM Movies M JOIN (SELECT movie_id, AVG(rating) AS avg_rating FROM MovieRating WHERE created_at BETWEEN '2020-02-01' AND '2020-02-28' GROUP BY movie_id) MR ON M.movie_id = MR.movie_id ORDER BY avg_rating DESC, M.title LIMIT 1";

        // Execute these queries
        try (Connection connection = DriverManager.getConnection("jdbc:url_to_your_database", "username", "password")) {
            // Execute query1
            try (PreparedStatement ps1 = connection.prepareStatement(query1)) {
                ResultSet rs1 = ps1.executeQuery();
                while (rs1.next()) {
                    System.out.println(rs1.getString("name"));
                }
            }
            
            // Execute query2
            try (PreparedStatement ps2 = connection.prepareStatement(query2)) {
                ResultSet rs2 = ps2.executeQuery();
                while (rs2.next()) {
                    System.out.println(rs2.getString("title"));
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

// SQL operations are usually done via database procedures, so leveraging SQL queries

#include <iostream>

int main() {
    // Connect to database and execute these queries sequentially

    // User with most ratings
    std::string query1 = "SELECT name FROM Users U JOIN (SELECT user_id, COUNT(*) as count FROM MovieRating GROUP BY user_id ORDER BY count DESC, user_id LIMIT 1) MR ON U.user_id = MR.user_id";

    // Movie with highest average rating in February 2020
    std::string query2 = "SELECT M.title FROM Movies M JOIN (SELECT movie_id, AVG(rating) as avg_rating FROM MovieRating WHERE created_at BETWEEN '2020-02-01' AND '2020-02-28' GROUP BY movie_id) MR ON M.movie_id = MR.movie_id ORDER BY avg_rating DESC, M.title LIMIT 1";
    
    // Execute using a database connection

    return 0;
}

// Typically, SQL queries would be executed on a server-side code, here is a conceptual representation:

(async function() {
    // Assume availability of a database client

    // User with most ratings
    const query1 = `SELECT name FROM Users U JOIN (
        SELECT user_id, COUNT(*) AS count FROM MovieRating
        GROUP BY user_id ORDER BY count DESC, user_id LIMIT 1
    ) MR ON U.user_id = MR.user_id`;

    // Movie with highest average rating in February 2020
    const query2 = `SELECT M.title FROM Movies M JOIN (
        SELECT movie_id, AVG(rating) AS avg_rating FROM MovieRating
        WHERE created_at BETWEEN '2020-02-01' AND '2020-02-28'
        GROUP BY movie_id
    ) MR ON M.movie_id = MR.movie_id ORDER BY avg_rating DESC, M.title LIMIT 1`;

    try {
        const results1 = await db.query(query1);
        const results2 = await db.query(query2);

        console.log(results1.rows[0].name);
        console.log(results2.rows[0].title);
    } catch (err) {
        console.error('Error executing queries: ', err);
    }
})();

// SQL operations are usually done via database procedures, hence SQL statement usage here

#include <stdio.h>

int main() {
    // Connect to database and execute these queries sequentially

    // Query 1: Find User with most ratings
    const char *query1 = "SELECT name FROM Users U JOIN (SELECT user_id, COUNT(*) as count FROM MovieRating GROUP BY user_id ORDER BY count DESC, user_id LIMIT 1) MR ON U.user_id = MR.user_id";

    // Query 2: Find Movie with highest average rating in February 2020
    const char *query2 = "SELECT M.title FROM Movies M JOIN (SELECT movie_id, AVG(rating) as avg_rating FROM MovieRating WHERE created_at BETWEEN '2020-02-01' AND '2020-02-28' GROUP BY movie_id) MR ON M.movie_id = MR.movie_id ORDER BY avg_rating DESC, M.title LIMIT 1";
    
    // Execute these queries using a database connection and fetch results

    return 0;
}

using System;
using System.Data.SqlClient;

class Program {
    static void Main() {
        string connectionString = "Data Source=your_database_source;Initial Catalog=MovieDB;Integrated Security=True";

        // Query 1
        string query1 = "SELECT name FROM Users U JOIN (SELECT user_id, COUNT(*) AS count FROM MovieRating GROUP BY user_id ORDER BY count DESC, user_id LIMIT 1) MR ON U.user_id = MR.user_id";
        
        // Query 2
        string query2 = "SELECT M.title FROM Movies M JOIN (SELECT movie_id, AVG(rating) AS avg_rating FROM MovieRating WHERE created_at BETWEEN '2020-02-01' AND '2020-02-28' GROUP BY movie_id) MR ON M.movie_id = MR.movie_id ORDER BY avg_rating DESC, M.title LIMIT 1";

        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlCommand command = new SqlCommand(query1, connection);
            connection.Open();

            using (SqlDataReader reader = command.ExecuteReader()) {
                while (reader.Read()) {
                    Console.WriteLine(reader["name"]);
                }
            }

            command.CommandText = query2;
            using (SqlDataReader reader = command.ExecuteReader()) {
                while (reader.Read()) {
                    Console.WriteLine(reader["title"]);
                }
            }
        }
    }
}

import sqlite3

def main():
    # Database connection
    conn = sqlite3.connect('database.db')
    cursor = conn.cursor()

    # User Movies Count CTE
    query1 = '''
    WITH UserCounting AS (
        SELECT user_id, COUNT(*) AS count
        FROM MovieRating
        GROUP BY user_id
    )
    SELECT name FROM Users U JOIN UserCounting UC ON U.user_id = UC.user_id
    ORDER BY UC.count DESC, U.name
    LIMIT 1
    '''

    # Movie Rating CTE
    query2 = '''
    WITH FebruaryMovieAvg AS (
        SELECT movie_id, AVG(rating) AS average_rating
        FROM MovieRating
        WHERE created_at >= '2020-02-01' AND created_at <= '2020-02-28'
        GROUP BY movie_id
    )
    SELECT title FROM Movies M JOIN FebruaryMovieAvg FMA ON M.movie_id = FMA.movie_id
    ORDER BY FMA.average_rating DESC, M.title
    LIMIT 1
    '''

    # Execute first CTE-based query
    cursor.execute(query1)
    user_result = cursor.fetchone()
    if user_result:
        print('User:', user_result[0])

    # Execute second CTE-based query
    cursor.execute(query2)
    movie_result = cursor.fetchone()
    if movie_result:
        print('Movie:', movie_result[0])

    # Close connection
    conn.close()

if __name__ == '__main__':
    main()

import java.sql.*;

public class OptimizedMovieSolution {
    public static void main(String[] args) {
        // Connect to a database
        String url = "jdbc:url_to_db";

        // User Rating CTE
        String query1 = "WITH UserRatingCount AS (SELECT user_id, COUNT(*) AS count FROM MovieRating GROUP BY user_id) SELECT name FROM Users U JOIN UserRatingCount URC ON U.user_id = URC.user_id ORDER BY count DESC, U.name LIMIT 1";

        // Average Rating Reports
        String query2 = "WITH FebruaryRatings AS (SELECT movie_id, AVG(rating) AS average_rating FROM MovieRating WHERE created_at BETWEEN '2020-02-01' AND '2020-02-28' GROUP BY movie_id) SELECT title FROM Movies M JOIN FebruaryRatings FR ON M.movie_id = FR.movie_id ORDER BY average_rating DESC, M.title LIMIT 1";

        try (Connection conn = DriverManager.getConnection(url, "username", "password")) {
            Statement stmt = conn.createStatement();

            ResultSet rs1 = stmt.executeQuery(query1);
            while (rs1.next()) {
                System.out.println("User: " + rs1.getString("name"));
            }

            ResultSet rs2 = stmt.executeQuery(query2);
            while (rs2.next()) {
                System.out.println("Movie: " + rs2.getString("title"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

// SQL management typically requires database-specific scripts

#include <iostream>

int main() {
    // Execute using a database manager CTE query

    // User Ratings with recursive CTE
    std::string query1 = "WITH UserRatingCount AS (SELECT user_id, COUNT(*) as count FROM MovieRating GROUP BY user_id) SELECT name FROM Users U JOIN UserRatingCount URC ON U.user_id = URC.user_id ORDER BY count DESC, U.name LIMIT 1";

    // Movie Average Rating for February 2020 with recursive CTE
    std::string query2 = "WITH FebruaryRatings AS (SELECT movie_id, AVG(rating) as avg_rating FROM MovieRating WHERE created_at BETWEEN '2020-02-01' AND '2020-02-28' GROUP BY movie_id) SELECT title FROM Movies M JOIN FebruaryRatings FR ON M.movie_id = FR.movie_id ORDER BY avg_rating DESC, M.title LIMIT 1";

    // Use them with consistent database establishment and verification protocol
    return 0;
}

// Conceptual server-side implementation leveraging CTE

(async function() {
    // User Ratings with CTE
    const query1 = `WITH UserRatings AS (
        SELECT user_id, COUNT(*) AS num_ratings
        FROM MovieRating
        GROUP BY user_id
    )
    SELECT name FROM Users U JOIN UserRatings UR ON U.user_id = UR.user_id
    ORDER BY num_ratings DESC, name
    LIMIT 1`;

    // Movie Ratings in February 2020 with CTE
    const query2 = `WITH FebRatings AS (
        SELECT movie_id, AVG(rating) AS avg_rating
        FROM MovieRating
        WHERE created_at BETWEEN '2020-02-01' AND '2020-02-28'
        GROUP BY movie_id
    )
    SELECT title FROM Movies M JOIN FebRatings FR ON M.movie_id = FR.movie_id
    ORDER BY avg_rating DESC, title
    LIMIT 1`;

    try {
        const result1 = await db.query(query1);
        const result2 = await db.query(query2);

        console.log('User:', result1.rows[0].name);
        console.log('Movie:', result2.rows[0].title);
    } catch (err) {
        console.error('Error:', err);
    }
})();

// SQL handling is better within a relational database manager

#include <stdio.h>

int main() {
    // Connect to database and execute these CTE based queries

    // CTE Approach for User Ratings
    const char *query1 = "WITH UserRatingCount AS (SELECT user_id, COUNT(*) as num_ratings FROM MovieRating GROUP BY user_id) SELECT name FROM Users U JOIN UserRatingCount URC ON U.user_id = URC.user_id ORDER BY URC.num_ratings DESC, U.name LIMIT 1";

    // CTE Approach for Movie Ratings
    const char *query2 = "WITH FebruaryRatings AS (SELECT movie_id, AVG(rating) as avg_rating FROM MovieRating WHERE created_at BETWEEN '2020-02-01' AND '2020-02-28' GROUP BY movie_id) SELECT title FROM Movies M JOIN FebruaryRatings FR ON M.movie_id = FR.movie_id ORDER BY FR.avg_rating DESC, M.title LIMIT 1";

    // Execute these queries using a database connection and fetch results

    return 0;
}

using System;
using System.Data.SqlClient;

class AdvancedMovieRating {
    static void Main() {
        string connectionString = "Data Source=your_source;Initial Catalog=MovieData;User ID=user;Password=password;";

        // CTE-based query for user ratings
        string query1 = "WITH UserCount AS (SELECT user_id, COUNT(*) as count FROM MovieRating GROUP BY user_id) SELECT name FROM Users U JOIN UserCount UC ON U.user_id = UC.user_id ORDER BY UC.count DESC, U.name LIMIT 1";

        // CTE-based query for movie average in February 2020
        string query2 = "WITH FebRating AS (SELECT movie_id, AVG(rating) AS avg_rating FROM MovieRating WHERE created_at >= '2020-02-01' AND created_at <= '2020-02-28' GROUP BY movie_id ) SELECT title FROM Movies M JOIN FebRating FR ON M.movie_id = FR.movie_id ORDER BY avg_rating DESC, M.title LIMIT 1";

        using (SqlConnection connection = new SqlConnection(connectionString)) {
            SqlCommand command = new SqlCommand(query1, connection);
            connection.Open();

            using (SqlDataReader reader = command.ExecuteReader()) {
                while (reader.Read()) {
                    Console.WriteLine("User: " + reader["name"]);
                }
            }

            command.CommandText = query2;
            using (SqlDataReader reader = command.ExecuteReader()) {
                while (reader.Read()) {
                    Console.WriteLine("Movie: " + reader["title"]);
                }
            }
        }
    }
}