Where the Signal Drops

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

mid

Asked in

Verizon

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.

Every endpoint answers. Some just answer wrong. Find the ones dropping the most.

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

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

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