# Once and Only Once > Repeated readings are noise. The value seen a single time is the signal. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L4 ## 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. ## 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 is usually 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 the worst recurring endpoint and label it an anomaly. > **Trick to solving** > > 'Seen exactly once' is a predicate on a COUNT, not on a row, so you cannot reach it with WHERE. Count occurrences per (endpoint, latency) first, keep the groups whose count is 1, then take the max latency per endpoint. Two aggregations stacked, not one. --- ### Why a plain MAX fails **Naive: MAX(latency) per endpoint** For /api/v1/search the tallest reading is 37.5, so this returns 37.5. But 37.5 was recorded twice, a recurring slow path, exactly the noise on-call wants to ignore. **Correct: MAX over the one-offs** After dropping 37.5 (count 2), the only exactly-once value left for /api/v1/search is 11.6, so the correct answer is 11.6. The filter changes the row, not just the number. #### 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 order by that max descending so the worst offenders sit on top. **Max of the one-off latencies per endpoint** ```sql 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 pitfall** > > The classic miss is reaching for WHERE COUNT(*) = 1, which the engine rejects because the count does not exist until after grouping. The subtler miss is collapsing with DISTINCT or grouping by latency alone, which loses the endpoint and silently answers a different question. Group by both keys, filter on the count, then aggregate again. > **Interviewers watch for** > > The tell is whether you see the two-pass structure immediately: aggregate to counts, filter the counts, aggregate again. Candidates who try to do it in a single pass, or who forget that the biggest latency is often the repeated one, are the ones who ship the wrong row and never notice. > **Performance insight** > > At 500M rows the inner aggregation over (endpoint, latency) is the whole cost, and there is no time predicate here so partition pruning on `call_time` buys nothing. With latency cardinality near 3M the grouping hashes to a large but bounded set; a covering index on (endpoint, latency) lets the engine stream the counts and skip random heap I/O. If this feeds a dashboard, precompute the per-endpoint one-off maxes in a summary table rather than rescanning 128GB per refresh. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/once_and_only_once) - [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.