Average Search Endpoint Latency

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Forbes

Problem

Autocomplete feels sluggish and the frontend team suspects the search backend is the bottleneck. What is the average latency for API calls hitting the '/api/v1/search' endpoint?

One endpoint. Average speed.

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 filtered aggregate wearing an incident costume. The whole skill is scoping: AVG(latency) must see only the '/api/v1/search' rows, so the endpoint predicate narrows api_calls before the average is taken. Everyone gets the shape right. What separates candidates is not letting the question drift into a table-wide number, and knowing what the average does with a call that was never timed. Drop the filter and you hand the frontend team a latency blended across every route, a number that can neither clear nor convict the search backend.

Building it

Step 1: Pin the rows to the exact search route

Use equality: endpoint = '/api/v1/search'. The table carries near-miss routes like '/api/v1/users/' and '/api/v2/products/', plus padded spellings of other endpoints, so a loose pattern is the habit that eventually sweeps a sibling route into a latency number. Equality is exact and the cheapest predicate there is.

Step 2: Average only what was timed

AVG(latency) skips NULLs. Of the 86 search calls, one has no latency recorded, so the mean is taken over 85 timed calls. That is the right reading: an untimed call is not a zero-latency call, and wrapping it in COALESCE(latency, 0) would quietly make search look faster than it is.

Step 3: Return one number

The question asks for a single figure, so the filtered rows collapse into one row named avg_latency. No grouping and no sorting: a GROUP BY endpoint would add a column nobody asked for, and there is only one row to order.

SELECT AVG(latency) AS avg_latency
FROM api_calls
WHERE endpoint = '/api/v1/search'

Whole table

SELECT AVG(latency) FROM api_calls averages every call to every route: orders, users, products, auth and search together. It runs, it returns one tidy number, and that number describes the whole API rather than autocomplete.

Search only

WHERE endpoint = '/api/v1/search' narrows the input first, so the 85 timed search calls are the only ones AVG ever sees, and the answer is about 347.89.

Common follow-up questions

• How would you compare the search endpoint against every other endpoint in one result? (Moves from a scoped scalar to a per-endpoint breakdown, and tests whether the candidate notices the padded `endpoint` spellings need trimming first.)
• Autocomplete feels slow to some users but fine to others. What would you report instead of the mean? (Tests whether the candidate knows a mean hides the tail and reaches for a p95 or p99.)
• How would you limit this to the last seven days of `call_time`? (Tests date filtering and pruning on the partition key.)
• Should failed search calls (a 5xx `status`) count toward the latency figure? (Tests whether the candidate asks which population the business actually means.)