Highest Latency 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
- L3
- Asked in
- City and County of San Francisco
Problem
Surface the 3 endpoints straining the API the hardest by peak latency, listing the worst offender first on down to the mildest, and show each endpoint alongside its maximum latency value.
The slowest endpoints. Everyone notices.
Tables
api_calls
Columns: call_id INTEGER, endpoint TEXT, method TEXT, status INTEGER, latency REAL, user_id INTEGER, call_time TEXT, err_msg TEXT
| 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
| endpoint | max_latency |
|---|---|
| /api/v1/search | 1713.7 |
| /api/v2/products | 1696.4 |
| /api/v1/orders | 1679.1 |
Worked solution and explanation
Why this problem exists in real interviews
Finding the top 3 endpoints by peak latency tests aggregation with MAX and LIMIT.
Break down the requirements
Step 1: Find max latency per endpoint
GROUP BY endpoint with MAX(latency) gives peak latency per endpoint.
Step 2: Order and limit
ORDER BY max_latency DESC LIMIT 3 returns the top 3.
The solution
SELECT endpoint, MAX(latency) AS max_latency
FROM api_calls
GROUP BY endpoint
ORDER BY max_latency DESC
LIMIT 3Common follow-up questions
- How would you use p99 instead of MAX? (Tests `PERCENTILE_CONT`.)
- How would you include ties at position 3? (Tests `DENSE_RANK` instead of LIMIT.)
- How would you filter to endpoints with at least 100 calls? (Tests HAVING COUNT(*) >= 100.)