Rapid Retry Detection

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

L5

Asked in

Vanguard

Problem

List every recovered retry in api_calls: a later call from the same user to the same endpoint, made within 5 minutes of a server-side failure (a 5xx status), that came back with no error message. The on-call engineer wants each pair as the user_id, the endpoint, the failed call's time and the retry's time.

Detect retried API calls within 5 minutes of failure.

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 is

This is a bounded, forward-only pairing of a table with itself, dressed up as incident forensics. Every row in the answer is two rows of api_calls glued together: the call that broke and the call that got through. Anyone can write the self-join. What separates candidates is putting each test on the right side of the pair: the 5xx test belongs to the earlier call, the clean-response test to the later one. Swap them, or write one combined status filter, and you report users still stuck in a loop of failing retries as rescued, and the postmortem says the incident healed minutes before it did.

Build the pair

Step 1: Alias the table as failed and retry

Read api_calls twice and give the copies names that say what each row is: failed and retry. Aliases like a and b are exactly how people end up testing a.err_msg when they meant the retry. Equality on user_id and endpoint scopes every pair to one user hammering one endpoint.

Step 2: Bound the window forward

retry.call_time > failed.call_time makes the retry strictly later, so no call pairs with itself. julianday returns days, so the gap times 24 * 60 is minutes, and <= 5 keeps a call landing exactly on the five-minute mark.

Step 3: Qualify each side on its own terms

failed.status >= 500 keeps server-side failures only; a 404 or 401 is the client's problem and is not what a retry storm is made of. retry.err_msg IS NULL keeps only retries that came back clean. Two tests, two different rows.

The solution

SELECT
  failed.user_id,
  failed.endpoint,
  failed.call_time AS failed_call_time,
  retry.call_time AS retry_call_time
FROM api_calls AS failed
JOIN api_calls AS retry
  ON retry.user_id = failed.user_id
 AND retry.endpoint = failed.endpoint
 AND retry.call_time > failed.call_time
 AND (julianday(retry.call_time) - julianday(failed.call_time)) * 24 * 60 <= 5
WHERE failed.status >= 500
  AND retry.err_msg IS NULL

One side tested

Filtering only failed.status >= 500 returns every later call within five minutes, including retries that hit the same 503 again. Those rows say the user recovered when they were still failing.

Both sides tested

Adding retry.err_msg IS NULL keeps only the call that actually got through, so each row is a real recovery and the time gap is the true time to recover.

Common follow-up questions

• Return only the first clean retry after each failure. What changes? (Tests whether they see that the pairing returns every later clean call, not the first one.)
• How would you report the median minutes to recover per `endpoint`? (Tests the time-to-recover framing: one row per failure, minimum gap.)
• What if a retry must also use the same `method` as the failed call? (Tests requirement clarification on the join key.)