Keys That Never Die

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: L4
Asked in: Amazon

Problem

An auditor going through api_tokens noticed that some tokens have no expires date at all, so they never lapse no matter what their status says. What percentage of all tokens are these never-expiring ones, returned as a decimal percentage named perpetual_pct?

Some API keys have no expiry date at all. That should worry someone.

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_id	owner_id	scope	status	issued	expires	last_used	requests
1043	197	write	revoked	2025-02-02	2027-02-02	2026-02-02	47
1086	294	admin	expired	2025-03-03	2027-03-03	2026-03-03	94
1129	391	read:users	Active	2025-04-04	2027-04-04	2026-04-04	141
1172	488	write:orders	REVOKED	2025-05-05	2027-05-05	2026-05-05	188
1215	585	read:analytics	active	2025-06-06	2027-06-06	2026-06-06	235
1258	682	full_access	revoked	2025-07-07	NULL	2026-07-07	282
1301	779	read	expired	2025-08-08	2027-08-08	2026-08-08	329
1344	876	write	Active	2025-09-09	2027-09-09	NULL	0
1387	973	admin	REVOKED	2025-10-10	2027-10-10	2026-01-10	423
1430	1070	read:users	active	2025-11-11	2027-11-11	2026-02-11	470

Expected output

First 1 of 1 rows
perpetual_pct
16

Worked solution and explanation

What this really is

This is a NULL-rate dressed up as a security audit. Everyone sees that the risky tokens are the ones with no expires. What separates candidates is two quiet details: counting the NULL rows rather than the filled ones, and dividing so the share survives as a decimal. Miss the first and you report 84, the safe tokens. Miss the second and 32 over 200 truncates to 0, and the audit says there is no risk at all.

Step 1: Flag the tokens with no expiry

A token lives forever when expires IS NULL. Writing expires = NULL compares against unknown and never matches. Wrap the test as CASE WHEN expires IS NULL THEN 1 ELSE 0 END so every row contributes a 1 or a 0, and SUM of that is the perpetual count.

Step 2: Take the whole population as the denominator

COUNT(*) counts every token, revoked and expired ones included. Status is mixed-case noise here and plays no part: the risk is the missing date, not the label.

Step 3: Make the share a decimal before scaling

SUM(...) and COUNT(*) are both integers, and integer over integer floors to 0 when the top is smaller. CAST(... AS REAL) turns the numerator into a decimal first, so 32 over 200 stays 0.16, and multiplying by 100 gives the percentage.

One scan, one flag, one decimal division

SELECT CAST(SUM(CASE WHEN expires IS NULL THEN 1 ELSE 0 END) AS REAL) / COUNT(*) * 100 AS perpetual_pct FROM api_tokens

Integer division: SUM(CASE ...) / COUNT(*) * 100 computes 32 / 200 = 0 first, then 0 * 100 = 0. The audit comes back clean.
Decimal division: CAST(SUM(CASE ...) AS REAL) / COUNT(*) * 100 computes 0.16, then 16.0. The order of operations is exactly why the cast has to sit on the numerator.

Common follow-up questions

How would you also report the share of tokens whose `expires` is already in the past? (A second conditional count in the same scan; an already-lapsed token is a different risk from one that never lapses.)
What if security wants the perpetual share per `scope`? (Add `GROUP BY scope`; the flag and the decimal division stay identical, and `full_access` with no expiry is the row they will care about.)
What happens if `api_tokens` is ever empty? (`COUNT(*)` is 0 and the division fails or returns NULL depending on the engine; `NULLIF(COUNT(*), 0)` makes the empty case explicit.)