Katkat
Back

Return count and renewal percentage of unreturned good copies

MetaData Engineer · Technical Screen
MediumData EngineerTechnical ScreenData Manipulation (SQL/Python)

Last updated

This question evaluates proficiency in SQL data manipulation, including conditional filtering, NULL handling, aggregation, and computing conditional percentages across related tables.

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