# The Loudest Caller

> One account carries the traffic. Trace it back to everything it can touch.

Canonical URL: <https://datadriven.io/problems/the_loudest_caller>

Domain: SQL · Difficulty: easy · Seniority: L4 · Asked in: Amazon

## Problem

One account's tokens in `api_tokens` carry more total request volume than any other account's, and we want to know which parts of the API it never touches. List the scopes that appear on our tokens but on none of the busiest account's tokens; if several accounts tie for the top total, leave out the scopes of all of them.

## Tables

### api_tokens

- token_id INTEGER
- owner_id INTEGER
- scope TEXT
- status TEXT
- issued TEXT
- expires TEXT
- last_used TEXT
- requests INTEGER

Sample rows (10 of 200):

| token_id | owner_id | scope | status | issued | expires | last_used | requests |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 1043 | 197 | write | revoked | 2025-02-02 | 2027-02-02 | 2026-02-02 | 47 |
| 1086 | 294 | admin | expired | 2025-03-03 | 2027-03-03 | 2026-03-03 | 94 |
| 1129 | 391 | read:users | Active | 2025-04-04 | 2027-04-04 | 2026-04-04 | 141 |
| 1172 | 488 | write:orders | REVOKED | 2025-05-05 | 2027-05-05 | 2026-05-05 | 188 |
| 1215 | 585 | read:analytics | active | 2025-06-06 | 2027-06-06 | 2026-06-06 | 235 |
| 1258 | 682 | full_access | revoked | 2025-07-07 | NULL | 2026-07-07 | 282 |
| 1301 | 779 | read | expired | 2025-08-08 | 2027-08-08 | 2026-08-08 | 329 |
| 1344 | 876 | write | Active | 2025-09-09 | 2027-09-09 | NULL | 0 |
| 1387 | 973 | admin | REVOKED | 2025-10-10 | 2027-10-10 | 2026-01-10 | 423 |
| 1430 | 1070 | read:users | active | 2025-11-11 | 2027-11-11 | 2026-02-11 | 470 |

## Expected output

First 5 of 5 rows:

| scope |
| --- |
| read:users |
| write:orders |
| read:analytics |
| full_access |
| read |

## Worked solution and explanation

### What this problem really is

This is a filter-to-the-argmax feeding a set difference, dressed up as an access review. Anyone can total requests per account. The trap is that the winner is an account, not a token: one owner holds several tokens, and a tie can crown more than one owner. Sort raw tokens by `requests` and grab the top one, and you subtract a single token's scope instead of everything the busiest account holds. The scopes it really does reach then leak into your answer as 'never touched', which is exactly the wrong thing to hand a security review.

---

### Three hops, in order

#### Step 1: Total requests per account

Group `api_tokens` on `owner_id` and add up `requests`. The 'busiest account' metric lives at the owner grain; any comparison against a single token's `requests` is comparing apples to orchards.

#### Step 2: Keep every owner at the top

`MAX` over those per-owner totals gives you one number with no owner attached. Feeding it back into `HAVING SUM(requests) = (...)` returns every owner who hit it, so a tie at the top quietly brings in both accounts.

#### Step 3: Subtract the scopes they hold

Collect every `scope` on those owners' tokens, then return the `DISTINCT` scopes in `api_tokens` that are `NOT IN` that set. `DISTINCT` matters because each surviving scope sits on thousands of tokens.

---

### The solution

**Scopes no top account holds**

```sql
SELECT DISTINCT scope
FROM api_tokens
WHERE scope NOT IN (
    SELECT scope
    FROM api_tokens
    WHERE owner_id IN (
        SELECT owner_id
        FROM api_tokens
        GROUP BY owner_id
        HAVING SUM(requests) = (
            SELECT MAX(total_req)
            FROM (
                SELECT SUM(requests) AS total_req
                FROM api_tokens
                GROUP BY owner_id
            ) AS owner_totals
        )
    )
)
```

*Innermost out: per-owner totals, their maximum, the owners at it, their scopes, then everything else.*

> **A maximum is a value, not an owner**
>
> `MAX()` hands you a value, not an owner. The whole trick is going back to the table with that value: first ask which `owner_id` summed to it, then ask which scopes their tokens carry. Only after both hops can you subtract.

> **The top token is not the top account**
>
> `ORDER BY requests DESC LIMIT 1` finds the single loudest token. Its owner may not be the busiest account, and even when it is, the other tokens that account holds carry scopes you then fail to subtract, so they show up as 'never touched'.

> **`NOT IN` is only safe without NULLs**
>
> `scope` is never NULL here, so `NOT IN` is safe. If the inner list could ever contain a NULL, `NOT IN` would return nothing at all; on a nullable column, `NOT EXISTS` is the habit that never bites.

> **Two owner-grain scans, both cheap**
>
> The per-owner `GROUP BY` runs over 300K tokens and about 60K owners, twice. A covering index on (`owner_id`, `requests`) makes both aggregates index-only, and the final pass over `scope` touches a column with just 8 values.

> **Ties are the seniority tell**
>
> Strong candidates ask what happens on a tie before writing anything, then show that matching on the maximum handles it with no extra code. A hardcoded single top row is the tell that they did not think about it.

---

## Common follow-up questions

- If two accounts tie for the highest total, which scopes should be excluded? _(Tests tie handling: matching on the max total keeps every tied owner, which a single-row limit would miss.)_
- What changes if `scope` can be NULL on some tokens? _(Tests NULL semantics of `NOT IN` versus `NOT EXISTS`.)_
- How would you limit both sides of the comparison to tokens whose `status` is 'active'? _(Tests whether the candidate can restrict the base set without breaking the argmax.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_loudest_caller)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.