# Latency Gap to 10th Fastest > One server. Compared to the 10th fastest. Canonical URL: 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. ## Tables ### server_logs - log_id INTEGER - server_name TEXT - log_level TEXT - message TEXT - response_time_ms INTEGER - log_timestamp TEXT Sample rows (10 of 200): | log_id | server_name | log_level | message | response_time_ms | log_timestamp | | --- | --- | --- | --- | --- | --- | | 137 | web-prod-02 | WARN | Connection timeout after 30s | 23 | 2026-02-02 01:07:13 | | 174 | api-prod-01 | ERROR | Database query slow: 2.5s | NULL | 2026-03-03 02:14:26 | | 211 | api-prod-02 | DEBUG | Cache miss for key user_123 | 49 | 2026-04-04 03:21:39 | | 248 | db-primary | CRITICAL | Memory usage at 85% | 0 | 2026-05-05 04:28:52 | | 285 | db-replica-01 | INFO | SSL certificate expires in 7 days | NULL | 2026-06-06 05:35:05 | | 322 | cache-01 | WARN | Rate limit exceeded for IP 192.168.1.x | 88 | 2026-07-07 06:42:18 | | 359 | worker-01 | ERROR | Disk space low: 10% remaining | 101 | 2026-08-08 07:49:31 | | 396 | cdn-edge-us | DEBUG | New deployment v2.3.1 started | NULL | 2026-09-09 08:56:44 | | 433 | cdn-edge-eu | CRITICAL | Health check passed | 127 | 2026-01-10 09:03:57 | | 470 | monitor-01 | INFO | Failed to connect to upstream | 140 | 2026-02-11 10:10:10 | ## Expected output First 1 of 1 rows: | latency_gap | | --- | | 39 | ## Worked solution and explanation ### Why this problem exists in real interviews On-call SREs constantly compare a single host to a peer benchmark: 'how does web-prod-07 stack up against the tenth-fastest box in the fleet?' Interviewers use this prompt to see whether you can compute per-group averages, rank them, and then subtract two specific scalar values out of that ranked set. The trick is that the answer is one number, not a table, so the candidate has to lift two scalars from the same CTE and combine them with `ABS`. --- ### Break down the requirements #### Step 1: Average response time per server Aggregate per server: in `server_avg` compute AVG(`response_time_ms`) grouped by `server_name`, so each server has exactly one average response time. #### Step 2: Rank servers ascending with no gaps on ties Rank to find a single 10th-fastest server: in ranked apply `ROW_NUMBER`() OVER (ORDER BY `avg_rt` ASC, `server_name` ASC). Lowest average response time = fastest; `ROW_NUMBER` guarantees exactly one row per rank even when two servers share an average (`server_name` breaks ties deterministically), so rnk = 10 always identifies one server. #### Step 3: Pull two scalars and subtract them Pull the two target averages as scalar subqueries: web-prod-01's average from `server_avg`, and the rnk = 10 average from ranked. --- ### The solution **Average per server, dense-rank, then absolute scalar gap** ```sql 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; ``` > **Cost Analysis** > > `server_logs` holds 100M rows partitioned daily across 365 days. The aggregate must touch every partition since the prompt does not narrow time. After the GROUP BY there are only 30 rows, so the window function and the two scalar subqueries are essentially free. The dominant cost is the single full scan plus the hash aggregate. > **Interviewers Watch For** > > Interviewers watch whether you pick `DENSE_RANK` (matches the 'no gaps on ties' phrasing) over `RANK` or `ROW_NUMBER`, whether you remember `ABS()` because the prompt asks for an absolute difference, and whether you handle the multi-row case at rank 10 with `LIMIT 1` so the scalar subquery cannot raise an error if two servers tie. > **Common Pitfall** > > Writing `(SELECT avg_rt FROM server_ranks WHERE rnk = 10)` without `LIMIT 1` raises 'subquery returned more than one row' the moment two servers share rank 10. The other classic miss is omitting `ABS`: if web-prod-07 is faster than the rank-10 server, the gap comes back negative and the autograder rejects the row. --- ## Common follow-up questions - What would change in your CTE if `response_time_ms` were 100% NULL for one of the 30 servers? _(Tests NULL behavior in `AVG` and rank assignment. `AVG` returns NULL for that group, and `DENSE_RANK` places NULL at the start (SQLite, Postgres) or end (some other engines), shifting which server lands at rank 10.)_ - How would you generalize this to compute the gap for every server against the rank-10 baseline in one query? _(Tests whether the candidate replaces the second scalar subquery with a CROSS JOIN to a one-row CTE holding the rank-10 average, so every server row in `server_ranks` can subtract from it without rescanning.)_ - How would you scope this to only the last 7 days of `log_timestamp` without breaking partition pruning? _(Tests partition-key awareness. `server_logs` is partitioned by `log_timestamp`. A range predicate `log_timestamp >= date('now', '-7 days')` lets the planner prune; wrapping `log_timestamp` in `strftime` disables it.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/latency_gap_to_10th_fastest) - [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.