# The Compliance Order

> Token scopes need to be in the right sequence before the audit.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

A compliance team needs API token scopes sorted for an audit trail. List all tokens alphabetically by the second character of the scope value.

## Worked solution and explanation

### Why this problem exists in real interviews

Interviewers use this analytics scenario to test string manipulation against the `api_tokens` table. The focus is on how you handle the `token_id` and `scope` columns when building the result.

---

### Break down the requirements

#### Step 1: Sort the final output

The `ORDER BY` clause ensures the result appears in the expected sequence. Interviewers check that the sort direction matches the prompt.

#### Step 2: Select the target columns

The SELECT clause picks exactly the columns the prompt asks for. Returning extra columns or missing a required alias would fail the grading check.

---

### The solution

**Sort the final output to find sort tokens by scope character**

```sql
SELECT token_id, scope
FROM api_tokens
ORDER BY SUBSTR(scope, 2, 1) ASC
```

> **Cost Analysis**
>
> With ~300K rows, the query performs a single sequential scan. An index on the filter/join columns would reduce the scan to a seek.

> **Interviewers Watch For**
>
> Interviewers watch for whether the query returns exactly the columns and ordering the prompt specifies; how quickly you identify the core operation and write clean, minimal code.

> **Common Pitfall**
>
> Returning extra columns that the prompt did not ask for, or using the wrong column alias, causes a grading mismatch even when the logic is correct.

---

## Common follow-up questions

- If `expires` in `api_tokens` is NULL for some rows, how would your aggregation or join logic be affected? _(Probes understanding of NULL propagation through joins and aggregate functions on `api_tokens.expires`.)_
- With 250,000 distinct values in `api_tokens.issued`, how would a composite index on the GROUP BY columns change the execution plan? _(Probes understanding of how cardinality in `issued` affects grouping and sort operations.)_
- `api_tokens.scope` only has 8 distinct values. If a new category were added, would your query automatically include it? _(Tests whether the query hard-codes values or dynamically adapts to `scope` changes.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/sort_tokens_by_scope_character)
- [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.