Longest Gap Between Token Events

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

Yelp

Problem

The security team is auditing credentials that rotate on a fixed expiry. Looking only at tokens that carry an expiration date, find the longest stretch in days between consecutive issuances and the longest stretch between consecutive expirations. Return both maximum gaps.

The longest gap between token events.

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

This is a sub-population gap analysis wearing a key-rotation costume. The real skill: restrict to the tokens that actually expire, then measure the longest run between consecutive dates over two independent orderings of that SAME filtered set. Everyone remembers to drop the null expirations from the expiration timeline, because a NULL date is obviously junk there. The trap is the issuance timeline: leave every token in it and a never-expiring token lands inside the true longest issuance gap and splits it. And because a NULL sorts to one end and MAX quietly skips it, nothing errors. The longest issuance gap just comes back as 52 days instead of 55, a smaller, wrong answer that looks perfectly plausible.

Break it down

Step 1: Define the population once

Build the audit universe in one CTE: SELECT ... FROM api_tokens WHERE expires IS NOT NULL. This is the whole point of the problem. Both timelines read from this filtered set, so a never-expiring token cannot sneak into either one.

Step 2: Issuance gaps with LAG

Over that population, order by issued with no partitioning and compute JULIANDAY(issued) - JULIANDAY(LAG(issued) OVER (ORDER BY issued)). Each row now carries the days since the previous issuance among expiring tokens.

Step 3: Expiration gaps with LAG

Over the same population, order by expires and apply the same LAG trick. Because the population is already filtered, every row here is a real expiration event.

Step 4: Max each stream into one row

MAX(gap) over each stream is the longest gap; the first row of each stream has a NULL gap that MAX ignores. Emit one row with two scalar subqueries, one per stream.

The solution

WITH expiring_tokens AS (
    SELECT issued, expires
    FROM api_tokens
    WHERE expires IS NOT NULL
),
issue_gaps AS (
    SELECT JULIANDAY(issued)
           - JULIANDAY(LAG(issued) OVER (ORDER BY issued)) AS gap
    FROM expiring_tokens
),
expire_gaps AS (
    SELECT JULIANDAY(expires)
           - JULIANDAY(LAG(expires) OVER (ORDER BY expires)) AS gap
    FROM expiring_tokens
)
SELECT (SELECT MAX(gap) FROM issue_gaps)  AS max_issue_gap_days,
       (SELECT MAX(gap) FROM expire_gaps) AS max_expire_gap_days

Common follow-up questions

• What if you wanted the longest gap per owner rather than across the whole fleet? (Add `PARTITION BY owner_id` to each window and group the final `MAX` by `owner_id`.)
• How would you return the actual dates that bookended the longest gap? (Keep `issued` and `LAG(issued)` in the CTE, then order by `gap DESC LIMIT 1` to grab the bookend dates alongside the max.)
• How would you convince a reviewer the null-expiry exclusion actually changed the issuance answer? (The exclusion only matters when a null-expiry token's issue date sits inside a candidate gap; with dense data it rarely moves the answer, but it is still the correct scoping.)