# Top API Token Scopes

> The highest-value token scopes.

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

Domain: SQL · Difficulty: easy · Seniority: L4

## Problem

Find the scopes of API tokens whose owner has the most total requests. If multiple tokens share the highest count, include all their scopes.

## Worked solution and explanation

### Why this problem exists in real interviews

The `api_tokens` table is the foundation for this filtering to the top rows after aggregation problem. It tests whether you can compose a CTE or subquery that aggregates before ranking, then filter to the desired slice.

---

### Break down the requirements

#### Step 1: Aggregate per scope

`GROUP BY scope` with the appropriate aggregate function produces one summary row per group from the `api_tokens` table.

#### Step 2: Rank the results

`ORDER BY` the aggregate descending with `LIMIT` to surface the top entries.

---

### The solution

**Subquery for top owner_id by total requests then select their token scopes**

```sql
SELECT
    scope,
    SUM(requests) AS total_requests
FROM api_tokens
GROUP BY scope
ORDER BY total_requests DESC
LIMIT 10
```

> **Cost Analysis**
>
> The GROUP BY reduces the 300K-row `api_tokens` table to the number of distinct `scope` values. A covering index on `(scope, requests)` enables an index-only aggregate scan.

> **Interviewers Watch For**
>
> Interviewers verify you aggregate before sorting. Sorting raw rows gives per-row values, not group totals. The correct grain is one row per `scope`.

> **Common Pitfall**
>
> Using the wrong aggregate function. `SUM` gives totals, `COUNT` gives volume, `AVG` gives rates. Read the prompt to determine which metric is needed.

---

## Common follow-up questions

- If two owners tie for the highest total requests, should you return scopes for both? _(Tests tie-handling; using a CTE with DENSE_RANK catches ties that LIMIT 1 would miss.)_
- Should you return distinct scopes or include duplicates if the same owner has multiple tokens with the same scope? _(Tests whether DISTINCT is appropriate given the prompt says 'scopes' not 'unique scopes'.)_
- How does aggregating requests at the owner level vs. the token level change the result? _(Tests SUM(requests) grouped by owner_id vs. picking the single highest-request token.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/top_api_token_scopes)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.