Once and Only Once

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

LinkedIn

Problem

On-call is triaging latency anomalies, where a reading that recurs is routine noise but a reading seen a single time is the real signal. For each endpoint, surface the highest such one-off latency, worst first.

Repeated readings are noise. The value seen a single time is the signal.

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 is really testing

Strip the SRE costume off and this is a singleton filter wearing a max. The real question: within each endpoint, which latency values were recorded exactly once, and of those, which is the largest? Anyone can write MAX(latency) grouped by endpoint. The catch is that the loudest latency on an endpoint can be a value that fired more than once, a systemic slow path, not a one-off. Filter for 'exactly once' AFTER you count occurrences, or you will hand on-call a recurring reading and label it an anomaly.

Why a plain MAX fails

Naive: MAX(latency) per endpoint

Takes the tallest reading per endpoint whatever its count. If an endpoint logged 1200 ms twice and 900 ms once, this reports 1200: a recurring slow path, exactly the noise on-call wants to ignore.

Correct: MAX over the one-offs

Drops 1200 first because its count is 2, so the answer for that endpoint is 900. The filter changes which reading wins, not just how it is computed.

Step 1: Count occurrences per endpoint and latency

Count occurrences per endpoint and latency with GROUP BY endpoint, latency. This gives you one group per distinct latency an endpoint saw, along with how many times it fired.

Step 2: Keep the exactly-once values

Keep only the singletons with HAVING COUNT(*) = 1. This is the post-aggregation predicate: it runs on the grouped counts, which is why WHERE cannot express it. What survives is the set of one-off latencies for each endpoint.

Step 3: Take the highest per endpoint

Take the tallest survivor per endpoint with an outer GROUP BY endpoint and MAX(latency), then sort by that max descending so the worst offenders sit on top.

SELECT endpoint, MAX(latency) AS rarest_highest
FROM (
    SELECT endpoint, latency
    FROM api_calls
    GROUP BY endpoint, latency
    HAVING COUNT(*) = 1
) unique_vals
GROUP BY endpoint
ORDER BY rarest_highest DESC

Common follow-up questions

• If 'seen exactly once' meant once across the entire table rather than once per endpoint, how would the query change, and when would the two answers differ? (Tests whether the candidate can rescope the singleton predicate from per-endpoint to global and reason about when the two answers diverge.)
• Suppose the definition softened to 'recorded at most twice' instead of exactly once. What is the one change, and does the shape of the result stay the same? (Probes generalizing a fixed count threshold into a parameterized rarity band.)
• How would you make endpoints with no one-off latency still appear in the output with a NULL, rather than dropping out? (Tests handling of endpoints that produce no qualifying row and whether they should surface with a NULL.)