Between Two Extremes

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 profiling latency across every call in api_calls, where the lowest and highest latency mark the ends of the range and any call sitting exactly at either end counts as outside it. Return the overall min and max alongside the total latency of the calls strictly inside that range.

Ignore the loudest and the quietest. The truth lives in the middle.

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 two aggregates over two different row sets, dressed up as a latency report. The boundary has to come from every call in api_calls, and the sum has to come only from the calls strictly inside it. Anyone can write SUM(latency) with a filter. What separates candidates is refusing to let the filtered rows also supply the boundary. Aggregate the interior alone and the report says 4.2 and 1696.4 instead of 1.0 and 1713.7. Loosen the bounds to inclusive and both extremes flow back into the sum, 1714.7 too high.

Fix the boundary before you sum

Step 1: Pin the extremes on the whole table

(SELECT MIN(latency) FROM api_calls) and (SELECT MAX(latency) FROM api_calls) read every row, untouched by any filter. They are uncorrelated, so each is a single value the outer query can reuse anywhere.

Step 2: Keep only the calls strictly inside

latency > min AND latency < max compares each row against the value, not against one row, so every call at the floor or ceiling drops out, however many there are. The 6 calls with a NULL latency fail both comparisons and vanish without a separate guard.

Step 3: Report the boundary, not the interior

The outer FROM now sees only interior rows, so an outer MIN(latency) would answer a different question. Project the same scalar subqueries as min_latency and max_latency, and sum the filtered rows as sum_between.

SELECT (SELECT MIN(latency) FROM api_calls) AS min_latency, (SELECT MAX(latency) FROM api_calls) AS max_latency, SUM(latency) AS sum_between
FROM api_calls
WHERE latency > (SELECT MIN(latency) FROM api_calls) AND latency < (SELECT MAX(latency) FROM api_calls)

Subtract one copy of each extreme

SUM(latency) - MIN(latency) - MAX(latency) removes exactly one row's worth of each extreme. On this seed, 1.0 and 1713.7 each occur once, so it happens to match. On a production log where thousands of calls share the floor value, every extra copy stays in the total.

Filter every row at an extreme

latency > ... AND latency < ... tests each row against the value, so every call tied at the floor or ceiling leaves together. The answer stays right no matter how the extremes repeat.

SELECT MIN(latency) AS min_latency,
       MAX(latency) AS max_latency,
       SUM(latency) - MIN(latency) - MAX(latency) AS sum_between
FROM api_calls

Common follow-up questions

• If many calls share the exact minimum `latency`, does your query drop all of them or only one? (Value-based exclusion versus subtracting one copy.)
• What does `sum_between` return when every `latency` is identical, so min equals max? (Nothing is strictly inside, so the sum is `NULL` rather than zero.)
• Could you get the same answer in one pass with `MIN(latency) OVER ()` and `MAX(latency) OVER ()`, and what does that cost? (Window aggregates over the full table versus cached scalar lookups.)