The Loudest Caller

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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.

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

Tables

api_tokens

Columns: token_id INTEGER, owner_id INTEGER, scope TEXT, status TEXT, issued TEXT, expires TEXT, last_used TEXT, requests INTEGER

Expected output

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

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
        )
    )
)

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.)