Find Books with No Available Copies - Practice Coding Problems

Find Books with No Available Copies - Problem

Database Easy

📚 Library Management: Find Books with No Available Copies

You're helping a library manage their book inventory and borrowing system. The library has two main databases:

library_books: Contains information about all books including total copies owned
borrowing_records: Tracks when books are borrowed and returned

Your task is to find books that are in a critical situation: all copies are currently borrowed and none are available for new borrowers.

Key Rules:

A book is currently borrowed if there's a borrowing record with return_date = NULL
You need books where the number of unreturned copies equals the total copies
Results should be ordered by current borrowers (descending), then by book title (ascending)

This helps librarians identify popular books that might need additional copies purchased!

Input & Output

example_1.sql — Basic Case

$ Input: library_books: | book_id | title | author | genre | publication_year | total_copies | |---------|-------|--------|-------|------------------|-------------| | 1 | Harry Potter | J.K. Rowling | Fantasy | 1997 | 3 | | 2 | 1984 | George Orwell | Dystopian | 1949 | 2 | | 3 | Dune | Frank Herbert | Sci-Fi | 1965 | 1 | borrowing_records: | record_id | book_id | borrower_name | borrow_date | return_date | |-----------|---------|---------------|-------------|-------------| | 1 | 1 | Alice | 2024-01-01 | NULL | | 2 | 1 | Bob | 2024-01-02 | NULL | | 3 | 1 | Charlie | 2024-01-03 | NULL | | 4 | 2 | David | 2024-01-04 | NULL | | 5 | 3 | Eve | 2024-01-05 | 2024-01-10 |

› Output: | book_id | title | author | genre | publication_year | total_copies | current_borrowers | |---------|-------|--------|-------|------------------|-------------|------------------| | 1 | Harry Potter | J.K. Rowling | Fantasy | 1997 | 3 | 3 |

💡 Note: Harry Potter has 3 total copies and 3 current borrowers (Alice, Bob, Charlie), so no copies are available. 1984 has 2 total copies but only 1 current borrower. Dune has 0 current borrowers.

example_2.sql — Multiple Sold Out Books

$ Input: library_books: | book_id | title | author | total_copies | |---------|-------|--------|-------------| | 1 | Book A | Author A | 2 | | 2 | Book B | Author B | 1 | | 3 | Book C | Author C | 3 | borrowing_records: | record_id | book_id | borrower_name | return_date | |-----------|---------|---------------|-------------| | 1 | 1 | User1 | NULL | | 2 | 1 | User2 | NULL | | 3 | 2 | User3 | NULL | | 4 | 3 | User4 | NULL | | 5 | 3 | User5 | NULL | | 6 | 3 | User6 | NULL |

› Output: | book_id | title | author | total_copies | current_borrowers | |---------|-------|--------|-------------|------------------| | 3 | Book C | Author C | 3 | 3 | | 1 | Book A | Author A | 2 | 2 | | 2 | Book B | Author B | 1 | 1 |

💡 Note: All three books are completely sold out. Results are ordered by current_borrowers DESC (3, 2, 1), then by title ASC within same borrower count.

example_3.sql — Edge Case: No Sold Out Books

$ Input: library_books: | book_id | title | author | total_copies | |---------|-------|--------|-------------| | 1 | Available Book | Author | 5 | | 2 | Another Book | Author2 | 3 | borrowing_records: | record_id | book_id | borrower_name | return_date | |-----------|---------|---------------|-------------| | 1 | 1 | User1 | NULL | | 2 | 1 | User2 | 2024-01-15 | | 3 | 2 | User3 | NULL | | 4 | 2 | User4 | NULL |

› Output: Empty result set

💡 Note: No books are completely sold out. Book 1 has 1/5 copies borrowed, Book 2 has 2/3 copies borrowed. Both have available copies.

Constraints

1 ≤ book_id ≤ 10⁶
1 ≤ total_copies ≤ 100
Book titles are unique within the library
return_date is NULL for currently borrowed books
Performance requirement: Solution should handle up to 10⁵ books and 10⁶ borrowing records efficiently

Visualization

Tap to expand

Understanding the Visualization

Join Data

Combine book information with current borrowing records

Filter Active

Keep only books that are currently borrowed (return_date IS NULL)

Group & Count

Group by book and count how many people currently have each book

Find Full Books

Keep only books where current borrowers = total copies (completely unavailable)

Key Takeaway

🎯 Key Insight: Use JOIN + GROUP BY + HAVING to efficiently find books where current borrower count equals total copies in a single database operation

Asked in

a Amazon 45 G Google 38 ⊞ Microsoft 32 f Meta 25

The optimal solution uses a single JOIN query with GROUP BY and HAVING clauses. Join the tables on book_id, filter for unreturned books (return_date IS NULL), group by book details, count current borrowers, and use HAVING to keep only books where borrower count equals total copies. This approach is efficient with O(n + m) time complexity and leverages database optimization.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Multiple Subqueries)	O(n²)	O(1)	Check each book individually with separate subqueries
JOIN with GROUP BY (Optimal)	O(n + m)	O(k)	Single query using JOIN and GROUP BY to efficiently count and filter

Brute Force (Multiple Subqueries) — Algorithm Steps

Get all books from library_books table
For each book, count current borrowers with a subquery
Compare current borrowers with total_copies
Filter books where they match
Sort the results

Visualization

Tap to expand

Step-by-Step Walkthrough

Scan Books

Iterate through each book in library_books table

Count Borrowers

For each book, execute subquery to count current borrowers

Compare & Filter

Compare count with total_copies and filter matches

Repeat

Repeat process for every book (inefficient)

Code -

solution.c — C

-- Brute force approach with multiple subqueries

SELECT 
    lb.book_id,
    lb.title,
    lb.author,
    lb.total_copies,
    (
        SELECT COUNT(*) 
        FROM borrowing_records br 
        WHERE br.book_id = lb.book_id 
        AND br.return_date IS NULL
    ) as current_borrowers
FROM library_books lb
WHERE (
    SELECT COUNT(*) 
    FROM borrowing_records br 
    WHERE br.book_id = lb.book_id 
    AND br.return_date IS NULL
) = lb.total_copies
AND (
    SELECT COUNT(*) 
    FROM borrowing_records br 
    WHERE br.book_id = lb.book_id 
    AND br.return_date IS NULL
) > 0
ORDER BY current_borrowers DESC, lb.title ASC;

Time & Space Complexity

Time Complexity

⏱️

O(n²)

For each book (n), we scan borrowing records (m), leading to O(n*m) operations

⚠ Quadratic Growth

Space Complexity

O(1)

No additional space needed beyond result storage

✓ Linear Space

52.0K Views

Medium Frequency

~15 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

📚 Library Management: Find Books with No Available Copies

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force (Multiple Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler