Still Standing

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

L3

Asked in

Lyft

Problem

Tokens issued without an expiry date never lapse, so our audit treats them as live alongside every token whose expiry is still ahead of today. List the 20 busiest live tokens in api_tokens with each one's issuance date and request count, busiest first.

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

Tables

api_tokens

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

Expected output

Worked solution and explanation

What this is really asking

This reads like a leaderboard, but it is a NULL-handling question in an audit costume. expires is NULL on 32 tokens, and a missing expiry is the most alive a token can be: it never lapses. Anyone can write the ORDER BY and the LIMIT. What separates candidates is the liveness filter. Write expires > date('now') alone and every never-expiring token disappears, because a comparison with NULL is NULL and WHERE keeps only true. On this data four of those tokens sit inside the top 20, so the shortcut does not trim the edges of the answer. It swaps four real rows for four quieter ones.

Step 1: Define live as two paths

A token is live if expires IS NULL (issued without an expiry) or expires > date('now') (the date is still ahead). Ignore status: it says 'expired' on rows whose expires is in 2027, and it comes in mixed case besides.

Step 2: Sort by requests and cut at 20

Order by requests DESC and keep 20. Return requests next to token_id and issued: two pairs of tokens tie on request count inside the top 20, and showing the measure makes the order checkable. The 20th and 21st rows do not tie, so the cut is clean.

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

Reads straight off the prompt

WHERE expires > date('now') keeps only rows where the comparison is true. The four never-expiring tokens at 4,230 and 3,948 requests evaluate to NULL and drop out, and tokens with 3,713 down to 3,525 requests backfill the list.

Survives the NULLs

WHERE expires IS NULL OR expires > date('now') gives the missing expiry its own branch, so never-expiring tokens compete on traffic like everyone else and the top 20 is the real top 20.

Common follow-up questions

Common follow-up questions

• What if the schema stored '9999-12-31' instead of NULL for no expiry? (The bare comparison then works, but every date calculation on `expires` has to know the sentinel is not a real date.)
• How would you make the order fully deterministic when `requests` ties? (Add `token_id` as a second sort key so tied tokens come back in the same order every run.)
• The audit now wants the top 5 live tokens per `owner_id`. What changes? (The global cut becomes a per-owner one: number rows within each owner by `requests` and keep the first five.)