Grokking SQL for Tech Interviews
Ask Author
Back to course home

0% completed

Vote For New Content
23. Library Book Loans
Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

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

MYSQL
MYSQL
. . . .

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 |
+---------+------+

.....

.....

.....

Like the course? Get enrolled and start learning!

Table of Contents

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible

Contents are not accessible