Library Book Loans

The mechanism

Loan Frequency is a per-book share of a single global total: each book's loan count is divided by the same denominator — the total number of loan rows across every book. The database computes that denominator once with an independent scalar subquery, then reuses the scalar for every grouped row, so the engine never recomputes it per group.

Two moving parts make the query work:

• A LEFT JOIN from Books to Loans keeps a book in the result even when it has zero loans (the unmatched side produces NULL loan rows, and COUNT(l.loan_id) counts non-NULL keys, so it lands on 0 instead of vanishing).
• A correlation-free scalar subquery (SELECT COUNT(*) FROM Loans) supplies the denominator. It does not reference the outer row, so it is the same constant 7 for every group.

The solution

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;

This is MySQL-correct: in MySQL/SQLite the / operator always performs floating-point division, so 4 / 7 yields 0.5714… and ROUND(…, 2) gives 0.57. (See Pitfalls — this exact line silently returns all zeros on SQL Server and Postgres.)

Worked trace on the sample data

The Loans table has 7 rows. Book 1 appears in loans 1, 2, 5, 7 (×4); book 2 in loans 3, 6 (×2); book 3 in loan 4 (×1). Walk the query stages in order.

Step 1 — scalar subquery (computed once). SELECT COUNT(*) FROM Loans = 7. This constant is now frozen for the whole query.

Step 2 — LEFT JOIN. Every book matches at least one loan here, so no book is dropped. (If book 4 existed with no loans, it would appear with a single row whose l.loan_id is NULL.)

Step 3 — GROUP BY b.book_id, then COUNT(l.loan_id) per group.

Step 4 — ORDER BY LF DESC, book_id ASC. Rows already descend by LF (0.57, 0.29, 0.14); the book_id ASC tiebreak only matters if two books shared an LF. Final output matches the expected 0.57 / 0.29 / 0.14.

Pitfalls

1. Integer division silently returns all zeros (the real systems gotcha)

The query is portable in shape but not in arithmetic. The result of int / int depends entirely on the engine:

Why the naive version is wrong on Postgres/SQL Server: both COUNT(...) and (SELECT COUNT(*) …) are integers, and those engines define integer / integer as integer division — it truncates toward zero before ROUND sees it. Every numerator smaller than the denominator becomes 0, so you ship a column of 0.00 that passes a smoke test on small data and looks plausible in code review. The portable fix forces floating-point by promoting one operand:

-- Portable everywhere: multiply numerator by 1.0 (or CAST)
SELECT b.book_id,
       ROUND(
         COUNT(l.loan_id) * 1.0 / (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;

Equivalent forms: CAST(COUNT(l.loan_id) AS DECIMAL) / (SELECT COUNT(*) FROM Loans), or in Postgres COUNT(...)::numeric / .... Promote the numerator, not the result — wrapping the whole truncated quotient in CAST is too late; the zero already happened.

2. INNER JOIN drops never-loaned books

Swap LEFT JOIN for INNER JOIN and a book with no loans disappears entirely instead of reporting LF = 0.00. The problem asks for the LF of each book, so the outer join is load-bearing, not stylistic.

3. COUNT(*) vs COUNT(l.loan_id) inside the grouped count

With a LEFT JOIN, a never-loaned book still produces one row whose join columns are NULL. COUNT(*) would count that phantom row as 1; COUNT(l.loan_id) ignores it because the key is NULL, correctly yielding 0. Always count a column from the right table in a left-joined aggregate.

4. Division-by-zero on an empty Loans table

If Loans is empty the denominator is 0. MySQL returns NULL (no error); SQL Server raises divide by zero. Guard with NULLIF((SELECT COUNT(*) FROM Loans), 0) if empty input is possible in production.

Takeaways

• A correlation-free scalar subquery is computed once and reused as a constant across all groups — ideal for a shared global denominator.
• LEFT JOIN + COUNT(right_table.key) is the canonical pattern for "count per parent, including zero" — the join keeps the parent, the column-count zeroes out the misses.
• int / int is the most portability-dangerous expression in SQL: identical text returns 0.57 on MySQL and 0.00 on Postgres/SQL Server. Force float by multiplying the numerator by 1.0 or CAST-ing it before the divide.
• ROUND happens last; it can only round what division already produced, so a truncated 0 stays 0.

Sources: LeetCode problem "Loan Frequency" / Library Book Loans (problem statement and expected output 0.57 / 0.29 / 0.14); MySQL 8.0 Reference Manual, "Arithmetic Operators" (/ yields exact-value or floating-point division); PostgreSQL 16 documentation, "Mathematical Functions and Operators" (integer division truncates toward zero); Microsoft SQL Server T-SQL documentation, "Arithmetic Operators" (integer division). Re-authored / deepened for this guide: added the 4-step trace, the shared-denominator diagram, and the integer-division portability pitfall with the * 1.0 / CAST fix that the original page omitted.