DigitalOcean Interview Questions

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:

1. avg_borrow_duration_days: average borrowing duration in days (rounded to 2 decimals)
2. 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

borrows Table

Output

Example

Input

books Table

borrows Table

Output

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'.