# Successful Call Volume per Endpoint

> Not every ping is honest.

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

Domain: SQL · Difficulty: medium · Seniority: L5 · Asked in: PayPal

## Problem

The reliability team is zeroing in on the routes that carry real load: the endpoints handling at least half the call volume of the single busiest route. For those endpoints, count the successful calls, where a successful call has status 200, listed from the most such calls to the fewest and settling any tie alphabetically by endpoint.

## Tables

### api_calls

- call_id INTEGER
- endpoint TEXT
- method TEXT
- status INTEGER
- latency REAL
- user_id INTEGER
- call_time TEXT
- err_msg TEXT

Sample rows (10 of 670):

| call_id | endpoint | method | status | latency | user_id | call_time | err_msg |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 8079 | /api/v1/orders | POST | 200 | 4.2 | 100 | 2026-02-02 01:07:00 | NULL |
| 8158 | /api/v2/products | PUT | 200 | 7.9 | 197 | 2026-03-03 02:14:00 | NULL |
| 8237 | /api/v1/search | DELETE | 201 | 11.6 | 294 | 2026-04-04 03:21:00 | NULL |
| 8316 | /api/v1/auth/login | PATCH | 204 | 15.3 | 391 | 2026-05-05 04:28:00 | NULL |
| 8395 | /api/v1/auth/logout | get | 301 | 19 | NULL | 2026-06-06 05:35:00 | NULL |
| 8474 | /api/v2/analytics | post | 400 | 22.7 | 585 | 2026-07-07 06:42:00 | Not found |
| 8553 | /api/v1/payments | GET | 401 | 26.4 | 682 | 2026-08-08 07:49:00 | Unauthorized |
| 8632 | /api/v1/users/ | POST | 403 | 30.1 | 779 | 2026-09-09 08:56:00 | Rate limit exceeded |
| 8711 | /api/v2/products/ | PUT | 404 | 33.8 | 876 | 2026-01-10 09:03:00 | Internal server error |
| 8790 | /api/v1/users | DELETE | 500 | 37.5 | NULL | 2026-02-11 10:10:00 | Bad gateway |

## Expected output

First 5 of 5 rows:

| endpoint | successful_calls |
| --- | --- |
| /api/v1/users | 116 |
| /api/v1/orders | 113 |
| /api/v2/products | 107 |
| /api/v1/search | 66 |
| /api/v1/auth/login | 51 |

## Worked solution and explanation

### What this really is

Strip the costume and this is a volume filter benchmarked against the busiest route. Roll up each endpoint's total call volume, keep only the routes that carry at least half of what the single busiest route carries, and only then count the status-200 calls for the survivors. Anyone can write the final count. The bar is whether you set the benchmark from the per-endpoint totals (the busiest route's total, a MAX over the rollup) and gate on TOTAL traffic, not successful traffic. Get either wrong and the threshold lands on a different set of routes: you quietly keep endpoints that look busy only because they succeed a lot, and drop the busy-but-flaky ones a reliability team actually cares about.

### The two-stage shape

#### Step 1: Roll up total volume per endpoint

You need each endpoint's TOTAL calls, successes and failures alike, because that is what defines 'busy'. A single pass grouped by endpoint gives you one row per route. Materialize it in a CTE so you compute it once and reuse it twice: once for the busiest-route benchmark and once for the membership test.

#### Step 2: Benchmark against the busiest route

The busiest route's total is MAX(`total_calls`) over the rolled-up CTE, a scalar. Keep the endpoints whose own total is at least half of it. Taking the MAX over the totals, not over raw rows, is what makes the benchmark a route-level figure instead of a per-call one.

#### Step 3: Count only the successes for the survivors

Now scan the raw table again, keep status = 200, restrict to the endpoints that passed the volume gate, and count per endpoint. Order by that count, biggest first, with endpoint as a stable tiebreak so equal counts have one defined order.

> **Compute the rollup once**
>
> The per-endpoint totals feed both the MAX benchmark (a scalar subquery) and the IN membership test. Put them in one CTE and reference it twice instead of scanning and grouping the 50M-row table a second time just to derive the threshold.

> **Benchmark over endpoints, gate on total volume**
>
> MAX(`total_calls`) over the rolled-up CTE is the busiest ENDPOINT's volume. Benchmark against raw rows, or gate on successful-call counts instead of total volume, and the surviving set shifts: high-traffic error-heavy routes fall out and low-traffic routes sneak in. Roll up totals first, then take the MAX.

**Naive: gate on the wrong thing**

Filter endpoints by their successful-call volume, or benchmark against a row-grain figure. Either way the endpoint set is wrong: error-heavy but high-traffic routes fall out, and a row-grain benchmark lets everything through.

**Correct: rollup then gate**

Materialize per-endpoint totals, take the busiest route's total with MAX, keep endpoints at or above half of it, then count only the status-200 rows for that set.

**Successful call volume for the heavy-traffic endpoints**

```sql
WITH endpoint_totals AS (
    SELECT endpoint, COUNT(*) AS total_calls
    FROM api_calls
    GROUP BY endpoint
)
SELECT endpoint, COUNT(*) AS successful_calls
FROM api_calls
WHERE status = 200
  AND endpoint IN (
      SELECT endpoint
      FROM endpoint_totals
      WHERE total_calls * 2 >= (SELECT MAX(total_calls) FROM endpoint_totals)
  )
GROUP BY endpoint
ORDER BY successful_calls DESC, endpoint
```

*One CTE feeds both the MAX benchmark and the membership test; the final count filters to status 200.*

> **Interviewers watch for the grain**
>
> The tell that separates candidates is whether the benchmark is the busiest endpoint's total (a MAX over the rollup) or something computed over raw rows, and whether the volume gate uses total traffic or successful traffic. Say the grain out loud before you write it, and gate on total volume; that is the signal you have actually read the requirement.

> **Why the plan stays cheap**
>
> The table is 50M rows across 365 daily partitions. The `endpoint_totals` CTE collapses to one row per endpoint (a few dozen) before the MAX and the IN test, so the threshold work is negligible. The real cost is one scan to build totals and one scan to count 200s, both on the narrow endpoint and status columns.

## Common follow-up questions

- Return each surviving endpoint's success rate, not just the raw count of 200s. _(Tests combining two aggregates over the same group.)_
- The benchmark is the single busiest route, which one outlier can dominate. Would using the average or median endpoint volume change which routes qualify, and how would you compute a median here? _(Distribution awareness and skew.)_
- How does the plan change if you needed this per day rather than over all time? _(Using the `call_time` partition key.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/successful_call_volume_per_endpoint)
- [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.