Tokens With Non-Read Scope Prefix

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

Domain
SQL
Difficulty
medium
Seniority
L4
Asked in
City and County of San Francisco

Problem

A security audit needs to verify that all API tokens have a scope starting with 'read'. Count the number of unique owners whose scope does not begin with 'read'.

Tokens that don't start with 'read'.

Tables

api_tokens

Columns: token_id INTEGER, owner_id INTEGER, scope TEXT, status TEXT, issued TEXT, expires TEXT, last_used TEXT, requests INTEGER

api_tokens: 10 of 200 rows
token_idowner_idscopestatusissuedexpireslast_usedrequests
1043197writerevoked2025-02-022027-02-022026-02-0247
1086294adminexpired2025-03-032027-03-032026-03-0394
1129391read:usersActive2025-04-042027-04-042026-04-04141
1172488write:ordersREVOKED2025-05-052027-05-052026-05-05188
1215585read:analyticsactive2025-06-062027-06-062026-06-06235
1258682full_accessrevoked2025-07-07NULL2026-07-07282
1301779readexpired2025-08-082027-08-082026-08-08329
1344876writeActive2025-09-092027-09-09NULL0
1387973adminREVOKED2025-10-102027-10-102026-01-10423
14301070read:usersactive2025-11-112027-11-112026-02-11470

Expected output

First 1 of 1 rows
non_read_owner_count
50

Worked solution and explanation

Why this problem exists in real interviews

This tests string-based filtering with negation. Interviewers want to see if you can use NOT LIKE to exclude rows matching a prefix pattern.

Break down the requirements

Step 1: Identify the exclusion pattern

The audit goal is: every token must have a scope starting with 'read'. An owner VIOLATES the policy if they hold AT LEAST ONE token whose scope does not begin with 'read'.

Step 2: Return matching tokens

Group the tokens by owner_id so each owner is evaluated as a unit across all of their tokens.

The solution

Negated prefix filter with NOT LIKE
SELECT COUNT(*) AS non_read_owner_count
FROM (
  SELECT owner_id
  FROM api_tokens
  GROUP BY owner_id
  HAVING SUM(CASE WHEN scope LIKE 'read%' THEN 0 ELSE 1 END) > 0
) violators

Common follow-up questions