# Top Active API Tokens

> The five busiest tokens.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

Surface the issuance dates of the 5 most-used tokens that have not yet expired. A token is unexpired if it has no expiration date or its expiration is still in the future. Rank by request volume descending. Show each token and its issuance date.

## Worked solution and explanation

### What this is really asking

`expires` is nullable and NULL means `no expiry`, the most-alive state. Skip that branch and the longest-lived tokens never reach the ORDER BY.

---

### Break down the requirements

#### Step 1: Unexpired definition

Two paths: `expires IS NULL` or `expires > today`. Both qualify.

#### Step 2: Rank and trim

Order by `requests DESC`, take 5, project `token_id` and `issued` only.

---

### The solution

**TOP 5 UNEXPIRED TOKENS BY VOLUME**

```sql
SELECT token_id, issued
FROM api_tokens
WHERE expires IS NULL OR expires > date('now')
ORDER BY requests DESC
LIMIT 5
```

> **Cost Analysis**
>
> 400k rows scan once with an OR predicate, then a top-5 heap. A partial index on `requests DESC WHERE expires IS NULL OR expires > current_date` turns it into a 5-row read.

> **Interviewers Watch For**
>
> Whether you handle the NULL branch explicitly, and whether you pair `LIMIT` with `ORDER BY`. `LIMIT` alone returns an arbitrary 5 rows and is a quiet correctness bug.

> **Common Pitfall**
>
> `expires > date('now')` alone drops NULL rows because `NULL > anything` is NULL, which WHERE treats as false. The `IS NULL` branch is the entire definition of unexpired, not an extra.

> **The False Start**
>
> First instinct is `WHERE expires > date('now')` since it reads off the prompt. That loses every never-expiring token, the cohort most likely to dominate the top 5. Pivot to `expires IS NULL OR expires > date('now')` so three-valued logic stops eating rows.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- What if the schema used `9999-12-31` instead of NULL for no-expiry? _(A single `expires > date('now')` works, but a check constraint must keep the sentinel honest.)_
- How would ties on `requests` be handled? _(Add `ORDER BY requests DESC, issued ASC, token_id` so the top 5 is stable run to run.)_
- What index pays for itself here? _(A filtered index on `requests DESC WHERE expires IS NULL OR expires > current_date`; otherwise a plain index on `requests` with a residual filter.)_

## Related

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