Time Served

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

Yammer

Problem

Security is profiling the API tokens still in service, split out by permission scope. A token counts as in service when it has no expiration date, or its expiration is today or later. Within each scope, report the day span between its oldest and newest tokens, along with how many tokens were issued on that oldest day and how many on that newest day, widest span first.

The tokens still in service each carry a history; measure how far it stretches, scope by scope.

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 tests

Beneath the security framing this is a per-scope minimum and maximum that still needs row-level detail at both ends, sitting behind a NULL-aware filter. Two traps decide it. First, 'in service' must keep tokens that never expire. A bare expires >= DATE('now') drops them, because comparing anything to NULL yields NULL, not true, and 32 of the 200 tokens here have no expiration. Second, one GROUP BY scope with MIN(issued) and MAX(issued) collapses the rows. It tells you where each scope starts and ends, but not how many tokens landed on those boundary days. Anyone can write the CTEs. What separates candidates is going back to the rows for those counts and keeping each count inside its own scope.

Working through it

Step 1: Define the in-service population

Build active_tokens with WHERE expires IS NULL OR expires >= DATE('now'). The IS NULL branch keeps tokens that never expire, and the comparison keeps those expiring today or later. Ignore status: its 'active' and 'revoked' labels, in mixed casing, are not what the question means by in service.

Step 2: Take each scope's issue-date extremes

Group active_tokens by scope with MIN(issued) and MAX(issued). That is the grain of the answer, one row per scope. It is also the point where the individual tokens disappear, which is why the last step goes back for them.

Step 3: Turn the two dates into a whole-day span

CAST(JULIANDAY(max_issued) - JULIANDAY(min_issued) AS INTEGER) gives the gap in days. The dates are 'YYYY-MM-DD' text with no time part, so the difference is already whole and the cast only fixes the type. On Postgres the same idea is max_issued - min_issued on two dates.

Step 4: Count the boundary days, scoped

For each bounds row, one scalar subquery counts active_tokens rows where a.issued = b.min_issued, and another counts those where a.issued = b.max_issued. Both carry a.scope = b.scope, so each count stays inside the scope on that row. Sort widest span first. Scopes that share a span may come in either order, because the span is the only thing the question sorts by.

The solution

WITH active_tokens AS (
    SELECT scope, issued
    FROM api_tokens
    WHERE expires IS NULL OR expires >= DATE('now')
),
bounds AS (
    SELECT scope,
           MIN(issued) AS min_issued,
           MAX(issued) AS max_issued
    FROM active_tokens
    GROUP BY scope
)
SELECT
    b.scope,
    CAST(JULIANDAY(b.max_issued) - JULIANDAY(b.min_issued) AS INTEGER) AS day_spread,
    (SELECT COUNT(*) FROM active_tokens a WHERE a.scope = b.scope AND a.issued = b.min_issued) AS tokens_at_earliest,
    (SELECT COUNT(*) FROM active_tokens a WHERE a.scope = b.scope AND a.issued = b.max_issued) AS tokens_at_latest
FROM bounds b
ORDER BY day_spread DESC

Common follow-up questions

• If `issued` were a timestamp instead of a date, what changes about the day span and the boundary-day counts? (Tests truncating timestamps before comparing and differencing them.)
• How would you produce the same four columns in one pass, using per-scope window minimums and maximums and a conditional count, without the scalar subqueries? (Tests collapsing the CTEs and subqueries into a single scan.)
• How would the query change if security wanted a single global spread across all in-service tokens instead of one row per scope? (Tests reasoning about the global rollup versus the per-scope grain.)
• If `api_tokens` grew to 100M rows, what index would let the in-service filter and the per-scope bounds avoid a full scan? (Tests partial-index design for a selective filter at scale.)