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-group min/max with tie counts, and two traps decide it. First, 'in service' is a NULL-aware predicate: a token with no expiration date never expires, so a plain expires >= today silently drops it because comparing anything to NULL yields NULL, not true. Second, the counts at each end have to stay inside their own scope. A tie count that forgets to correlate on scope will sweep in tokens from every scope and inflate both boundary columns. Anyone can write the CTEs; the separation is whether the exclusion of NULLs and the scoping of the tie counts are both correct.

Working through it

Step 1: Define the active population

Build active_tokens with WHERE expires IS NULL OR expires >= DATE('now'). The IS NULL branch keeps tokens that never expire; the >= today branch keeps tokens whose expiration is today or in the future. Drop the IS NULL branch and every never-expiring token vanishes from the population.

Step 2: Per-scope issue-date extremes

Group active_tokens by scope and take MIN(issued) and MAX(issued). Each scope gets one row carrying its earliest and latest issue dates. This is the grain of the final answer: one row per scope.

Step 3: Day span between the extremes

Convert the two dates to a whole-day gap: CAST(JULIANDAY(max_issued) - JULIANDAY(min_issued) AS INTEGER). JULIANDAY handles the calendar math and the CAST drops the fractional part. On Postgres the same idea is (max_issued - min_issued) cast to integer days.

Step 4: Count ties at each boundary, scoped

For each scope row, run a scalar subquery over active_tokens counting rows where issued = min_issued, and another where issued = max_issued. The key detail: both subqueries carry a.scope = b.scope so the count stays inside the current scope. Project scope, day_spread, tokens_at_earliest, tokens_at_latest, widest span first.

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, b.scope

Common follow-up questions

• If issued were a TIMESTAMP instead of a DATE, what changes about the day span calculation? (Tests handling timestamps and timezones in date arithmetic.)
• How would you produce the same four columns using MIN, MAX, and COUNT with FILTER in a single grouped SELECT, without the scalar subqueries? (Tests collapsing the CTEs and subqueries into one grouped pass.)
• How would the query change if security wanted a single global spread across all active 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 active filter and the per-scope bounds avoid a full scan? (Tests partial-index design for selective filters at scale.)