Welcome to Subscribe On Youtube

3570. Find Books with No Available Copies

Description

Table: library_books

+++
\| book_id          \| int     \|
\| title            \| varchar \|
\| author           \| varchar \|
\| genre            \| varchar \|
\| publication_year \| int     \|
\| total_copies     \| int     \|
++++++++++++++--++-+-+
\| record_id \| book_id \| borrower_name \| borrow_date \| return_date \|
++--++-+-+

Output:

++++--++-+
\| 1       \| The Great Gatsby \| F. Scott      \| Fiction   \| 1925             \| 3                 \| 
\| 3       \| 1984             \| George Orwell \| Dystopian \| 1949             \| 1                 \|
++----+

Explanation:

  • The Great Gatsby (book_id = 1):
    • Total copies: 3
    • Currently borrowed by Alice Smith, Bob Johnson, and Grace Miller (3 borrowers)
    • Available copies: 3 - 3 = 0
    • Included because available_copies = 0
  • 1984 (book_id = 3):
    • Total copies: 1
    • Currently borrowed by David Brown (1 borrower)
    • Available copies: 1 - 1 = 0
    • Included because available_copies = 0
  • Books not included:
    • To Kill a Mockingbird (book_id = 2): Total copies = 3, current borrowers = 2, available = 1
    • Pride and Prejudice (book_id = 4): Total copies = 2, current borrowers = 1, available = 1
    • The Catcher in the Rye (book_id = 5): Total copies = 1, current borrowers = 0, available = 1
    • Brave New World (book_id = 6): Total copies = 4, current borrowers = 1, available = 3
  • Result ordering:
    • The Great Gatsby appears first with 3 current borrowers
    • 1984 appears second with 1 current borrower

Output table is ordered by current_borrowers in descending order, then by book_title in ascending order.

</div>

Solutions

Solution 1: Group Aggregation + Join Query

First, we count the current number of borrowers for each book, then join this result with the book information table to filter out books where the current number of borrowers equals the total number of copies. Finally, we sort the results by the number of current borrowers in descending order, and if there is a tie, by book title in ascending order.

  • import pandas as pd


def find_books_with_no_available_copies(
    library_books: pd.DataFrame, borrowing_records: pd.DataFrame
) -> pd.DataFrame:
    current_borrowers = (
        borrowing_records[borrowing_records["return_date"].isna()]
        .groupby("book_id")
        .size()
        .rename("current_borrowers")
        .reset_index()
    )

    merged = library_books.merge(current_borrowers, on="book_id", how="inner")
    fully_borrowed = merged[merged["current_borrowers"] == merged["total_copies"]]
    fully_borrowed = fully_borrowed.sort_values(
        by=["current_borrowers", "title"], ascending=[False, True]
    )

    cols = [
        "book_id",
        "title",
        "author",
        "genre",
        "publication_year",
        "current_borrowers",
    ]
    return fully_borrowed[cols].reset_index(drop=True)


  • # Write your MySQL query statement below
WITH
    T AS (
        SELECT book_id, COUNT(1) current_borrowers
        FROM borrowing_records
        WHERE return_date IS NULL
        GROUP BY 1
    )
SELECT book_id, title, author, genre, publication_year, current_borrowers
FROM
    library_books
    JOIN T USING (book_id)
WHERE current_borrowers = total_copies
ORDER BY 6 DESC, 2;

All Problems

All Solutions