Slow Failures

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

Problem

Only calls that failed with a client or server error and also ran longer than 200 milliseconds belong in the timeout review. Pull those calls from api_calls, slowest failures first.

SRE is hunting for the endpoints that fail slowly enough to burn timeouts.

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

The column that lies

This is a two-condition filter dressed up as incident triage, and the only real decision is which column gets to define 'failed'. err_msg looks like the honest answer, since a failed call ought to carry a message. In this log it often does not: of the 70 calls that failed and ran past 200 ms, just 24 have a message, and the other 46 carry a 4xx or 5xx status next to a NULL err_msg. Key failure off the message and the review loses two thirds of its incidents, starting with the slowest one, a 403 on '/api/v1/search' at 1713.7 ms.

Getting there

Step 1: Let status define a failure

A client error is the 400s and a server error the 500s, so status >= 400 captures both in one comparison. The log tops out at 503, so there is no upper bound to guard, and a 301 redirect stays out. Narrowing to status >= 500 because 'timeouts are server-side' throws away every slow 401 and 403, and those belong in the result.

Step 2: Require slow AND failed

latency > 200 is the second predicate, joined with AND. Either one alone is wrong in its own direction: status alone pulls in 4xx calls that answered in 30 ms, and latency alone pulls in hundreds of healthy 200s from the busy February traffic. No call sits at exactly 200 ms, but the statement says longer than, so the comparison is strict.

Step 3: Put the slowest at the top

ORDER BY latency DESC hands the reviewer the worst offenders first. A NULL latency never reaches the sort: latency > 200 is unknown for a NULL, so WHERE has already dropped it, and you never have to argue about where NULLs land.

SELECT endpoint, status, latency, err_msg
FROM api_calls
WHERE status >= 400 AND latency > 200
ORDER BY latency DESC

Keyed off the message

WHERE err_msg IS NOT NULL AND latency > 200 returns 24 rows. Every failure logged without a message vanishes, including the 1713.7 ms 403 that should top the list.

Keyed off the status

WHERE status >= 400 AND latency > 200 returns all 70 failures that crossed the threshold, with err_msg carried along as context, NULL where nothing was logged.

Common follow-up questions

• The review now wants only the last seven days. What changes, and why does the query suddenly get cheaper? (Tests whether the candidate connects a `call_time` filter to partition pruning.)
• Two endpoints are stored with stray whitespace, such as '/api/v1/orders '. If the team wants slow-failure counts per endpoint, what do you do first? (Tests normalizing a dirty key with `TRIM` before grouping.)
• How would you return only the single slowest failure per endpoint? (Tests per-group selection with a ranking scoped to each `endpoint`.)