Books With the Longest Reservation Queue — SQL Problem
Members reserve books that are out on loan and wait in a queue.
- Difficulty: Medium
- Topics: Subqueries, Aggregation
- Dialect: MySQL
- Problem: #87
Problem statement
Members reserve books that are out on loan and wait in a queue. Only reservations with status waiting are in a queue; fulfilled and cancelled ones are history.
The acquisitions team wants to buy another copy of the book(s) with the most waiting reservations. Return book_id, title and waiting (the number of waiting reservations) for every book that ties for the maximum, in any order. If nothing is waiting, return no rows.
Tables
Table: catalogue
| Column | Type |
|---|---|
| book_id | int |
| title | varchar |
| author | varchar |
Primary key: book_id.
One row per title held by the library.
Table: reservations
| Column | Type |
|---|---|
| reservation_id | int |
| book_id | int |
| member_id | int |
| reserved_on | date |
| status | enum(waiting, fulfilled, cancelled) |
Primary key: reservation_id.
Every book_id is in catalogue.
Examples
Example 1
catalogue
| book_id | title | author |
|---|---|---|
| 1 | The White Tiger | Aravind Adiga |
| 2 | Wings of Fire | A. P. J. Abdul Kalam |
| 3 | The God of Small Things | Arundhati Roy |
| 4 | Train to Pakistan | Khushwant Singh |
reservations
| reservation_id | book_id | member_id | reserved_on | status |
|---|---|---|---|---|
| 1 | 1 | 21 | 2025-01-03 | waiting |
| 2 | 1 | 22 | 2025-01-04 | waiting |
| 3 | 2 | 23 | 2025-01-04 | fulfilled |
| 4 | 2 | 24 | 2025-01-05 | waiting |
| 5 | 2 | 25 | 2025-01-06 | waiting |
| 6 | 3 | 26 | 2025-01-06 | waiting |
| 7 | 3 | 27 | 2025-01-07 | cancelled |
| 8 | 2 | 28 | 2025-01-08 | cancelled |
Output
| book_id | title | waiting |
|---|---|---|
| 1 | The White Tiger | 2 |
| 2 | Wings of Fire | 2 |
How to solve Books With the Longest Reservation Queue
The answer depends on a value computed across all books — the largest queue — so the query has two levels. The CTE queue counts waiting reservations per book (fulfilled and cancelled ones are filtered out first). The outer query keeps the books whose count equals (SELECT MAX(waiting) FROM queue) and joins the catalogue for the title.
ORDER BY waiting DESC LIMIT 1 is the tempting shortcut and the wrong one: in the example two books have two waiting members each, and LIMIT would return one of them arbitrarily. Comparing with the maximum keeps both. When nothing is waiting, queue is empty, the maximum is NULL, and no row is returned, as asked.
The alternatives rank the grouped counts with RANK() OVER (ORDER BY COUNT(*) DESC) — a window over an aggregate — or keep a group when NOT EXISTS finds another book with a longer queue. Each is one grouping pass over the reservations.
Reference solution (MySQL)
WITH queue AS (
SELECT book_id, COUNT(*) AS waiting
FROM reservations
WHERE status = 'waiting'
GROUP BY book_id
)
SELECT c.book_id, c.title, q.waiting
FROM queue q
JOIN catalogue c ON c.book_id = q.book_id
WHERE q.waiting = (SELECT MAX(waiting) FROM queue)Another way
SELECT book_id, title, waiting FROM (
SELECT c.book_id, c.title, COUNT(*) AS waiting, RANK() OVER (ORDER BY COUNT(*) DESC) AS rnk
FROM catalogue c JOIN reservations r ON r.book_id = c.book_id AND r.status = 'waiting'
GROUP BY c.book_id, c.title
) t WHERE rnk = 1Another way
SELECT c.book_id, c.title, COUNT(*) AS waiting
FROM catalogue c JOIN reservations r ON r.book_id = c.book_id
WHERE r.status = 'waiting'
GROUP BY c.book_id, c.title
HAVING NOT EXISTS (SELECT 1 FROM reservations r2 WHERE r2.status = 'waiting' GROUP BY r2.book_id HAVING COUNT(*) > (SELECT COUNT(*) FROM reservations r3 WHERE r3.status = 'waiting' AND r3.book_id = c.book_id))← Resale Listings Marked Up Above Their Event's Average · Monthly Library Issues by Genre →