Find Books with No Available Copies

Difficulty: Easy | Acceptance: 54.10% | Paid: No Topics: Database

Table: Books +-------------+---------+ | Column Name | Type | +-------------+---------+ | book_id | int | | title | varchar | | author | varchar | | published_year | int | | stock_quantity | int | +-------------+---------+ book_id is the primary key (column with unique values) for this table.

Table: Loans +--------------+---------+ | Column Name | Type | +--------------+---------+ | loan_id | int | | book_id | int | | user_id | int | | loan_date | date | | return_date | date | +--------------+---------+ loan_id is the primary key (column with unique values) for this table. return_date is NULL if the book has not been returned yet. book_id is a foreign key (reference column) to the Books table.

Write a solution to find the book_id and title of books that have no available copies.

A book has no available copies if the number of copies currently on loan (where return_date is NULL) is equal to the stock_quantity.

The result can be returned in any order.

• Examples
• Constraints
• Approach 1: Subquery in WHERE Clause
• Approach 2: LEFT JOIN with Aggregation

Examples

Example 1:

Input: Books table: +---------+-----------------------+--------+----------------+----------------+ | book_id | title | author | published_year | stock_quantity | +---------+-----------------------+--------+----------------+----------------+ | 1 | The Great Gatsby | F. | 1925 | 3 | | 2 | 1984 | G. | 1949 | 2 | | 3 | The Catcher in the Rye| J. | 1951 | 1 | +---------+-----------------------+--------+----------------+----------------+ Loans table: +----------+---------+---------+------------+--------------+ | loan_id | book_id | user_id | loan_date | return_date | +----------+---------+---------+------------+--------------+ | 1 | 1 | 101 | 2023-01-01 | 2023-01-10 | | 2 | 1 | 102 | 2023-01-05 | NULL | | 3 | 1 | 103 | 2023-01-06 | NULL | | 4 | 2 | 104 | 2023-01-07 | NULL | | 5 | 2 | 105 | 2023-01-08 | NULL | +----------+---------+---------+------------+--------------+

Output: +---------+-------+ | book_id | title | +---------+-------+ | 2 | 1984 | +---------+-------+

Explanation:

• Book 1 has 3 copies in stock. 2 copies are currently on loan (loan_id 2 and 3). 3 != 2, so it has available copies.
• Book 2 has 2 copies in stock. 2 copies are currently on loan (loan_id 4 and 5). 2 == 2, so it has no available copies.
• Book 3 has 1 copy in stock. 0 copies are on loan. 1 != 0, so it has available copies.

Constraints

1 <= stock_quantity <= 10^5

Approach 1: Subquery in WHERE Clause

Intuition We can directly compare the stock_quantity of each book with the count of its active loans. A subquery in the WHERE clause allows us to filter books where the count of unreturned loans matches the total stock.

Steps

• Select book_id and title from the Books table.
• Filter rows where stock_quantity equals the result of a subquery.
• The subquery counts the number of records in the Loans table for the current book_id where return_date is NULL.

# LeetCode SQL problems require SQL solutions.
# This Python block contains the SQL query as a string for display purposes.

solution = """
SELECT book_id, title
FROM Books
WHERE stock_quantity = (
    SELECT COUNT(*)
    FROM Loans
    WHERE book_id = Books.book_id AND return_date IS NULL
)
"""

Complexity

• Time: O(n * m) in the worst case where n is books and m is loans, though optimized by database indexes.
• Space: O(k) where k is the number of books with no available copies.
• Notes: The correlated subquery runs once for each row in the Books table.

Approach 2: LEFT JOIN with Aggregation

Intuition We can pre-calculate the number of active loans for each book using a GROUP BY query, then join this result with the Books table to compare the counts.

Steps

• Create a derived table (subquery) that counts active loans (return_date IS NULL) grouped by book_id.
• Perform a LEFT JOIN between the Books table and this derived table on book_id.
• Use COALESCE to handle books with zero active loans (treating NULL as 0).
• Filter the results where stock_quantity equals the count of active loans.

# LeetCode SQL problems require SQL solutions.
# This Python block contains the SQL query as a string for display purposes.

solution = """
SELECT b.book_id, b.title
FROM Books b
LEFT JOIN (
    SELECT book_id, COUNT(*) as active_loans
    FROM Loans
    WHERE return_date IS NULL
    GROUP BY book_id
) l ON b.book_id = l.book_id
WHERE b.stock_quantity = COALESCE(l.active_loans, 0)
"""

Complexity

• Time: O(n + m) for scanning and joining, assuming efficient hashing or sorting.
• Space: O(n) for storing the intermediate aggregation results.
• Notes: This approach can be more efficient than a correlated subquery on large datasets because the aggregation happens once.