0% completed
Problem Statement
Table: Patrons
Each row in this table represents an individual library patron, detailing their unique ID and name.
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| patron_id | int |
| patron_name | varchar |
+-------------+---------+
patron_id is the primary key for this table.
Each row contains the name and the ID of a library patron.
Table: Checkouts
This table tracks the checkout records of books by patrons, including the book ID and the patron ID who checked out the book.
+-----------+----------+
| Column Name | Type |
+-------------+--------+
| book_id | int |
| patron_id | int |
+-------------+--------+
(book_id, patron_id) is the primary key for this table.
Each row contains the ID of a book and the ID of the patron who checked it out.
Problem Definition
Develop a solution to calculate the percentage of patrons who have checked out each book, rounded to two decimals.
Return the result table ordered by percentage in descending order. In case of a tie, order it by book_id in ascending order.
Example
Input:
Patrons table: +-----------+-------------+ | patron_id | patron_name | +-----------+-------------+ | 1 | Alice | | 2 | Bob | | 3 | Alex | +-----------+-------------+
Checkouts table: +---------+-----------+ | book_id | patron_id | +---------+-----------+ | 101 | 1 | | 102 | 2 | | 101 | 2 | | 103 | 1 | | 101 | 3 | | 102 | 3 | | 103 | 2 | | 104 | 1 | | 105 | 2 | +---------+-----------+
Output:
+---------+------------+ | book_id | percentage | +---------+------------+ | 101 | 100.00 | | 102 | 66.67 | | 103 | 66.67 | | 104 | 33.33 | | 105 | 33.33 | +---------+------------+
Try It Yourself
Solution
To solve this problem, we need to calculate the unique number of patrons who have checked out each book and divide this by the total number of patrons in the library, then multiply by 100 to get the percentage.
- Count Unique Checkouts: Count the unique number of patrons who have checked out each book.
- Calculate Total Number of Patrons: Determine the total number of patrons in the library.
- Calculate Percentage: Divide the unique checkout count by the total number of patrons and multiply by 100 to get the percentage. We also round the resulting percentage to two decimal places.
- Order the Result: by
percentage
in descending order and bybook_id
in ascending order in case of a tie.
SQL Query
SELECT book_id, ROUND((COUNT(DISTINCT patron_id) / (SELECT COUNT(*) FROM Patrons) * 100), 2) AS percentage FROM Checkouts GROUP BY book_id ORDER BY percentage DESC, book_id;
Step by Step Approach
Step 1: Count Unique Checkouts
Count the unique number of patrons for each book in the Checkouts
table.
SELECT book_id, COUNT(DISTINCT patron_id) AS unique_patrons FROM Checkouts GROUP BY book_id
Output After Step 1:
+---------+---------------+ | book_id | unique_patrons| +---------+---------------+ | 101 | 3 | | 102 | 2 | | 103 | 2 | | 104 | 1 | | 105 | 1 | +---------+---------------+
Step 2: Calculate Total Number of Patrons
Determine the total number of patrons by counting all entries in the Patrons
table.
SELECT COUNT(*) FROM Patrons
Output After Step 2:
3
Step 3: Calculate Percentage
Combine the results of Step 1 and Step 2 to calculate the checkout percentage for each book.
SELECT book_id, ROUND((unique_patrons / total_patrons * 100), 2) AS percentage FROM Checkouts GROUP BY book_id
Output After Step 3:
+---------+------------+ | book_id | percentage | +---------+------------+ | 101 | 100.00 | | 102 | 66.67 | | 103 | 66.67 | | 104 | 33.33 | | 105 | 33.33 | +---------+------------+
Step 4: Order the Result
Finally, order the results by percentage
in descending order and by book_id
in ascending order in case of a tie.
SELECT book_id, ROUND((COUNT(DISTINCT patron_id) / (SELECT COUNT(*) FROM Patrons) * 100), 2) AS percentage FROM Checkouts GROUP BY book_id ORDER BY percentage DESC, book_id
Final Output:
+---------+------------+ | book_id | percentage | +---------+------------+ | 101 | 100.00 | | 102 | 66.67 | | 103 | 66.67 | | 104 | 33.33 | | 105 | 33.33 | +---------+------------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible