Compute reservation diff for largest member

Given copies(copy_id, reserved_by_member_id) and members(member_id, referred_by_member_id), find the member with the largest member_id. Return a single row with: member_id, referred_by_member_id, and diff_of_reserved_copy_num defined as (number of copies reserved by that member) minus (number of copies reserved by their referrer). If the referrer is NULL or has no reservations, treat their count as 0.

Compute reservation diff for largest member

You are given two tables:

1. copies(copy_id, reserved_by_member_id)
2. members(member_id, referred_by_member_id)

Each row in copies represents a copy that has been reserved by a member. Each row in members represents a member and the member who referred them (if any).

Write a SQL query to find the member with the largest member_id and return a single row with the following columns:

• member_id
• referred_by_member_id
• diff_of_reserved_copy_num, defined as: (number of copies reserved by that member) minus (number of copies reserved by their referrer)

If the referrer is NULL or the referrer has no reservations, treat the referrer's reservation count as 0.

Return exactly one row for the member with the largest member_id.

Table members

column	type	constraints
member_id	INT	PRIMARY KEY, NOT NULL
referred_by_member_id	INT

Sample data:

member_id	referred_by_member_id
1	NULL
2	1
3	1
4	2
5	NULL

Table copies

column	type	constraints
copy_id	INT	PRIMARY KEY, NOT NULL
reserved_by_member_id	INT	NOT NULL

Sample data:

copy_id	reserved_by_member_id
1	1
2	2
3	2
4	3
5	4
6	4
7	4
8	5
9	5

Expected output

A single row for the member with the largest member_id (here, member_id = 5), including their referrer and the difference between their reservation count and their referrer's reservation count. Since member 5 has no referrer (NULL), the referrer's reservation count is treated as 0, so diff_of_reserved_copy_num = 2 - 0 = 2.

diff_of_reserved_copy_num	member_id	referred_by_member_id
2	5	NULL

Hints

1. First aggregate the number of copies reserved per member using GROUP BY.
2. Find the member with the largest member_id, then join their row to their referrer’s reservation count and subtract, using COALESCE when the referrer is NULL or has no reservations.