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: ```python 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: ```python 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: ```python 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: ```python 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: ```python 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.