Endpoint Performance Report

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

Tata Consultancy Services

Problem

Surface the total call count, the average latency rounded to two decimals, and how many calls exceeded the 500ms SLA for each high-traffic endpoint in api_calls, meaning one that handled at least 80 calls. Some paths were logged with stray spaces or a trailing slash, and those calls belong to the same endpoint as the clean path.

Every endpoint has a speed and a reliability story.

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 is

Strip the reliability-report costume and this is one grouped pass that computes four numbers per endpoint, then judges each endpoint by its own volume. Anyone can write COUNT and AVG. What separates candidates is three decisions: which key the groups form on, whether the 500ms breaches are tallied without filtering the faster calls out of the total, and whether the 80-call floor reads the group's own count after grouping. Group on the raw string and '/api/v1/users/' siphons nine calls away from '/api/v1/users'. Filter slow calls in WHERE and total_calls collapses to just the breaches. Put the floor in WHERE and there is no count to test yet.

Break down the requirements

Step 1: Clean the path before grouping

endpoint was logged inconsistently: '/api/v1/orders ' carries a trailing space, ' /api/v2/products' a leading one, and '/api/v1/users/' a trailing slash. RTRIM(TRIM(endpoint), '/') strips the padding first and the slash second, so a padded path that also ends in a slash still loses it, and every spelling lands on one key.

Step 2: Count every call

COUNT(*) counts every row in the group, including calls with no recorded latency, which is what a call total means. COUNT(latency) would quietly drop them.

Step 3: Average only the recorded latencies

ROUND(AVG(latency), 2) skips null latencies, so the total and the average draw from different populations by design: the total includes null-latency calls, the average does not.

Step 4: Tally breaches inside the aggregate

SUM(CASE WHEN latency > 500 THEN 1 ELSE 0 END) marks the breaches while every other call stays in the group. A call at exactly 500ms meets the SLA and is not counted; a null latency falls through to ELSE 0.

Step 5: Filter the groups with HAVING

HAVING COUNT(*) >= 80 runs after grouping, on the cleaned key, so the stray spellings add their calls before the endpoint is judged. No row order is promised, so there is no ORDER BY.

The solution

SELECT RTRIM(TRIM(endpoint), '/') AS endpoint,
       COUNT(*) AS total_calls,
       ROUND(AVG(latency), 2) AS avg_latency,
       SUM(CASE WHEN latency > 500 THEN 1 ELSE 0 END) AS high_latency_count
FROM api_calls
GROUP BY RTRIM(TRIM(endpoint), '/')
HAVING COUNT(*) >= 80

Group on the raw string

GROUP BY endpoint gives '/api/v1/users' 135 calls and strands the nine '/api/v1/users/' calls in a small group that the floor throws away. Every busy endpoint is undercounted, and its average describes only part of its traffic.

Group on the cleaned path

GROUP BY RTRIM(TRIM(endpoint), '/') folds every spelling in: '/api/v1/users' reaches 144 calls, '/api/v2/products' 134 and '/api/v1/orders' 133, and the averages move with them.

Common follow-up questions

• How would you add a p99 latency column? (Tests `PERCENTILE_CONT` or an approximate percentile function, and why exact percentiles are expensive at scale.)
• What if the review only wanted endpoints with more than 100 calls? (Tests adjusting the `HAVING` threshold and getting `>` versus `>=` right.)
• How would you compute these per hour instead of over all time? (Tests truncating `call_time` to the hour and adding it to the grouping key.)