The Quiet Outlier

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

Problem

Surface the largest latency outlier in api_calls, judging each exact latency value by how many calls logged it. A value logged only once could be a measurement glitch and one logged three or more times is ordinary traffic; the outliers are the values in between.

Ignore what the traffic does all day. Find the spike that barely showed up.

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 asks

This is a frequency filter wearing an SRE badge. Each exact latency value is judged by how many calls logged it, only the values logged exactly twice count as outliers, and the answer is the largest of those. Anyone can write MAX(latency). What separates candidates is pinning the count at the value grain and reading the band correctly: a value logged once is a possible glitch, not an outlier. Get that wrong and you hand back 1713.7, a one-off reading the definition throws out, instead of 241.3.

The seed makes the trap bite. The top of the latency range is a long tail of one-offs: 1713.7, 1696.4 and their neighbours were each logged a single time. Keep singletons and they win every time, which is why a bare MAX(latency) and HAVING COUNT(*) <= 2 land on the same wrong number.

Step 1: Collapse to one row per latency value

GROUP BY latency turns the call log into one row per exact reading, and COUNT(*) inside each group is the number of calls that hit that value. This grain shift is the whole problem: frequency belongs to the value, not to any single call, so no row-level filter can see it.

Step 2: Keep only values logged exactly twice

HAVING COUNT(*) = 2 is the band the statement describes: more than once rules out the glitch, fewer than three rules out normal traffic. Watch 248.4: it looks twice-seen inside the February traffic, but a third call logged it earlier, so it drops out. Park the survivors in a CTE called twice_seen.

Step 3: Return the largest survivor as one value

Read the answer back with a scalar subquery, (SELECT MAX(latency) FROM twice_seen). It yields exactly one row and one column, so it is the single metric the question wants. The six calls with no latency form their own group of six and never pass the filter, and MAX would ignore them anyway.

WITH twice_seen AS (
  SELECT latency
  FROM api_calls
  GROUP BY latency
  HAVING COUNT(*) = 2
)
SELECT (
  SELECT MAX(latency)
  FROM twice_seen
) AS largest_outlier_latency

Reads outlier as rare

HAVING COUNT(*) <= 2, or no count at all and a straight MAX(latency). Both return 1713.7, a reading logged once.

Reads the band exactly

HAVING COUNT(*) = 2 in the twice_seen CTE, then MAX(latency) over the survivors. Returns 241.3, the largest value two calls agree on.

Common follow-up questions

• How would you also return which endpoints logged the winning value? (Tests joining the scalar result back to `api_calls` without re-deriving the count.)
• What if latency were stored with more decimals, so near-identical readings never match exactly? (Checks whether they would bucket with `ROUND` before counting and how that shifts the band.)
• How would you return the top three outliers instead of one? (Pushes them from a scalar to an ordered, limited set over `twice_seen`.)