Latency Variance and Std Dev

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Vesta Innovations

Problem

For successful API calls (status 200), compute the mean latency, variance, and standard deviation.

How much does latency actually vary?

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 asking

This is a population-moments problem dressed up as a latency report. The mean is the free third; the skill is building the variance out of the only two averages you can always compute, AVG(latency) and AVG(latency * latency), because Var = E[X^2] - E[X]^2 needs nothing else. The trap is habit: reaching for a variance aggregate the engine does not give you, or dividing by N-1. The first gets you no number at all. The second lands about 89 above the expected 45,607.5, and the grade checks three decimals.

Step 1: Keep only status = 200

The sample rows already show 201 and 204, which look like success but are not the slice being measured. Filter on the exact code, once, so every moment comes from the same set of calls.

Step 2: Take both averages in one pass

AVG(latency) and AVG(latency * latency) sit side by side in one SELECT. Six status 200 rows have a NULL latency; both averages skip them, so both are over the same 512 calls and the identity stays honest.

Step 3: Subtract, then take the root

AVG(latency * latency) - AVG(latency) * AVG(latency) is the population variance: AVG already divides by N, so the divisor is decided for you. SQRT of that same expression is the standard deviation. The engine keeps one accumulator per distinct aggregate, so writing the expression twice costs no extra work, only care: the two copies must match exactly.

SELECT
  AVG(latency) AS mean_latency,
  AVG(latency * latency) - AVG(latency) * AVG(latency) AS variance_latency,
  SQRT(AVG(latency * latency) - AVG(latency) * AVG(latency)) AS stddev_latency
FROM api_calls
WHERE status = 200;

Three scalar subqueries

Each output column gets its own (SELECT ... FROM api_calls WHERE status = 200). That is three scans of the table and three copies of the filter. If one copy drifts, the stddev stops describing the same calls as the mean.

One SELECT, one filter

One WHERE status = 200 feeds every aggregate, so api_calls is read once and both averages accumulate in the same pass. The variance and the stddev are just arithmetic on those two numbers.

Common follow-up questions

Common follow-up questions

• Should this be population or sample variance? (Population (divide by N) treats the 200s as the whole world. Sample (N-1) treats them as a draw from a larger distribution. For a slice of production traffic you already hold in full, population is usually right.)
• How would you report variance per endpoint and still scan once? (Add `TRIM(endpoint)` to the select list and group by it; every average and the `SQRT` carry over row by row, still in one scan. The padded `endpoint` values are why the `TRIM` is there.)
• Latency is heavy-tailed. Is the mean even the right metric? (Usually not on its own. Tail SLOs read p95 or p99, and a few timeouts can inflate both the mean and the stddev without moving the typical call.)