# Between Two Extremes > Ignore the loudest and the quietest. The truth lives in the middle. Canonical URL: 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. ## Tables ### api_calls - call_id INTEGER - endpoint TEXT - method TEXT - status INTEGER - latency REAL - user_id INTEGER - call_time TEXT - err_msg TEXT Sample rows (10 of 670): | call_id | endpoint | method | status | latency | user_id | call_time | err_msg | | --- | --- | --- | --- | --- | --- | --- | --- | | 8079 | /api/v1/orders | POST | 200 | 4.2 | 100 | 2026-02-02 01:07:00 | NULL | | 8158 | /api/v2/products | PUT | 200 | 7.9 | 197 | 2026-03-03 02:14:00 | NULL | | 8237 | /api/v1/search | DELETE | 201 | 11.6 | 294 | 2026-04-04 03:21:00 | NULL | | 8316 | /api/v1/auth/login | PATCH | 204 | 15.3 | 391 | 2026-05-05 04:28:00 | NULL | | 8395 | /api/v1/auth/logout | get | 301 | 19 | NULL | 2026-06-06 05:35:00 | NULL | | 8474 | /api/v2/analytics | post | 400 | 22.7 | 585 | 2026-07-07 06:42:00 | Not found | | 8553 | /api/v1/payments | GET | 401 | 26.4 | 682 | 2026-08-08 07:49:00 | Unauthorized | | 8632 | /api/v1/users/ | POST | 403 | 30.1 | 779 | 2026-09-09 08:56:00 | Rate limit exceeded | | 8711 | /api/v2/products/ | PUT | 404 | 33.8 | 876 | 2026-01-10 09:03:00 | Internal server error | | 8790 | /api/v1/users | DELETE | 500 | 37.5 | NULL | 2026-02-11 10:10:00 | Bad gateway | ## Expected output First 1 of 1 rows: | min_latency | max_latency | sum_between | | --- | --- | --- | | 1 | 1713.7 | 197499.4 | ## 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`. **Global bounds from scalar subqueries, summed strictly inside them** ```sql 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. **The one-pass shortcut** ```sql SELECT MIN(latency) AS min_latency, MAX(latency) AS max_latency, SUM(latency) - MIN(latency) - MAX(latency) AS sum_between FROM api_calls ``` *Matches here only because each extreme appears once; the first duplicated min or max breaks it.* > **A filtered aggregate reports the wrong ends** > > Putting `MIN(latency)` and `MAX(latency)` in the same `SELECT` as the filtered `SUM` gives the interior's own floor and ceiling, 4.2 and 1696.4 here. The query looks complete and the numbers look plausible, which is exactly why it ships. > **Say out loud where the NULLs go** > > Six calls have no `latency`. Strong candidates note unprompted that `MIN`, `MAX` and `SUM` skip them, and that `NULL > x` is never true, so they cannot leak into `sum_between`. > **Repeated subqueries are not repeated work** > > Uncorrelated scalar subqueries are evaluated once and cached, so the four copies cost two lookups plus one scan for the sum. At hundreds of millions of rows, an index on `latency` turns each bound into a single seek, and the outer scan is the only real cost. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/between_two_extremes) - [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.