The Shape of Waiting

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

Vesta Innovations

Problem

We're running a latency review across the API and want to see how each endpoint's response times spread out rather than collapse into a single average. Split every endpoint's calls into four equal-sized tiers from fastest to slowest, and for each endpoint-tier report the fastest, slowest, and average latency, skipping any call that has no recorded latency. Lay the report out one endpoint at a time in alphabetical order, and within each endpoint walk its tiers from the fastest group down to the slowest.

Every request waits its turn, and some wait far longer than the rest. Map where the patience runs out.

Tables

api_calls

Columns: call_id INTEGER, endpoint TEXT, method TEXT, status INTEGER, latency REAL, user_id INTEGER, call_time TEXT, err_msg TEXT

Expected output

Worked solution and explanation

What this is really testing

This is per-endpoint equal-count bucketing wearing a performance-review costume. 'Four equal-sized tiers' means each tier holds a quarter of that endpoint's calls, not a quarter of its latency range. Latency is skewed, so slicing MAX(latency) - MIN(latency) into four equal widths dumps most calls into the fastest band and leaves the slow bands nearly empty. Anyone can reach for NTILE. The trap is where it goes: the tiering has to restart per endpoint, run over non-null latencies only, and finish before you aggregate. Bucket the whole table at once, or let NULL latencies into the sort, and every tier boundary shifts underneath you.

Building it

Step 1: Tier each endpoint's calls

In the tiered CTE, order each endpoint's calls by latency and label them with NTILE(4) OVER (PARTITION BY endpoint ORDER BY latency). The PARTITION BY endpoint is what makes tier 1 the fastest quarter of that endpoint, not of the whole API. Put WHERE latency IS NOT NULL here, so untimed calls never take a slot in the ordering.

Step 2: Summarize each tier

Outside the CTE, GROUP BY endpoint, bucket and compute MIN(latency), MAX(latency) and AVG(latency). You are now at the endpoint-tier grain, coarser than the per-row grain the window produced. MIN and MAX are the tier's edges; AVG is its center.

Step 3: Order the output

ORDER BY endpoint, bucket gives the sequence the preview shows: endpoints alphabetically, then tiers 1 through 4. That pair is unique per output row, so no further key is needed. The padded ' /api/v2/products' leads because of its leading space.

The solution

WITH tiered AS (
    SELECT endpoint,
           latency,
           NTILE(4) OVER (PARTITION BY endpoint ORDER BY latency) AS bucket
    FROM api_calls
    WHERE latency IS NOT NULL
)
SELECT endpoint,
       bucket,
       MIN(latency) AS min_latency,
       MAX(latency) AS max_latency,
       AVG(latency) AS avg_latency
FROM tiered
GROUP BY endpoint, bucket
ORDER BY endpoint, bucket

Global tiers (wrong grain)

NTILE(4) OVER (ORDER BY latency) with no partition tiers the entire table at once. A slow endpoint's fastest calls can land in tier 3 or 4 because they are slow relative to the whole API. The tiers no longer describe any single endpoint.

Per-endpoint tiers (correct)

NTILE(4) OVER (PARTITION BY endpoint ORDER BY latency) restarts at every endpoint, so tier 1 is always that endpoint's own fastest quarter. That reset is what makes the endpoints comparable.

Common follow-up questions

• If `api_calls` were sharded across many databases by `user_id`, how would you compute per-endpoint tiers without pulling every row to one node? (Tests whether the candidate knows equal-count bucketing does not split cleanly into scatter-gather.)
• The team now wants the p50, p95 and p99 latency per endpoint instead of tier summaries. How does your query change? (Tests percentile semantics versus equal-count bucketing.)
• If this fed a dashboard refreshing every five minutes, could you make it incremental, and where does that break down for quantiles? (Tests awareness of why quantiles resist incremental computation.)
• One endpoint carries 90% of all traffic. What does that do to the per-partition sort, and how would you mitigate it? (Tests data-skew reasoning on sort-based window operations.)