Poles Apart

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

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

City and County of San Francisco

Problem

Never-used API tokens all sit at zero requests, so in the security team's review the quiet end is measured only over tokens that have actually been used. From our API tokens, surface the busiest and the quietest tokens along with every admin-scoped token that has never been used at all, each with its request count, busiest first.

Two extremes, one result: the busiest tokens and the quietest.

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

Beneath the security-audit framing, this is three conditions folded into one WHERE, each scoped differently: the global MAX(requests), the MIN(requests) taken only over tokens that have been used, and a dormant-privilege flag. Scoping each one correctly is the whole difficulty. Every never-used token carries requests = 0, so a naive MIN(requests) over the full table comes back 0, and your 'quietest' list becomes tokens that never served a call. The busy side has no such trap: an unused token can never be the busiest, so guarding it does nothing. The dormant branch, last_used IS NULL AND scope = 'admin', only means what you want because AND binds tighter than the surrounding OR. Drop either predicate and the flag pulls in every admin token or every dormant one.

Working it out

Step 1: Take the busy end straight

The busiest extreme is a plain MAX(requests) scalar subquery over the whole table. A never-used token sits at 0, so it can never be the maximum. Adding a used-only filter here removes nothing and hides the point.

Step 2: Scope the quiet end to used tokens

The quiet extreme is MIN(requests) taken over last_used IS NOT NULL. Skip that guard and the minimum is 0, contributed by tokens that never ran, and they swamp the answer. This is the one place the null check earns its keep.

Step 3: Flag the dormant admin keys

A token that has never been used but still carries an admin scope is a risk of its own. Express it as last_used IS NULL AND scope = 'admin'. Both predicates pull their weight: without the null check you flag every admin token, and without the scope check you flag every dormant token.

Step 4: Combine and order

Join the three conditions with OR so every token at an extreme or flagged as dormant admin comes back, and wrap the AND branch in parentheses so the grouping is explicit. Then ORDER BY requests DESC so the busiest lead. Tokens tied on a count can come back in any order.

The solution

SELECT token_id, requests
FROM api_tokens
WHERE requests = (SELECT MAX(requests) FROM api_tokens)
   OR requests = (SELECT MIN(requests) FROM api_tokens WHERE last_used IS NOT NULL)
   OR (last_used IS NULL AND scope = 'admin')
ORDER BY requests DESC

Common follow-up questions

• How would you return both extremes from a single scan instead of two subqueries? (Tests fluency with `MIN(requests) OVER ()` and `MAX(requests) OVER ()`; the null guard then moves into a filtered window or a pre-filtered CTE.)
• What changes if the dormant-privilege rule covered `admin` or `full_access` scopes? (A small change to an `IN` list; the candidate should confirm that the AND/OR precedence and the OR semantics still hold.)
• What if the spec became the top five and bottom five used tokens instead of the single extremes? (Forces `DENSE_RANK` or ranked CTEs unioned together to keep ties at each boundary.)