easy Library Book Checkout Percentage
What fraction of all patrons checked out each book
Patrons, Checkouts(book_id, patron_id). For each book, compute the percentage of
all library patrons who checked it out, rounded to 2 dp, ordered by percentage desc then
book_id. The earlier Step-3 query was invalid — it referenced unique_patrons and
total_patrons as if they were columns of Checkouts. The correct query computes the distinct
patron count per book over the constant total:
SELECT book_id,
ROUND(COUNT(DISTINCT patron_id) * 100.0 / (SELECT COUNT(*) FROM Patrons), 2) AS percentage
FROM Checkouts
GROUP BY book_id
ORDER BY percentage DESC, book_id ASC;Traced on the sample (3 patrons total)
| book | distinct patrons | / 3 × 100 |
|---|---|---|
| 101 | {1,2,3} = 3 | 100.00 |
| 102 | {2,3} = 2 | 66.67 |
| 103 | {1,2} = 2 | 66.67 |
| 104 | {1} = 1 | 33.33 |
| 105 | {2} = 1 | 33.33 |
Pitfalls
COUNT(DISTINCT patron_id)— a patron who checks the same book twice must count once.- Multiply by
100.0(not100) or divide by a cast so PostgreSQL/SQLite don't do integer division and return 0; the denominator is a scalar subquery overPatrons, the whole library.
Takeaways
- Per-book rate =
COUNT(DISTINCT patron_id) / (total patrons) × 100, grouped by book. - Compute named intermediates as real expressions — you can't reference a CTE/derived column that was never defined.
Re-authored for correctness for this guide (the prior Step-3 query referenced undefined columns and didn't run). See also: Aggregate Functions (COUNT DISTINCT), Subqueries.
🤖 Don't fully get this? Learn it with Claude
Stuck on Library Book Checkout Percentage? Open Claude, copy a block below, and it'll teach you this exact concept — visually and interactively.
Progressively stronger hints — you still solve it.
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.
See the technique, not just code.
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.
Catch bugs, edge cases, sub-optimality.
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.
Lock in recognition with look-alikes.
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.