Behind the Fleet

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

mid

Asked in

Slalom

Problem

The reliability team is combing through server logs to find the machines dragging response times up. Give each server its average response time and keep only the machines slower than the typical server, meaning those sitting above the mean of the per-server averages. List the worst offender at the top and work down to the least sluggish, so the team fixes the slowest machine first.

Every machine keeps its own pace. Find the ones falling short of the average.

Tables

server_logs

Columns: log_id INTEGER, server_name TEXT, log_level TEXT, message TEXT, response_time_ms INTEGER, log_timestamp TEXT

Expected output

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

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

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.)