Peak Company

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

L5

Asked in

Meta

Problem

The platform security team's capacity review keeps quoting peak token counts that nobody can reproduce, so for each token in api_tokens they want the most tokens that were live on any single day of that token's own active window, and the first date that peak was hit. A token is live from its issued date up to the day before it expires (a missing expiry never ends it), and a token that expires on or before the day it was issued was never live and is left out.

Every token lived among others. Find the moment the crowd was largest.

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 asking

Strip the API-token costume off and this is per-window peak load: over each token's own lifetime, what was the biggest crowd of credentials alive on a single day? Almost everyone reaches for one self-join that counts how many tokens overlap each token's window. That number is a lie: two tokens can both overlap your window without ever being alive on the same day, so the overlap count inflates the peak and has no date to report at all. Concurrency is a per-day fact, so you measure it day by day and only then take the max inside each window.

Breaking it down

Step 1: Define 'active on a day' once

Compute DATE(issued) and DATE(expires) once in token_windows. A window covers day d when start_day <= d and (end_day IS NULL or end_day > d). The comparison on end_day is strict because the expiry day is the first dead day, and the null branch keeps never-expiring tokens in the crowd.

Step 2: Collapse tokens that share a window

Tokens issued and expiring on the same days add to the crowd identically. window_sizes keeps one row per distinct window with a tokens weight, so the expensive day-by-window range join runs over windows instead of raw tokens, and day_active sums the weights rather than counting rows.

Step 3: Only issue days can hold the peak

The crowd is a step function: it rises on issue days and falls on expiry days. A new high can only land on an issue day, so the distinct start_day values in event_days are the only candidate days you need.

Step 4: Peak per token, first day on ties

token_days joins each token to the candidate days inside its own window, and MAX(active_count) OVER (PARTITION BY t.token_id) stamps the peak on every one of those rows. The final query keeps the rows that hit the peak and takes MIN(d). A token whose window is empty matches no day and drops out on its own.

The solution

WITH token_windows AS (
  SELECT token_id,
         DATE(issued) AS start_day,
         DATE(expires) AS end_day
  FROM api_tokens
),
window_sizes AS (
  SELECT start_day, end_day, COUNT(*) AS tokens
  FROM token_windows
  GROUP BY start_day, end_day
),
event_days AS (
  SELECT DISTINCT start_day AS d
  FROM token_windows
),
day_active AS (
  SELECT e.d AS d, SUM(w.tokens) AS active_count
  FROM event_days e
  JOIN window_sizes w
    ON w.start_day <= e.d
   AND (w.end_day IS NULL OR w.end_day > e.d)
  GROUP BY e.d
),
token_days AS (
  SELECT t.token_id, da.d, da.active_count,
         MAX(da.active_count) OVER (PARTITION BY t.token_id) AS peak_concurrent
  FROM token_windows t
  JOIN day_active da
    ON da.d >= t.start_day
   AND (t.end_day IS NULL OR da.d < t.end_day)
)
SELECT token_id, peak_concurrent, MIN(d) AS peak_date
FROM token_days
WHERE active_count = peak_concurrent
GROUP BY token_id, peak_concurrent

Count overlapping windows

Self-join api_tokens to itself on overlapping windows and COUNT(*) per token. One join, fast, wrong: it counts tokens that never shared a day, and there is no peak_date to report.

Max of daily crowds

Count the crowd on each issue day first, then take the max of those daily counts inside each token's window and the first day it was reached.

Common follow-up questions

• Rewrite `day_active` with the sweepline pattern. What does the SQL look like? (Tests scalability awareness: issue and expiry events with a cumulative sum replace the first range join.)
• What if a token can be revoked before its `expires` date? (Tests data-model awareness: the window ends at the earlier of `expires` and a revocation timestamp.)
• Why is it safe to evaluate concurrency only on distinct issue days? (Tests semantic precision: the crowd only rises on issue days, so any window maximum coincides with one of them.)