Coding questions · Meta
Write SQL for library analytics
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.
Given a library database, write SQL to answer the following:
- Count the number of books that are currently not returned (i.e., still checked out) and are in good condition.
- 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).
- 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).
- Find the member–referrer pair with the greatest difference in the number of reservations they have made, and return the pair and the difference.
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.
Table books
| column | type | constraints |
|---|---|---|
| book_id | INT | PRIMARY KEY, NOT NULL |
| title | VARCHAR(200) | NOT NULL |
| total_copies | INT | NOT NULL |
| condition | VARCHAR(20) | NOT NULL |
Sample data:
| book_id | condition | title | total_copies |
|---|---|---|---|
| 1 | Good | SQL Basics | 12 |
| 2 | Good | Data Science 101 | 20 |
| 3 | Good | History of Rome | 15 |
| 4 | Good | Rare Manuscript | 2 |
| 5 | Damaged | Damaged Book | 30 |
| 6 | Good | Python for Data | 11 |
Table members
| column | type | constraints |
|---|---|---|
| member_id | INT | PRIMARY KEY, NOT NULL |
| member_name | VARCHAR(100) | NOT NULL |
| referrer_member_id | INT |
Sample data:
| member_id | member_name | referrer_member_id |
|---|---|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Cara | 1 |
| 4 | Dan | 2 |
| 5 | Eva | 2 |
| 6 | Finn | 3 |
Table loans
| column | type | constraints |
|---|---|---|
| loan_id | INT | PRIMARY KEY, NOT NULL |
| book_id | INT | NOT NULL |
| member_id | INT | NOT NULL |
| checkout_date | DATE | NOT NULL |
| due_date | DATE | NOT NULL |
| return_date | DATE | |
| renew_count | INT | NOT NULL |
Sample data:
| book_id | checkout_date | due_date | loan_id | member_id | renew_count | return_date |
|---|---|---|---|---|---|---|
| 1 | 2025-05-01 | 2025-05-15 | 101 | 1 | 1 | 2025-05-20 |
| 1 | 2025-05-25 | 2025-06-08 | 102 | 2 | 3 | NULL |
| 2 | 2025-04-01 | 2025-04-15 | 103 | 3 | 2 | 2025-05-15 |
| 2 | 2025-05-20 | 2025-06-03 | 104 | 4 | 1 | NULL |
| 3 | 2025-03-01 | 2025-03-15 | 105 | 5 | 0 | 2025-04-15 |
| 3 | 2025-05-10 | 2025-05-24 | 106 | 1 | 4 | NULL |
| 5 | 2025-01-01 | 2025-01-15 | 107 | 2 | 1 | 2025-05-01 |
| 6 | 2025-05-28 | 2025-06-11 | 108 | 3 | 0 | NULL |
| 6 | 2025-02-01 | 2025-02-15 | 109 | 4 | 1 | 2025-03-01 |
| 2 | 2025-02-10 | 2025-02-24 | 110 | 2 | 0 | 2025-02-20 |
| 1 | 2025-01-10 | 2025-01-24 | 111 | 5 | 0 | 2025-01-25 |
Table reservations
| column | type | constraints |
|---|---|---|
| reservation_id | INT | PRIMARY KEY, NOT NULL |
| member_id | INT | NOT NULL |
| book_id | INT | NOT NULL |
| reservation_date | DATE | NOT NULL |
Sample data:
| book_id | member_id | reservation_date | reservation_id |
|---|---|---|---|
| 2 | 1 | 2025-01-05 | 201 |
| 3 | 1 | 2025-02-07 | 202 |
| 1 | 1 | 2025-03-10 | 203 |
| 6 | 1 | 2025-04-12 | 204 |
| 4 | 1 | 2025-05-01 | 205 |
| 1 | 2 | 2025-03-03 | 206 |
| 2 | 3 | 2025-01-15 | 207 |
| 6 | 3 | 2025-02-16 | 208 |
| 3 | 3 | 2025-05-20 | 209 |
| 1 | 3 | 2025-05-25 | 210 |
| 2 | 5 | 2025-01-20 | 211 |
| 3 | 5 | 2025-01-22 | 212 |
| 6 | 5 | 2025-02-10 | 213 |
| 1 | 5 | 2025-03-08 | 214 |
| 5 | 5 | 2025-04-01 | 215 |
| 4 | 5 | 2025-05-10 | 216 |
| 2 | 5 | 2025-05-30 | 217 |
| 3 | 6 | 2025-02-25 | 218 |
| 6 | 6 | 2025-03-18 | 219 |
Expected output
One row with the count of loan records that are still checked out (return_date is NULL) where the book's condition is 'Good'.
| not_returned_good_count |
|---|
| 4 |
Hints
- A loan is active if return_date is NULL.
- 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.
Table books
| column | type | constraints |
|---|---|---|
| book_id | INT | PRIMARY KEY, NOT NULL |
| title | VARCHAR(200) | NOT NULL |
| total_copies | INT | NOT NULL |
| condition | VARCHAR(20) | NOT NULL |
Sample data:
| book_id | condition | title | total_copies |
|---|---|---|---|
| 1 | Good | SQL Basics | 12 |
| 2 | Good | Data Science 101 | 20 |
| 3 | Good | History of Rome | 15 |
| 4 | Good | Rare Manuscript | 2 |
| 5 | Damaged | Damaged Book | 30 |
| 6 | Good | Python for Data | 11 |
Table members
| column | type | constraints |
|---|---|---|
| member_id | INT | PRIMARY KEY, NOT NULL |
| member_name | VARCHAR(100) | NOT NULL |
| referrer_member_id | INT |
Sample data:
| member_id | member_name | referrer_member_id |
|---|---|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Cara | 1 |
| 4 | Dan | 2 |
| 5 | Eva | 2 |
| 6 | Finn | 3 |
Table loans
| column | type | constraints |
|---|---|---|
| loan_id | INT | PRIMARY KEY, NOT NULL |
| book_id | INT | NOT NULL |
| member_id | INT | NOT NULL |
| checkout_date | DATE | NOT NULL |
| due_date | DATE | NOT NULL |
| return_date | DATE | |
| renew_count | INT | NOT NULL |
Sample data:
| book_id | checkout_date | due_date | loan_id | member_id | renew_count | return_date |
|---|---|---|---|---|---|---|
| 1 | 2025-05-01 | 2025-05-15 | 101 | 1 | 1 | 2025-05-20 |
| 1 | 2025-05-25 | 2025-06-08 | 102 | 2 | 3 | NULL |
| 2 | 2025-04-01 | 2025-04-15 | 103 | 3 | 2 | 2025-05-15 |
| 2 | 2025-05-20 | 2025-06-03 | 104 | 4 | 1 | NULL |
| 3 | 2025-03-01 | 2025-03-15 | 105 | 5 | 0 | 2025-04-15 |
| 3 | 2025-05-10 | 2025-05-24 | 106 | 1 | 4 | NULL |
| 5 | 2025-01-01 | 2025-01-15 | 107 | 2 | 1 | 2025-05-01 |
| 6 | 2025-05-28 | 2025-06-11 | 108 | 3 | 0 | NULL |
| 6 | 2025-02-01 | 2025-02-15 | 109 | 4 | 1 | 2025-03-01 |
| 2 | 2025-02-10 | 2025-02-24 | 110 | 2 | 0 | 2025-02-20 |
| 1 | 2025-01-10 | 2025-01-24 | 111 | 5 | 0 | 2025-01-25 |
Table reservations
| column | type | constraints |
|---|---|---|
| reservation_id | INT | PRIMARY KEY, NOT NULL |
| member_id | INT | NOT NULL |
| book_id | INT | NOT NULL |
| reservation_date | DATE | NOT NULL |
Sample data:
| book_id | member_id | reservation_date | reservation_id |
|---|---|---|---|
| 2 | 1 | 2025-01-05 | 201 |
| 3 | 1 | 2025-02-07 | 202 |
| 1 | 1 | 2025-03-10 | 203 |
| 6 | 1 | 2025-04-12 | 204 |
| 4 | 1 | 2025-05-01 | 205 |
| 1 | 2 | 2025-03-03 | 206 |
| 2 | 3 | 2025-01-15 | 207 |
| 6 | 3 | 2025-02-16 | 208 |
| 3 | 3 | 2025-05-20 | 209 |
| 1 | 3 | 2025-05-25 | 210 |
| 2 | 5 | 2025-01-20 | 211 |
| 3 | 5 | 2025-01-22 | 212 |
| 6 | 5 | 2025-02-10 | 213 |
| 1 | 5 | 2025-03-08 | 214 |
| 5 | 5 | 2025-04-01 | 215 |
| 4 | 5 | 2025-05-10 | 216 |
| 2 | 5 | 2025-05-30 | 217 |
| 3 | 6 | 2025-02-25 | 218 |
| 6 | 6 | 2025-03-18 | 219 |
Expected output
One row with the percentage (0-100) of active good-condition loans that have renew_count > 2, rounded to 2 decimals.
| pct_renewed_gt_2 |
|---|
| 50 |
Hints
- Use the same filter as Question 1 to define the denominator.
- 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.
Table books
| column | type | constraints |
|---|---|---|
| book_id | INT | PRIMARY KEY, NOT NULL |
| title | VARCHAR(200) | NOT NULL |
| total_copies | INT | NOT NULL |
| condition | VARCHAR(20) | NOT NULL |
Sample data:
| book_id | condition | title | total_copies |
|---|---|---|---|
| 1 | Good | SQL Basics | 12 |
| 2 | Good | Data Science 101 | 20 |
| 3 | Good | History of Rome | 15 |
| 4 | Good | Rare Manuscript | 2 |
| 5 | Damaged | Damaged Book | 30 |
| 6 | Good | Python for Data | 11 |
Table members
| column | type | constraints |
|---|---|---|
| member_id | INT | PRIMARY KEY, NOT NULL |
| member_name | VARCHAR(100) | NOT NULL |
| referrer_member_id | INT |
Sample data:
| member_id | member_name | referrer_member_id |
|---|---|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Cara | 1 |
| 4 | Dan | 2 |
| 5 | Eva | 2 |
| 6 | Finn | 3 |
Table loans
| column | type | constraints |
|---|---|---|
| loan_id | INT | PRIMARY KEY, NOT NULL |
| book_id | INT | NOT NULL |
| member_id | INT | NOT NULL |
| checkout_date | DATE | NOT NULL |
| due_date | DATE | NOT NULL |
| return_date | DATE | |
| renew_count | INT | NOT NULL |
Sample data:
| book_id | checkout_date | due_date | loan_id | member_id | renew_count | return_date |
|---|---|---|---|---|---|---|
| 1 | 2025-05-01 | 2025-05-15 | 101 | 1 | 1 | 2025-05-20 |
| 1 | 2025-05-25 | 2025-06-08 | 102 | 2 | 3 | NULL |
| 2 | 2025-04-01 | 2025-04-15 | 103 | 3 | 2 | 2025-05-15 |
| 2 | 2025-05-20 | 2025-06-03 | 104 | 4 | 1 | NULL |
| 3 | 2025-03-01 | 2025-03-15 | 105 | 5 | 0 | 2025-04-15 |
| 3 | 2025-05-10 | 2025-05-24 | 106 | 1 | 4 | NULL |
| 5 | 2025-01-01 | 2025-01-15 | 107 | 2 | 1 | 2025-05-01 |
| 6 | 2025-05-28 | 2025-06-11 | 108 | 3 | 0 | NULL |
| 6 | 2025-02-01 | 2025-02-15 | 109 | 4 | 1 | 2025-03-01 |
| 2 | 2025-02-10 | 2025-02-24 | 110 | 2 | 0 | 2025-02-20 |
| 1 | 2025-01-10 | 2025-01-24 | 111 | 5 | 0 | 2025-01-25 |
Table reservations
| column | type | constraints |
|---|---|---|
| reservation_id | INT | PRIMARY KEY, NOT NULL |
| member_id | INT | NOT NULL |
| book_id | INT | NOT NULL |
| reservation_date | DATE | NOT NULL |
Sample data:
| book_id | member_id | reservation_date | reservation_id |
|---|---|---|---|
| 2 | 1 | 2025-01-05 | 201 |
| 3 | 1 | 2025-02-07 | 202 |
| 1 | 1 | 2025-03-10 | 203 |
| 6 | 1 | 2025-04-12 | 204 |
| 4 | 1 | 2025-05-01 | 205 |
| 1 | 2 | 2025-03-03 | 206 |
| 2 | 3 | 2025-01-15 | 207 |
| 6 | 3 | 2025-02-16 | 208 |
| 3 | 3 | 2025-05-20 | 209 |
| 1 | 3 | 2025-05-25 | 210 |
| 2 | 5 | 2025-01-20 | 211 |
| 3 | 5 | 2025-01-22 | 212 |
| 6 | 5 | 2025-02-10 | 213 |
| 1 | 5 | 2025-03-08 | 214 |
| 5 | 5 | 2025-04-01 | 215 |
| 4 | 5 | 2025-05-10 | 216 |
| 2 | 5 | 2025-05-30 | 217 |
| 3 | 6 | 2025-02-25 | 218 |
| 6 | 6 | 2025-03-18 | 219 |
Expected output
Three rows: the books with total_copies > 10 having the highest total_lend_days across all loans (including ongoing loans counted through 2025-06-01).
| book_id | title | total_lend_days |
|---|---|---|
| 5 | Damaged Book | 120 |
| 3 | History of Rome | 67 |
| 2 | Data Science 101 | 66 |
Hints
- Use COALESCE to treat unreturned loans as ending on 2025-06-01.
- 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.
Table books
| column | type | constraints |
|---|---|---|
| book_id | INT | PRIMARY KEY, NOT NULL |
| title | VARCHAR(200) | NOT NULL |
| total_copies | INT | NOT NULL |
| condition | VARCHAR(20) | NOT NULL |
Sample data:
| book_id | condition | title | total_copies |
|---|---|---|---|
| 1 | Good | SQL Basics | 12 |
| 2 | Good | Data Science 101 | 20 |
| 3 | Good | History of Rome | 15 |
| 4 | Good | Rare Manuscript | 2 |
| 5 | Damaged | Damaged Book | 30 |
| 6 | Good | Python for Data | 11 |
Table members
| column | type | constraints |
|---|---|---|
| member_id | INT | PRIMARY KEY, NOT NULL |
| member_name | VARCHAR(100) | NOT NULL |
| referrer_member_id | INT |
Sample data:
| member_id | member_name | referrer_member_id |
|---|---|---|
| 1 | Alice | NULL |
| 2 | Bob | 1 |
| 3 | Cara | 1 |
| 4 | Dan | 2 |
| 5 | Eva | 2 |
| 6 | Finn | 3 |
Table loans
| column | type | constraints |
|---|---|---|
| loan_id | INT | PRIMARY KEY, NOT NULL |
| book_id | INT | NOT NULL |
| member_id | INT | NOT NULL |
| checkout_date | DATE | NOT NULL |
| due_date | DATE | NOT NULL |
| return_date | DATE | |
| renew_count | INT | NOT NULL |
Sample data:
| book_id | checkout_date | due_date | loan_id | member_id | renew_count | return_date |
|---|---|---|---|---|---|---|
| 1 | 2025-05-01 | 2025-05-15 | 101 | 1 | 1 | 2025-05-20 |
| 1 | 2025-05-25 | 2025-06-08 | 102 | 2 | 3 | NULL |
| 2 | 2025-04-01 | 2025-04-15 | 103 | 3 | 2 | 2025-05-15 |
| 2 | 2025-05-20 | 2025-06-03 | 104 | 4 | 1 | NULL |
| 3 | 2025-03-01 | 2025-03-15 | 105 | 5 | 0 | 2025-04-15 |
| 3 | 2025-05-10 | 2025-05-24 | 106 | 1 | 4 | NULL |
| 5 | 2025-01-01 | 2025-01-15 | 107 | 2 | 1 | 2025-05-01 |
| 6 | 2025-05-28 | 2025-06-11 | 108 | 3 | 0 | NULL |
| 6 | 2025-02-01 | 2025-02-15 | 109 | 4 | 1 | 2025-03-01 |
| 2 | 2025-02-10 | 2025-02-24 | 110 | 2 | 0 | 2025-02-20 |
| 1 | 2025-01-10 | 2025-01-24 | 111 | 5 | 0 | 2025-01-25 |
Table reservations
| column | type | constraints |
|---|---|---|
| reservation_id | INT | PRIMARY KEY, NOT NULL |
| member_id | INT | NOT NULL |
| book_id | INT | NOT NULL |
| reservation_date | DATE | NOT NULL |
Sample data:
| book_id | member_id | reservation_date | reservation_id |
|---|---|---|---|
| 2 | 1 | 2025-01-05 | 201 |
| 3 | 1 | 2025-02-07 | 202 |
| 1 | 1 | 2025-03-10 | 203 |
| 6 | 1 | 2025-04-12 | 204 |
| 4 | 1 | 2025-05-01 | 205 |
| 1 | 2 | 2025-03-03 | 206 |
| 2 | 3 | 2025-01-15 | 207 |
| 6 | 3 | 2025-02-16 | 208 |
| 3 | 3 | 2025-05-20 | 209 |
| 1 | 3 | 2025-05-25 | 210 |
| 2 | 5 | 2025-01-20 | 211 |
| 3 | 5 | 2025-01-22 | 212 |
| 6 | 5 | 2025-02-10 | 213 |
| 1 | 5 | 2025-03-08 | 214 |
| 5 | 5 | 2025-04-01 | 215 |
| 4 | 5 | 2025-05-10 | 216 |
| 2 | 5 | 2025-05-30 | 217 |
| 3 | 6 | 2025-02-25 | 218 |
| 6 | 6 | 2025-03-18 | 219 |
Expected output
One row showing the member and their referrer with the maximum absolute difference in reservation counts, plus that difference.
| member_id | member_name | referrer_member_id | referrer_name | reservation_diff |
|---|---|---|---|---|
| 5 | Eva | 2 | Bob | 6 |
Hints
- Pre-aggregate reservation counts per member with a LEFT JOIN so members with 0 reservations still appear.
- Join each member to their referrer and take ABS of the two counts.