Return count and renewal percentage of unreturned good copies
Company: Meta
Role: Data Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: Medium
Interview Round: Technical Screen
Tables: copies(copy_id, condition), checkouts(copy_id, checkout_date, return_date, renewal_count). Write a single SQL query that returns one row with two columns:
(
1) total_unreturned_good_copies: the count of active checkouts (return_date IS NULL) for copies whose condition = 'good' (exact match), and
(
2) pct_renewals_gt_2: among those active checkouts, the percentage with renewal_count > 2 (return a decimal fraction 0–
1).
Quick Answer: This question evaluates proficiency in SQL data manipulation, including conditional filtering, NULL handling, aggregation, and computing conditional percentages across related tables.