# Fastest Regions by Latency > The fastest regions. Benchmarked. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem The API performance team wants to highlight the fastest endpoints. Find the top 3 endpoints with the lowest average latency, including all ties at the third position. Return the endpoint and its average latency. ## Worked solution and explanation ### Why this problem exists in real interviews Performance teams constantly answer 'which N services are fastest' for SLO dashboards and capacity planning. Interviewers use this prompt to see whether you can pick a top-N strategy that survives ties: LIMIT 3 silently drops endpoints tied at the third position, while DENSE_RANK keeps every endpoint sharing that latency. The test is whether you reach for ranking rather than truncation. --- ### Break down the requirements #### Step 1: Compute average latency per endpoint Group `api_calls` by `endpoint` and compute `AVG(latency)`. This collapses 300M rows down to one row per endpoint (only ~150 distinct values), so the heavy lifting is the scan and aggregate, not the ranking. #### Step 2: Rank from fastest to slowest with ties preserved Apply `DENSE_RANK() OVER (ORDER BY AVG(latency) ASC)` in the same SELECT as the GROUP BY. DENSE_RANK assigns the same rank to ties and does not skip values, so endpoints sharing the third-lowest latency all receive rnk = 3. #### Step 3: Keep ranks 1 through 3 and project the asked columns Wrap the ranked aggregate in a subquery and filter `WHERE rnk <= 3`. Project only `endpoint` and `avg_latency`, since the prompt asks for two columns and the rank is scaffolding. --- ### The solution **Rank endpoints by average latency, keep ties at position 3** ```sql SELECT endpoint, avg_latency FROM (SELECT endpoint, AVG(latency) AS avg_latency, DENSE_RANK() OVER (ORDER BY AVG(latency) ASC) AS rnk FROM api_calls GROUP BY endpoint) ranked WHERE rnk <= 3 ``` > **Cost Analysis** > > `api_calls` holds 300M rows across 365 daily partitions (~77 GB). The full scan and aggregate dominate cost. After grouping, only ~150 endpoint rows reach the window function, so the DENSE_RANK and outer filter are effectively free. If an index existed on `(endpoint, latency)` you could read the aggregate from a covering index, but at this scale a partition-parallel scan is what the planner will pick. > **Interviewers Watch For** > > Interviewers watch whether you ask 'should ties at rank 3 be included?' before choosing between LIMIT, RANK, DENSE_RANK, and ROW_NUMBER. Strong candidates name the difference: ROW_NUMBER breaks ties arbitrarily, RANK leaves gaps after ties (1, 2, 2, 4), DENSE_RANK does not (1, 2, 2, 3). Picking DENSE_RANK and explaining why earns the point. > **Common Pitfall** > > Writing `ORDER BY AVG(latency) ASC LIMIT 3` looks correct but silently drops any endpoint tied for third place. The prompt explicitly says 'include all ties at the third position', so LIMIT is wrong here. Using `RANK()` instead of `DENSE_RANK()` also fails when two endpoints tie for first: RANK skips to 3, so position 3 ends up missing. --- ## Common follow-up questions - Walk through what changes if you swap `DENSE_RANK` for `RANK` or `ROW_NUMBER`. Which endpoints might appear or disappear from the result? _(Tests whether the candidate distinguishes the three ranking functions. ROW_NUMBER gives 1,2,3,4; RANK gives 1,2,2,4 (skipping 3); DENSE_RANK gives 1,2,2,3. Only DENSE_RANK satisfies the 'include all ties at third' requirement.)_ - `latency` has a zipf skew with 2M distinct values, meaning a few requests run extremely slowly. How does using AVG bias the ranking, and what would you compute instead? _(Tests sensitivity to outliers: AVG is pulled by tail latencies, so a single 30-second timeout can ruin an endpoint's ranking. Median or p95 is more representative of typical user experience.)_ - If product asked for fastest endpoints over the last 7 days only, how would you change the query so it scans 7 partitions instead of all 365? _(Tests partition-pruning awareness. With `call_time` as the partition key over 365 daily partitions, adding `WHERE call_time >= date('now', '-7 days')` lets the planner read only 7 partitions instead of 365.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/fastest_regions_by_latency) - [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.