# 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 List every token in `api_tokens` with its scope, alphabetically by the second character of the scope, as the line-up for a compliance audit trail. Tokens sharing that character run from earliest issued to latest, and tokens still tied on issue date go lowest token id 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 changes the result in two places: '`full_access`' (second character 'u') belongs at the very end, after every 'write' scope, but a whole-string sort puts it right after 'admin'. And inside the 'e' group, 'read', 'read:analytics' and 'read:users' should interleave by issue date instead of sitting in three separate blocks. --- ### 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 token id When two scopes share that second character, the earliest-issued token comes first, so `issued` is the next sort key. It is not enough on its own: on this seed, 16 pairs of tokens share both the scope and the issue date. The statement names `token_id` (which is unique) as the last key. Without it, those pairs come back in whatever order the engine likes 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. It keeps each scope in its own block and drops '`full_access`' in second place instead of last. It looks 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/the_compliance_order) - [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.