# The Quiet Outlier

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

Canonical URL: <https://datadriven.io/problems/the_quiet_outlier>

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.

## 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:

| largest_outlier_latency |
| --- |
| 241.3 |

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

**Largest latency logged exactly twice**

```sql
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
```

*Count per value in the CTE, keep the twice-seen values, then read the extreme back as one scalar.*

> **`<= 2` lets the glitches back in**
>
> The most common miss is reading 'outlier' as 'rare' and writing `HAVING COUNT(*) <= 2`. That readmits every singleton, and on this table the biggest singleton is also the biggest reading overall, so the filter changes nothing and you return 1713.7 with no error to warn you.

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

> **The count lives at the value grain**
>
> Strong candidates say out loud that frequency is a property of the latency value across the whole table, so it needs a grouped count and `HAVING`, not `WHERE`. The ones who try to squeeze `COUNT(*)` into a `WHERE`, or take the max first and filter second, show they have not internalized when aggregates exist in the evaluation order.

> **One grouped scan, then a tiny max**
>
> This is a single hash aggregate over `api_calls` followed by a `MAX` over the handful of twice-seen values. There is no self-join and no per-row correlated lookup, so it stays cheap at tens of millions of calls.

## 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`.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_quiet_outlier)
- [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.