First Contact

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

The performance team is measuring first-impression latency: for the first time each user hits a given endpoint, how long that call took, counting only whole milliseconds because the timer's fractional digits are treated as noise. For every endpoint, report the average of those first-contact latencies, with the slowest first impressions at the top.

Every user's first hello to an endpoint. Timed.

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-group first-occurrence pick wearing a latency-report costume. For every (user_id, endpoint) pair you want exactly one row, the earliest call, and only then do you average within each endpoint. The trap is AVG(latency) GROUP BY endpoint straight off the raw table. That answers 'average latency per endpoint', a different number entirely, dominated by whoever calls the most. Miss the dedup and your first-impression metric quietly becomes a traffic-weighted average of everyone's repeat traffic.

Break down the requirements

Step 1: Rank each user's calls to each endpoint by time

ROW_NUMBER() OVER (PARTITION BY user_id, endpoint ORDER BY call_time). Partitioning by both columns is the whole point: one earliest row per user per endpoint, so a chatty user contributes a single first-contact latency to each endpoint they touched, not hundreds. Ties on call_time would be broken arbitrarily; add call_id to the window's ordering if you need determinism.

Step 2: Keep only each user's first call per endpoint

Filter on rn = 1 in an outer query. A window function cannot live in WHERE, so the subquery wrapper is mandatory, not stylistic. This is the row set that represents genuine first impressions.

Step 3: Floor each latency, average per endpoint, and order

AVG(CAST(latency AS INTEGER)) floors every surviving first-contact latency to a whole millisecond before averaging, so the timer's fractional jitter never reaches the mean. Grouping by endpoint gives one number per endpoint with each user counted once, and sorting that average descending puts the slowest first impressions on top.

The solution

SELECT endpoint,
       AVG(CAST(latency AS INTEGER)) AS avg_initial_call_latency
FROM (
  SELECT endpoint,
         latency,
         ROW_NUMBER() OVER (
           PARTITION BY user_id, endpoint
           ORDER BY call_time
         ) AS rn
  FROM api_calls
) first_calls
WHERE rn = 1
GROUP BY endpoint
ORDER BY avg_initial_call_latency DESC

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would you report the median first-contact latency per endpoint instead of the average? (Wrap the `rn = 1` set and apply `PERCENTILE_CONT(0.5)` per endpoint, since an average hides tail behavior on latency distributions.)
• What if you wanted the average over each user's first three calls to an endpoint? (Change the filter to `rn <= 3`. The partition definition stays the same, which is why `ROW_NUMBER` scales cleanly here.)
• How would you make this incremental for a daily job? (Materialize each (user, endpoint) first `call_time` once, then only add pairs that appear for the first time in each new day instead of re-ranking 400M rows nightly.)