# The Long Tail > Averages hide the pain. The tail is where SLOs live. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: General Assembly ## Problem Some calls in `api_calls` were never timed and carry no `latency`, and the HTTP `method` was logged in inconsistent casing, so 'get' and 'GET' are the same verb. An on-call engineer setting a best-case baseline wants each method's average latency across its five fastest timed calls, rounded to two decimals. ## 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 5 of 5 rows: | method | fastest_five_avg | | --- | --- | | DELETE | 50.94 | | GET | 8.2 | | PATCH | 57.36 | | POST | 24.06 | | PUT | 44.52 | ## Worked solution and explanation ### What this really is Behind the baseline language this is a bottom-five-per-group average over a dirty log. Anyone can number rows inside a method. What separates candidates is noticing that the missing latencies do not politely disappear. Number calls fastest first and an untimed call sorts to the very front of its method, quietly taking slot one. The average then skips it, so you report the mean of four calls while believing it is five. Skip the case fold as well and 'get' and 'post' become phantom methods, each holding a fraction of the real traffic. > **Clean before you number, not after** > > Fold the casing and drop the unmeasured calls first, then number, then keep five, then average. A filter applied after the numbering is too late: the slot is already gone. --- ### Building it #### Step 1: Fold the casing and drop the untimed calls Uppercase `method` so 'get' and 'GET' land in one bucket, and keep only rows where `latency IS NOT NULL`. Every later step reads this cleaned set. #### Step 2: Number each method's calls fastest first `ROW_NUMBER() OVER (PARTITION BY UPPER(method) ORDER BY latency)` gives each method's fastest timed call position 1. Because the NULLs are already gone, positions 1 through 5 are five real measurements. #### Step 3: Keep five and average them Filter to `speed_rank <= 5`, then `GROUP BY method` and take `ROUND(AVG(latency), 2)`. A method with fewer than five timed calls simply averages what it has. --- ### The solution **Five fastest timed calls per method** ```sql WITH timed_calls AS ( SELECT UPPER(method) AS method, latency, ROW_NUMBER() OVER (PARTITION BY UPPER(method) ORDER BY latency) AS speed_rank FROM api_calls WHERE latency IS NOT NULL ) SELECT method, ROUND(AVG(latency), 2) AS fastest_five_avg FROM timed_calls WHERE speed_rank <= 5 GROUP BY method; ``` **No filter on missing latency** An untimed call sorts ahead of every real one, takes position 1, and `AVG(latency)` ignores it. The method's baseline is the mean of four calls, and nothing in the output says so. **Filter first, then number** `WHERE latency IS NOT NULL` runs before `ROW_NUMBER()`, so positions 1 to 5 are always measured calls and the average covers exactly the five fastest. > **NULL placement is an engine choice, not a guarantee** > > Where NULL lands in a sort depends on the engine: some put it first when ascending, others last. A query that works by luck on one engine breaks on the next. Removing unmeasured rows explicitly makes the answer the same everywhere. > **Profiling the data first is the senior move** > > Whether you profile the data before writing anything: spotting the lowercase verbs and the missing latencies, and asking what an untimed call means for a baseline. That is someone who has computed latency metrics on real logs. > **Five survivors per partition should not cost a full sort** > > At 2B rows the cost is the sort behind the window inside each method partition. With only five verbs the partitions are huge, yet five rows per partition survive, so a top-N-per-group plan or a bounded heap beats a full sort. In production you would maintain a small per-method floor incrementally rather than rescanning raw logs per dashboard load. --- ## Common follow-up questions - The team now wants the five slowest calls as well as the five fastest. What changes, and does the missing-latency filter still matter in that direction? _(Tests generalizing the top-N-per-group shape to a parameter and flipping its direction.)_ - Two calls tie for the fifth-fastest latency. Which one does your query keep, and how would you make that choice deliberate? _(Tests understanding of `ROW_NUMBER` when latencies tie at the boundary.)_ - How would you add each method's count of untimed calls without disturbing the baseline average? _(Tests reporting data completeness next to the metric.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_long_tail) - [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.