The Long Tail

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

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.

Averages hide the pain. The tail is where SLOs live.

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

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.

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

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.

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.)