easy Library Book Checkout Percentage

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.

1. Count Unique Checkouts: Count the unique number of patrons who have checked out each book.
2. Calculate Total Number of Patrons: Determine the total number of patrons in the library.
3. 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.
4. 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:

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

I'm working on the problem **Library Book Checkout Percentage** (Databases). Give me a HINT LADDER: start with the tiniest nudge, then wait. Only reveal the next, stronger hint when I ask. Do NOT show the full solution unless I type 'show solution'. Keep me doing the thinking. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Explain the optimal approach to **Library Book Checkout Percentage** with a VISUAL walkthrough: trace it on a small concrete example using ASCII art / a step-by-step diagram, narrate what changes each step, then give time & space complexity with a one-line derivation. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

I'll paste my solution to **Library Book Checkout Percentage**. Review it for correctness, missed edge cases, and time/space complexity, then coach me toward the optimal — don't just rewrite it. Ask me to paste my code now. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.

Give me 2 problems that use the SAME underlying pattern as **Library Book Checkout Percentage**. For each, let me attempt first, then review my answer and name the trigger signal that reveals the pattern. If you're unsure or a claim isn't standard, say so and reason from first principles instead of guessing.