Unused Read Tokens

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain
SQL
Difficulty
easy
Seniority
L3
Asked in
Airbnb

Problem

Only a token that has served zero requests counts as never used, whatever its last-used timestamp says. Before we purge them, how many tokens in api_tokens whose scope is exactly 'read' (the narrower 'read:...' sub-scopes stay out) fall into that bucket?

Active tokens that nobody uses.

Tables

api_tokens

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

api_tokens: 10 of 200 rows
token_idowner_idscopestatusissuedexpireslast_usedrequests
1043197writerevoked2025-02-022027-02-022026-02-0247
1086294adminexpired2025-03-032027-03-032026-03-0394
1129391read:usersActive2025-04-042027-04-042026-04-04141
1172488write:ordersREVOKED2025-05-052027-05-052026-05-05188
1215585read:analyticsactive2025-06-062027-06-062026-06-06235
1258682full_accessrevoked2025-07-07NULL2026-07-07282
1301779readexpired2025-08-082027-08-082026-08-08329
1344876writeActive2025-09-092027-09-09NULL0
1387973adminREVOKED2025-10-102027-10-102026-01-10423
14301070read:usersactive2025-11-112027-11-112026-02-11470

Expected output

First 1 of 1 rows
unused_read_tokens
2

Worked solution and explanation

The field that lies

This is a two-predicate filter wearing a security costume, and the whole question is which column means 'never used'. Everyone gets the scope filter right. What separates candidates is reaching for requests = 0 instead of the tempting last_used IS NULL. On this table last_used is missing for about 15% of rows for its own reasons, while requests is never NULL. The two sets overlap but are not the same, so the NULL proxy purges tokens that are actively serving traffic and leaves behind idle ones that happen to carry a timestamp.

Step 1: Scope to plain read tokens

scope = 'read' is an exact match. The narrower 'read:...' sub-scopes are separate credentials with their own cleanup, so a prefix match would inflate the purge.

Step 2: Define 'never used' by the counter

requests = 0 is the activity record itself. last_used is a side effect that can fail to be written, so it is evidence, not the definition.

Step 3: Count what survives both filters

The ask is how big the purge is, so COUNT(*) over the filtered rows answers it in one number named unused_read_tokens.

Count idle read tokens
SELECT COUNT(*) AS unused_read_tokens
FROM api_tokens
WHERE scope = 'read'
  AND requests = 0
Timestamp proxy
WHERE scope = 'read' AND last_used IS NULL counts tokens whose timestamp was never written, including ones with thousands of requests.
Request counter
WHERE scope = 'read' AND requests = 0 counts exactly the tokens that never served a call, regardless of last_used.

Common follow-up questions