Code Review: Spotting Anti-Patterns Before They Hit Production

You're reviewing a pull request that adds an "account summary" API endpoint. The relevant code (Python, using a lightweight query builder, roughly translated to the SQL it issues) is:

accounts = db.query("SELECT * FROM accounts WHERE region = %s", region)

summaries = []
for account in accounts:
    orders = db.query(
        "SELECT * FROM orders WHERE account_id = %s AND status = 'active'",
        account.id
    )
    total = sum(o.amount_cents for o in orders)
    summaries.append({
        "account_id": account.id,
        "name": account.name,
        "active_total_cents": total,
    })

accounts typically returns 200-800 rows per region.

1. Identify every distinct performance anti-pattern in this code — there are at least two, and they are not the same issue wearing two hats.
2. Rewrite the logic (in SQL, plus however much surrounding code structure is needed to make the point) to eliminate each one, and explain the specific mechanism each fix addresses.
3. Suppose, after your fix, the endpoint is still slow because region is a low-cardinality column (12 possible values, roughly evenly distributed across a 40-million-row accounts table). Should you add an index on accounts(region)? Justify your answer using the reasoning for when indexing is and isn't appropriate.