# Endpoint Latency Spread

> Latency spread across endpoints.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

Our API monitoring tracks latency per endpoint. For each endpoint, surface the gap between the highest and lowest observed latency, the ratio of highest to lowest, and both extreme values. Exclude any entries with zero latency, and rank by the ratio from highest to lowest.

## Worked solution and explanation

### Why this problem exists in real interviews

Latency spread (max minus min) is a key observability metric. This tests per-endpoint aggregation and derived metric computation.

---

### Break down the requirements

#### Step 1: Aggregate latency per endpoint

`GROUP BY endpoint` with `MAX(latency)` and `MIN(latency)` for each.

#### Step 2: Calculate the spread

`MAX(latency) - MIN(latency)` in the same SELECT measures how variable each endpoint is.

#### Step 3: Order by spread

Sort descending so the most variable endpoints appear first.

---

### The solution

**Per-endpoint latency spread via aggregation**

```sql
SELECT endpoint,
       MIN(latency) AS min_latency,
       MAX(latency) AS max_latency,
       MAX(latency) - MIN(latency) AS latency_spread
FROM api_calls
GROUP BY endpoint
ORDER BY latency_spread DESC
```

> **Cost Analysis**
>
> Single pass with hash aggregation. A composite index on `(endpoint, latency)` enables efficient per-group min/max.

> **Interviewers Watch For**
>
> Strong candidates mention that min/max spread is sensitive to outliers and suggest percentile-based spread as a more robust alternative.

> **Common Pitfall**
>
> Using `AVG` instead of `MAX - MIN` does not measure spread. The average collapses the distribution and hides variability.

---

## Common follow-up questions

- How would you compute p95 minus p50 instead? _(Tests PERCENTILE_CONT knowledge.)_
- What if a single outlier inflates the max? _(Tests outlier handling and percentile-based metrics.)_
- How would you compute this over a rolling 1-hour window? _(Tests window function RANGE framing.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/endpoint_latency_spread)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.