1341. Movie Rating

Problem¶

Given users, movies, and ratings, return one column with two rows:

The user name with the most ratings across all time, breaking ties by smallest name.
The movie title with the highest average rating in February 2020, breaking ties by smallest title.

Input:

# 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        | 2       | 4      | 2020-02-25

Output:

--------
results
--------
Daniel
Frozen 2

Key trick¶

Compute two independent ranked aggregations, take the top row from each, then concatenate them in the required order.

Trap¶

Use all ratings for the top user, not only February ratings.
Use only February 2020 ratings for the top movie.
Break ties lexicographically with ascending name or title.
Prefer half-open date filtering to avoid month-end mistakes.
Preserve output order: user first, movie second.

Why is it interesting?¶

It tests aggregation, joins, tie-breaking, date filtering, and combining unrelated result sets into one output.

SQL solution¶

WITH top_user AS (
    SELECT
        u.name AS results,
        COUNT(*) AS rating_count
    FROM MovieRating AS r
    JOIN Users AS u
        ON u.user_id = r.user_id
    GROUP BY u.user_id, u.name
    ORDER BY rating_count DESC, u.name ASC
    LIMIT 1
),
top_movie AS (
    SELECT
        m.title AS results,
        AVG(r.rating) AS avg_rating
    FROM MovieRating AS r
    JOIN Movies AS m
        ON m.movie_id = r.movie_id
    WHERE r.created_at >= '2020-02-01'
      AND r.created_at < '2020-03-01'
    GROUP BY m.movie_id, m.title
    ORDER BY avg_rating DESC, m.title ASC
    LIMIT 1
)
SELECT results
FROM (
    SELECT 1 AS ord, results FROM top_user
    UNION ALL
    SELECT 2 AS ord, results FROM top_movie
) AS ranked
ORDER BY ord;

Pandas solution¶

import pandas as pd


def movie_rating(
    movies: pd.DataFrame,
    users: pd.DataFrame,
    movie_rating: pd.DataFrame,
) -> pd.DataFrame:
    # Count all ratings per user, then apply the tie-break.
    top_user = (
        movie_rating.merge(users, on="user_id", how="inner")
        .groupby(["user_id", "name"], as_index=False)
        .size()
        .sort_values(["size", "name"], ascending=[False, True])
        .iloc[0]["name"]
    )

    # Filter only February 2020 with a half-open range.
    feb_ratings = movie_rating.loc[
        (movie_rating["created_at"] >= pd.Timestamp("2020-02-01"))
        & (movie_rating["created_at"] < pd.Timestamp("2020-03-01"))
    ]

    # Average February ratings per movie, then apply the tie-break.
    top_movie = (
        feb_ratings.merge(movies, on="movie_id", how="inner")
        .groupby(["movie_id", "title"], as_index=False)["rating"]
        .mean()
        .sort_values(["rating", "title"], ascending=[False, True])
        .iloc[0]["title"]
    )

    return pd.DataFrame({"results": [top_user, top_movie]})

Pytest test¶

import sqlite3

import pandas as pd
import pytest


SQLITE_QUERY = """
WITH most_rated AS (
    SELECT
        u.name AS results,
        COUNT(*) AS rating_count
    FROM MovieRating AS r
    JOIN Users AS u
        ON u.user_id = r.user_id
    GROUP BY
        u.user_id,
        u.name
    ORDER BY
        rating_count DESC,
        u.name ASC
    LIMIT 1
),
highest_avg AS (
    SELECT
        m.title AS results,
        AVG(r.rating) AS avg_rating
    FROM MovieRating AS r
    JOIN Movies AS m
        ON m.movie_id = r.movie_id
    WHERE r.created_at >= '2020-02-01'
      AND r.created_at < '2020-03-01'
    GROUP BY
        m.movie_id,
        m.title
    ORDER BY
        avg_rating DESC,
        m.title ASC
    LIMIT 1
)
SELECT results
FROM (
    SELECT
        1 AS ord,
        results
    FROM most_rated

    UNION ALL

    SELECT
        2 AS ord,
        results
    FROM highest_avg
) AS ranked
ORDER BY ord;
"""


def solve_pandas(
    movies: pd.DataFrame,
    users: pd.DataFrame,
    movie_rating: pd.DataFrame,
) -> pd.DataFrame:
    top_user = (
        movie_rating.merge(users, on="user_id", how="inner")
        .groupby(["user_id", "name"], as_index=False)
        .size()
        .sort_values(["size", "name"], ascending=[False, True])
        .iloc[0]["name"]
    )

    feb_ratings = movie_rating.loc[
        (movie_rating["created_at"] >= pd.Timestamp("2020-02-01"))
        & (movie_rating["created_at"] < pd.Timestamp("2020-03-01"))
    ]

    top_movie = (
        feb_ratings.merge(movies, on="movie_id", how="inner")
        .groupby(["movie_id", "title"], as_index=False)["rating"]
        .mean()
        .sort_values(["rating", "title"], ascending=[False, True])
        .iloc[0]["title"]
    )

    return pd.DataFrame({"results": [top_user, top_movie]})


def solve_sqlite(
    movies: pd.DataFrame,
    users: pd.DataFrame,
    movie_rating: pd.DataFrame,
) -> pd.DataFrame:
    conn = sqlite3.connect(":memory:")

    try:
        movies.to_sql("Movies", conn, index=False)
        users.to_sql("Users", conn, index=False)

        sql_movie_rating = movie_rating.copy()
        sql_movie_rating["created_at"] = sql_movie_rating["created_at"].dt.strftime(
            "%Y-%m-%d"
        )
        sql_movie_rating.to_sql("MovieRating", conn, index=False)

        return pd.read_sql_query(SQLITE_QUERY, conn)
    finally:
        conn.close()


@pytest.mark.parametrize(
    "movies_rows, users_rows, rating_rows, expected",
    [
        (
            [
                [1, "Avengers"],
                [2, "Frozen 2"],
                [3, "Joker"],
            ],
            [
                [1, "Daniel"],
                [2, "Monica"],
                [3, "Maria"],
                [4, "James"],
            ],
            [
                [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, 2, 4, "2020-02-25"],
            ],
            ["Daniel", "Frozen 2"],
        ),
        (
            [
                [1, "Alpha"],
                [2, "Beta"],
            ],
            [
                [1, "Amy"],
                [2, "Bob"],
            ],
            [
                [1, 1, 5, "2020-02-01"],
                [2, 1, 5, "2020-02-29"],
                [1, 2, 3, "2020-01-31"],
                [2, 2, 3, "2020-03-01"],
            ],
            ["Amy", "Alpha"],
        ),
        (
            [
                [1, "M1"],
                [2, "M2"],
            ],
            [
                [1, "Zoe"],
                [2, "Yan"],
            ],
            [
                [1, 1, 4, "2020-01-10"],
                [2, 1, 5, "2020-03-10"],
                [1, 2, 1, "2020-02-10"],
            ],
            ["Zoe", "M1"],
        ),
    ],
)
def test_movie_rating(movies_rows, users_rows, rating_rows, expected):
    movies = pd.DataFrame(movies_rows, columns=["movie_id", "title"])
    users = pd.DataFrame(users_rows, columns=["user_id", "name"])

    movie_rating = pd.DataFrame(
        rating_rows,
        columns=["movie_id", "user_id", "rating", "created_at"],
    )
    movie_rating["created_at"] = pd.to_datetime(movie_rating["created_at"])

    expected_df = pd.DataFrame({"results": expected})

    sql_result = solve_sqlite(movies, users, movie_rating)
    pandas_result = solve_pandas(movies, users, movie_rating)

    pd.testing.assert_frame_equal(sql_result, expected_df)
    pd.testing.assert_frame_equal(pandas_result, expected_df)

Comment on my solution¶

The overall SQL logic is correct.
The SQL date literal syntax is not SQLite-compatible; SQLite should use string dates or date functions.
UNION ALL is correct, but adding an explicit ordering column makes the output order safer.
The Pandas approach is correct and readable.
Resetting the final index is safer for exact dataframe comparisons.

WITH most_rated AS (
    SELECT
        r.user_id,
        u.name AS results,
        COUNT(*) AS r_nb
    FROM MovieRating AS r
    JOIN Users AS u
      ON r.user_id = u.user_id
    GROUP BY r.user_id, u.name
    ORDER BY r_nb DESC, u.name
    LIMIT 1
),
-- Give the movie name with the **highest average** rating in `February 2020`.
highest_avg AS (
    SELECT
        r.movie_id,
        m.title AS results,
        AVG(r.rating) AS avg_rating
    FROM MovieRating AS r
    JOIN Movies AS m
      ON r.movie_id = m.movie_id
    WHERE r.created_at BETWEEN DATE '2020-02-01' AND DATE '2020-02-29'
    GROUP BY r.movie_id, m.title
    ORDER BY avg_rating DESC, m.title
    LIMIT 1
)
SELECT results FROM most_rated
UNION ALL
SELECT results FROM highest_avg;

import pandas as pd

def movie_rating(movies: pd.DataFrame, users: pd.DataFrame, movie_rating: pd.DataFrame) -> pd.DataFrame:
    most_rated_user_id = (
        movie_rating.groupby("user_id", as_index=False)
        .agg(r_nb=("rating", "size"))
    )
    most_rated = (
        most_rated_user_id.merge(users, on="user_id", how="inner")
        .sort_values(["r_nb", "name"], ascending=[False, True])
        .head(1).rename(columns={"name": "results"})
    )

    movie_rating_february = (
        movie_rating.loc[movie_rating["created_at"].between(
            left=pd.Timestamp("2020-02-01"),
            right=pd.Timestamp("2020-02-29"),
            inclusive="both"
        )].copy()
    )

    movie_avg_rating = (
        movie_rating_february.groupby("movie_id", as_index=False)
        .agg(avg_rate=("rating", "mean"))
    )

    highest_avg_rating = (
        movie_avg_rating.merge(movies, on="movie_id", how="inner")
        .sort_values(["avg_rate", "title"], ascending=[False, True])
        .head(1).rename(columns={"title": "results"})
    )

    return pd.concat([most_rated[["results"]], highest_avg_rating[["results"]]])