Latency Gap to 10th Fastest

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

L4

Asked in

Civis Analytics

Problem

During an SRE review, the VP of Infrastructure asked how server 'web-prod-01' compares to the 10th-fastest server by average response time. Calculate the absolute difference in their average response times. Return a single latency gap value.

One server. Compared to the 10th fastest.

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

What this really is

This is a two-stage problem wearing a single-number costume: collapse the log into one average per server_name, put those averages in speed order, then pull one value by name and one by position and subtract. Everyone gets the GROUP BY. What separates candidates is treating the 10th position as a contract: a bare ORDER BY avg_rt LIMIT 1 OFFSET 9 returns whichever server the engine emits first when two averages tie, and the gap quietly moves between runs. The second trap is the third of the rows with no response_time_ms: AVG already skips them, and hand-rolling the division or coalescing them to zero drags every server's average down.

Step 1: Average each server once

In server_avg, AVG(response_time_ms) per server_name turns 200 log rows into 11 server rows. AVG divides by the non-NULL count, so a request whose timing was never captured neither adds latency nor counts as a request. The single genuine 0 ms reading on 'db-primary' is real data and stays in.

Step 2: Number the servers by speed

In ranked, ROW_NUMBER() OVER (ORDER BY avg_rt ASC, server_name ASC) gives the fastest server rnk = 1. ROW_NUMBER hands out one number per row, and server_name settles any tie alphabetically, so rnk = 10 always names exactly one server. With 11 servers on the seed, the 10th-fastest is the second-slowest: 'cdn-edge-us' at 757.5 ms.

Step 3: Lift two scalars and take the gap

One scalar subquery reads 'web-prod-01' from server_avg by name (796.5 ms), and the other reads rnk = 10 from ranked. ABS of the difference gives 39.0, and it stays positive whichever server turns out slower.

The solution

WITH server_avg AS (
  SELECT server_name,
         AVG(response_time_ms) AS avg_rt
  FROM server_logs
  GROUP BY server_name
),
ranked AS (
  SELECT server_name,
         avg_rt,
         ROW_NUMBER() OVER (ORDER BY avg_rt ASC, server_name ASC) AS rnk
  FROM server_avg
)
SELECT ABS(
         (SELECT avg_rt FROM server_avg WHERE server_name = 'web-prod-01')
         - (SELECT avg_rt FROM ranked WHERE rnk = 10)
       ) AS latency_gap;

Offset pick

ORDER BY avg_rt LIMIT 1 OFFSET 9 with no second key. It happens to be right on this seed, where no two averages tie, but a tie around position 10 lets the engine return either server, and the gap changes from run to run.

Numbered pick

ROW_NUMBER() OVER (ORDER BY avg_rt, server_name) filtered to rnk = 10. It yields one row, the same row on every engine, and the scalar subquery can never see two values.

Common follow-up questions

• What happens if every `response_time_ms` for one server is `NULL`? (That server's `avg_rt` is `NULL`. This tests where `NULL` sorts in the window (engines disagree) and whether it should be dropped before numbering.)
• How would you show every server's gap to the 10th-fastest in one query? (Tests replacing the second scalar subquery with a one-row CTE joined onto every row of `server_avg`.)
• How would you scope this to the last 7 days without breaking partition pruning? (Tests filtering `log_timestamp` with a plain range predicate instead of wrapping the column in a function.)