Smooth Latency

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

L5

Problem

Some of our API endpoints look like they are drifting slower as traffic grows, and we want the underlying trend rather than the per-call noise. For each endpoint, report at every call the average latency across that call and every earlier one on the same endpoint (a call made at the same moment counts as earlier when its call_id is lower), skipping any call with no recorded latency.

Noisy latency readings, smoothed into a trend you can trust.

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 is

Strip the SRE costume off and this is a per-endpoint cumulative average: at each call, the mean of that call and every earlier one for the same endpoint, in a single ordered pass. Anyone can reach for AVG. Two things separate the seniors. First, do you get there without a correlated subquery that re-averages every prior call per row (O(n^2), dead at two billion rows)? Second, do you nail the ordering? Order by call_time alone and tied timestamps advance in an arbitrary order, so your numbers are non-deterministic; lean on the ORDER BY default and you get RANGE, which lumps every call sharing a call_time into one step and makes the average jump in plateaus exactly where traffic bursts. The fix is a ROWS frame ordered by call_time then call_id, which also makes the naive correlated subquery (scoped with call_time <=) not just slow but wrong on ties.

Break down the requirements

Step 1: Filter nulls before windowing

The WHERE latency IS NOT NULL filter removes the ~0.1% of rows with missing latency before the window function runs. Filtering first is cheaper than letting the window absorb nulls.

Step 2: Partition by endpoint

PARTITION BY endpoint resets the running average for each endpoint independently. Without this, AVG accumulates across all endpoints and produces a meaningless global trend.

Step 3: Order, tiebreak, and frame

ORDER BY call_time, call_id fixes the sequence of calls: call_time gives chronological order, and call_id is the deterministic tiebreak so calls sharing a timestamp still advance one at a time. The frame ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW covers every call up to the current one, which is exactly the cumulative average definition.

The solution

SELECT
    endpoint,
    latency,
    AVG(latency) OVER (
        PARTITION BY endpoint
        ORDER BY call_time, call_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_avg
FROM api_calls
WHERE latency IS NOT NULL

Common follow-up questions

• Two calls to the same endpoint share an identical `call_time`. How does your window frame handle ties, and would RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW give a different result than ROWS? (Tests whether the candidate understands RANGE vs ROWS frame semantics and the need for a tiebreak when the ORDER BY key is non-unique.)
• If you changed the WHERE to allow NULLs and used AVG(latency) over the full table, how would NULLs affect the running average compared to a query that replaces NULLs with zero? (Tests knowledge of how NULL propagates through AVG vs COUNT.)
• How would you change this query to compute a 5-call rolling average instead of the running average from the start of history? (Tests ability to convert a running average into a bounded rolling window.)