620. Not Boring Movies

Problem¶

Given a Cinema table, return movies whose id is odd and whose description is not exactly boring, ordered by highest rating.

Input:

# Cinema
---+------------+-------------+-------
id | movie      | description | rating
---+------------+-------------+-------
1  | 'War'      | 'great 3D'  | 8.9
2  | 'Science'  | 'fiction'   | 8.5
3  | 'irish'    | 'boring'    | 6.2
5  | 'House'    | 'Fun'       | 9.1

Output:

---+---------+-------------+-------
id | movie   | description | rating
---+---------+-------------+-------
5  | 'House' | 'Fun'       | 9.1
1  | 'War'   | 'great 3D'  | 8.9

Key trick¶

• Use modulo to keep odd ids, then filter out exactly boring, then sort descending by rating.

Trap¶

• Confusing exact string comparison with pattern matching.
• Forgetting descending order.
• In Pandas, forgetting to reset the index if the expected output is a clean result table.

Why is it interesting?¶

• It checks three core data tasks at once:
  • numeric filtering
  • string filtering
  • result ordering

SQL solution¶

sql -- SQLite SELECT id, movie, description, rating FROM Cinema WHERE id % 2 = 1 -- keep odd ids AND description <> 'boring' -- exact exclusion, not pattern matching ORDER BY rating DESC; -- highest rated first

Pandas solution¶

``python import pandas as pd

def not_boring_movies(cinema: pd.DataFrame) -> pd.DataFrame: # Build the boolean mask first for readability. mask = (cinema["id"] % 2 == 1) & (cinema["description"] != "boring")

# Filter, sort by rating descending, and return a clean result index.
return (
    cinema.loc[mask, ["id", "movie", "description", "rating"]]
    .sort_values("rating", ascending=False)
    .reset_index(drop=True)
)

``

Pytest test¶

``python import sqlite3

import pandas as pd import pytest from pandas.testing import assert_frame_equal

SQL_QUERY = """ SELECT id, movie, description, rating FROM Cinema WHERE id % 2 = 1 AND description <> 'boring' ORDER BY rating DESC; """

def not_boring_movies(cinema: pd.DataFrame) -> pd.DataFrame: mask = (cinema["id"] % 2 == 1) & (cinema["description"] != "boring")

return (
    cinema.loc[mask, ["id", "movie", "description", "rating"]]
    .sort_values("rating", ascending=False)
    .reset_index(drop=True)
)

def run_sql(rows): con = sqlite3.connect(":memory:")

con.execute(
    """
    CREATE TABLE Cinema (
        id INTEGER,
        movie TEXT,
        description TEXT,
        rating REAL
    );
    """
)

con.executemany(
    """
    INSERT INTO Cinema (id, movie, description, rating)
    VALUES (?, ?, ?, ?);
    """,
    rows,
)

result = pd.read_sql_query(SQL_QUERY, con)
con.close()

return result

@pytest.mark.parametrize( "rows, expected_rows", [ ( [ (1, "War", "great 3D", 8.9), (2, "Science", "fiction", 8.5), (3, "irish", "boring", 6.2), (4, "Ice song", "Fantacy", 8.6), (5, "House card", "Interesting", 9.1), ], [ (5, "House card", "Interesting", 9.1), (1, "War", "great 3D", 8.9), ], ), ( [ (2, "Even A", "fun", 9.9), (4, "Even B", "great", 8.8), (1, "Odd boring", "boring", 7.7), ], [], ), ( [ (1, "A", "fun", 5.0), (3, "B", "great", 9.0), (5, "C", "nice", 7.0), ], [ (3, "B", "great", 9.0), (5, "C", "nice", 7.0), (1, "A", "fun", 5.0), ], ), ( [], [], ), ], ) def test_not_boring_movies_sql_and_pandas(rows, expected_rows): columns = ["id", "movie", "description", "rating"]

cinema = pd.DataFrame(rows, columns=columns)
expected = pd.DataFrame(expected_rows, columns=columns)

sql_result = run_sql(rows)
pandas_result = not_boring_movies(cinema)

assert_frame_equal(sql_result, expected, check_dtype=False)
assert_frame_equal(pandas_result, expected, check_dtype=False)

``

Comment on my solution¶

• Your SQL solution is correct.
• Prefer exact inequality over pattern matching here because the problem says the description is not exactly boring.
• Your Pandas solution is correct.
• Adding reset_index(drop=True) makes the returned table cleaner and more interview-ready.

SELECT *
FROM Cinema
WHERE description NOT LIKE 'boring'
  AND id % 2 = 1
ORDER BY rating DESC;

import pandas as pd

def not_boring_movies(cinema: pd.DataFrame) -> pd.DataFrame:
    return (
        cinema[(cinema["description"] != "boring") & (cinema["id"] % 2 == 1)]
        .sort_values(["rating"], ascending=[False])
    )

Extra¶

.reset_index()¶

In python pandas, what does drop=True means in .reset_index(drop=True)?

drop=True means:

• When resetting the index, pandas discards the old index instead of adding it as a new column.

Example:

python df.reset_index(drop=True)

• drop=False or default:
  • old index becomes a column
• drop=True:
  • old index is removed
  • a fresh default integer index is created

df = pd.DataFrame({"id": ["a", "b", "c"]})
df.sort_values("id", ascending=False)
#   id
# 2  c
# 1  b
# 0  a

df.sort_values("id", ascending=False).reset_index(drop=False)
#    index id
# 0      2  c
# 1      1  b
# 2      0  a

df.sort_values("id", ascending=False).reset_index(drop=True)
#   id
# 0  c
# 1  b
# 2  a