Quick Overview

This question evaluates proficiency in SQL-based data manipulation and analytical querying, focusing on relational schema navigation, joins, aggregation, filtering, and handling temporal and conditional metrics within a library dataset.

Write SQL for library analytics

Company: Meta

Role: Data Engineer

Category: Data Manipulation (SQL/Python)

Difficulty: medium

Interview Round: Technical Screen

Given a library database, write SQL to answer the following: 1) Count the number of books that are currently not returned (i.e., still checked out) and are in good condition. 2) Among those currently not returned and in good condition, compute the percentage that have been renewed more than 2 times (use that subset as the denominator). 3) Return the top 3 book titles or IDs that have more than 10 copies and the longest total lending time across all their loans (sum of loan durations). 4) Find the member–referrer pair with the greatest difference in the number of reservations they have made, and return the pair and the difference.

Overview: This question evaluates proficiency in SQL-based data manipulation and analytical querying, focusing on relational schema navigation, joins, aggregation, filtering, and handling temporal and conditional metrics within a library dataset.

Count active (not returned) loans for books in good condition

You are given a library database with books and loan transactions. A book is considered "currently not returned" if its loan record has return_date IS NULL. Write a SQL query to count how many loaned books are currently not returned AND the associated book is in 'Good' condition. Return a single row with a single column named not_returned_good_count.

Tables

books(book_id INT, title VARCHAR(200), total_copies INT, condition VARCHAR(20))

members(member_id INT, member_name VARCHAR(100), referrer_member_id INT)

loans(loan_id INT, book_id INT, member_id INT, checkout_date DATE, due_date DATE, return_date DATE, renew_count INT)

reservations(reservation_id INT, member_id INT, book_id INT, reservation_date DATE)

Hints

  1. A loan is active if return_date is NULL.
  2. Join loans to books and filter condition = 'Good'.

Percentage of active good-condition loans renewed more than 2 times

Using the same definition as in Question 1: - "currently not returned" means return_date IS NULL - book must be in 'Good' condition Among those active good-condition loans, compute the percentage of loans that have been renewed more than 2 times (renew_count > 2). Return a single row with a single column named pct_renewed_gt_2, rounded to 2 decimals.

Tables

books(book_id INT, title VARCHAR(200), total_copies INT, condition VARCHAR(20))

members(member_id INT, member_name VARCHAR(100), referrer_member_id INT)

loans(loan_id INT, book_id INT, member_id INT, checkout_date DATE, due_date DATE, return_date DATE, renew_count INT)

reservations(reservation_id INT, member_id INT, book_id INT, reservation_date DATE)

Hints

  1. Use the same filter as Question 1 to define the denominator.
  2. Compute numerator with a CASE expression and divide by COUNT(*).

Top 3 books with >10 copies by total lending time

Find the top 3 books (by book_id and title) that: - have more than 10 total copies (books.total_copies > 10) - have the longest total lending time across all their loans Define a loan's lending time in days as: (COALESCE(return_date, DATE '2025-06-01') - checkout_date) So if a loan has not been returned yet, assume it is lent through 2025-06-01. Return book_id, title, and total_lend_days (sum of lending time across all loans for that book), ordered by total_lend_days descending and limit to 3 rows.

Tables

books(book_id INT, title VARCHAR(200), total_copies INT, condition VARCHAR(20))

members(member_id INT, member_name VARCHAR(100), referrer_member_id INT)

loans(loan_id INT, book_id INT, member_id INT, checkout_date DATE, due_date DATE, return_date DATE, renew_count INT)

reservations(reservation_id INT, member_id INT, book_id INT, reservation_date DATE)

Hints

  1. Use COALESCE to treat unreturned loans as ending on 2025-06-01.
  2. Aggregate lending time per book and then ORDER BY the sum.

Member–referrer pair with the largest reservation-count difference

Each member may have been referred by another member (members.referrer_member_id). For every member that has a referrer, compute: - reservations made by the member - reservations made by the referrer - the absolute difference between those two counts Return the single member–referrer pair with the greatest difference, along with the difference. Output columns: member_id, member_name, referrer_member_id, referrer_name, reservation_diff If multiple pairs tie for greatest difference, return the one with the smallest member_id.

Tables

books(book_id INT, title VARCHAR(200), total_copies INT, condition VARCHAR(20))

members(member_id INT, member_name VARCHAR(100), referrer_member_id INT)

loans(loan_id INT, book_id INT, member_id INT, checkout_date DATE, due_date DATE, return_date DATE, renew_count INT)

reservations(reservation_id INT, member_id INT, book_id INT, reservation_date DATE)

Hints

  1. Pre-aggregate reservation counts per member with a LEFT JOIN so members with 0 reservations still appear.
  2. Join each member to their referrer and take ABS of the two counts.

Loading coding console...