Noisy Endpoints

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

Problem

Error alerts during last night's incident keep pointing at a handful of API routes, so the on-call engineer wants every endpoint in api_calls that failed more than 5 times, where any response code of 400 or above counts as a failure. Show each one's failure count alongside its average latency across just those failed calls.

The routes generating the most noise.

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

A two-stage filter in an incident costume

This is a two-stage filter wearing an incident-triage costume. The row-level cut, status >= 400, isolates the failed calls; the group-level cut, more than 5 of them, can only be applied after the rows are grouped, which is exactly what HAVING exists for. The trap: candidates write WHERE COUNT(*) > 5 and hit an aggregate-not-allowed-in-WHERE error, or drop the threshold entirely and bury the real hotspots under every route that failed a handful of times.

Break down the requirements

Step 1: Filter to error responses

WHERE status >= 400 keeps only failed API calls, before any grouping happens. status is an integer, so one range check catches 4xx and 5xx together; a string match on '4' would silently drop every 5xx.

Step 2: Group by endpoint and aggregate

GROUP BY endpoint with COUNT(*) for the failure count and AVG(latency) for the average latency. AVG ignores calls with a NULL latency, so it reports the mean of the values that actually exist.

Step 3: Apply the HAVING threshold

HAVING COUNT(*) > 5 runs against the aggregate itself, dropping every endpoint with 5 or fewer failures. That includes the stray padded paths and trailing-slash variants that only fail once or twice. WHERE cannot express this, because the count does not exist until the groups are formed.

Step 4: Leave the order alone

The grader compares the set of rows, not their sequence, and nothing in the ask commits to an order. Adding a sort is harmless for a human reader, but it is not part of the answer.

The solution

SELECT
    endpoint,
    COUNT(*) AS failure_count,
    AVG(latency) AS avg_latency
FROM api_calls
WHERE status >= 400
GROUP BY endpoint
HAVING COUNT(*) > 5

Hand-rolled mean

SUM(latency) / COUNT(*) divides by every failed row, including the ones whose latency is NULL, so the result is pulled low.

Built-in mean

AVG(latency) sums and counts only the non-null latency values, which is the average of what was actually measured.

Common follow-up questions

• Some failed calls have a `NULL` latency. How does `AVG(latency)` treat them, and would `SUM(latency)/COUNT(*)` give a different answer? (Tests whether the candidate understands that `AVG` silently skips `NULL` values and whether that is the intended behavior.)
• Why can the 'more than 5 failures' cutoff not be expressed with a `WHERE` clause? (Tests whether the candidate understands pre-aggregation vs post-aggregation filtering.)
• A couple of `endpoint` values carry stray spaces. When would you clean them before grouping, and what changes if you do? (Tests whether the candidate spots the padded paths in `endpoint` and knows when to `TRIM`.)
• What index would let this query avoid a full scan of `api_calls` during an incident? (Tests indexing knowledge for a filter-then-group-then-aggregate query at scale.)