# The Slow Lane

> One machine is dragging the fleet down. Find the ones running behind.

Canonical URL: <https://datadriven.io/problems/the-slow-lane>

Domain: SQL · Difficulty: medium · Seniority: mid

## Problem

The reliability team is combing through server logs to find the machines that are dragging response times up. Give each server its average response time, then surface the ones sitting above the average machine, the mean of those per-server averages, slowest first.

## Worked solution and explanation

### Why this problem exists in real interviews

This looks like a monitoring question, but it is really a grain trap wearing an SRE costume. The baseline is 'the average machine', which means the average OF the per-server averages, not the average over all log rows. Those two numbers differ the moment servers log different volumes: a chatty cache node with 10x the rows would drag a row-level average toward itself and quietly redefine 'typical'. The prompt tells you which one it wants; the skill is expressing that grain in SQL without accidentally collapsing back to the row-level number.

---

### Break down the requirements

#### Step 1: Roll up to server grain

GROUP BY server_name with AVG(response_time_ms). This is the unit the question cares about: one row per machine, not per log line. Park it in a CTE so the baseline can reuse the same rollup instead of recomputing it.

#### Step 2: Define the baseline at the server grain

The cutoff is (SELECT AVG(avg_response_ms) FROM server_latency): the average of the per-server averages, so every machine gets one vote. Averaging response_time_ms over the raw rows instead would weight each server by its log volume, which is a different and wrong number.

#### Step 3: Filter and sort

Keep servers strictly above that scalar, then ORDER BY avg_response_ms DESC so the worst offender surfaces first.

---

### The solution

**SERVERS ABOVE THE TYPICAL LATENCY**

```sql
WITH server_latency AS (
  SELECT server_name,
         AVG(response_time_ms) AS avg_response_ms
  FROM server_logs
  GROUP BY server_name
)
SELECT server_name,
       avg_response_ms
FROM server_latency
WHERE avg_response_ms > (SELECT AVG(avg_response_ms) FROM server_latency)
ORDER BY avg_response_ms DESC
```

> **Baseline at the right grain**
>
> The whole problem is one decision: is the baseline averaged over rows or over servers? The CTE lets you answer it explicitly. AVG(avg_response_ms) over the rollup treats every server as one vote; AVG(response_time_ms) over base rows lets a high-volume server stuff the ballot. The prompt asks for the per-server mean, so compare against the rollup, not the raw table.

> **Cost Analysis**
>
> On a real fleet, server_logs is a firehose (say 2B rows/year). After the base scan, one hash aggregate collapses billions of log lines to a few hundred server rows; the scalar AVG is a cheap second pass over that tiny rollup, materialized once. The expensive part is the base scan, so in production you pre-aggregate per-server rollups on a schedule and compare against those, never re-reading raw logs to answer this.

> **Common Pitfall**
>
> Reaching for HAVING AVG(response_time_ms) > AVG(AVG(response_time_ms)) in a single GROUP BY. HAVING evaluates per group and cannot see the cross-server average, so nested aggregates like that either error or silently compute nonsense. You need the CTE plus a scalar subquery, or AVG(avg_response_ms) OVER () on the rollup.

---

## Common follow-up questions

- Rewrite the baseline with a window function instead of a scalar subquery. What is the tradeoff? _(Tests whether they can swap (SELECT AVG...) for AVG(avg_response_ms) OVER () and reason about one extra pass versus a windowed frame.)_
- The team now wants servers above the 90th percentile of latency, not the mean. What changes? _(Pushes toward NTILE or a percentile function and the difference between a mean baseline and a quantile threshold.)_
- How would you make this incremental so it runs hourly without rescanning all of history? _(Surfaces pre-aggregated per-server rollups and incremental maintenance instead of full scans.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the-slow-lane)
- [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.