Library Loans Overdue on Audit Day — SQL Basics Problem
The city library runs a stock audit on 2025-03-01 and wants every book that is still out and past its due date on that day.
- Difficulty: Easy
- Topics: Basics, Dates
- Dialect: MySQL
- Problem: #73
Problem statement
The city library runs a stock audit on 2025-03-01 and wants every book that is still out and past its due date on that day.
A loan is still out when returned_on is NULL, and it is overdue when due_on is strictly before 2025-03-01 (a book due on the audit day itself is not overdue yet). Return loan_id, member_id and days_overdue — the number of days from due_on to 2025-03-01 — ordered by days_overdue descending, then loan_id ascending.
Tables
Table: book_loans
| Column | Type |
|---|---|
| loan_id | int |
| member_id | int |
| book_id | int |
| issued_on | date |
| due_on | date |
| returned_on | date |
Primary key: loan_id.
One row per book issued. returned_on is NULL while the book is still with the member; every return so far happened before the audit day.
Examples
Example 1
book_loans
| loan_id | member_id | book_id | issued_on | due_on | returned_on |
|---|---|---|---|---|---|
| 1 | 11 | 501 | 2025-01-20 | 2025-02-03 | 2025-02-01 |
| 2 | 12 | 502 | 2025-01-28 | 2025-02-11 | NULL |
| 3 | 13 | 503 | 2025-02-15 | 2025-03-01 | NULL |
| 4 | 11 | 504 | 2025-02-01 | 2025-02-15 | 2025-02-25 |
| 5 | 14 | 505 | 2025-02-08 | 2025-02-22 | NULL |
| 6 | 15 | 506 | 2025-02-08 | 2025-02-22 | NULL |
| 7 | 12 | 507 | 2025-02-20 | 2025-03-06 | NULL |
Output
| loan_id | member_id | days_overdue |
|---|---|---|
| 2 | 12 | 18 |
| 5 | 14 | 7 |
| 6 | 15 | 7 |
How to solve Library Loans Overdue on Audit Day
This is a filter followed by one computed column. A loan is still out exactly when returned_on IS NULL; comparing with = NULL would be unknown for every row and return nothing, which is the classic mistake here.
The overdue condition is strict: due_on < '2025-03-01'. Because dates are stored in a fixed YYYY-MM-DD form, comparing them is comparing the calendar, and a loan due on the audit day itself is excluded. Writing the same test as DATEDIFF('2025-03-01', due_on) > 0 is equivalent.
DATEDIFF('2025-03-01', due_on) counts the days late; TIMESTAMPDIFF(DAY, due_on, '2025-03-01') is the same number with the arguments the other way round. Two loans can be equally late, so the order needs loan_id as a tie-breaker to be fully determined. The query is a single scan of the loans table; an index on (returned_on, due_on) would let a large library skip returned books.
Reference solution (MySQL)
SELECT loan_id, member_id, DATEDIFF('2025-03-01', due_on) AS days_overdue
FROM book_loans
WHERE returned_on IS NULL AND due_on < '2025-03-01'
ORDER BY days_overdue DESC, loan_idAnother way
SELECT loan_id, member_id, TIMESTAMPDIFF(DAY, due_on, '2025-03-01') AS days_overdue FROM book_loans WHERE returned_on IS NULL AND DATEDIFF('2025-03-01', due_on) > 0 ORDER BY 3 DESC, 1← Days From Account Opening to a ₹1 Lakh Balance · Civic Grievances Filed per Department →