Top 10 Slowest Endpoints

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L4

Asked in

ESPN

Problem

List every endpoint whose peak single-request latency in api_calls is among the ten highest peaks of 2026, with that peak and its place on the list, slowest first. The on-call lead reading it wants no endpoint cut for sharing a peak with another, even when that pushes the list past ten rows.

The ten endpoints nobody wants to call.

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

Ten places, not ten rows

This is a top-N over distinct values dressed up as a latency review. Everyone gets the easy part right: filter call_time to 2026, take MAX(latency) per endpoint. What separates candidates is reading "the ten highest peaks" as ten places rather than ten rows. Write the sort-and-cut-at-ten reflex and you drop whichever endpoint tied at the boundary, which is the endpoint the review was convened to catch.

Build it in three moves

Step 1: Scope the calls to 2026

call_time is stored as text, so strftime('%Y', call_time) = '2026' keeps only this year's calls. Both sides are text, so no implicit cast decides whether a row matches.

Step 2: Reduce each endpoint to its peak

GROUP BY endpoint with MAX(latency) AS max_latency collapses each endpoint to its single worst request. The aggregate keeps one running value per endpoint, so a hundred endpoints cost a hundred slots of memory no matter how many calls there were.

Step 3: Number the places, then keep the first ten

DENSE_RANK() OVER (ORDER BY MAX(latency) DESC) can sit in the same SELECT as the GROUP BY because the window runs after aggregation. Tied peaks get one place and the next peak takes the next number. Wrap it, keep rnk <= 10, and sort by rnk with endpoint breaking ties so the output is stable.

The solution

SELECT endpoint, max_latency, rnk FROM (
  SELECT endpoint, MAX(latency) AS max_latency,
         DENSE_RANK() OVER (ORDER BY MAX(latency) DESC) AS rnk
  FROM api_calls
  WHERE strftime('%Y', call_time) = '2026'
  GROUP BY endpoint
) ranked
WHERE rnk <= 10
ORDER BY rnk ASC, endpoint ASC

Cut at ten rows

Sorting the peaks descending and keeping ten rows with LIMIT 10 stops at a row count. Two endpoints tied at the cutoff means one vanishes, and a tie higher up means the tenth distinct peak never appears.

Cut at ten places

DENSE_RANK() hands tied peaks one shared rnk, and rnk <= 10 keeps every endpoint holding one of the ten highest peaks, even if that is twelve rows.

Common follow-up questions

• How would you make this cheap on the full 150M-row table? (Filter `call_time` by range so partitions prune, or keep a daily rollup of peak latency per `endpoint`.)
• What if the review wanted p99 latency instead of the single worst request? (One outlier dominates `MAX(latency)`; a percentile per `endpoint` is the steadier signal.)
• How would you show which endpoints entered or left the list since last year? (Compare two yearly lists on `endpoint` and decide what an endpoint present in only one year means.)