Top Percentile API Tokens

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

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

Google

Problem

Consider tokens at or above the 95th percentile by total requests within each scope as high-risk. Show the token ID, scope, request count, and percentile rank, rounded to 2 decimal places. Present the results in alphabetical order by scope, listing each scope's busiest tokens first and, when two tokens are tied on requests, the lower-numbered one ahead.

The most suspicious tokens.

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

Why this problem exists in real interviews

"95th percentile" sounds unambiguous until you try three functions and get three answers. PERCENT_RANK, CUME_DIST, and NTILE all return something defensible, and only one matches what a security engineer means when they flag the noisiest tokens in each scope.

Break down the requirements

Step 1: Partition by scope

High-volume is relative inside each scope. A read-only token doing 10k requests is normal; an admin token doing the same is not. Every percentile calculation must PARTITION BY scope.

Step 2: Pick the right percentile function

Use PERCENT_RANK() with ORDER BY requests ASC. It returns (rank - 1) / (n - 1), so the >= 0.95 filter captures the top 5 percent within each partition.

Step 3: Round for presentation only

ROUND(pct_rank, 2) shapes the output column. Do not round inside the window or before the comparison or you lose precision on the boundary.

The solution

WITH ranked AS (
  SELECT token_id, scope, requests,
         PERCENT_RANK() OVER (
           PARTITION BY scope
           ORDER BY requests ASC
         ) AS pct_rank
  FROM api_tokens
)
SELECT token_id, scope, requests,
       ROUND(pct_rank, 2) AS percentile_rank
FROM ranked
WHERE pct_rank >= 0.95
ORDER BY scope, requests DESC, token_id

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would the output differ if you used `CUME_DIST` instead of `PERCENT_RANK`? (Tests the practical distinction: `CUME_DIST` counts ties on the same side, so it can return values above 0.95 for rows that `PERCENT_RANK` puts at 0.94.)
• What happens to the result if a scope has only 20 tokens? (Probes awareness that `PERCENT_RANK` on a 20-row partition only yields discrete steps of roughly 0.053, so the top single token clears 0.95 trivially.)
• Rewrite this to also exclude tokens where `status = 'revoked'` without changing the percentile semantics. (Forces the filter into the CTE before the window function runs, since filtering after `PERCENT_RANK()` would keep the revoked tokens in the denominator.)