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" | +-----------+--------------------+
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.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| LEFT JOIN + GROUP BY Aggregation | O(B + O) | O(1) | General case; simplest query structure and commonly expected in SQL interviews |
| Pre-aggregated Orders Subquery | O(B + O) | O(B) | Useful when you want to isolate aggregation logic or reuse summarized order data |
Leetcode MEDIUM 1098 - Unpopular Books DATE_SUB() TIMESTAMPDIFF - Explained by Everyday Data Science • Everyday Data Science • 948 views views
Practice Unpopular Books with our built-in code editor and test cases.
Practice on FleetCode