# Top 10 Slowest Endpoints

> The ten endpoints nobody wants to call.

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

Domain: SQL · Difficulty: easy · Seniority: L4

## Problem

For the quarterly performance review, rank endpoints by their peak single-request latency in 2025. Ties should share the same rank. Include all endpoints ranked in the top 10, even if ties push the count beyond 10, and show the endpoint with its peak latency.

## Worked solution and explanation

### Why this problem exists in real interviews

SRE teams need a no-gaps top-10 for tail latency by endpoint, scoped to a specific year, so on-call engineers can compare year over year. Interviewers use this prompt to see whether you can extract the year from a TEXT timestamp with strftime, aggregate MAX(latency) per endpoint, and apply DENSE_RANK so ties are retained without gaps.

---

### Break down the requirements

#### Step 1: Scope to the previous year using strftime

call_time is stored as TEXT, so strftime('%Y', call_time) = '2026' carves the slice. Comparing a string ('2026') against the strftime result keeps the predicate text-vs-text and avoids implicit casts.

#### Step 2: Aggregate MAX(latency) per endpoint

GROUP BY endpoint and project MAX(latency) AS max_latency. MAX is a streaming aggregate so it does not need to materialize all latencies per endpoint, which matters at 150M rows.

#### Step 3: DENSE_RANK and slice to rnk <= 10

Apply DENSE_RANK() OVER (ORDER BY MAX(latency) DESC) inside the same query as the GROUP BY (window functions can reference aggregates). Wrap in a subquery and filter rnk <= 10 in the outer query, then ORDER BY rnk ASC, endpoint ASC for the deterministic tie-break.

---

### The solution

**strftime year filter, MAX per endpoint, DENSE_RANK top 10**

```sql
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
```

> **Cost Analysis**
>
> api_calls is 150,000,000 rows. The strftime filter on call_time is non-sargable, so without an index this is a full scan. A real production system would store year as a generated column or partition by call_time so a year filter becomes a partition prune. After filtering, the per-endpoint MAX is a hash aggregate; the cardinality of endpoint is small, so the aggregate state stays in memory and the rank step is cheap.

> **Interviewers Watch For**
>
> They want strftime('%Y', call_time) (this is SQLite, not Postgres EXTRACT), and they want DENSE_RANK rather than ROW_NUMBER or RANK. The explicit tie-break ORDER BY rnk ASC, endpoint ASC is checked because 'top 10 slowest endpoints' often produces ties on max_latency for round numbers like 1000ms.

> **Common Pitfall**
>
> Writing EXTRACT(YEAR FROM call_time) or DATE_PART('year', call_time) is the Postgres reflex and fails in SQLite. Comparing strftime('%Y', call_time) = 2026 (integer literal) is also wrong: strftime returns text, and SQLite's type affinity rules can make the comparison silently mismatch. Always quote the year as a string.

---

## Common follow-up questions

- How would you make this scale on a real 150M-row table? _(Tests partitioning intuition: store call_time as a date partition column, or pre-aggregate per (endpoint, day) into a rollup table so the year query reads thousands of rows instead of millions.)_
- What if the spec wanted p99 latency instead of MAX? _(SQLite has no PERCENTILE_CONT, so the candidate should reach for an approximate p99 via ROW_NUMBER + COUNT or NTILE. This is a meaningful follow-up because MAX is sensitive to one bad request.)_
- How would you compare year over year for the same endpoints? _(Forces a join of two yearly aggregates on endpoint, plus a discussion of which endpoints exist in only one year and how to handle those (LEFT JOIN with NULL handling).)_

## Related

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