Active API 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

City and County of San Francisco

Problem

A token that was issued but never called has no last-used time recorded at all. An engineer on the developer platform team is auditing token usage and wants every field of each token in api_tokens that has actually been used.

Tokens that have actually been used.

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

This is a missing-value test dressed up as a usage audit. 24 of the 200 tokens in api_tokens were issued and never called, and the only trace of that is an empty last_used. Anyone can write SELECT * FROM api_tokens WHERE .... What separates candidates is the predicate. Write last_used <> NULL and every comparison comes back unknown, the filter keeps nothing, and the audit reports zero used tokens when 176 have been used.

Reading the requirement

Step 1: Return every field

The audit wants each token row as stored, from token_id through requests, so SELECT * is the honest projection here. Listing eight columns by hand reads the same and only adds a place to drop one.

Step 2: Define used as a recorded last_used

The table never says 'used' outright. A never-called token has no last_used at all, and its requests sits at 0. The statement names the last-used time, so the presence of that value is the whole filter.

Step 3: Test presence, not equality

IS NOT NULL asks whether a value exists. last_used <> NULL asks whether the value differs from an unknown, and the answer is always unknown, which WHERE treats as false.

The solution

SELECT *
FROM api_tokens
WHERE last_used IS NOT NULL

Comparing to NULL

WHERE last_used <> NULL evaluates to unknown on all 200 rows. WHERE keeps only true, so the result is empty and the audit says no token was ever used.

Testing for NULL

WHERE last_used IS NOT NULL is true on the 176 rows with a timestamp and false on the 24 without one. Every used token comes back, and nothing else does.

Common follow-up questions

• How would you list the tokens that were issued but never called, and which owners hold the most of them? (This flips the predicate to `IS NULL` and adds a count per `owner_id`.)
• `status` holds both 'active' and 'Active'. How would you return only the used tokens that are currently active? (Tests whether the candidate notices inconsistent case before filtering on `status`.)
• If `last_used` and `requests` ever disagree (a timestamp with `requests` at 0), which do you trust for the audit, and why? (Tests reasoning about which field is the source of truth.)
• What changes if 'used' means used within the last 30 days? (Adds a date window on `last_used` on top of the presence test.)