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.
Quick Answer: 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.