Second Highest Latency by Method

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

L4

Asked in

KPMG

Problem

On-call keeps finding that the single slowest endpoint for each HTTP method in api_calls is a one-off outlier, so the API platform team wants each method's runner-up instead: the endpoint, and its latency, at the second-highest latency logged for that method, with verbs the log wrote in lowercase ('get', 'post') counted under their uppercase name. If several endpoints tie for a method's slowest latency, the runner-up is the next lower latency, and every endpoint sitting at it belongs on the list.

Almost the slowest. By method.

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

This is a runner-up lookup dressed up as latency monitoring: inside each HTTP method, find the second-highest latency value and every endpoint sitting at it. Anyone can sort by latency. What separates candidates is whether 'second' means the second row or the second value, whether the ordering restarts per method, and whether 'get' and 'GET' are one method. Order the whole table and you get one call (1696.4 on a 'GET') instead of five rows. Leave the casing alone and the 'get' call at 1661.8 sits in its own group, so GET's runner-up drops to 1575.3. Count rows instead of values, and the first time two endpoints tie for a method's slowest, you report a slowest endpoint as the runner-up.

Walking to the answer

Step 1: Fold the spelling before anything else

The log writes some verbs as 'get' and 'post'. UPPER(method) in the calls CTE turns them into 'GET' and 'POST' once, so every later step partitions and reports the folded name. Skip it and GET's true runner-up, a lowercase call at 1661.8, never competes with the other GET calls.

Step 2: Restart the ordering inside each method

The runner-up is per method, so the ordering has to reset at every method boundary: PARTITION BY method, over the folded name. Leave it out and the second-slowest call in the entire log wins, one row for the whole report.

Step 3: Number latency values, not rows

The statement's tie sentence is a definition: a shared slowest latency is still first place, and the next lower value is second, with every endpoint at it included. DENSE_RANK() is exactly that rule: equal latencies share a number, and the next value gets the next number with no gap.

Step 4: Filter after the numbers exist

A window result cannot be filtered in the WHERE of the SELECT that computes it, so the numbering lives in the ranked CTE and the outer query keeps rnk = 2, returning only the three columns the report needs.

WITH calls AS (
    SELECT UPPER(method) AS method, endpoint, latency
    FROM api_calls
),
ranked AS (
    SELECT method, endpoint, latency,
           DENSE_RANK() OVER (PARTITION BY method ORDER BY latency DESC) AS rnk
    FROM calls
)
SELECT method, endpoint, latency
FROM ranked
WHERE rnk = 2

Counting rows

ROW_NUMBER() gives tied rows different numbers, so with two endpoints tied for slowest, row 2 is still a slowest endpoint. RANK() gives both 1 and then jumps to 3, so position 2 never exists and the method vanishes.

Counting values

DENSE_RANK() gives both tied endpoints 1 and the next lower latency 2, keeping every endpoint at that value. It is the only one of the three that matches the statement's tie sentence.

Common follow-up questions

• Some endpoints are logged with a trailing slash, like '/api/v1/users/'. Should they fold into '/api/v1/users', and does the answer move? (Spotting a second dirty key and checking whether it actually reaches the result.)
• Return the third-slowest instead. What comes back for a method with only two latency values? (Whether the candidate sees that missing ranks silently drop a method.)
• With 200 million rows and most of them 'GET', how would you avoid sorting one giant partition? (Skew awareness and the two-pass aggregate alternative.)
• Write it without a window function. (The correlated max-below-the-max, joined back to recover every endpoint at that value.)