Find books with no available copies

#
# Table: library_books
# +------------------+---------+
# | Column Name      | Type    |
# +------------------+---------+
# | book_id          | int     |
# | title            | varchar |
# | author           | varchar |
# | genre            | varchar |
# | publication_year | int     |
# | total_copies     | int     |
# +------------------+---------+
# book_id is the unique identifier for this table.
# Each row contains information about a book in the library, including the total number of copies owned by the library.
# Table: borrowing_records
# +---------------+---------+
# | Column Name   | Type    |
# +---------------+---------+
# | record_id     | int     |
# | book_id       | int     |
# | borrower_name | varchar |
# | borrow_date   | date    |
# | return_date   | date    |
# +---------------+---------+
# record_id is the unique identifier for this table.
# Each row represents a borrowing transaction and return_date is NULL if the book is currently borrowed and hasn't been returned yet.
# Write a solution to find all books that are currently borrowed (not returned) and have zero copies available in the library.
# A book is considered currently borrowed if there exists a borrowing record with a NULL return_date
# Return the result table ordered by current borrowers in descending order, then by book title in ascending order.
# The result format is in the following example.
# Example:
# Input:
# library_books table:
# +---------+------------------------+------------------+----------+------------------+--------------+
# | book_id | title                  | author           | genre    | publication_year | total_copies |
# +---------+------------------------+------------------+----------+------------------+--------------+
# | 1       | The Great Gatsby       | F. Scott         | Fiction  | 1925             | 3            |
# | 2       | To Kill a Mockingbird  | Harper Lee       | Fiction  | 1960             | 3            |
# | 3       | 1984                   | George Orwell    | Dystopian| 1949             | 1            |
# | 4       | Pride and Prejudice    | Jane Austen      | Romance  | 1813             | 2            |
# | 5       | The Catcher in the Rye | J.D. Salinger    | Fiction  | 1951             | 1            |
# | 6       | Brave New World        | Aldous Huxley    | Dystopian| 1932             | 4            |
# +---------+------------------------+------------------+----------+------------------+--------------+
# borrowing_records table:
# +-----------+---------+---------------+-------------+-------------+
# | record_id | book_id | borrower_name | borrow_date | return_date |
# +-----------+---------+---------------+-------------+-------------+
# | 1         | 1       | Alice Smith   | 2024-01-15  | NULL        |
# | 2         | 1       | Bob Johnson   | 2024-01-20  | NULL        |
# | 3         | 2       | Carol White   | 2024-01-10  | 2024-01-25  |
# | 4         | 3       | David Brown   | 2024-02-01  | NULL        |
# | 5         | 4       | Emma Wilson   | 2024-01-05  | NULL        |
# | 6         | 5       | Frank Davis   | 2024-01-18  | 2024-02-10  |
# | 7         | 1       | Grace Miller  | 2024-02-05  | NULL        |
# | 8         | 6       | Henry Taylor  | 2024-01-12  | NULL        |
# | 9         | 2       | Ivan Clark    | 2024-02-12  | NULL        |
# | 10        | 2       | Jane Adams    | 2024-02-15  | NULL        |
# +-----------+---------+---------------+-------------+-------------+
# Output:
# +---------+------------------+---------------+-----------+------------------+-------------------+
# | book_id | title            | author        | genre     | publication_year | current_borrowers |
# +---------+------------------+---------------+-----------+------------------+-------------------+
# | 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.
#

# Test case:
# CREATE TABLE library_books (
#     book_id INT, title VARCHAR(255), author VARCHAR(255),
#     genre VARCHAR(100), publication_year INT, total_copies INT
# );
# INSERT INTO library_books VALUES
#     (1,'Becoming','Michelle Obama','Non-Fiction',1994,2),
#     (2,'The Catcher in the Rye','J.D. Salinger','Fiction',1904,1),
#     (3,'Educated','Tara Westover','Non-Fiction',1964,3),
#     (4,'Thinking Fast and Slow','Daniel Kahneman','Non-Fiction',1983,4),
#     (5,'Harry Potter','J.K. Rowling','Fantasy',1957,3),
#     (6,'Great Expectations','Charles Dickens','Fiction',2003,2),
#     (7,'The Chronicles of Narnia','C.S. Lewis','Fantasy',1935,2),
#     (8,'Outlander','Diana Gabaldon','Romance',1911,2),
#     (9,'The Time Traveler''s Wife','Audrey Niffenegger','Romance',1977,2),
#     (10,'Wuthering Heights','Emily Bronte','Fiction',1958,3),
#     (11,'The Lord of the Rings','J.R.R. Tolkien','Fantasy',1970,2),
#     (12,'The Fault in Our Stars','John Green','Romance',1924,4),
#     (13,'To Kill a Mockingbird','Harper Lee','Fiction',1998,2),
#     (14,'The Great Gatsby','F. Scott Fitzgerald','Fiction',2010,2),
#     (15,'Me Before You','Jojo Moyes','Romance',1981,4),
#     (16,'The Immortal Life','Rebecca Skloot','Non-Fiction',1945,2),
#     (17,'The Notebook','Nicholas Sparks','Romance',1977,3),
#     (18,'Sapiens','Yuval Noah Harari','Non-Fiction',1959,2);
# CREATE TABLE borrowing_records (
#     record_id INT, book_id INT, borrower_name VARCHAR(255),
#     borrow_date DATE, return_date DATE
# );
# INSERT INTO borrowing_records VALUES
#     (1,4,'Mia Anderson','2024-01-05','2024-01-30'),
#     (2,4,'Wendy Jackson','2024-01-14','2024-02-02'),
#     (3,5,'Emma Anderson','2024-01-09','2024-02-02'),
#     (4,5,'Ruby Anderson','2024-02-17','2024-03-13'),
#     (5,5,'Mia White','2024-01-15',NULL),
#     (6,6,'Quinn Garcia','2024-01-06',NULL),
#     (7,8,'Uma Wilson','2024-02-20',NULL),
#     (8,8,'Victor Martin','2024-02-08',NULL),
#     (9,10,'Mia Rodriguez','2024-01-30','2024-02-23'),
#     (10,10,'Alice Lee','2024-02-16','2024-03-11'),
#     (11,10,'Yolanda Anderson','2024-02-19','2024-02-29'),
#     (12,11,'Noah Wilson','2024-01-30',NULL),
#     (13,11,'Alice Hall','2024-02-26',NULL),
#     (14,11,'Xavier Anderson','2024-02-02',NULL),
#     (15,12,'David Lewis','2024-01-20',NULL),
#     (16,12,'Quinn Rodriguez','2024-01-13','2024-01-25'),
#     (17,12,'Ruby Martinez','2024-02-28','2024-03-16'),
#     (18,12,'Paul Smith','2024-01-08',NULL),
#     (19,13,'Henry Johnson','2024-01-16',NULL),
#     (20,13,'Carol Brown','2024-02-16','2024-02-25'),
#     (21,14,'Emma Lee','2024-01-31',NULL),
#     (22,15,'Ivy Martinez','2024-02-25','2024-03-09'),
#     (23,16,'Wendy Thomas','2024-01-26',NULL),
#     (24,17,'Olivia Martinez','2024-01-29','2024-02-12'),
#     (25,17,'Carol Jackson','2024-01-02','2024-01-16'),
#     (26,18,'Henry Smith','2024-01-05',NULL),
#     (27,18,'Wendy Lewis','2024-01-04',NULL),
#     (28,18,'Henry Brown','2024-02-27','2024-03-15'),
#     (29,18,'Carol Martinez','2024-01-16','2024-02-07');
#
# Expected output:
# +---------+-----------+-------------------+-------------+------------------+-------------------+
# | book_id | title     | author            | genre       | publication_year | current_borrowers |
# +---------+-----------+-------------------+-------------+------------------+-------------------+
# | 8       | Outlander | Diana Gabaldon    | Romance     | 1911             | 2                 |
# | 18      | Sapiens   | Yuval Noah Harari | Non-Fiction | 1959             | 2                 |
# +---------+-----------+-------------------+-------------+------------------+-------------------+
# Note: book_id 11 (The Lord of the Rings) has 3 active borrows but total_copies=2,
# so available = 2 - 3 = -1, which is a data inconsistency and not "zero available"
# (available_copies = total_copies - current_borrowers = 0, strictly)

# Write your MySQL query statement below

SELECT lb.book_id, lb.title, lb.author, lb.genre, lb.publication_year,
  COUNT(br.record_id) AS current_borrowers
FROM library_books lb
JOIN borrowing_records br ON lb.book_id = br.book_id AND br.return_date IS NULL
GROUP BY lb.book_id, lb.title, lb.author, lb.genre, lb.publication_year, lb.total_copies
HAVING COUNT(br.record_id) = lb.total_copies
ORDER BY current_borrowers DESC, lb.title ASC;