Find top 3 books by total borrowed time

Using copies(copy_id, book_id) and checkouts(copy_id, checkout_date, return_date), compute for each book_id the total borrowed duration as the sum over all completed checkouts of DATEDIFF(day, checkout_date, return_date). Consider only rows where return_date IS NOT NULL when summing durations. Return the top 3 book_ids with at least 10 copies (COUNT(DISTINCT copy_id) > 10) ordered by total borrowed duration descending, breaking ties by book_id ascending.

Find Top 3 Books by Total Borrowed Time

You are given two tables in a library checkout system:

• copies(copy_id, book_id) — each row is one physical copy of a book.
• checkouts(copy_id, checkout_date, return_date) — each row is one checkout of a particular copy. return_date is NULL when the copy has not yet been returned.

Task. For each book_id, compute its total borrowed duration in days, defined as the sum, over all of that book's completed checkouts (rows where return_date IS NOT NULL), of (return_date - checkout_date). Ignore checkouts that have not been returned.

Then restrict to books that have more than 10 distinct copies in the copies table (i.e. COUNT(DISTINCT copy_id) > 10), and return the top 3 such books ranked by total borrowed duration.

Output. One row per selected book, with exactly these columns:

• book_id
• total_borrowed_days — the summed duration in days.

Order the result by total_borrowed_days descending, breaking ties by book_id ascending, and return at most 3 rows.

Table copies

column	type	constraints
copy_id	INT	NOT NULL
book_id	INT	NOT NULL

Sample data:

book_id	copy_id
101	1
101	2
101	3
101	4
101	5
101	6
101	7
101	8
101	9
101	10
101	11
102	12
102	13
102	14
102	15
102	16
102	17
102	18
102	19
102	20
102	21
102	22
104	23
104	24
104	25
104	26
104	27
104	28
104	29
104	30
104	31
104	32
104	33
103	34
103	35
103	36
103	37
103	38
103	39
103	40

Table checkouts

column	type	constraints
copy_id	INT	NOT NULL
checkout_date	DATE	NOT NULL
return_date	DATE

Sample data:

checkout_date	copy_id	return_date
2025-01-01	1	2025-01-11
2025-02-01	2	2025-02-06
2025-03-10	3	2025-03-20
2025-04-01	4	2025-04-04
2025-05-01	1	NULL
2025-01-05	12	2025-01-15
2025-02-10	13	2025-02-25
2025-03-01	14	2025-03-16
2025-04-01	12	NULL
2025-01-10	23	2025-02-09
2025-03-01	24	2025-03-31
2025-04-01	25	2025-04-21
2025-05-10	23	NULL
2025-01-02	34	2025-01-05
2025-02-01	35	2025-02-02

Expected output

Top 3 books with more than 10 distinct copies, ranked by total borrowed days over completed checkouts (return_date NOT NULL), ordered by total_borrowed_days DESC then book_id ASC.

book_id	total_borrowed_days
104	80
102	40
101	28

Hints

1. PostgreSQL has no DATEDIFF — subtract one DATE from another (return_date - checkout_date) to get the number of days as an integer.
2. Find the eligible books first with a CTE that groups copies by book_id and keeps HAVING COUNT(DISTINCT copy_id) > 10, then sum durations only for those books.
3. Use LIMIT 3 (not SELECT TOP 3) after an explicit ORDER BY total_borrowed_days DESC, book_id ASC to get a deterministic top-3.