Library Book Loans

Problem

Table: Books

+---------------+---------+
| Column Name   | Type    |
+---------------+---------+
| book_id       | int     |
| title         | varchar |
+---------------+---------+
book_id is the primary key for this table.
Each row of this table contains the ID and the title of one book in the library.

Table: Loans

+---------------+---------+------+
| Column Name   | Type    | Note |
+---------------+---------+------+
| loan_id       | int     |      |
| book_id       | int     |      |
| user_id       | int     |      |
| loan_date     | date    |      |
+---------------+---------+------+
loan_id is the primary key for this table.
Each row of this table records the fact that a book with ID book_id was loaned by user with ID user_id on a specific loan_date.

Problem Definition

A library wants to analyze the loan performance of each book.

Performance of a book is measured using Loan Frequency (LF), where:

`LF` = (Total number of times a book was loaned) / (Total number of books loaned)

Write a solution to find the LF of each book. Round LF to two decimal points.

Return the result table ordered by LF in descending order and by book_id in ascending order in case of a tie.

Example

Input: 
Books table:
+---------+------------+
| book_id | title      |
+---------+------------+
| 1       | Moby Dick  |
| 2       | Hamlet     |
| 3       | Don Quixote|
+---------+------------+

Loans table:
+---------+---------+---------+-----------+
| loan_id | book_id | user_id | loan_date |
+---------+---------+---------+-----------+
| 1       | 1       | 1       | 2020-07-01|
| 2       | 1       | 2       | 2020-07-02|
| 3       | 2       | 1       | 2020-07-01|
| 4       | 3       | 3       | 2020-07-04|
| 5       | 1       | 4       | 2020-07-03|
| 6       | 2       | 2       | 2020-07-05|
| 7       | 1       | 5       | 2020-07-06|
+---------+---------+---------+-----------+

Output: 
+---------+-------+
| book_id | LF    |
+---------+-------+
| 1       | 0.57  |
| 2       | 0.29  |
| 3       | 0.14  |
+---------+-------+

Try It Yourself

-- TODO: Write your user queries here

Solution

To analyze the loan performance of each book in the library, we need to calculate the Loan Frequency (LF) for every book. The LF is defined as the ratio of the total number of times a book was loaned to the total number of books loaned across all books. This metric provides insight into how frequently each book is being borrowed relative to the overall lending activity.

• Calculate Total Number of Loans: Determine the total count of all loan records to establish the denominator for the LF calculation.
• Count Loans per Book: For each book, count the number of times it has been loaned to establish the numerator for the LF.
• Compute Loan Frequency (LF): Calculate the LF for each book by dividing the number of loans per book by the total number of loans.
• Round LF to Two Decimal Places: Ensure the LF values are precise by rounding them to two decimal points.
• Order the Results: Sort the final output by LF in descending order and by book_id in ascending order in case of ties.

SQL Query

SELECT b.book_id,
       ROUND(COUNT(l.loan_id) / (SELECT COUNT(*) FROM Loans), 2) AS LF
FROM Books b
LEFT JOIN Loans l ON b.book_id = l.book_id
GROUP BY b.book_id
ORDER BY LF DESC, b.book_id ASC;

Step-by-Step Approach

Step 1: Calculate the Total Number of Loans

Determine the total number of loan records in the Loans table to use as the denominator in the LF calculation.

SQL Query:

SELECT COUNT(*) AS total_loans
FROM Loans;

Explanation:

• SELECT COUNT(*) AS total_loans:
  • Counts all rows in the Loans table, representing the total number of loans made.
• FROM Loans:
  • Specifies the Loans table as the source of data.

Output After Step 1:

Assuming the example input provided:

+------------+
| total_loans|
+------------+
| 7          |
+------------+

Step 2: Count the Number of Loans per Book

For each book, count how many times it has been loaned to identify the numerator for the LF calculation.

SQL Query:

SELECT b.book_id, COUNT(l.loan_id) AS book_loans
FROM Books b
LEFT JOIN Loans l ON b.book_id = l.book_id
GROUP BY b.book_id;

Explanation:

• SELECT b.book_id, COUNT(l.loan_id) AS book_loans:
  • Retrieves each book_id from the Books table and counts the number of corresponding loan records in the Loans table.
• FROM Books b LEFT JOIN Loans l ON b.book_id = l.book_id:
  • Performs a LEFT JOIN to include all books, even those that have never been loaned. Books without loans will have a book_loans count of 0.
• GROUP BY b.book_id:
  • Groups the results by book_id to aggregate loan counts per book.

Output After Step 2:

Based on the example input:

+---------+------------+
| book_id | book_loans |
+---------+------------+
| 1       | 4          |
| 2       | 2          |
| 3       | 1          |
+---------+------------+

Step 3: Compute Loan Frequency (LF) for Each Book

Calculate the LF for each book by dividing the number of loans per book by the total number of loans, then round the result to two decimal places.

SQL Query:

SELECT b.book_id,
       ROUND(COUNT(l.loan_id) / (SELECT COUNT(*) FROM Loans), 2) AS LF
FROM Books b
LEFT JOIN Loans l ON b.book_id = l.book_id
GROUP BY b.book_id;

Explanation:

• SELECT b.book_id, ROUND(COUNT(l.loan_id) / (SELECT COUNT(*) FROM Loans), 2) AS LF:
  • Selects each book_id and calculates the LF by dividing the count of loans for that book by the total number of loans. The ROUND function ensures the LF is rounded to two decimal places.

Output After Step 3:

Based on the example input:

+---------+------+
| book_id | LF   |
+---------+------+
| 1       | 0.57 |
| 2       | 0.29 |
| 3       | 0.14 |
+---------+------+

Step 4: Order the Results

Sort the final output by Loan Frequency (LF) in descending order. In cases where multiple books have the same LF, sort those books by book_id in ascending order.

SQL Query:

SELECT b.book_id,
       ROUND(COUNT(l.loan_id) / (SELECT COUNT(*) FROM Loans), 2) AS LF
FROM Books b
LEFT JOIN Loans l ON b.book_id = l.book_id
GROUP BY b.book_id
ORDER BY LF DESC, b.book_id ASC;

Explanation:

• ORDER BY LF DESC, b.book_id ASC:
  • Orders the results first by LF in descending order to list the highest-performing books at the top.
  • For books with identical LF values, orders them by book_id in ascending order to maintain consistency and readability.

Final Output:

+---------+------+
| book_id | LF   |
+---------+------+
| 1       | 0.57 |
| 2       | 0.29 |
| 3       | 0.14 |
+---------+------+

I just read a lesson on **Library Book Loans** (Databases) and want to truly understand it. Explain Library Book Loans from first principles using ONE vivid real-world analogy and a visual mental model — draw it as ASCII art or a clear step-by-step diagram — with a concrete example using real numbers. Then ask me one question to check I got the mental picture, and wait for my reply. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Teach me **Library Book Loans** interactively. Ask me ONE guiding question at a time, wait for my answer, and adapt to my confusion — build the idea with me step by step instead of explaining it all at once. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Quiz me on **Library Book Loans** with 5 questions, easy to tricky, ONE at a time. Tell me if each answer is right; at the end, explain clearly what I got wrong and why. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Help me remember **Library Book Loans** for the long term: give the one-sentence intuition, a memorable hook/mnemonic, a tiny worked example, and 3 active-recall flashcards (Q -> A). If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.