Skip to main content
Talentd

Unpopular Books - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min readAsked at: Meta
Practice this problem

Problem Statement

Table: Books

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| book_id        | int     |
| name           | varchar |
| available_from | date    |
+----------------+---------+
book_id is the primary key (column with unique values) of this table.

 

Table: Orders

+----------------+---------+
| Column Name    | Type    |
+----------------+---------+
| order_id       | int     |
| book_id        | int     |
| quantity       | int     |
| dispatch_date  | date    |
+----------------+---------+
order_id is the primary key (column with unique values) of this table.
book_id is a foreign key (reference column) to the Books table.

 

Write a solution to report the books that have sold less than 10 copies in the last year, excluding books that have been available for less than one month from today. Assume today is 2019-06-23.

Return the result table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Books table:
+---------+--------------------+----------------+
| book_id | name               | available_from |
+---------+--------------------+----------------+
| 1       | "Kalila And Demna" | 2010-01-01     |
| 2       | "28 Letters"       | 2012-05-12     |
| 3       | "The Hobbit"       | 2019-06-10     |
| 4       | "13 Reasons Why"   | 2019-06-01     |
| 5       | "The Hunger Games" | 2008-09-21     |
+---------+--------------------+----------------+
Orders table:
+----------+---------+----------+---------------+
| order_id | book_id | quantity | dispatch_date |
+----------+---------+----------+---------------+
| 1        | 1       | 2        | 2018-07-26    |
| 2        | 1       | 1        | 2018-11-05    |
| 3        | 3       | 8        | 2019-06-11    |
| 4        | 4       | 6        | 2019-06-05    |
| 5        | 4       | 5        | 2019-06-20    |
| 6        | 5       | 9        | 2009-02-02    |
| 7        | 5       | 8        | 2010-04-13    |
+----------+---------+----------+---------------+
Output: 
+-----------+--------------------+
| book_id   | name               |
+-----------+--------------------+
| 1         | "Kalila And Demna" |
| 2         | "28 Letters"       |
| 5         | "The Hunger Games" |
+-----------+--------------------+

Approach Overview

Problem Overview: You have two tables: Books and Orders. The task is to return books that sold fewer than 10 copies in the last year while excluding books that were added recently. Only books available for at least one month before the reference date should be considered.

Approach 1: LEFT JOIN + Aggregation (O(B + O) time, O(1) extra space)

The clean solution joins Books with Orders using a LEFT JOIN. Filter orders to the last year using a condition on dispatch_date, then aggregate sales per book with SUM(quantity). Books without orders still appear because of the LEFT JOIN, which is critical for identifying zero-sales books. Use GROUP BY book_id and a HAVING clause to keep only totals less than 10. Also filter books whose available_from date is at least one month before the reference date so newly added books are excluded.

This approach scans both tables once and performs aggregation per book. Databases handle this efficiently with indexes on book_id and dispatch_date. The pattern of joining then aggregating appears frequently in SQL and database interview questions.

Approach 2: Pre-aggregated Subquery (O(B + O) time, O(B) intermediate space)

Another option aggregates the Orders table first in a subquery. Compute total quantity per book_id within the last year using GROUP BY. Then join that result with the Books table. Use COALESCE(total_quantity, 0) so books without orders count as zero sales. Apply a filter to keep totals below 10 and exclude books released within the last month.

This structure can be easier to reason about when debugging because the aggregation logic is isolated. It also mirrors patterns commonly used in reporting queries and SQL joins.

Recommended for interviews: The LEFT JOIN + GROUP BY solution is what most interviewers expect. It shows you understand joins, filtering by date ranges, and aggregation logic. The subquery approach is equally valid but slightly more verbose. Demonstrating both signals strong SQL fundamentals.

Solution

Code

MySQL

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
LEFT JOIN + GROUP BY AggregationO(B + O)O(1)General case; simplest query structure and commonly expected in SQL interviews
Pre-aggregated Orders SubqueryO(B + O)O(B)Useful when you want to isolate aggregation logic or reuse summarized order data

Video Solution

Leetcode MEDIUM 1098 - Unpopular Books DATE_SUB() TIMESTAMPDIFF - Explained by Everyday Data Science • Everyday Data Science • 1,009 views views

Frequently Asked Questions

Is Unpopular Books easy or hard?
Unpopular Books is generally considered a medium-level SQL problem. The challenge comes from combining multiple conditions: filtering by date range, excluding recently added books, and counting total sales with aggregation. Understanding LEFT JOIN behavior with missing rows is the key concept.
Unpopular Books Python/Java solution
This problem is solved with SQL instead of Python or Java because it operates directly on relational tables. In coding interviews, the expected answer is a MySQL query using LEFT JOIN, SUM aggregation, and GROUP BY filtering. Application languages would simply execute the SQL query against the database.
How to solve Unpopular Books in O(n)?
Filter orders within the last year, aggregate quantities by book_id using GROUP BY, and join the result with the Books table. Apply a HAVING clause to keep totals below 10 and filter out books added in the last month. Since each table is scanned once and aggregated, the overall complexity is linear relative to the number of rows.
What is the best approach for Unpopular Books?
The most common solution uses a LEFT JOIN between Books and Orders, filters orders from the last year, and aggregates with SUM(quantity). GROUP BY book_id and HAVING SUM(quantity) < 10 identifies books with low sales. This approach also captures books with zero sales because the LEFT JOIN preserves unmatched rows.
Is Unpopular Books asked at Google/Amazon/Meta?
Problems like Unpopular Books reflect common SQL interview patterns used by companies such as Amazon, Meta, and Google. They test joins, date filtering, and aggregation logic rather than advanced algorithms. Similar database questions appear frequently in backend and data engineering interviews.
What data structure is used in Unpopular Books?
The problem relies on relational database operations rather than traditional in-memory data structures. SQL concepts such as joins, grouping, and aggregation act as the primary mechanisms. GROUP BY effectively builds grouped sets of rows, similar to hash-based grouping in database engines.
What is the time complexity of Unpopular Books?
The query typically runs in O(B + O) time where B is the number of books and O is the number of orders. The database scans the orders for the relevant date range and aggregates results by book_id. With indexes on book_id and dispatch_date, the query performs efficiently even on large datasets.

Ready to solve this problem?

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

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary