The Long Wait

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

Spotify

Problem

An endpoint's speed is judged by its average latency across every call it served, never by its single slowest call. List each endpoint in api_calls with that average, slowest first.

Every route runs at its own pace. Find the ones lagging.

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

What this really asks

This is a per-group average wearing an SLO costume. The skill being probed: can you collapse 670 call rows into one latency figure per endpoint, then sort the endpoints by that figure and not by anything stored on a row. Anyone can type AVG(latency). What separates candidates is what they sort on. Order by the raw latency column and you are ordering individual calls, so one slow call on a quick route lands above a route that is slow every time.

The other trap: average everything and you hand back one platform number when the question was which routes are slow. Put endpoint in the select list but leave it out of the grouping, and the engine either errors or, worse in a lax dialect, staples an arbitrary endpoint to a global average that looks plausible and is wrong.

The one decision that matters

Step 1: Group at the endpoint grain

Collapse the rows with GROUP BY endpoint and let AVG(latency) run inside each group. That one choice is the difference between a per-route breakdown and a meaningless global scalar. Every non-aggregated column you select must appear in the grouping.

Step 2: Order so the slow routes surface

The worst offenders belong on top, so sort by avg_latency descending. Sorting by the aggregate is the natural reading of 'slowest first', and it is what makes the result actionable instead of merely correct.

SELECT endpoint, AVG(latency) AS avg_latency
FROM api_calls
GROUP BY endpoint
ORDER BY avg_latency DESC

Global average (the mistake)

SELECT AVG(latency) FROM api_calls returns one number for the whole platform. It answers a question nobody asked and hides the slow routes inside the mean.

Per-endpoint average (the answer)

GROUP BY endpoint keeps one row per route, so '/api/v1/search', averaging about 347.9, surfaces on top instead of disappearing into the platform-wide mean.

Common follow-up questions

• How would you restrict this to only the last 7 days of calls, and where does that filter go? (Tests time-window reasoning and where a filter belongs relative to the aggregate.)
• The SLO is defined on p95 latency, not the mean. How would you compute a per-endpoint p95 instead? (Tests understanding that the mean hides tail behavior, which is what SLOs actually track.)
• If '/api/v1/users' and '/api/v1/users/' should be one endpoint, how do you fold them together before grouping? (Tests path normalization and grouping on a derived expression.)