Pairwise Latency Maximum

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

Deloitte

Problem

We're studying how call latencies in api_calls compare head to head, so every call with a recorded latency is paired with every such call, itself included, showing both call ids, both latencies and whichever latency is larger. Return just the first 100 pairs, smallest first by the first latency and then by the second, and where two pairs share both latencies the one with the lower second call id comes first.

Every pair compared.

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 really is

This is a Cartesian self-product dressed up as latency analysis. Nothing links the two copies of api_calls on a key: every call meets every call, itself included, and the only arithmetic is a larger-of-two inside one row. The word 'maximum' is the trap. Reach for MAX() and roughly 441,000 pairs collapse into one number. Reach for a keyed or self-excluding pairing and the diagonal pair (1.0, 1.0) that heads the expected output vanishes. The quieter second trap is the cut: latencies repeat, so the 100th and 101st pairs can share both values, and only the second call id decides which one makes the list.

Getting there

Step 1: Pair api_calls with itself, no condition

CROSS JOIN api_calls to itself under two aliases, first_call and second_call. There is no join condition because there is no relationship to express. 'With replacement' means the call with the smallest latency is paired with itself, and that diagonal row is the first line of the answer.

Step 2: Filter latency IS NOT NULL on both sides

Six calls never recorded a latency. Filter both aliases. If you skip either side, those calls either sort ahead of everything in an ascending result (engines that put NULL first) or produce rows whose larger value is NULL. Either way they push real pairs out of the 100.

Step 3: Compare within the row

CASE WHEN first_call.latency >= second_call.latency THEN first_call.latency ELSE second_call.latency END reads two columns of the same row. GREATEST() says the same thing where the engine has it. On the diagonal both branches give the same number; everywhere else in the first 100 the second latency is the larger one.

Step 4: Sort, then break the tie the cut lands on

Order by latency_1, then latency_2, then call_id_2, all ascending, then LIMIT 100. The smallest latency belongs to a single call, so every surviving row shares latency_1. latency_2 repeats across many calls, so without call_id_2 two correct queries can put different calls at row 100.

SELECT first_call.call_id AS call_id_1,
       first_call.latency AS latency_1,
       second_call.call_id AS call_id_2,
       second_call.latency AS latency_2,
       CASE
           WHEN first_call.latency >= second_call.latency THEN first_call.latency
           ELSE second_call.latency
       END AS max_latency
FROM api_calls AS first_call
CROSS JOIN api_calls AS second_call
WHERE first_call.latency IS NOT NULL
  AND second_call.latency IS NOT NULL
ORDER BY latency_1, latency_2, call_id_2
LIMIT 100

Aggregate maximum

MAX(latency) compares down a column, across rows: one number for the whole set, and selecting latency_1 next to it either errors or forces a GROUP BY the question never asked for.

Row-wise maximum

CASE WHEN first_call.latency >= second_call.latency ... compares across two columns of one pair: one value per row, 100 rows out, every pair intact.

Common follow-up questions

• How would you return each unordered pair once, diagonal included? (Tests swapping the unrestricted product for `first_call.call_id <= second_call.call_id` and predicting the new row count.)
• At 150 million calls, how do you produce these same 100 rows without building the product? (Tests noticing that only the minimum `latency_1` can appear, so the answer is that call crossed with the 100 smallest latencies.)
• What if a missing latency should count as zero instead of dropping the pair? (Tests `COALESCE` placement and how `NULL` flows through `CASE` and `GREATEST` in different engines.)