After the Handshake

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

Asked in

Uber

Problem

On the platform team's latency board, endpoints look faster than they are because every user's earliest call in api_calls lands quickly, so those opening calls should not count. Report each endpoint's average latency over the remaining calls as a whole number of milliseconds with the fraction dropped, slowest endpoint first.

The first call is a promise. Judge the ones that follow.

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 is really asking

This is a per-user 'drop the opener' problem dressed up as a latency report. The skill being probed is whether you can remove exactly one row per user, chosen by time, and then regroup what is left by a completely different key. Anyone can write the per-endpoint average. What separates candidates is where they scope 'earliest': per user, not per user and endpoint, and not once for the whole table. Scope it per user and endpoint and you drop an opener on every endpoint a user touched, which is most of their history, since each user's 4 to 13 calls spread across many endpoints. Scope it globally and you drop one row, and the board still flatters every endpoint.

Step 1: Number each user's calls in time order

ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY call_time) restarts at 1 for every user. The opener is the row numbered 1, and every repeat call is 2 or higher. call_time is ISO text, so it sorts chronologically as stored. Calls with no user_id share one partition and lose a single opener between them.

Step 2: Filter on the number one level up

WHERE runs before window functions, so call_seq > 1 cannot sit beside the SELECT that defines call_seq. Wrap the numbering in a subquery and filter in the outer query.

Step 3: Average by endpoint, then drop the fraction

Group the surviving rows by endpoint and take AVG(latency). Calls with no recorded latency are skipped by the average. The board shows whole milliseconds, so CAST(AVG(latency) AS INTEGER) truncates: 335.8 becomes 335, not 336. Sort by that figure, highest first.

The solution

SELECT endpoint,
       CAST(AVG(latency) AS INTEGER) AS avg_repeat_latency_ms
FROM (
  SELECT endpoint,
         latency,
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY call_time) AS call_seq
  FROM api_calls
) numbered_calls
WHERE call_seq > 1
GROUP BY endpoint
ORDER BY avg_repeat_latency_ms DESC

Opener per user and endpoint

PARTITION BY user_id, endpoint numbers each user's calls separately on every endpoint, so a user who hit six endpoints loses six calls. Most histories shrink sharply and every average moves.

Opener per user

PARTITION BY user_id alone numbers the whole history, so exactly one call per user leaves the set, whichever endpoint it happened to hit.

Follow-up questions

Common follow-up questions

• How would you report average repeat-call latency per user instead of per endpoint? (Same numbered subquery; the outer query groups by `user_id` instead of `endpoint`.)
• What happens to a user who has only ever made one call? (That user contributes nothing after `call_seq > 1`, which is right: they have no repeat calls.)
• Two of a user's calls share the same `call_time`. Which one is the opener? (The row number picks one arbitrarily; add `call_id` to the window ordering to make the choice reproducible.)
• The board should also show how many repeat calls each average is based on. What changes? (Add a `COUNT(latency)` beside the average, so thin endpoints are visible.)