3. Library Book Checkout Percentage (easy)

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

Database Exercise

Database Schema:


-- Database schema would be rendered here

Exercise Script:


-- Exercise script would be rendered here

Available actions: Execute

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 by book_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:

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