# Token Churn Rate > Tokens come and go. How fast is the revolving door? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem Compute churn for API tokens in September 2026. Churn rate is the difference between tokens active on September 1 and tokens active on September 30, divided by the count on September 1. A token is active if it was issued on or before the target date and either has no expiration or expires on or after that date. Return a single churn rate. ## Worked solution and explanation ### Why this problem exists in real interviews Platform teams need point-in-time active counts to track API token health, and the interviewer wants to see whether you can express 'active on date X' as a single boolean predicate, compute two such snapshots in one pass, and divide them safely as floats. --- ### Break down the requirements #### Step 1: Define 'active on date X' as a single predicate A token is active on a date when issued <= X AND (expires IS NULL OR expires >= X). The NULL branch matters: a token with no expiration is active forever after issuance. Wrap that predicate inside SUM(CASE WHEN ... THEN 1 ELSE 0 END) for each snapshot date. #### Step 2: Compute both snapshots in one scan Two SUM(CASE) expressions in the same SELECT scan api_tokens once and produce both the September 1 count and the September 30 count. Subtracting inside the same SELECT avoids a self-join. #### Step 3: Force DOUBLE division for the rate CAST the numerator and the denominator to DOUBLE before dividing; otherwise SQLite does integer division and you get 0 for almost any churn rate below 100 percent. The denominator is the September 1 count, not the average. --- ### The solution **Two snapshots, one scan, DOUBLE-cast division** ```sql SELECT CAST(SUM(CASE WHEN issued <= '2026-09-01' AND (expires IS NULL OR expires >= '2026-09-01') THEN 1 ELSE 0 END) - SUM(CASE WHEN issued <= '2026-09-30' AND (expires IS NULL OR expires >= '2026-09-30') THEN 1 ELSE 0 END) AS DOUBLE) / CAST(SUM(CASE WHEN issued <= '2026-09-01' AND (expires IS NULL OR expires >= '2026-09-01') THEN 1 ELSE 0 END) AS DOUBLE) AS churn_rate FROM api_tokens ``` > **Cost Analysis** > > api_tokens has 400k rows; this is a single full scan with three SUM(CASE) accumulators, so it runs in milliseconds. An index on (issued, expires) doesn't help because both predicates use ranges on different columns; the scan is already optimal. > **Interviewers Watch For** > > Did you handle expires IS NULL as 'still active', cast to DOUBLE before dividing, and use the September 1 count as the denominator (not September 30, not the average)? Candidates often miss the NULL branch and undercount perpetual tokens. > **Common Pitfall** > > The exact dates matter: this query hardcodes 2026-09-01 and 2026-09-30. If the interviewer changes the year or asks for end-of-month robustness (some months have 31 days), you need date arithmetic, not a hardcoded literal. Also: if zero tokens were active on September 1, the rate is undefined and you should guard with NULLIF. --- ## Common follow-up questions - How would you generalize to any month? _(Parameterize the start and end dates, or compute end = date(start, 'start of month', '+1 month', '-1 day') so it works for any 28/30/31 day month.)_ - What if you needed weekly churn instead of monthly? _(Same shape but the snapshot dates step by 7 days. For a time series of weekly churn, generate a calendar CTE and CROSS JOIN to api_tokens.)_ - How does this differ from cohort retention? _(Churn here is a stock metric (count on date X minus count on date Y). Cohort retention is a flow metric on a fixed cohort over time. Don't confuse 'tokens active on date X' with 'tokens issued in month X'.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/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.