Fastest Regions by Latency

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

L3

Asked in

EY

Problem

The api_calls log holds one row per request with the endpoint it hit and its latency, so an endpoint's speed is the average latency over all of its calls. List every endpoint whose average is among the three lowest averages, ties included, alongside that average.

The fastest regions. Benchmarked.

Tables

api_calls

Columns: call_id INTEGER, endpoint TEXT, method TEXT, status INTEGER, latency REAL, user_id INTEGER, call_time TEXT, err_msg TEXT

Expected output

Worked solution and explanation

This is a top-N over distinct values dressed up as a performance highlight. The skill is ranking the per-endpoint averages so that 'top 3' means the three lowest average figures, not the first three rows: every endpoint that holds one of those figures belongs in the answer. Anyone can write the GROUP BY endpoint with AVG(latency). What separates candidates is refusing ORDER BY AVG(latency) LIMIT 3, which counts rows instead of figures. On the day two endpoints land on the same third-lowest average, LIMIT 3 keeps an arbitrary one and hides the other, and the highlight silently drops an endpoint that is exactly as fast.

Getting there

Step 1: Average each endpoint over all its calls

Collapse api_calls to one row per endpoint with AVG(latency). The six calls whose latency is NULL drop out of the average on their own, which is what you want: a call that was never timed says nothing about speed.

Step 2: Number the averages, not the rows

DENSE_RANK() OVER (ORDER BY AVG(latency) ASC) runs after the grouping, in the same SELECT, so it sees one row per endpoint. Equal averages share a number and the next figure takes the next number, so rnk <= 3 means the three lowest figures however many endpoints hold them.

Step 3: Filter the rank one level up

A window result cannot be tested in the WHERE of the query that computes it, so wrap the ranked aggregate as ranked and keep rnk <= 3 outside. Project endpoint and avg_latency only; the rank is scaffolding.

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

Cut by rows

ORDER BY AVG(latency) LIMIT 3 returns exactly three rows. With averages of 40, 80, 150 and 150 it keeps one of the two 150 endpoints, and which one depends on the plan.

Cut by figures

DENSE_RANK numbers those averages 1, 2, 3, 3, and rnk <= 3 returns all four. Today's data happens to have no tie in the three lowest, so both return the same three rows; only this one is right on every day.

Common follow-up questions

• Walk through what changes if you swap `DENSE_RANK` for `RANK` or `ROW_NUMBER`. Which endpoints might appear or disappear? (Whether the candidate can reason about the 1, 2, 2, 4 gap and the arbitrary tie break.)
• `latency` is heavily skewed, with a few calls running extremely slowly. How does an average bias this list, and what would you compute instead? (Sensitivity to outliers: a median or p95 describes typical experience better.)
• How would you fold the padded endpoint values into their real endpoints, and what happens to this list when you do? (Whether the candidate notices the one-call groups and cleans the key before averaging.)
• If product wanted the fastest endpoints over the last 7 days only, how would you make the query read 7 partitions instead of 365? (Partition pruning on `call_time`: the filter must sit on the partition column itself.)