Return on Patience

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

L4

Asked in

General Assembly

Problem

Return the call count, success rate, average latency, and efficiency of every endpoint in the api_calls log with at least 5 calls, most efficient first. Only a status 200 counts as a success, and efficiency is an endpoint's successful calls divided by its average latency, so more good responses for less waiting scores higher.

The best answers cost the least time. Find the ones that pay off.

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 really tests

This is conditional aggregation wearing an SRE costume. Every column is computed over all of an endpoint's calls, while one ingredient counts only the status 200 subset, so the success test has to live inside the aggregate, not in WHERE. Then there is the quiet one: success_rate is an integer count over an integer count. Divide them as written and the engine does integer division. Every endpoint on this log has at least one failed call, so every success rate reads 0. Anyone gets the GROUP BY; what separates candidates is knowing which division needs help and which does not.

Building it

Step 1: Aggregate every call per endpoint

Group by endpoint and take COUNT(*) for volume and AVG(latency) for typical wait. AVG skips the null latencies on its own, while COUNT(*) still counts those calls.

Step 2: Count successes inside the aggregate

SUM(CASE WHEN status = 200 THEN 1 ELSE 0 END) is the success count. A 201 or 204 is not a success here, so test for 200 exactly rather than a 2xx range.

Step 3: Force a real fraction for the success rate

The success count over COUNT(*) is integer over integer, so wrap the numerator in CAST(... AS REAL). The efficiency division needs no cast: AVG(latency) is already a real, so dividing by it keeps the fraction.

Step 4: Drop thin endpoints and sort

HAVING COUNT(*) >= 5 filters groups, because the threshold is on an aggregate that does not exist until after grouping. Then sort by efficiency_ratio DESC.

The solution

SELECT endpoint,
       COUNT(*) AS call_count,
       CAST(SUM(CASE WHEN status = 200 THEN 1 ELSE 0 END) AS REAL) / COUNT(*) AS success_rate,
       AVG(latency) AS avg_latency,
       SUM(CASE WHEN status = 200 THEN 1 ELSE 0 END) / AVG(latency) AS efficiency_ratio
FROM api_calls
GROUP BY endpoint
HAVING COUNT(*) >= 5
ORDER BY efficiency_ratio DESC

Wrong: filter successes first

WHERE status = 200 before GROUP BY makes call_count the success count, success_rate 1.0 everywhere, and avg_latency an average of successful calls only.

Right: count successes in the aggregate

SUM(CASE WHEN status = 200 THEN 1 ELSE 0 END) counts successes while COUNT(*) and AVG(latency) still see every call.

Common follow-up questions

• The team now wants any 2xx status to count as a success. How do the success count and the order change? (Generalizing the conditional count to a range predicate.)
• Two `endpoint` values on this log carry stray whitespace and fall under the 5-call floor. How would you fold them into their real endpoint, and which counts move? (Noticing dirty grouping keys before trusting a per-key metric.)
• If every call to an endpoint has a `NULL` latency, what does `efficiency_ratio` become, and how would you keep it well-defined? (Null propagation in the denominator.)