Find top 3 books by total borrowed time
Company: Meta
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: Technical Screen
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.
Overview: This question evaluates proficiency in data manipulation and aggregation using SQL or Python, covering joins, date arithmetic and null handling, grouping with distinct-count constraints, and top-N ordering with tie-breakers.
Read the full Meta Data Engineer interview experience this question came from
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.
Tables
copies(copy_id INT, book_id INT)
checkouts(copy_id INT, checkout_date DATE, return_date DATE)
Hints
- PostgreSQL has no DATEDIFF — subtract one DATE from another (return_date - checkout_date) to get the number of days as an integer.
- 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.