Digital Library Borrowing Metrics
0:00:00
You’re a data scientist at Meta working on a feature that helps users discover trending books and understand borrowing behavior across the platform’s digital library.
Every time a user borrows a book, the system logs a borrowed_at timestamp and a returned_at timestamp. The team wants to measure how long books are typically kept, while also flagging which books have large inventories.
Write a SQL query to compute the following metrics for each book:
avg_borrow_duration_days: average borrowing duration in days (rounded to 2 decimals)has_over_10_copies:'Yes'if the book has more than 10 copies available, otherwise'No'
Only include completed borrows where returned_at is not NULL.
Sort results by avg_borrow_duration_days DESC, then by book_id ASC.
Note: Every book that has at least one completed borrow should appear in the output.
Schema
Input
books Table
| Column | Type |
|---|---|
| book_id | INTEGER |
| title | VARCHAR |
| copies_available | INTEGER |
borrows Table
| Column | Type |
|---|---|
| borrow_id | INTEGER |
| user_id | INTEGER |
| book_id | INTEGER |
| borrowed_at | DATETIME |
| returned_at | DATETIME |
Output
| Column | Type |
|---|---|
| book_id | INTEGER |
| avg_borrow_duration_days | DECIMAL |
| has_over_10_copies | VARCHAR |
Example
Input
books Table
| book_id | title | copies_available |
|---|---|---|
| 1 | Dune | 12 |
| 2 | Sapiens | 8 |
| 3 | Clean Code | 15 |
borrows Table
| borrow_id | user_id | book_id | borrowed_at | returned_at |
|---|---|---|---|---|
| 101 | 10 | 1 | 2026-01-01 09:00:00 | 2026-01-06 09:00:00 |
| 102 | 11 | 1 | 2026-01-10 10:00:00 | 2026-01-13 22:00:00 |
| 103 | 12 | 2 | 2026-01-02 12:00:00 | 2026-01-04 12:00:00 |
| 104 | 13 | 2 | 2026-01-05 09:00:00 | NULL |
| 105 | 14 | 3 | 2026-01-03 08:00:00 | 2026-01-10 08:00:00 |
Output
| book_id | avg_borrow_duration_days | has_over_10_copies |
|---|---|---|
| 3 | 7.00 | Yes |
| 1 | 4.25 | Yes |
| 2 | 2.00 | No |
Explanation
For book 1, there are two completed borrows. The durations are 5.00 days (Jan 1 → Jan 6) and 3.50 days (Jan 10 10:00 → Jan 13 22:00). The average borrowing duration is (5.00 + 3.50) / 2 = 4.25 days. Since copies_available = 12, the book is flagged as 'Yes'.
For book 2, only one borrow is completed (the other has returned_at = NULL and is excluded). The completed duration is 2.00 days. Since copies_available = 8, it is flagged as 'No'.
For book 3, there is one completed borrow lasting 7.00 days, and copies_available = 15, so it is flagged as 'Yes'.
.
.
.
.
Comments