# The Compliance Order > Token scopes need to be in the right sequence before the audit. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: Amazon ## Problem A compliance team needs API token scopes arranged for an audit trail. List every token alphabetically by the second character of its scope, and when two scopes share that character, the earliest-issued token comes first. ## Tables ### api_tokens - token_id INTEGER - owner_id INTEGER - scope TEXT - status TEXT - issued TEXT - expires TEXT - last_used TEXT - requests INTEGER Sample rows (10 of 200): | token_id | owner_id | scope | status | issued | expires | last_used | requests | | --- | --- | --- | --- | --- | --- | --- | --- | | 1043 | 197 | write | revoked | 2025-02-02 | 2027-02-02 | 2026-02-02 | 47 | | 1086 | 294 | admin | expired | 2025-03-03 | 2027-03-03 | 2026-03-03 | 94 | | 1129 | 391 | read:users | Active | 2025-04-04 | 2027-04-04 | 2026-04-04 | 141 | | 1172 | 488 | write:orders | REVOKED | 2025-05-05 | 2027-05-05 | 2026-05-05 | 188 | | 1215 | 585 | read:analytics | active | 2025-06-06 | 2027-06-06 | 2026-06-06 | 235 | | 1258 | 682 | full_access | revoked | 2025-07-07 | NULL | 2026-07-07 | 282 | | 1301 | 779 | read | expired | 2025-08-08 | 2027-08-08 | 2026-08-08 | 329 | | 1344 | 876 | write | Active | 2025-09-09 | 2027-09-09 | NULL | 0 | | 1387 | 973 | admin | REVOKED | 2025-10-10 | 2027-10-10 | 2026-01-10 | 423 | | 1430 | 1070 | read:users | active | 2025-11-11 | 2027-11-11 | 2026-02-11 | 470 | ## Expected output First 5 of 200 rows: | token_id | scope | | --- | --- | | 10005386 | admin | | 10005351 | admin | | 10005361 | read:analytics | | 10005366 | read:users | | 10005391 | read | ## Worked solution and explanation ### What this is really testing This looks like a plain sort, but the sort key is not a column. 'Alphabetically by the second character of its scope' means you order by an expression: the single character at position 2, pulled out with `SUBSTR(scope, 2, 1)`. The tell for a weaker answer is `ORDER BY scope`, which collates the whole string from its first character. On this seed that reorders everything: `'admin'` sorts by 'd', but `'read'`, `'read:analytics'` and `'read:users'` all share second character 'e' and should sit together, while a whole-string sort scatters them by their trailing text. --- ### Build the ordering #### Step 1: Order by the extracted second character The primary key is the character at position 2. `SUBSTR(scope, 2, 1)` takes exactly one character starting at index 2, so `'admin'` yields 'd' and every `'read...'` variant yields 'e'. Sort on that expression, not on `scope`. #### Step 2: Break ties by issue date, then a unique key When two scopes share that second character, the prompt wants the earliest-issued token first, so `issued` is the next sort key. Because `issued` is not unique across 300K rows, append `token_id` (which is unique) as a final key so the ordering is fully deterministic; without it, rows sharing a second character and an issue date would come back in arbitrary engine order and fail an order-sensitive check. #### Step 3: Select exactly the two columns asked for Return only `token_id` and `scope`. No status filter: the audit covers every token, and the mixed-case `status` values are a distractor. --- ### The solution **Order by the second scope character, then issue date** ```sql SELECT token_id, scope FROM api_tokens ORDER BY SUBSTR(scope, 2, 1) ASC, issued, token_id ``` > **The sort key can be an expression** > > `ORDER BY` accepts any expression, not just a column name. Sorting by `SUBSTR(scope, 2, 1)` is the whole trick: the moment you realize the sort key can be computed inline, the problem collapses to one clause. > **Whole-string sort is not second-character sort** > > `ORDER BY scope` sorts by the whole string from its first character, so it groups by the leading letter and sequences the rest by trailing text, not by the second character. It happens to look plausible and is the most common wrong answer here. > **A graded order must be total** > > For an order-sensitive result the sort must be total: two answers that differ only in the arrangement of rows with identical sort keys cannot both be graded correct. A unique final key like `token_id` is what makes the output reproducible. --- ## Common follow-up questions - How would the ordering change if some scopes were a single character long, so there is no second character to extract? _(Tests whether the candidate reaches for a CASE expression or a second SUBSTR rather than assuming a fixed position.)_ - Could an index help this sort, given the key is `SUBSTR(scope, 2, 1)` rather than `scope` itself? _(Probes understanding of expression indexing versus plain column indexes.)_ - If the audit instead wanted the second character in descending order, what changes? _(Checks the candidate can invert an expression-based sort cleanly.)_ ## 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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.