# Return on Patience > The best answers cost the least time. Find the ones that pay off. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem We score an API endpoint's efficiency as its successful calls (status 200) divided by its average latency, so an endpoint that returns more good responses for less waiting scores higher. For every endpoint with at least 5 calls, return its call count, average latency, and efficiency, most efficient first. ## Worked solution and explanation ### What this really tests This is conditional aggregation wearing an SRE costume. Everyone sees COUNT and AVG and reaches for a GROUP BY. The skill being probed is whether you can express 'successes per unit of latency' as one number inside a single grouped pass, then defend it against two traps: integer division silently flooring your ratio to zero, and a low-volume endpoint with two lucky fast calls hijacking the top of the board. Miss the CAST and every efficiency reads 0.0; miss the call-count floor and your 'most efficient endpoint' is statistical noise. > **Trick to solving** > > The numerator is a conditional count, not a filter. SUM(CASE WHEN status = 200 THEN 1 ELSE 0 END) counts successes while every other row still contributes to COUNT(*) and AVG(latency). Filtering to status = 200 in WHERE would throw away the failed calls you need for the denominator. --- ### Building it #### Step 1: Aggregate every call per endpoint Group by endpoint and, in the same pass, take COUNT(*) for total volume and AVG(latency) for typical wait. Both are computed over all calls, successful or not. #### Step 2: Form the efficiency ratio Count successes with SUM(CASE WHEN status = 200 THEN 1 ELSE 0 END), then divide by AVG(latency). Wrap the numerator in CAST(... AS REAL): in strict engines an integer numerator over a numeric average can floor to 0, and you want the fractional ratio. #### Step 3: Filter noise and rank Apply the at-least-5-calls rule with HAVING COUNT(*) >= 5, because the threshold is on an aggregate that does not exist until after grouping. Then order by efficiency descending with endpoint as the tie-break so the ranking is stable. --- ### The solution **One grouped pass: conditional count over average latency** ```sql SELECT endpoint, COUNT(*) AS call_count, AVG(latency) AS avg_latency, CAST(SUM(CASE WHEN status = 200 THEN 1 ELSE 0 END) AS REAL) / AVG(latency) AS efficiency_ratio FROM api_calls GROUP BY endpoint HAVING COUNT(*) >= 5 ORDER BY efficiency_ratio DESC, endpoint ``` **Wrong: filter in WHERE** WHERE COUNT(*) >= 5 is illegal; row-level predicates cannot see aggregates. **Right: filter in HAVING** HAVING COUNT(*) >= 5 filters after grouping, keeping only high-volume endpoints. > **The integer-division trap** > > Drop the CAST and, in engines with strict integer arithmetic, an integer success count divided by a value can truncate toward zero, so a real 0.39 becomes 0 and your whole leaderboard collapses to ties at zero. Casting the numerator to REAL forces floating-point division. This is the single most common reason a correct-looking query returns all zeros. > **Interviewers watch for** > > The 5-call floor is not decoration. Without it, an endpoint hit twice with sub-millisecond successes shows an enormous efficiency and tops the board on pure noise. Interviewers watch for whether you notice that a ratio metric is meaningless without a minimum sample size, and whether you can articulate why. > **Where the cost goes** > > Over 250M rows the aggregation is the whole cost: one scan, hash-grouped to roughly 130 endpoints, and the HAVING plus ORDER BY run on that tiny grouped output, not the raw table. Because call_time is the partition key and this query spans all endpoints, expect a full scan; if you only needed a recent window, a call_time predicate would prune partitions before the group runs. --- ## Common follow-up questions - The team now wants any 2xx status to count as successful. How does the numerator change, and does the ranking shift? _(Tests whether the candidate can generalize the conditional count to a range predicate and reconsider the success definition.)_ - If some endpoints have null or zero recorded latency, how do you keep the efficiency ratio well-defined? _(Tests awareness of division-by-zero and null propagation in the denominator.)_ - Two endpoints come back with identical efficiency ratios. How do you guarantee a stable, reproducible order across runs? _(Tests understanding of tie behavior and deterministic ordering at scale.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/return_on_patience) - [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.