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).
Overview: This question evaluates proficiency in SQL data manipulation, including conditional filtering, NULL handling, aggregation, and computing conditional percentages across related tables.
Read the full Meta Data Engineer interview experience this question came from
You are given two tables:
- copies(copy_id, condition)
- checkouts(copy_id, checkout_date, return_date, renewal_count)
Write a single SQL query that returns exactly one row with two columns:
1) total_unreturned_good_copies: the count of active checkouts (rows where return_date IS NULL) for copies whose condition = 'good' (exact string match).
2) pct_renewals_gt_2: among those active checkouts for 'good' copies, the percentage that have renewal_count > 2, returned as a decimal fraction between 0 and 1.
If there are no active checkouts for 'good' copies, pct_renewals_gt_2 should be 0.0 instead of NULL.
Tables
copies(copy_id INT, condition VARCHAR(20))
checkouts(copy_id INT, checkout_date DATE, return_date DATE, renewal_count INT)
Hints
- Start by joining copies to checkouts and filter to condition = 'good' and return_date IS NULL.
- Use conditional aggregation (e.g., AVG of a CASE expression) to compute the percentage of rows with renewal_count > 2.