Solve SQL and Python coding tasks

You are given a small library system with the following relational schema and several Python data-processing tasks. Answer the SQL questions and implement the described Python functions.

Part 1: SQL Questions

Assume the following tables:

Table: Books

• book_id INT, primary key
• title VARCHAR
• condition ENUM('good', 'damaged', 'lost') — current physical condition of the book copy
• copies INT — number of physical copies the library owns for this title
• lifetime_value DECIMAL — total revenue (e.g., rental fees) generated by this title over its lifetime

Table: Loans

• loan_id INT, primary key
• book_id INT, foreign key to Books(book_id)
• member_id INT, foreign key to Members(member_id)
• loaned_at DATETIME
• returned_at DATETIME NULL — NULL means the book has not yet been returned

Table: Members

• member_id INT, primary key
• invited_by INT NULL, foreign key to Members(member_id) — the member who invited this member, if any
• reserved_copies INT — number of book copies this member currently has reserved

Write SQL queries for the following:

1. Count good, not-yet-returned books
   Return a single integer: the number of book loans where the book is in condition = 'good' and the book has not yet been returned (i.e., the corresponding Loans.returned_at is NULL ).
2. Top 3 high-value books with many copies
   Return the top 3 books (their book_id and title ) that have:
   • more than 10 copies ( copies > 10 ), and
   • the highest lifetime_value among such books.
     Order results by lifetime_value descending, and if needed, break ties by book_id ascending. Limit the result to 3 rows.
3. Largest absolute difference in reserved copies between inviter and invitee
   For each member who was invited by another member ( invited_by is not NULL ), consider the pair:
   • the inviter: Members.member_id = invited_by
   • the invitee: Members.member_id (the one who was invited)
   Each member has a reserved_copies value. For every inviter–invitee pair, compute the absolute difference:

|\text{inviter.reserved_copies} - \text{invitee.reserved_copies}|

Write a query that returns a single row with a single column giving the maximum such absolute difference over all valid inviter–invitee pairs.

Part 2: Python Coding Questions

Implement the following functions in Python. Aim for efficient, readable solutions.

Python Q1: Maximum reading score without consecutive books

You are given a list of non-negative integers scores, where scores[i] is the number of points a student earns by reading the i-th book in a sequence. The student may choose to read any subset of books, but cannot read two adjacent books in the sequence (they need rest between adjacent books).

Write a function:

from typing import List

def max_reading_score(scores: List[int]) -> int:
    """Return the maximum total score achievable without reading adjacent books."""

Constraints:

• 0 <= len(scores) <= 10**5
• 0 <= scores[i] <= 10**4

The function should return the maximum possible total score.

Python Q2: Validate a sequence of open/close log entries

You are given a list of log entries representing operations on resources. Each entry is a tuple (action, resource_id) where:

• action is a string: either 'OPEN' or 'CLOSE'
• resource_id is a string identifying the resource

A log is considered valid if all of the following hold:

• A resource cannot be closed before it has been opened.
• A resource cannot be opened twice in a row without being closed in between.
• At the end of the log, all opened resources must have been closed (no resource remains open).

Write a function:

from typing import List, Tuple

def is_log_valid(logs: List[Tuple[str, str]]) -> bool:
    """Return False if the log sequence is inconsistent; True otherwise."""

Return False if the log does not make sense according to the above rules; otherwise return True.

Constraints:

• 0 <= len(logs) <= 10**5

Python Q3: Count employees at each open office

You are given a list of office objects and a set of office IDs that are closed.

Assume offices are represented as:

from typing import List, Dict, Set

Office = Dict[str, object]  # keys: "id" (int), "employees" (List[Dict[str, object]])

Each Office dictionary has:

• "id" : int — office identifier
• "employees" : List[Dict[str, object]] — a list of employee objects assigned to that office (you do not need to inspect the employee fields; just count how many there are).

You are also given a set closed_office_ids: Set[int] containing the IDs of offices that are closed.

Write a function:

from typing import Dict

def count_employees_by_open_office(
    offices: List[Office], closed_office_ids: Set[int]
) -> Dict[int, int]:
    """Return a mapping from office_id to employee count for offices that are open."""

The function should:

• Ignore any office whose id is in closed_office_ids .
• For every remaining (open) office, compute how many employees it has by taking len(office["employees"]) .
• Return a dictionary mapping office_id (int) to that employee count (int) for only the open offices.

You may assume:

• Office IDs are unique within offices .
• 0 <= len(offices) <= 10**4 .

Python Q4: Merge two strings to create a new name

You are given two non-empty strings a and b. Create a new string by alternating characters from a and b, starting with a. When one string runs out of characters, append the remaining characters from the other string.

For example:

• a = "abc" , b = "XYZ" → result: "aXbYcZ"
• a = "ab" , b = "WXYZ" → result: "aWbXYZ"

Write a function:

def merge_names(a: str, b: str) -> str:
    """Return a new string formed by alternating characters from a and b."""

Assume:

• 1 <= len(a), len(b) <= 10**5 .

The function should run in O(len(a) + len(b)) time.