Fundamentals that hold up live or take-home

-- make it work: correlated subquery, SELECT * — returns correct rows today
SELECT *,
  (SELECT SUM(amount) FROM orders o WHERE o.customer_id = c.id) AS total_spent
FROM customers c;

-- make it right: explicit columns, JOIN instead of a correlated subquery,
-- COALESCE guards customers with zero orders, params replace string-built SQL
SELECT
  c.id,
  c.name,
  c.email,
  COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.is_active = TRUE
GROUP BY c.id, c.name, c.email
ORDER BY total_spent DESC
LIMIT $1 OFFSET $2;

-- make it fast: EXPLAIN ANALYZE pointed at a sequential scan on orders —
-- add the index the planner was missing, then wrap the query so future
-- callers reuse one definition instead of copy-pasting the JOIN
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_id ON orders (customer_id);

CREATE VIEW active_customer_spend AS
SELECT
  c.id,
  c.name,
  c.email,
  COALESCE(SUM(o.amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE c.is_active = TRUE
GROUP BY c.id, c.name, c.email;

def get_active_users(user_ids):
    results = []
    for uid in user_ids:
        user = requests.get("https://api.example.com/users/" + str(uid)).json()
        if user["status"] == "active":
            if user not in results:  # dedup via list scan
                results.append(user)
    print(results)  # debug leftover
    return results

DEFAULT_TIMEOUT_SECONDS = 5
API_BASE_URL = "https://api.example.com/users"

def get_active_users(user_ids: list[int]) -> list[dict]:
    """Fetch each user's profile and return the active ones, deduplicated by id.

    Args:
        user_ids: User IDs to look up.

    Returns:
        Active user records, in first-seen order, with duplicate ids removed.
    """
    seen_ids: list[int] = []
    active_users: list[dict] = []
    for user_id in user_ids:
        try:
            response = requests.get(f"{API_BASE_URL}/{user_id}", timeout=DEFAULT_TIMEOUT_SECONDS)
            response.raise_for_status()
            user = response.json()
        except requests.RequestException as exc:
            logger.warning("user_fetch_failed user_id=%s error=%s", user_id, exc)
            continue

        if user["status"] == "active" and user["id"] not in seen_ids:
            seen_ids.append(user["id"])
            active_users.append(user)

    return active_users  # leftover print and the O(n²) dedup scan are left for the optimize pass

MAX_CONCURRENT_REQUESTS = 10

async def get_active_users(user_ids: list[int]) -> list[dict]:
    """Fetch each user's profile and return the active ones, deduplicated by id."""
    semaphore = asyncio.Semaphore(MAX_CONCURRENT_REQUESTS)

    async def fetch_one(client: httpx.AsyncClient, user_id: int) -> dict | None:
        async with semaphore:
            try:
                response = await client.get(f"{API_BASE_URL}/{user_id}", timeout=DEFAULT_TIMEOUT_SECONDS)
                response.raise_for_status()
                return response.json()
            except httpx.HTTPError as exc:
                logger.warning("user_fetch_failed user_id=%s error=%s", user_id, exc)
                return None

    async with httpx.AsyncClient() as client:
        users = await asyncio.gather(*(fetch_one(client, uid) for uid in user_ids))

    seen_ids: set[int] = set()  # O(n) list scan -> O(1) set membership
    active_users: list[dict] = []
    for user in users:
        if user and user["status"] == "active" and user["id"] not in seen_ids:
            seen_ids.add(user["id"])
            active_users.append(user)
    return active_users