Find Books with No Available Copies - Practice Coding Problems

Find Books with No Available Copies - Problem

Database Easy

You are given two tables: library_books and borrowing_records.

The library_books table contains information about each book in the library, including the total number of copies owned. The borrowing_records table tracks all borrowing transactions, where return_date is NULL if the book is currently borrowed.

Write a SQL query to find all books that are currently borrowed (not returned) and have zero copies available in the library. This means all copies of the book are currently checked out.

Return the result ordered by:

Current borrowers count in descending order
Book title in ascending order

Table Schema

library_books

Column Name	Type	Description
`book_id` PK	int	Unique identifier for each book
`title`	varchar	Title of the book
`author`	varchar	Author of the book
`genre`	varchar	Genre category of the book
`publication_year`	int	Year the book was published
`total_copies`	int	Total number of copies owned by library

Primary Key: book_id

borrowing_records

Column Name	Type	Description
`record_id` PK	int	Unique identifier for each borrowing record
`book_id`	int	Foreign key referencing library_books.book_id
`borrower_name`	varchar	Name of the person who borrowed the book
`borrow_date`	date	Date when the book was borrowed
`return_date`	date	Date when book was returned (NULL if still borrowed)

Primary Key: record_id

Input & Output

Example 1 — Multiple Books with Different Availability

Input Tables:

library_books

book_id	title	author	genre	publication_year	total_copies
1	SQL Guide	John Smith	Technology	2020	2
2	Python Basics	Jane Doe	Technology	2019	1
3	History 101	Bob Wilson	History	2018	3

borrowing_records

record_id	book_id	borrower_name	borrow_date	return_date
1	1	Alice	2024-01-10
2	1	Bob	2024-01-12
3	2	Charlie	2024-01-15
4	3	David	2024-01-08	2024-01-20
5	3	Eve	2024-01-18

Output:

book_id	title	author	genre	publication_year	total_copies	current_borrowers
1	SQL Guide	John Smith	Technology	2020	2	2
2	Python Basics	Jane Doe	Technology	2019	1	1

💡 Note:

Book ID 1 'SQL Guide' has 2 total copies and 2 current borrowers (Alice and Bob with NULL return dates), so all copies are borrowed. Book ID 2 'Python Basics' has 1 total copy and 1 current borrower (Charlie), so it's also fully borrowed. Book ID 3 'History 101' has 3 copies but only 1 current borrower (Eve), so it's not included.

Example 2 — No Fully Borrowed Books

Input Tables:

library_books

book_id	title	author	genre	publication_year	total_copies
1	Math Theory	Dr. Johnson	Mathematics	2021	3
2	Art History	Sarah Lee	Arts	2020	2

borrowing_records

record_id	book_id	borrower_name	borrow_date	return_date
1	1	Tom	2024-01-10
2	2	Lisa	2024-01-12	2024-01-25

Output:

book_id	title	author	genre	publication_year	total_copies	current_borrowers

💡 Note:

No books are fully borrowed. 'Math Theory' has 3 copies but only 1 current borrower (Tom). 'Art History' has no current borrowers since Lisa already returned her copy.

Constraints

1 ≤ book_id ≤ 10000
1 ≤ record_id ≤ 100000
total_copies ≥ 1
return_date is NULL if book is currently borrowed

Visualization

Tap to expand

Understanding the Visualization

Input Tables

library_books and borrowing_records

JOIN & Filter

Combine tables and filter NULL return_date

Output

Books with all copies borrowed

Key Takeaway

🎯 Key Insight: Use HAVING to filter aggregated results where all copies are borrowed

Asked in

a Amazon 23 M Microsoft 18 G Google 15

Use INNER JOIN to combine tables, WHERE return_date IS NULL to find current borrowers, GROUP BY to count per book, and HAVING to filter books where current borrowers equals total copies.

Table Schema

library_books

Column Name	Type	Description
`book_id` PK	int	Unique identifier for each book
`title`	varchar	Title of the book
`author`	varchar	Author of the book
`genre`	varchar	Genre category of the book
`publication_year`	int	Year the book was published
`total_copies`	int	Total number of copies owned by library

Primary Key: book_id

borrowing_records

Column Name	Type	Description
`record_id` PK	int	Unique identifier for each borrowing record
`book_id`	int	Foreign key referencing library_books.book_id
`borrower_name`	varchar	Name of the person who borrowed the book
`borrow_date`	date	Date when the book was borrowed
`return_date`	date	Date when book was returned (NULL if still borrowed)

Primary Key: record_id

Common Approaches

Approach	Time	Space	Notes
✓ JOIN with GROUP BY and HAVING	O(n + m log m)	O(k)	Join tables, count current borrowers, filter where all copies are borrowed

JOIN with GROUP BY and HAVING — Algorithm Steps

Step 1: JOIN library_books with borrowing_records on book_id
Step 2: Filter for records where return_date IS NULL (currently borrowed)
Step 3: GROUP BY book details and count current borrowers
Step 4: Use HAVING to keep only books where borrowers = total_copies
Step 5: ORDER BY current borrowers DESC, then title ASC

Visualization

Tap to expand

Step-by-Step Walkthrough

JOIN

Combine tables on book_id

GROUP BY

Count current borrowers per book

HAVING

Filter where borrowers = total_copies

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n + m log m)

Join operation plus sorting for GROUP BY and ORDER BY

⚡ Linearithmic

Space Complexity

O(k)

Space for grouped results where k is number of unique books

✓ Linear Space

23.4K Views

Medium Frequency

~12 min Avg. Time

847 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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

JOIN with GROUP BY and HAVING — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler