Books With the Longest Reservation Queue

Medium
87.

Books With the Longest Reservation Queue

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

ColumnType
book_idint
titlevarchar
authorvarchar

Primary key: book_id.

One row per title held by the library.

Table: reservations

ColumnTypeValues
reservation_idint
book_idint
member_idint
reserved_ondate
statusenumwaiting, fulfilled, cancelled

Primary key: reservation_id.

Every book_id is in catalogue.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

catalogue

book_idtitleauthor
1The White TigerAravind Adiga
2Wings of FireA. P. J. Abdul Kalam
3The God of Small ThingsArundhati Roy
4Train to PakistanKhushwant Singh

reservations

reservation_idbook_idmember_idreserved_onstatus
11212025-01-03waiting
21222025-01-04waiting
32232025-01-04fulfilled
42242025-01-05waiting
52252025-01-06waiting
63262025-01-06waiting
73272025-01-07cancelled
82282025-01-08cancelled

Output

book_idtitlewaiting
1The White Tiger2
2Wings of Fire2
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.

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

ColumnType
book_idint
titlevarchar
authorvarchar

Primary key: book_id.

One row per title held by the library.

Table: reservations

ColumnType
reservation_idint
book_idint
member_idint
reserved_ondate
statusenum(waiting, fulfilled, cancelled)

Primary key: reservation_id.

Every book_id is in catalogue.

Examples

Example 1

catalogue

book_idtitleauthor
1The White TigerAravind Adiga
2Wings of FireA. P. J. Abdul Kalam
3The God of Small ThingsArundhati Roy
4Train to PakistanKhushwant Singh

reservations

reservation_idbook_idmember_idreserved_onstatus
11212025-01-03waiting
21222025-01-04waiting
32232025-01-04fulfilled
42242025-01-05waiting
52252025-01-06waiting
63262025-01-06waiting
73272025-01-07cancelled
82282025-01-08cancelled

Output

book_idtitlewaiting
1The White Tiger2
2Wings of Fire2

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 = 1

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