Solve library SQL and Python tasks

You are given a library domain. Assume these tables:

• books(book_id, author_id, title)
• authors(author_id, name)
• copies(copy_id, book_id, condition) -- condition in {'good','fair','poor'}
• checkouts(checkout_id, copy_id, member_id, checkout_date, return_date, renew_count)
• members(member_id, name, referrer_member_id) -- referrer_member_id may be NULL
• reservations(reservation_id, member_id, book_id, reserve_date) Write SQL for the following:

1. Return two metrics in one row: (a) total_active_good = count of checkouts where return_date IS NULL and the associated copy's condition = 'good'; (b) pct_renew_gt_2 = among those active-good checkouts, the percentage whose renew_count > 2 (as a decimal rounded to 2 decimals). If the denominator is 0, return 0.00.
2. Among books that have more than 10 copies (counted from copies), compute for each book the maximum completed-checkout duration in days using return_date - checkout_date (ignore rows with return_date IS NULL). Return the top 3 books by this maximum duration, with columns (book_id, max_duration_days). Break ties by book_id ASC.
3. For each referred member m (members.referrer_member_id IS NOT NULL), compute the absolute difference between the number of reservations made by m and the number made by their referrer r. Return the row with the largest absolute difference, with columns (member_id, referrer_member_id, reservations_m, reservations_r, abs_diff). Break ties by member_id ASC. Python tasks:
4. Given a list of tuples like [('category1', 4), ('category2', 6), ...], implement summarize(scores) that returns two integers: (total_sum, top3_max_sum) where total_sum is the sum of all scores; to compute top3_max_sum, first take the maximum score per category, then sum the top 3 category maxima (if fewer than 3 categories, sum what's available). Break ties by category name ASC when selecting top 3.
5. You receive a list of LogEntry objects where True means checkout and False means return:

class LogEntry:
    def __init__(self, book_id: int, is_checkout: bool):
        self.book_id = book_id
        self.is_checkout = is_checkout

Implement is_valid_checkout_log(logs) -> bool that validates the sequence with these rules for each book_id: (a) a return cannot occur before the first checkout; (b) two consecutive checkouts without an intervening return are invalid; (c) two consecutive returns are invalid. The check should run in O(n) time and O(n) space.

Active good-condition checkouts and renewal rate

Using the library schema below, write a SQL query that returns a single row with two metrics:

1. total_active_good: the count of checkouts where return_date IS NULL and the associated copy's condition = 'good'.
2. pct_renew_gt_2: among those active-good checkouts, the percentage whose renew_count > 2, returned as a decimal rounded to 2 decimal places. If there are no active-good checkouts (denominator 0), return 0.00. Return the result as one row with columns (total_active_good, pct_renew_gt_2).

Table books

column	type	constraints
book_id	INT	PRIMARY KEY
author_id	INT	NOT NULL
title	VARCHAR(100)	NOT NULL

Sample data:

author_id	book_id	title
1	1	Data Structures
2	2	Databases 101
1	3	Python Basics
3	4	Algorithms Advanced

Table copies

column	type	constraints
copy_id	INT	PRIMARY KEY
book_id	INT	NOT NULL
condition	VARCHAR(10)	NOT NULL

Sample data:

book_id	condition	copy_id
1	good	1
1	good	2
1	fair	3
1	good	4
1	poor	5
1	good	6
1	good	7
1	good	8
1	good	9
1	good	10
1	good	11
1	good	12
2	good	13
2	fair	14
2	good	15
2	good	16
2	good	17
2	poor	18
2	good	19
2	good	20
2	good	21
2	good	22
2	good	23
3	good	24
3	good	25
3	fair	26
3	good	27
3	poor	28
4	good	29
4	good	30
4	fair	31

Table members

column	type	constraints
member_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
referrer_member_id	INT

Sample data:

member_id	name	referrer_member_id
1	Mike	NULL
2	Nina	1
3	Oliver	1
4	Priya	2
5	Quinn	NULL

Table checkouts

column	type	constraints
checkout_id	INT	PRIMARY KEY
copy_id	INT	NOT NULL
member_id	INT	NOT NULL
checkout_date	DATE	NOT NULL
return_date	DATE
renew_count	INT	NOT NULL

Sample data:

checkout_date	checkout_id	copy_id	member_id	renew_count	return_date
2025-05-01	1	1	1	3	NULL
2025-05-10	2	2	2	1	2025-05-20
2025-05-15	3	3	1	1	NULL
2025-05-05	4	4	3	2	NULL
2025-04-01	5	5	2	4	2025-04-10
2025-03-01	6	6	1	5	NULL
2025-01-01	7	13	1	0	2025-01-20
2025-02-10	8	14	2	1	2025-02-12
2025-03-05	9	15	3	1	NULL
2025-01-01	10	7	2	2	2025-02-15

Table reservations

column	type	constraints
reservation_id	INT	PRIMARY KEY
member_id	INT	NOT NULL
book_id	INT	NOT NULL
reserve_date	DATE	NOT NULL

Sample data:

book_id	member_id	reservation_id	reserve_date
1	1	1	2025-05-01
1	2	2	2025-05-02
2	2	3	2025-05-03
2	3	4	2025-05-04
3	3	5	2025-05-05
4	3	6	2025-05-06
1	4	7	2025-05-07
2	4	8	2025-05-08
3	4	9	2025-05-09
2	1	10	2025-05-10
3	2	11	2025-05-11

Expected output

A single row summarizing the number of active checkouts for copies in 'good' condition and the percentage of those active-good checkouts with renew_count > 2, rounded to 2 decimal places.

pct_renew_gt_2	total_active_good
0.5	4

Hints

1. Filter first to active checkouts (return_date IS NULL) with copies in 'good' condition.
2. Compute the percentage as SUM of a CASE expression divided by COUNT(*), and handle the zero-denominator case with a CASE expression.

Max completed checkout duration for high-copy-count books

Using the same library schema, write a SQL query that:

1. Considers only books that have more than 10 copies (based on the copies table).
2. For each such book, looks at completed checkouts only (rows in checkouts where return_date IS NOT NULL) and computes the maximum duration in days as (return_date - checkout_date).
3. Returns the top 3 books ordered by this maximum duration in descending order, breaking ties by book_id ascending. Output columns should be (book_id, max_duration_days).

Table books

column	type	constraints
book_id	INT	PRIMARY KEY
author_id	INT	NOT NULL
title	VARCHAR(100)	NOT NULL

Sample data:

author_id	book_id	title
1	1	Data Structures
2	2	Databases 101
1	3	Python Basics
3	4	Algorithms Advanced

Table copies

column	type	constraints
copy_id	INT	PRIMARY KEY
book_id	INT	NOT NULL
condition	VARCHAR(10)	NOT NULL

Sample data:

book_id	condition	copy_id
1	good	1
1	good	2
1	fair	3
1	good	4
1	poor	5
1	good	6
1	good	7
1	good	8
1	good	9
1	good	10
1	good	11
1	good	12
2	good	13
2	fair	14
2	good	15
2	good	16
2	good	17
2	poor	18
2	good	19
2	good	20
2	good	21
2	good	22
2	good	23
3	good	24
3	good	25
3	fair	26
3	good	27
3	poor	28
4	good	29
4	good	30
4	fair	31

Table members

column	type	constraints
member_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
referrer_member_id	INT

Sample data:

member_id	name	referrer_member_id
1	Mike	NULL
2	Nina	1
3	Oliver	1
4	Priya	2
5	Quinn	NULL

Table checkouts

column	type	constraints
checkout_id	INT	PRIMARY KEY
copy_id	INT	NOT NULL
member_id	INT	NOT NULL
checkout_date	DATE	NOT NULL
return_date	DATE
renew_count	INT	NOT NULL

Sample data:

checkout_date	checkout_id	copy_id	member_id	renew_count	return_date
2025-05-01	1	1	1	3	NULL
2025-05-10	2	2	2	1	2025-05-20
2025-05-15	3	3	1	1	NULL
2025-05-05	4	4	3	2	NULL
2025-04-01	5	5	2	4	2025-04-10
2025-03-01	6	6	1	5	NULL
2025-01-01	7	13	1	0	2025-01-20
2025-02-10	8	14	2	1	2025-02-12
2025-03-05	9	15	3	1	NULL
2025-01-01	10	7	2	2	2025-02-15

Table reservations

column	type	constraints
reservation_id	INT	PRIMARY KEY
member_id	INT	NOT NULL
book_id	INT	NOT NULL
reserve_date	DATE	NOT NULL

Sample data:

book_id	member_id	reservation_id	reserve_date
1	1	1	2025-05-01
1	2	2	2025-05-02
2	2	3	2025-05-03
2	3	4	2025-05-04
3	3	5	2025-05-05
4	3	6	2025-05-06
1	4	7	2025-05-07
2	4	8	2025-05-08
3	4	9	2025-05-09
2	1	10	2025-05-10
3	2	11	2025-05-11

Expected output

For books that have more than 10 copies, returns up to 3 rows with each book's maximum completed-checkout duration (in days), ordered by this duration descending and then book_id ascending. Based on the sample data, only books 1 and 2 qualify.

book_id	max_duration_days
1	45
2	19

Hints

1. First identify books with more than 10 copies using a subquery or CTE on the copies table.
2. Join those books to checkouts and use MAX(return_date - checkout_date) grouped by book_id, then apply an ORDER BY with a LIMIT/FETCH to get the top 3.

Reservation difference between referred members and their referrers

Using the same library schema, write a SQL query that for each referred member m (rows in members where referrer_member_id IS NOT NULL) computes:

1. reservations_m: the number of reservations made by member m.
2. reservations_r: the number of reservations made by m's referrer r (members.member_id = m.referrer_member_id).
3. abs_diff: the absolute difference |reservations_m - reservations_r|. Return only the single row with the largest abs_diff. If there is a tie on abs_diff, return the row with the smallest member_id among the tied members. Output columns should be (member_id, referrer_member_id, reservations_m, reservations_r, abs_diff). Members with no reservations should be treated as having 0 reservations.

Table books

column	type	constraints
book_id	INT	PRIMARY KEY
author_id	INT	NOT NULL
title	VARCHAR(100)	NOT NULL

Sample data:

author_id	book_id	title
1	1	Data Structures
2	2	Databases 101
1	3	Python Basics
3	4	Algorithms Advanced

Table copies

column	type	constraints
copy_id	INT	PRIMARY KEY
book_id	INT	NOT NULL
condition	VARCHAR(10)	NOT NULL

Sample data:

book_id	condition	copy_id
1	good	1
1	good	2
1	fair	3
1	good	4
1	poor	5
1	good	6
1	good	7
1	good	8
1	good	9
1	good	10
1	good	11
1	good	12
2	good	13
2	fair	14
2	good	15
2	good	16
2	good	17
2	poor	18
2	good	19
2	good	20
2	good	21
2	good	22
2	good	23
3	good	24
3	good	25
3	fair	26
3	good	27
3	poor	28
4	good	29
4	good	30
4	fair	31

Table members

column	type	constraints
member_id	INT	PRIMARY KEY
name	VARCHAR(100)	NOT NULL
referrer_member_id	INT

Sample data:

member_id	name	referrer_member_id
1	Mike	NULL
2	Nina	1
3	Oliver	1
4	Priya	2
5	Quinn	NULL

Table checkouts

column	type	constraints
checkout_id	INT	PRIMARY KEY
copy_id	INT	NOT NULL
member_id	INT	NOT NULL
checkout_date	DATE	NOT NULL
return_date	DATE
renew_count	INT	NOT NULL

Sample data:

checkout_date	checkout_id	copy_id	member_id	renew_count	return_date
2025-05-01	1	1	1	3	NULL
2025-05-10	2	2	2	1	2025-05-20
2025-05-15	3	3	1	1	NULL
2025-05-05	4	4	3	2	NULL
2025-04-01	5	5	2	4	2025-04-10
2025-03-01	6	6	1	5	NULL
2025-01-01	7	13	1	0	2025-01-20
2025-02-10	8	14	2	1	2025-02-12
2025-03-05	9	15	3	1	NULL
2025-01-01	10	7	2	2	2025-02-15

Table reservations

column	type	constraints
reservation_id	INT	PRIMARY KEY
member_id	INT	NOT NULL
book_id	INT	NOT NULL
reserve_date	DATE	NOT NULL

Sample data:

book_id	member_id	reservation_id	reserve_date
1	1	1	2025-05-01
1	2	2	2025-05-02
2	2	3	2025-05-03
2	3	4	2025-05-04
3	3	5	2025-05-05
4	3	6	2025-05-06
1	4	7	2025-05-07
2	4	8	2025-05-08
3	4	9	2025-05-09
2	1	10	2025-05-10
3	2	11	2025-05-11

Expected output

The single referred member whose reservation count differs the most from their referrer's count, with ties broken by the smallest member_id. Based on the sample data, members 2 and 3 both have an absolute difference of 1 versus referrer 1, so member 2 is chosen by the tie-break rule.

abs_diff	member_id	referrer_member_id	reservations_m	reservations_r
1	2	1	3	2

Hints

1. First aggregate reservations per member, then join members to this aggregation twice: once for the referred member and once for the referrer.
2. Use COALESCE for members with zero reservations and compute abs_diff with ABS, then order by abs_diff DESC and member_id ASC, limiting the result to one row.