# Where the Signal Drops > Every endpoint answers. Some just answer wrong. Find the ones dropping the most. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: mid ## Problem On-call has been tracking API reliability and wants to know which endpoints are failing most often. Look only at endpoints with at least 20 calls, and for each one report its share of error responses (a 4xx or 5xx status), worst first. ## Worked solution and explanation ### Why this problem exists in real interviews This is conditional aggregation wearing a reliability costume. The metric is a ratio whose numerator (failed calls) and denominator (all calls) come from the SAME rows, so both have to survive into the aggregate. Anyone can COUNT and divide. The tell the interviewer is watching for: do you filter the error rows in WHERE, or do you keep every row and count the failures with a CASE? Filter them out and the denominator collapses to failures only, so every endpoint reports 100% and the whole ranking is meaningless. --- ### Break down the requirements #### Step 1: Keep every call in scope Do not put status >= 400 in WHERE. The denominator is total traffic, so all calls per endpoint must reach the GROUP BY. The error condition belongs inside the aggregate, not in the row filter. #### Step 2: Count failures with a CASE SUM(CASE WHEN status >= 400 THEN 1 ELSE 0 END) counts 4xx and 5xx responses while COUNT(*) counts everything. Same rows, two different tallies: that is the whole trick. #### Step 3: Guard the sample size HAVING COUNT(*) >= 20 drops thin-traffic endpoints where a single failure would read as a scary rate. HAVING (not WHERE) because the threshold is on the group's row count, computed after grouping. #### Step 4: Order worst first ORDER BY error_rate DESC surfaces the flakiest endpoints on top. The trailing endpoint tie-break just keeps the output stable when two rates match. Multiply the ratio by 100.0 (float) so integer division does not silently floor everything to 0. --- ### The solution **ERROR RATE PER ENDPOINT** ```sql SELECT endpoint, COUNT(*) AS total_calls, SUM(CASE WHEN status >= 400 THEN 1 ELSE 0 END) AS error_calls, ROUND(100.0 * SUM(CASE WHEN status >= 400 THEN 1 ELSE 0 END) / COUNT(*), 2) AS error_rate FROM api_calls GROUP BY endpoint HAVING COUNT(*) >= 20 ORDER BY error_rate DESC, endpoint ``` **Naive (wrong)** WHERE status >= 400 ... GROUP BY endpoint. Now COUNT(*) counts only failures, so error_rate is 100% for every endpoint that appears, and clean endpoints vanish entirely. The ranking is destroyed. **Correct** No status filter in WHERE. COUNT(*) is total traffic; SUM(CASE ...) is the failures. The ratio is a true error percentage and endpoints with zero failures still show up at 0.00. > **Common Pitfall** > > Two classics on this exact shape. First, pushing status >= 400 into WHERE, which turns the denominator into failures-only. Second, writing SUM(...) / COUNT(*) with integer operands: in SQLite that is integer division and every rate rounds to 0. Force a float with 100.0 * ... before dividing. > **Interviewers Watch For** > > Whether you reach for conditional aggregation without being told, and whether you volunteer the sample-size guard ('a 1-in-1 failure is not a 100% incident'). Strong candidates also ask whether near-duplicate endpoints like /api/v1/users and /api/v1/users/ should be folded together before rating them. > **Cost Analysis** > > On a real request log this is billions of rows, but it is a single streaming pass: hash-aggregate by endpoint (a few hundred groups), tally two counters per group, filter groups on the count, sort a tiny result. No self-join, no subquery. Partition pruning on call_time makes a windowed version (last 7 days) cheap, but the all-time version here still scans once. --- ## Common follow-up questions - Restrict this to the last 7 days of traffic. Where does the call_time predicate go, and why does that one belong in WHERE? _(Tests understanding that a row-level time filter is correct in WHERE, unlike the error condition; also partition-pruning awareness.)_ - Separate 4xx (client errors) from 5xx (server errors) in the same result. How? _(Pushes toward two independent CASE sums over the same scan instead of two queries.)_ - Endpoints like /api/v1/users and /api/v1/users/ are logically the same route. How would you normalize before rating? _(Surfaces data-hygiene instincts: trimming trailing slashes / casing before GROUP BY so the rate is not split across variants.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/where-the-signal-drops) - [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.