# Endpoint Performance Report

> Every endpoint has a speed and a reliability story.

Canonical URL: <https://datadriven.io/problems/endpoint_performance_report>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

Next week's reliability review needs endpoint stats. For each endpoint with at least 5 calls, surface the total call count, average latency, and the number of calls that exceeded the 500ms SLA threshold. Round to 2 decimal places.

## Worked solution and explanation

### Why this problem exists in real interviews

Performance reports aggregate multiple metrics per dimension. This tests computing `AVG`, `COUNT`, `MAX`, and conditional success rates in a single `GROUP BY` query.

---

### Break down the requirements

#### Step 1: Group by endpoint

Each endpoint gets one row. `GROUP BY endpoint` sets the output grain.

#### Step 2: Compute multiple metrics

In a single SELECT: `COUNT(*)` for total calls, `AVG(latency)` for average latency, and `AVG(CASE WHEN status < 400 THEN 1.0 ELSE 0.0 END)` for success rate.

#### Step 3: Filter for minimum call count

`HAVING COUNT(*) >= 5` ensures statistical relevance.

---

### The solution

**Multi-metric aggregation per endpoint**

```sql
SELECT endpoint,
       COUNT(*) AS total_calls,
       ROUND(AVG(latency), 2) AS avg_latency,
       MAX(latency) AS max_latency,
       ROUND(AVG(CASE WHEN status < 400 THEN 1.0 ELSE 0.0 END) * 100, 1) AS success_rate_pct
FROM api_calls
GROUP BY endpoint
HAVING COUNT(*) >= 5
ORDER BY avg_latency DESC
```

> **Cost Analysis**
>
> Single scan with hash aggregation. All metrics computed in one pass. A composite index on `(endpoint, latency, status)` covers the query.

> **Interviewers Watch For**
>
> Candidates who compute success rate using `CASE` inside `AVG` show they understand conditional aggregation, replacing multiple CTEs.

> **Common Pitfall**
>
> Computing success rate as `COUNT(success) / COUNT(*)` does not work because COUNT does not filter by value. Use `SUM(CASE ...)` or `AVG(CASE ...)`.

---

## Common follow-up questions

- How would you add a p99 latency column? _(Tests PERCENTILE_CONT or approximate percentile functions.)_
- What if you only wanted endpoints with more than 100 calls? _(Tests adjusting the HAVING threshold.)_
- How would you compute these per hour instead of globally? _(Tests date truncation with GROUP BY.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/endpoint_performance_report)
- [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.