# First Contact

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

Canonical URL: <https://datadriven.io/problems/first_contact>

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

The performance team is measuring first-impression latency: the first time each user hits a given endpoint, how long that call took. For every endpoint, report the average of those first-contact latencies, slowest endpoints first.

## 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 are broken arbitrarily; add call_id to the ORDER BY if you need determinism.

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

WHERE 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: Average per endpoint, cast, and order

GROUP BY endpoint with AVG over the surviving latencies gives one number per endpoint, each user counted once. CAST to REAL guards against integer division if latency is stored as INTEGER. ORDER BY the average descending puts the slowest first impressions on top.

---

### The solution

**FIRST CONTACT**

```sql
SELECT endpoint,
       CAST(AVG(latency) AS REAL) 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
```

> **Cost Analysis**
>
> ROW_NUMBER over 400M rows partitioned by (user_id, endpoint) sorts within each partition in one pass: one shuffle, one sort, one scan. A correlated subquery picking MIN(call_time) per user and endpoint reads the same volume twice and re-scans per group. The window approach also feeds the GROUP BY directly, so the aggregate rides the same shuffle.

> **Interviewers Watch For**
>
> Whether you dedup to one call per (user, endpoint) before averaging, or naively AVG the raw column and hand back a traffic-weighted number. Also whether you scope 'first call' to the endpoint versus the whole platform, whether you address call_time ties, and whether the REAL cast is justified by latency possibly being INTEGER.

> **Common Pitfall**
>
> Using RANK instead of ROW_NUMBER. If a user has two calls to the same endpoint at the identical call_time, RANK returns both as rank 1 and that user gets counted twice in the endpoint's average. ROW_NUMBER picks exactly one row per partition.

---

### 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 AVG 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 consider rows at or before it going forward instead of re-ranking 400M rows nightly.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/first_contact)
- [SQL Interview Questions](https://datadriven.io/sql-interview-questions)
- [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep)
- [Daily Challenge](https://datadriven.io/daily)

---

Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.