Endpoint Latency Spread

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

City and County of San Francisco

Problem

Only a 2xx status in api_calls marks a call that succeeded: errors fail instantly or hang until a timeout, and redirects never exercise the endpoint's real work, so neither says how the service performs. From the successful calls, report each endpoint's slowest and fastest latency, the gap between them, and how many whole times slower its slowest call is than its fastest, as an integer with any fraction dropped (a 7.9x slowdown counts as 7).

Latency spread across endpoints.

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 really tests

This is a per-group spread calculation wearing an observability costume, and the real skill is getting the scope and the output type right: extremes per endpoint, over successful calls only, then a slowdown reported as a whole multiple. Everyone writes MAX(latency) and MIN(latency). Leave the error rows in and a timed-out 5xx becomes an endpoint's slowest call, so the gap measures the failure path. Divide the two extremes and stop there and you hand back 7.83 where the ask was 7: latencies are fractional, so the quotient is too. The conversion to an integer has to wrap the finished division.

Break down the requirements

Step 1: Scope to successful calls

Errors fail fast or hang to a timeout, so their latencies describe the failure path, not the service. Filter with status < 300 before aggregating, so the extremes come only from calls that did their job.

Step 2: Take the extremes once per endpoint

Collapse each endpoint to its MAX(latency) and MIN(latency) with GROUP BY endpoint. The gap and the multiple are plain arithmetic on those same two aggregates in the same SELECT, so the table is scanned once.

Step 3: Derive the gap and the whole multiple

The gap is MAX(latency) - MIN(latency). For the multiple, divide the two extremes first, then wrap the whole quotient in CAST(... AS INTEGER): the conversion drops the fraction, so a slowest call 7.9 times the fastest reports 7. Converting only one operand changes nothing, because the other one is still fractional.

The solution

SELECT endpoint,
       MAX(latency) - MIN(latency) AS latency_diff,
       CAST(MAX(latency) / MIN(latency) AS INTEGER) AS latency_ratio,
       MAX(latency) AS max_latency,
       MIN(latency) AS min_latency
FROM api_calls
WHERE status < 300
GROUP BY endpoint

Common follow-up questions

• How would you compute p95 minus p50 instead, to be robust to outliers? (Tests percentile functions such as `PERCENTILE_CONT`.)
• What if `min_latency` can be 0 for a cached response? (Tests guarding a division against a zero denominator.)
• How would you compute this spread over a rolling 1-hour window of `call_time`? (Tests time-bucketing and window framing.)