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

We're measuring first-impression latency from api_calls: for the first call each user makes to a given endpoint, how long it took in whole milliseconds with the timer's fractional digits dropped, where calls with no user attached all count as one anonymous visitor. The log sometimes records an endpoint with stray spaces or a trailing slash, and those spellings are the same endpoint. Report the average first-contact latency for every endpoint, slowest first.

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, route) pair you want exactly one row, the earliest call, and only then do you average within each route. The trap is AVG(latency) GROUP BY endpoint straight off the raw table. That answers 'average latency per endpoint', a different number 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: Fold the endpoint spellings into one route

The log writes one route several ways: '/api/v1/orders ' with a trailing space, '/api/v1/users/' with a trailing slash. RTRIM(TRIM(endpoint), '/') strips the padding and then the slash. Clean the key first, because everything downstream partitions and groups on it.

Step 2: Number each user's calls to each route by time

ROW_NUMBER() OVER (PARTITION BY user_id, RTRIM(TRIM(endpoint), '/') ORDER BY call_time). Partitioning by user and cleaned route gives one earliest row per pair, so a chatty user contributes a single first contact per route. NULL user_id rows share one partition, which is exactly the one anonymous visitor the statement describes.

Step 3: Keep only each user's first call per route

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

Step 4: Floor each latency, average per route, and order

AVG(CAST(latency AS INTEGER)) floors every surviving latency to a whole millisecond before averaging. Grouping by the cleaned endpoint gives one number per route, 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 RTRIM(TRIM(endpoint), '/') AS endpoint,
         latency,
         ROW_NUMBER() OVER (
           PARTITION BY user_id, RTRIM(TRIM(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? (Keep the `rn = 1` set and apply `PERCENTILE_CONT(0.5)` per route, since an average hides tail behavior on latency.)
• 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.)
• How would you make this incremental for a daily job? (Fix the spellings at ingestion, persist each (user, route) first `call_time` once, then each day add only pairs seen for the first time instead of re-ranking 400M rows.)