# Unused Read Tokens

> Active tokens that nobody uses.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The security team is cleaning up unused tokens. Find all API tokens scoped to 'read' that have never been used (zero requests).

## Worked solution and explanation

### Why this problem exists in real interviews

This tests multi-condition filtering. Interviewers check whether you can combine equality and numeric comparisons in a single `WHERE` clause for a security audit scenario.

---

### Break down the requirements

#### Step 1: Filter to read scope

`WHERE scope = 'read'` restricts to tokens with read-only permissions.

#### Step 2: Filter to zero requests

`AND requests = 0` identifies tokens that have never been used.

---

### The solution

**Two-condition filter for unused tokens**

```sql
SELECT *
FROM api_tokens
WHERE scope = 'read'
  AND requests = 0
```

> **Cost Analysis**
>
> With 250K rows and 8 scopes, the scope filter reduces to ~31K. The requests filter further narrows. A composite index on `(scope, requests)` would make this very efficient.

> **Interviewers Watch For**
>
> Whether you use `SELECT *` or list specific columns. For a security audit, returning all columns is often appropriate since the analyst needs the full token context.

> **Common Pitfall**
>
> Using `requests IS NULL` instead of `requests = 0`. Zero usage is explicitly stored as 0, not NULL. NULLs likely represent unknown or untracked usage.

---

## Common follow-up questions

- How would you also check if the token is expired? _(Add AND expires < DATE('now') to find tokens that are both unused and expired.)_
- What if you needed the count of unused tokens per scope? _(GROUP BY scope with COUNT(*) WHERE requests = 0.)_
- How would you handle tokens where last_used is NULL vs. requests = 0? _(Tests understanding of the semantic difference between 'never used' (last_used IS NULL) and 'zero tracked requests'.)_

## Related

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