# API Token Churn Rate > Tokens come and go. What's the turnover? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem The developer experience team is measuring token churn for the platform health dashboard. Compute the fraction of all issued API tokens whose expiration date has already passed, treating tokens with no expiration as still active. Express the result as a decimal ratio, not a percentage. ## Worked solution and explanation ### Why this problem exists in real interviews Developer-experience teams hand you this to see whether you stop and define the cohort before typing. A `NULL` in `expires` means never-expires, not unknown. They want to hear you say that out loud, decide it belongs in the denominator but not the numerator, and confirm with the interviewer before you commit to a ratio. --- ### Break down the requirements #### Step 1: Pin the cohort Denominator is every row in `api_tokens`. No `WHERE` clause. Never-expires tokens stay in the population because they are still alive. #### Step 2: Define churned A token is churned when `expires IS NOT NULL AND expires < date('now')`. The `IS NOT NULL` guard keeps three-valued logic from silently dropping never-expires tokens out of the numerator math. #### Step 3: Count with CASE Use `SUM(CASE WHEN ... THEN 1 ELSE 0 END)` over the whole table. One pass, no subquery. `COUNT(*)` gives the denominator in the same pass. #### Step 4: Force real division `CAST(... AS REAL)` on the numerator. Without the cast, SQLite returns `0` for every churn rate under 100 percent because both sides are integers. --- ### The solution **CHURN RATE AS DECIMAL** ```sql SELECT CAST(SUM(CASE WHEN expires IS NOT NULL AND expires < date('now') THEN 1 ELSE 0 END) AS REAL) / COUNT(*) AS churn_rate FROM api_tokens ``` > **Cost Analysis** > > Single full scan over 300k rows. No grouping, no sort, no join. Sub-second on any laptop. An index on `expires` does not help: you still need `COUNT(*)` on the full table for the denominator. > **Interviewers Watch For** > > Before writing, ask: should never-expires tokens count as churned, ignored, or active? Stating the answer (active, so denominator yes, numerator no) earns the point. Skipping the question and assuming either way gets flagged. > **Common Pitfall** > > Dropping the `CAST` returns integer division: `0` for any churn under 100 percent. Second pitfall: writing `WHERE expires < date('now')` filters never-expires rows out of the denominator and inflates the rate. --- ### COMMON FOLLOW-UP QUESTIONS ## Common follow-up questions - How would you split churn by `scope`? _(Probes whether you reach for `GROUP BY scope` 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 join or window over `expires` buckets.)_ - What changes if `expires` is a timestamp instead of a date? _(Checks whether you swap `date('now')` for `datetime('now')` and avoid timezone drift.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/api_token_churn_rate) - [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.