# Latency Variance and Std Dev

> How much does latency actually vary?

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

Domain: SQL · Difficulty: hard · Seniority: L3 · Asked in: Vesta Innovations

## Problem

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

## Tables

### api_calls

- call_id INTEGER
- endpoint TEXT
- method TEXT
- status INTEGER
- latency REAL
- user_id INTEGER
- call_time TEXT
- err_msg TEXT

Sample rows (10 of 670):

| call_id | endpoint | method | status | latency | user_id | call_time | err_msg |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 8079 | /api/v1/orders | POST | 200 | 4.2 | 100 | 2026-02-02 01:07:00 | NULL |
| 8158 | /api/v2/products | PUT | 200 | 7.9 | 197 | 2026-03-03 02:14:00 | NULL |
| 8237 | /api/v1/search | DELETE | 201 | 11.6 | 294 | 2026-04-04 03:21:00 | NULL |
| 8316 | /api/v1/auth/login | PATCH | 204 | 15.3 | 391 | 2026-05-05 04:28:00 | NULL |
| 8395 | /api/v1/auth/logout | get | 301 | 19 | NULL | 2026-06-06 05:35:00 | NULL |
| 8474 | /api/v2/analytics | post | 400 | 22.7 | 585 | 2026-07-07 06:42:00 | Not found |
| 8553 | /api/v1/payments | GET | 401 | 26.4 | 682 | 2026-08-08 07:49:00 | Unauthorized |
| 8632 | /api/v1/users/ | POST | 403 | 30.1 | 779 | 2026-09-09 08:56:00 | Rate limit exceeded |
| 8711 | /api/v2/products/ | PUT | 404 | 33.8 | 876 | 2026-01-10 09:03:00 | Internal server error |
| 8790 | /api/v1/users | DELETE | 500 | 37.5 | NULL | 2026-02-11 10:10:00 | Bad gateway |

## Expected output

First 1 of 1 rows:

| mean_latency | variance_latency | stddev_latency |
| --- | --- | --- |
| 226.2494140625 | 45607.50585903168 | 213.5591390201592 |

## Worked solution and explanation

### What this is really asking

`api_calls` has 400M rows partitioned by `call_time`, and only `status = 200` counts. Three stats on `latency` from one filtered slice: mean, variance via `E[X^2] - E[X]^2`, and `SQRT(variance)`.

---

### Break down the requirements

#### Step 1: Filter once, conceptually

Every aggregate runs against `WHERE status = 200`. Pruning by `status` happens before the average, so the same filtered set feeds all three numbers.

#### Step 2: Variance as E[X^2\] minus E[X\]^2

`AVG(latency * latency) - AVG(latency) * AVG(latency)`. This is population variance, one pass over the data, no need to materialize the mean first.

#### Step 3: Stddev is SQRT of variance

Engines expose `STDDEV_POP` and `VAR_POP` directly. Building it by hand shows you understand the identity, but in production prefer the built-ins for numerical stability.

---

### The solution

**MEAN, VARIANCE, STDDEV ON STATUS 200**

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

> **Cost Analysis**
>
> Three scalar subqueries means three scans of 400M rows. Rewrite as one `SELECT AVG(latency), VAR_POP(latency), STDDEV_POP(latency) FROM api_calls WHERE status = 200` and it collapses to one pass.

> **Interviewers Watch For**
>
> Do you spot the three scans? Do you know population vs sample variance (`VAR_POP` vs `VAR_SAMP`, divide by N vs N-1)? Do you flag that `latency * latency` on a large INTEGER column can overflow before the average?

> **Common Pitfall**
>
> `E[X^2] - E[X]^2` is mathematically clean but numerically fragile when latencies are large and variance is small relative to the mean. Two huge numbers subtract to a tiny one, eating precision. `STDDEV_POP` uses Welford and stays stable.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- Should this be population or sample variance? _(Population (divide by N) treats the 200-rows as the whole world. Sample (N-1) treats them as a draw from a larger distribution. For monitoring slices of production traffic, population is usually correct.)_
- How would you report variance per endpoint without three scans each? _(`SELECT endpoint, AVG(latency), VAR_POP(latency), STDDEV_POP(latency) FROM api_calls WHERE status = 200 GROUP BY endpoint`. One pass, one shuffle, every endpoint.)_
- Latency is heavy-tailed. Is mean even the right metric? _(Probably not. Tail-sensitive SLOs use p95 or p99 via `PERCENTILE_CONT` or `APPROX_QUANTILES`. Mean and stddev mislead when a few timeouts dominate the distribution.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/latency_variance_and_std_dev)
- [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.