# Keys That Never Die > Some API keys have no expiry date at all. That should worry someone. Canonical URL: 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`? ## Tables ### api_tokens - token_id INTEGER - owner_id INTEGER - scope TEXT - status TEXT - issued TEXT - expires TEXT - last_used TEXT - requests INTEGER Sample rows (10 of 200): | 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** ```sql 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. > **`COUNT(expires)` counts the wrong side** > > `COUNT(column)` skips NULLs, so `COUNT(expires)` returns the 168 tokens that do expire. Candidates who reach for it report 84 and never notice, because 84 looks like a perfectly plausible percentage. > **Say where the decimal comes from** > > The tell is a candidate who explains why the division is safe rather than getting lucky with operand order. Naming the integer-truncation risk and placing the `CAST` deliberately reads as someone who has been burned by it in production. > **One pass is already optimal** > > The flag and the total come from the same scan, so even at 400k rows this is one sequential read with two accumulators. An index on `expires` cannot help: the denominator needs every row anyway. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/keys_that_never_die) - [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.