# Time Served > The tokens still in service each carry a history; measure how far it stretches, scope by scope. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L4 ## 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. ## 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. > **Trick to solving** > > Filter to the active population first, then take per-scope MIN and MAX of issued, then count how many rows in that same scope sit on each boundary date. > > 1. active_tokens: keep rows where expires IS NULL OR expires >= today > 2. bounds: MIN(issued), MAX(issued) grouped by scope > 3. Outer: two scalar subqueries, each correlated on scope, count the ties at each end --- ### 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 **NULL-aware filter, per-scope bounds, scoped tie counts** ```sql 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 ``` > **Time and space complexity** > > Time: one O(n) scan over api_tokens (500K rows) applies the active filter and the grouped bounds. scope has only about 10 distinct values, so the group is tiny. The two correlated tie-count subqueries each probe the active set per scope; on a partial index keyed by (scope, issued) they collapse to short range probes rather than repeated full scans. > > Space: O(active) for the materialized active set plus one small bounds row per scope. > **Interviewers watch for** > > Strong candidates flag the NULL trap before writing anything: expires >= today alone drops never-expiring tokens because a comparison against NULL is NULL, so they reach for expires IS NULL OR expires >= today immediately. The second tell is whether their tie counts are correlated on scope; an uncorrelated COUNT is the quiet bug that passes small samples and breaks in production. > **Common pitfall** > > Counting ties without the a.scope = b.scope correlation. The subquery then counts every token issued on that date across all scopes, so tokens_at_earliest and tokens_at_latest balloon and no longer describe the scope on that row. Keep the tie count scoped to the same partition the min/max came from. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/time_served) - [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.