Successful Call Volume per Endpoint

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

L5

Asked in

PayPal

Problem

Most of the gateway traffic logged in api_calls piles onto a few heavy routes, and the reliability team wants to know how much of that load actually succeeds. Count the successful calls, those with status 200, for every endpoint handling at least half the call volume of the single busiest route, most successes first.

Not every ping is honest.

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 costume and this is a group filter whose bar comes from the groups themselves. Roll up each endpoint's total traffic, take the busiest route's total, keep only routes carrying at least half of it, and only then count the status-200 calls for the survivors. Anyone can write the final count. The separation is which volume sets the bar: gate on successes instead of total calls and '/api/v1/auth/login' disappears, because its 51 successes sit under half of the top route's 116 while its total traffic easily clears the real bar. That is the route with the 401 storms, the one a reliability team most needs on the page.

The two-stage shape

Step 1: Roll up total volume per endpoint

Each endpoint's TOTAL calls, failures included, is what defines busy. One pass over api_calls grouped by endpoint gives one row per route. Stage it as endpoint_totals so it is computed once and read twice: for the benchmark and for the membership test.

Step 2: Benchmark against the busiest route

The busiest route's total is MAX(total_calls) over endpoint_totals, a single value. Keep the endpoints where total_calls * 2 >= that value. Taking the max over the rollup, not over raw rows, is what makes the bar a route-level figure; doubling the left side keeps the comparison in integers.

Step 3: Count only the successes for the survivors

Scan api_calls again, keep status = 200, restrict to endpoints that passed the gate, and count per endpoint. Sort by successful_calls descending; the grader checks the rows are in that order and nothing else.

Naive: gate on the wrong volume

Filter endpoints by their count of status = 200 rows, or compare against a row-grain figure. The error-heavy login route falls out, or nothing is filtered at all.

Correct: rollup, then gate

Build endpoint_totals, take MAX(total_calls), keep endpoints at or above half of it, then count only the status = 200 rows for that set.

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

Common follow-up questions

• Return each surviving endpoint's success rate, not just the raw count of 200s. (Two aggregates over the same group.)
• One outlier route can dominate the busiest-route bar. Would the median endpoint volume change which routes qualify, and how would you compute it? (Distribution awareness and skew.)
• How does the plan change if this were needed per day, using the `call_time` partition key? (Partition pruning and a per-day grain.)