Skip to main content
Talentd

Books with NULL Ratings - Solution & Explanation

EasyPremiumFree on FleetCodeDatabase4 min read
Practice this problem

Problem Statement

Table: books

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| book_id        | int     |
| title          | varchar |
| author         | varchar |
| published_year | int     |
| rating         | decimal |
+----------------+---------+
book_id is the unique key for this table.
Each row of this table contains information about a book including its unique ID, title, author, publication year, and rating.
rating can be NULL, indicating that the book hasn't been rated yet.

Write a solution to find all books that have not been rated yet (i.e., have a NULL rating).

Return the result table ordered by book_id in ascending order.

The result format is in the following example.

 

Example:

Input:

books table:

+---------+------------------------+------------------+----------------+--------+
| book_id | title                  | author           | published_year | rating |
+---------+------------------------+------------------+----------------+--------+
| 1       | The Great Gatsby       | F. Scott         | 1925           | 4.5    |
| 2       | To Kill a Mockingbird  | Harper Lee       | 1960           | NULL   |
| 3       | Pride and Prejudice    | Jane Austen      | 1813           | 4.8    |
| 4       | The Catcher in the Rye | J.D. Salinger    | 1951           | NULL   |
| 5       | Animal Farm            | George Orwell    | 1945           | 4.2    |
| 6       | Lord of the Flies      | William Golding  | 1954           | NULL   |
+---------+------------------------+------------------+----------------+--------+

Output:

+---------+------------------------+------------------+----------------+
| book_id | title                  | author           | published_year |
+---------+------------------------+------------------+----------------+
| 2       | To Kill a Mockingbird  | Harper Lee       | 1960           |
| 4       | The Catcher in the Rye | J.D. Salinger    | 1951           |
| 6       | Lord of the Flies      | William Golding  | 1954           |
+---------+------------------------+------------------+----------------+

Explanation:

  • The books with book_id 2, 4, and 6 have NULL ratings.
  • These books are included in the result table.
  • The other books (book_id 1, 3, and 5) have ratings and are not included.
The result is ordered by book_id in ascending order

Approach Overview

Problem Overview: The task is to return all books whose rating value is NULL. In SQL-style datasets, NULL represents missing or unknown data, so the query must explicitly check for that condition rather than comparing with standard equality operators.

Approach 1: Conditional Filtering (SQL / MySQL) (Time: O(n), Space: O(1))

Scan the Books table and filter rows where the rating column is NULL. SQL treats NULL as a special marker for missing values, so using = NULL will not work. Instead, the correct condition is IS NULL. The database engine evaluates this condition for each row during the table scan and returns only those entries with missing ratings. This approach is straightforward and commonly used in SQL queries when dealing with incomplete datasets.

Because the database must check each row's rating value, the time complexity is O(n), where n is the number of rows in the table. Space complexity remains O(1) since the query only filters rows without creating additional structures.

Approach 2: Conditional Filtering (Pandas) (Time: O(n), Space: O(n))

When solving the same problem using Pandas, load the Books table into a DataFrame and apply a boolean filter using isna() or isnull(). These methods detect missing values in a column and return a boolean mask. Applying that mask to the DataFrame keeps only rows where the rating is missing. This pattern is common in database-style data analysis tasks where datasets contain incomplete records.

The operation scans the column once, giving a time complexity of O(n). The boolean mask created by Pandas requires additional memory proportional to the dataset size, so the space complexity is O(n).

Recommended for interviews: Interviewers expect the SQL IS NULL filtering approach. The key detail they look for is understanding that NULL cannot be compared using =. Demonstrating the correct conditional filter shows familiarity with SQL semantics and real-world data handling.

Solution

We directly filter out books where rating is NULL, then sort them in ascending order by book_id.

Note that the result set should only include the fields book_id, title, author, and published_year.

Code

MySQLPandas

MySQL

Pandas

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
SQL Conditional Filtering (IS NULL)O(n)O(1)Standard database queries when filtering rows with missing values
Pandas DataFrame Filtering (isna)O(n)O(n)Data analysis workflows where the table is loaded into a Pandas DataFrame

Video Solution

Leetcode 3358 - Books With NULL Ratings - Filtering and Ordering Data in SQL | Everyday Data Science • Everyday Data Science • 591 views views

Frequently Asked Questions

Books with NULL Ratings Python solution
In Python using Pandas, filter the DataFrame with df[df['rating'].isna()]. The isna() function detects missing values and returns a boolean mask that keeps only rows where the rating column is NULL. The operation runs in O(n) time.
Is Books with NULL Ratings easy or hard?
Books with NULL Ratings is categorized as an Easy database problem. The main concept is understanding how SQL handles NULL values and applying the IS NULL condition correctly.
How to solve Books with NULL Ratings in O(n)?
Use a conditional filter that checks for missing values in the rating column. In SQL, apply WHERE rating IS NULL to return only rows with NULL ratings. The database performs a single pass through the table, resulting in O(n) time complexity.
What is the best approach for Books with NULL Ratings?
The best approach is conditional filtering using the SQL IS NULL operator. It directly checks for missing values in the rating column and returns rows where the value is not defined. This runs in O(n) time because the database scans each row in the table.
Is Books with NULL Ratings asked at Google/Amazon/Meta?
Problems involving NULL filtering and SQL data cleaning frequently appear in database interviews at companies like Amazon, Google, and Meta. Interviewers use them to test understanding of SQL semantics and handling of missing data.
What data structure is used in Books with NULL Ratings?
The problem operates on a relational database table. In SQL solutions, the table is scanned with a filtering condition. In Pandas implementations, the data is stored in a DataFrame and filtered using a boolean mask created by isna() or isnull().
What is the time complexity of Books with NULL Ratings?
The query typically runs in O(n) time where n is the number of rows in the Books table. The database engine evaluates the IS NULL condition for each record during the table scan. Space complexity is O(1) for SQL queries since no additional data structures are required.

Ready to solve this problem?

Practice Books with NULL Ratings with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary