# After the Handshake > The first call is a promise. Judge the ones that follow. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem An API endpoint can look fast on a user's very first call and slower on everything after. Excluding each user's earliest call, find the average latency of the remaining calls for each endpoint, slowest first. ## Worked solution and explanation ### What this is really asking Strip away the API-latency framing and this is a per-user 'drop the earliest row' problem with a grouped average bolted on top. The skill under test: can you exclude exactly one row per user (their first call) and nothing more, then aggregate what is left by endpoint? Anyone can write AVG(latency) with a GROUP BY. The trick is scoping 'earliest' to each user independently. Reach for RANK() and two calls tied on the same timestamp both count as first, so you drop too much. Reach for MIN(call_time) with NOT IN and you again nuke both tied rows. ROW_NUMBER() guarantees exactly one row per user leaves the set. --- ### Break it down #### Step 1: Number each user's calls in time order PARTITION BY user_id restarts the count for each user, and ORDER BY call_time puts that user's earliest call at rn = 1. Every later call for the same user gets 2, 3, and so on. Those higher-numbered rows are the repeat calls you care about. #### Step 2: Keep only the repeats Wrap the windowed query in a subquery and filter rn > 1 in the outer WHERE. You cannot filter rn in the same SELECT that computes it: WHERE is evaluated before window functions run, so the column does not exist yet. #### Step 3: Average per endpoint GROUP BY endpoint over the surviving rows and take AVG(latency). CAST AS REAL keeps the result decimal on engines where an average over an integer-typed column would truncate. Order by that average descending to put the slowest endpoints on top. --- ### The solution **AFTER THE HANDSHAKE** ```sql SELECT endpoint, CAST(AVG(latency) AS REAL) AS avg_update_latency FROM ( SELECT endpoint, latency, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY call_time) AS rn FROM api_calls ) ranked WHERE rn > 1 GROUP BY endpoint ORDER BY avg_update_latency DESC ``` > **Cost Analysis** > > Over 600M rows this is a single partitioned sort on (user_id, call_time) feeding one streaming aggregate. The table is partitioned on call_time, which does not help the user_id partitioning, but there is no join to blow up: the window pass and the group-by are two sequential passes with no self-join. > **Interviewers Watch For** > > The tell is which function you pick for 'earliest'. ROW_NUMBER versus RANK versus a MIN-based exclusion is the whole question. Say out loud why ROW_NUMBER: it is the only one that removes exactly one row per user even when two calls share a timestamp. > **Common Pitfall** > > Filtering rn > 1 beside the SELECT that defines rn. The window function is materialized after WHERE, so the reference to rn fails because WHERE runs before window functions are computed. The predicate has to live in an outer query over the wrapped subquery. --- ### COMMON FOLLOW-UP QUESTIONS ## Common follow-up questions - How would you compute average repeat-call latency per user instead of per endpoint? _(Same subquery, then GROUP BY user_id in the outer query instead of endpoint, with AVG(latency) per group.)_ - What happens to a user who has only ever made one call? _(They contribute zero rows after the rn > 1 filter, which is correct: a user with a single call has no repeat calls.)_ - Two of a user's calls share the same call_time. Which one is the handshake? _(ROW_NUMBER breaks the tie nondeterministically. Add a tiebreaker such as call_id to ORDER BY so the choice of first call is reproducible.)_ - What if an endpoint only ever shows up as users' first calls? _(That endpoint simply never appears in the rn > 1 set, so it drops out of the result entirely, which is expected.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/after_the_handshake) - [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.