0% completed
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
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.
- Counts all rows in the
FROM Loans
:- Specifies the
Loans
table as the source of data.
- Specifies the
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 theBooks
table and counts the number of corresponding loan records in theLoans
table.
- Retrieves each
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.
- Performs a LEFT JOIN to include all books, even those that have never been loaned. Books without loans will have a
GROUP BY b.book_id
:- Groups the results by
book_id
to aggregate loan counts per book.
- Groups the results by
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. TheROUND
function ensures the LF is rounded to two decimal places.
- Selects each
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.
- Orders the results first by
Final Output:
+---------+------+ | book_id | LF | +---------+------+ | 1 | 0.57 | | 2 | 0.29 | | 3 | 0.14 | +---------+------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible