The Compliance Order

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

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.

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

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

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

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

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.)