# Still Standing

> The busiest keys are the ones that never had a deadline.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

We're auditing which API tokens are still carrying live traffic. Surface the 5 busiest tokens that haven't expired, and show each token with its issuance date.

## Worked solution and explanation

### What this is really asking

This looks like a plain top-5 leaderboard, but the real test is three-valued logic. `expires` is nullable, and a NULL is the most-alive state a token can be in: no expiry at all. Write the filter as `expires > date('now')` alone and every never-expiring token silently vanishes, because `NULL > anything` evaluates to NULL and WHERE treats NULL as false. Those never-expiring tokens are exactly the cohort most likely to have piled up the highest request counts, so dropping them does not just trim the result, it corrupts the top 5.

---

### Break down the requirements

#### Step 1: Unexpired definition

A token is unexpired on two paths: `expires IS NULL` (never expires) or `expires > date('now')` (still in the future). Both must survive the filter.

#### Step 2: Rank and trim

Order by `requests DESC`, take 5, and project `token_id` and `issued` only. Pairing LIMIT with an explicit ORDER BY is what makes the 5 rows meaningful.

---

### 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 keeps memory flat. A partial index on requests DESC WHERE expires IS NULL OR expires > current_date turns the whole thing 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 that passes small test data by luck.

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

> **The False Start**
>
> The first instinct is `WHERE expires > date('now')` because it reads straight 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 has to keep the sentinel value honest so it never sorts as a real date.)_
- 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/still_standing)
- [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.