# Peak Company

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

Canonical URL: <https://datadriven.io/problems/peak_company>

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

For every API token ever issued, find the largest number of tokens that were active at the same time during this token's own active window, and the earliest date that peak was reached. A token counts as active from its issued date through the day before it expires, and a token with no expiration is treated as still active.

## Worked solution and explanation

### What this problem is really asking

Strip the API-token costume off and this is a peak-load problem: over each token's own active window, when were the most credentials valid at once? The move that separates candidates is realizing you do not measure concurrency once, at the token's issue date. You measure it across the whole window and take the maximum, because the fleet keeps growing after most tokens are issued, so the peak almost always lands on a later day than the issue date. Count only at the issue date and you report the concurrency the token was born into, not the peak it lived through: every early token comes back far too low.

---

### Breaking it down

#### Step 1: Define 'active on a day' precisely

A token B is active on day D when DATE(B.issued) <= D and (B.expires IS NULL OR DATE(B.expires) > D). The inequality on expires is strict: the prompt says active through the day before expiration, so a token expiring on D is already gone that day. The IS NULL branch keeps never-expiring tokens in the count; drop it and the comparison silently discards every one of them.

#### Step 2: Only issue dates can hold the peak

Concurrency is a step function: it rises by one on every issue date and falls on every expiry date. It can only reach a new high on an issue date, so you never have to walk the calendar day by day. Collect the distinct issue dates as your candidate days and evaluate concurrency only there. Within any token's window, the maximum is guaranteed to sit on one of those days.

#### Step 3: Peak over the window, earliest date on ties

For each token, keep the candidate days that fall inside its own active window, take MAX(active_count) as the peak, then take MIN of the days that tie for that peak to get the first date it was reached. That yields exactly one row per token.

---

### The solution

**Count active per event day, then take each token's window max**

```sql
WITH event_days AS (
  SELECT DISTINCT DATE(issued) AS d
  FROM api_tokens
),
day_active AS (
  SELECT e.d AS d, COUNT(*) AS active_count
  FROM event_days e
  JOIN api_tokens b
    ON DATE(b.issued) <= e.d
   AND (b.expires IS NULL OR DATE(b.expires) > e.d)
  GROUP BY e.d
),
token_days AS (
  SELECT t.token_id, da.d AS d, da.active_count
  FROM api_tokens t
  JOIN day_active da
    ON da.d >= DATE(t.issued)
   AND (t.expires IS NULL OR da.d < DATE(t.expires))
),
ranked AS (
  SELECT token_id, d, active_count,
         MAX(active_count) OVER (PARTITION BY token_id) AS peak_concurrent
  FROM token_days
)
SELECT token_id, peak_concurrent, MIN(d) AS peak_date
FROM ranked
WHERE active_count = peak_concurrent
GROUP BY token_id, peak_concurrent
ORDER BY token_id
```

> **The insight that cracks it**
>
> Concurrency only climbs on an issue date and only drops on an expiry date. That single observation collapses an unbounded per-day scan into a join over the handful of distinct issue dates: the peak within any window has to coincide with one of them, so evaluating those days is both correct and cheap.

> **Interviewers watch for**
>
> Whether the peak is taken across the whole window or lazily read off the issue date; whether expires uses a strict boundary (the day before expiration); whether the NULL expires branch is present; and whether the first-peak-date tie-break returns the earliest date rather than an arbitrary one.

> **Common pitfall**
>
> Two classic misses. Counting concurrency only at each token's issue date, which reports the wrong number for every token whose fleet kept growing after it was issued. And writing DATE(expires) >= D instead of strict greater-than, which keeps a token alive on its expiry day and overcounts by one.

> **Scaling it**
>
> The join over event days is fine for a modest fleet but grows quadratically as issue dates and tokens pile up. At real scale the production answer is a sweepline: emit +1 at each issued date and a negative one at each expiry date, sort by date, carry a running SUM, and the peak per window falls out of one ordered pass instead of a self-join.

**Count at the issue date**

One self-join, concurrency measured on a.issued only. Fast, simple, and wrong: peak_date is always the issue date and early tokens report the small crowd they launched into.

**Max across the window**

Evaluate concurrency on every issue-event day inside the token's window and keep the maximum. peak_date becomes a real later date and the number reflects the busiest moment the token actually lived through.

---

## Common follow-up questions

- Rewrite this with the sweepline pattern. What does the SQL look like? _(Tests scalability awareness. UNION ALL of (issued, +1) and (expires, negative one) events with a running SUM window is the standard production technique.)_
- What if a token can be revoked before its expires date? _(Tests data-model awareness. The candidate should ask about a revoked_at column and adjust the active predicate to end the window at the earliest of expires and revoked_at.)_
- Why is it safe to only evaluate concurrency on distinct issue dates rather than every day in the window? _(Tests semantic precision. Concurrency only rises on issue dates, so any window maximum must coincide with one of them; scanning every calendar day is unnecessary.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/peak_company)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.