# Tokens With Non-Read Scope Prefix

> Tokens that don't start with 'read'.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## 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'.

## 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

Use `WHERE scope NOT LIKE 'read%'` to exclude tokens whose scope starts with 'read'.

#### Step 2: Return matching tokens

Select token details for all non-read-scoped tokens.

---

### The solution

**Negated prefix filter with NOT LIKE**

```sql
SELECT token_id, owner_id, scope, status, issued
FROM api_tokens
WHERE scope NOT LIKE 'read%'
ORDER BY issued DESC
```

> **Cost Analysis**
>
> `NOT LIKE 'read%'` cannot use a standard B-tree index efficiently because it matches everything except one prefix range. The planner typically does a full scan.

> **Interviewers Watch For**
>
> Interviewers check whether you use `NOT LIKE` vs. `LEFT(scope, 4) != 'read'`. Both work, but `NOT LIKE` is more idiomatic.

> **Common Pitfall**
>
> Using `scope != 'read'` instead of `scope NOT LIKE 'read%'`. The exact inequality only excludes the literal string 'read', not scopes like 'read_write' or 'readonly'.

---

## Common follow-up questions

- How would you list the distinct non-read scope values? _(Tests adding SELECT DISTINCT scope.)_
- What if scope values are case-inconsistent? _(Tests LOWER(scope) NOT LIKE 'read%'.)_
- How would you count tokens per non-read scope? _(Tests GROUP BY scope with the NOT LIKE filter.)_

## Related

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