API Token Churn Rate

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

Lyft

Problem

Compute the churn rate of the issued tokens in api_tokens as a decimal fraction between 0 and 1, for the platform health dashboard. A token has churned once its expiration date is in the past; a token with no expiration date never lapses, so it stays active while still counting as issued.

Tokens come and go. What's the turnover?

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 really is

This is a three-valued-logic problem dressed up as a dashboard metric. The ratio is one line; what separates candidates is the 32 tokens with a NULL in expires. Define churn as everything that is not still live and a missing expiry makes the live test unknown, the CASE falls through to its ELSE, and every never-expiring token lands in the churned pile: the rate jumps from 0.42 to 0.58. The status column is the second bait, and it is wrong too.

Step 1: Keep every issued token in the denominator

COUNT(*) over api_tokens is the cohort: all 200 issued tokens, never-expiring ones included. Filtering to expires IS NOT NULL first would shrink it to 168 and inflate the rate.

Step 2: Define live before churned

A token is live when it has no expiry at all or its expires is today or later. Churned is everything else, which is why the churn flag is the ELSE 1 branch rather than a condition of its own.

Step 3: Catch the missing expiry by name

expires >= CURRENT_DATE is unknown, not false, when expires is NULL, and an unknown WHEN drops to ELSE. The expires IS NULL OR arm is what keeps those 32 tokens live.

Step 4: Force real division

SUM(...) and COUNT(*) are both integers, so the engine divides integers and returns 0. Casting the numerator with CAST(... AS REAL) makes the ratio a decimal.

SELECT
  CAST(SUM(CASE
             WHEN expires IS NULL OR expires >= CURRENT_DATE THEN 0
             ELSE 1
           END) AS REAL) / COUNT(*) AS churn_rate
FROM api_tokens

Flag churn directly

CASE WHEN expires < CURRENT_DATE THEN 1 ELSE 0 END survives without a guard: the NULL comparison is unknown, falls to ELSE 0, and never-expiring tokens are never churned by accident.

Flag churn as not live

CASE WHEN expires >= CURRENT_DATE THEN 0 ELSE 1 END sends the same unknown to ELSE 1. Here the expires IS NULL arm is mandatory; drop it and the rate reads 0.58.

Common follow-up questions

• How would you split churn by `scope`? (Probes whether you add a grouping while keeping the same conditional-sum shape.)
• What if the team wants churn for tokens issued in the last 90 days only? (Tests cohort filtering on `issued` without breaking the never-expires logic.)
• How would you express this as a 30-day rolling churn rate? (Pushes you toward a date series or buckets over `expires`.)
• What changes if `expires` is a timestamp instead of a date? (Checks whether a token expiring later today is still treated as live.)