Library Loans Overdue on Audit Day

Easy
73.

Library Loans Overdue on Audit Day

EasyBasicsDates

MySQL

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

ColumnType
loan_idint
member_idint
book_idint
issued_ondate
due_ondate
returned_ondate

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.

Example

The rows must come back in the order shown.

book_loans

loan_idmember_idbook_idissued_ondue_onreturned_on
1115012025-01-202025-02-032025-02-01
2125022025-01-282025-02-11NULL
3135032025-02-152025-03-01NULL
4115042025-02-012025-02-152025-02-25
5145052025-02-082025-02-22NULL
6155062025-02-082025-02-22NULL
7125072025-02-202025-03-06NULL

Output

loan_idmember_iddays_overdue
21218
5147
6157
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
loan_idint
member_idint
book_idint
issued_ondate
due_ondate
returned_ondate

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_idmember_idbook_idissued_ondue_onreturned_on
1115012025-01-202025-02-032025-02-01
2125022025-01-282025-02-11NULL
3135032025-02-152025-03-01NULL
4115042025-02-012025-02-152025-02-25
5145052025-02-082025-02-22NULL
6155062025-02-082025-02-22NULL
7125072025-02-202025-03-06NULL

Output

loan_idmember_iddays_overdue
21218
5147
6157

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_id

Another 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 →