7-Day Token Retention

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

L5

Asked in

Meta

Problem

List the first seven dates, earliest first, on which an active write token carrying traffic was issued in api_tokens, with how many owners received one that day and how many of those same owners still held one seven days later. Only the scope 'write' counts (not 'write:orders'), status is active in any letter case, and carrying traffic means at least one request. A token is held on a day if it was issued by then and expires that day or later, and one with no expiration never expires.

Premium tokens, day by day.

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 validity-interval test on a self-joined cohort, dressed up as a retention curve. Counting write-token owners per issuance date is the easy half. The trap is the day-7 side: it re-reads the same population, and one owner can hold several qualifying tokens on that day. On this data the sixth cohort's owner holds two a week later, the token issued that day and a never-expiring one from the year before, so COUNT(owner_id) reports 2 owners on both sides where there is 1. Miss the casing in status, the NULL in expires or the outer join, and rows come out wrong or vanish.

Four moves

Step 1: Filter once into active_write_tokens

Put scope = 'write', LOWER(status) = 'active' and requests > 0 in one CTE and read it on both sides, so day 0 and day 7 judge tokens by the same rule. The scope is an exact match: 'write:orders' is a different grant. Compute day7_mark there too, so the shifted date is written once.

Step 2: Join the CTE to itself on owner_id

The left copy day0 is the cohort; the right copy day7 is every qualifying token the same owner might still hold. The key is the owner, never the token: retention means the owner kept access, not that the original token survived.

Step 3: Treat each token as an interval

A token is held on day7_mark when day7.issued <= day0.day7_mark and it expires that day or later. A missing expiry is open-ended, so the test is day7.expires IS NULL OR day7.expires >= day0.day7_mark. Without the IS NULL branch the comparison is unknown and never-expiring tokens fall out.

Step 4: Keep empty cohorts, count owners

Every day-7 condition lives in the ON of a LEFT JOIN, so the second cohort, whose owner holds nothing a week later, stays with a NULL partner. COUNT(DISTINCT day7.owner_id) skips that NULL and reports 0, and COUNT(DISTINCT day0.owner_id) undoes the fan-out. Then ORDER BY the_date and LIMIT 7.

WITH active_write_tokens AS (
  SELECT owner_id,
         issued,
         expires,
         DATE(issued, '+7 days') AS day7_mark
  FROM api_tokens
  WHERE scope = 'write'
    AND LOWER(status) = 'active'
    AND requests > 0
)
SELECT day0.issued AS the_date,
       COUNT(DISTINCT day0.owner_id) AS active_day0,
       COUNT(DISTINCT day7.owner_id) AS active_day7
FROM active_write_tokens AS day0
LEFT JOIN active_write_tokens AS day7
  ON day7.owner_id = day0.owner_id
 AND day7.issued <= day0.day7_mark
 AND (day7.expires IS NULL OR day7.expires >= day0.day7_mark)
GROUP BY day0.issued
ORDER BY the_date
LIMIT 7;

Count joined rows

COUNT(day0.owner_id) and COUNT(day7.owner_id) count rows the join produced. The sixth cohort's owner matches two tokens on day 7, so that row reads 2 and 2.

Count owners

COUNT(DISTINCT ...) collapses the fan-out back to people: the same row reads 1 and 1, and the unretained cohort still reads 0 because its NULL partner is skipped.

Common follow-up questions

• Report day-1, day-7 and day-30 retention side by side. (Tests generalizing the `+7 days` offset into several horizons in one pass.)
• If `requests` can be `NULL`, how does your traffic filter change? (Probes whether they see that `requests > 0` already rejects `NULL`, and whether that is the intent.)
• Express day-7 retention as a percentage of day-0 owners. (Pushes toward `active_day0` as the denominator of a true retention rate.)