Average Latency by Status

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

Uber

Problem

During a latency spike investigation, the on-call engineer needs to know whether slow responses correlate with specific HTTP status codes. Show the average latency for each status code across all API calls, walking the codes in order from the lowest to the highest.

Each status code has its own latency story.

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 one-key breakdown dressed up as an incident investigation. The on-call engineer does not want the average latency; they want eleven averages, one per status, side by side so a slow code stands out. Anyone can type AVG(latency). What separates candidates is fixing the grain before writing it: average the whole api_calls table and the fast 200s, which make up most of the traffic, drown out every other code, so the spike's source never shows.

Getting to the answer

Step 1: Fix the grain at one row per status

Before writing any SQL, say the output shape out loud: one row per HTTP code with its average. That decides GROUP BY status, and it rules out the single-number answer the word 'average' tempts you toward.

Step 2: Average latency within each code

AVG(latency) inside each group gives every code its own mean. Six calls have no recorded latency, and AVG skips them, so each mean covers the calls that were actually timed. Nothing about user_id or err_msg removes a row.

Step 3: Sort by the code itself

status is an integer, so ORDER BY status walks 200, 201 and 204 up through 503 numerically. Name the column avg_latency so the result reads as the lookup table the engineer asked for.

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

One blended number

SELECT AVG(latency) FROM api_calls returns a single value that mixes the high-volume 200s with every other code. It cannot tell you which code is slow.

One row per code

Adding GROUP BY status returns eleven rows, each code's own mean, so any slow code stands out against the 200 baseline.

Common follow-up questions

• Six calls have a `NULL` `latency`. What does your average do with them, and how would you show how many calls each average actually covers? (Checks that the candidate knows `AVG` skips NULLs and can add `COUNT(latency)` next to `COUNT(*)`.)
• The 200 bucket is dominated by a burst of short February calls. How would you report the median or p95 per `status` instead of the mean? (Checks whether the candidate sees that a mean can hide the tail an on-call engineer cares about.)
• How would you narrow this to the hour of the spike without scanning the whole year of `call_time` partitions? (Checks whether the candidate uses partition pruning with a time predicate.)
• How would you break latency down by `status` within each `endpoint`, given that some `endpoint` values carry stray whitespace? (Checks that the candidate adds a second grouping key and cleans it with `TRIM` first.)