Retried Failed API Calls

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

PayPal

Problem

Reliability wants to catch clients that keep hammering an endpoint that keeps erroring back at them. For each user and endpoint, count the calls that themselves error within 5 minutes of that same user's previous call to the same endpoint, and return the user_id, endpoint, and that retry count, heaviest retriers first, then by user_id and endpoint.

Spot users who retry API calls within 5 minutes of a 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

Strip the reliability framing and this is a row-to-row temporal comparison inside each user and endpoint: order the calls, look at the gap between a call and the one right before it, and keep only the ones that came back fast and still failed. Anyone can COUNT calls per user and endpoint. The skill being probed is whether you can reach the PREVIOUS call's timestamp for that same user and endpoint, because a plain GROUP BY has no idea what 'the call before this one' even means. Get the partitioning wrong and you either string unrelated users together or measure the gap across the wrong endpoint, and every retry_count comes out inflated.

The trap everyone falls for

Building it up

Step 1: Reach the previous call with LAG

Partition by user_id AND endpoint, order by call_time, and LAG hands you the prior row for that same user on that same endpoint. LAG(call_time) gives you the previous timestamp for the gap, and LAG(status) surfaces the previous outcome so you can reason about it. The double partition key is the whole game: drop endpoint and you compare a payments call against an unrelated search call; drop user_id and you blend strangers into one timeline.

Step 2: Measure the gap in minutes

julianday() returns days, so (julianday(call_time) - julianday(prev_call_time)) * 24 * 60 gives minutes. Keep gaps of five or fewer. This one comparison quietly does a second job: the very first call in each partition has a NULL prev_call_time, the arithmetic is NULL, and a NULL is not <= 5, so first calls drop out for free without a separate guard.

Step 3: Keep only the retries that errored

err_msg is populated exactly when the call came back with an error and NULL on a clean 200, so err_msg IS NOT NULL keeps the failed retries and discards the ones that finally succeeded. status != 200 gets you the same rows here; the point is the filter is on THIS call, the retry, not on prev_status. That is why prev_status is computed but never filtered on: it is the tempting signal that does not discriminate.

Step 4: Count and order

COUNT(*) per user_id and endpoint is the retry tally. Order by retry_count descending so the worst offenders sit on top, then by user_id and endpoint so equal counts land in a stable, predictable order.

WITH consecutive_calls AS (
    SELECT
        user_id,
        endpoint,
        call_time,
        err_msg,
        LAG(call_time) OVER (PARTITION BY user_id, endpoint ORDER BY call_time) AS prev_call_time,
        LAG(status) OVER (PARTITION BY user_id, endpoint ORDER BY call_time) AS prev_status
    FROM api_calls
)
SELECT
    user_id,
    endpoint,
    COUNT(*) AS retry_count
FROM consecutive_calls
WHERE (julianday(call_time) - julianday(prev_call_time)) * 24 * 60 <= 5
  AND err_msg IS NOT NULL
GROUP BY user_id, endpoint
ORDER BY retry_count DESC, user_id, endpoint

Self-join (the naive reach)

Join api_calls to itself on user_id and endpoint with a correlated 'most recent earlier call' subquery. Correct, but at 50M rows and a hot endpoint it fans out badly before you collapse back to the nearest prior row.

LAG window

A single ordered pass. LAG walks each partition once, so the previous timestamp is already sitting on the row. Same answer, one scan, and it reads as a straight line.

Common follow-up questions

• Reliability now wants only users whose `retry_count` is above the average across all user-endpoint pairs. How do you add that? (Pushes toward a scalar subquery or a second aggregation stage over the CTE.)
• What if a null `user_id` could genuinely have two error calls within five minutes of each other? (Tests whether they see that PARTITION BY groups all NULL `user_ids` together and would need an explicit exclusion.)
• How would the answer change if you had to count only retries where the endpoint failed at least three times in a row? (Moves from adjacent-pair logic to run-length / gaps-and-islands thinking.)