Slowest Endpoint in Each Service — SQL Problem
The performance guild picks one endpoint per service to optimise next sprint: the endpoint with the highest average latency in that service's request log.
- Difficulty: Medium
- Topics: Window Functions, Aggregation
- Dialect: MySQL
- Problem: #333
Problem statement
The performance guild picks one endpoint per service to optimise next sprint: the endpoint with the highest average latency in that service's request log. If several endpoints of a service share the highest average, all of them are returned.
Return the columns service, endpoint, requests (the endpoint's request count) and avg_latency_ms — its average latency_ms rounded to 2 decimals (compare the unrounded averages). Return the rows in any order.
Tables
Table: ApiRequest
| Column | Type |
|---|---|
| request_id | int |
| service | varchar |
| endpoint | varchar |
| latency_ms | int |
| status_code | int |
Primary key: request_id.
One row per request served. An endpoint path can exist in more than one service.
Examples
Example 1
ApiRequest
| request_id | service | endpoint | latency_ms | status_code |
|---|---|---|---|---|
| 1 | checkout-api | /v1/cart | 120 | 200 |
| 2 | checkout-api | /v1/cart | 180 | 200 |
| 3 | checkout-api | /v1/orders | 340 | 200 |
| 4 | checkout-api | /v1/orders | 260 | 500 |
| 5 | search-svc | /v1/search | 95 | 200 |
| 6 | search-svc | /v1/suggest | 60 | 200 |
| 7 | search-svc | /v1/suggest | 130 | 200 |
| 8 | auth-svc | /v1/login | 210 | 200 |
| 9 | auth-svc | /v1/otp | 150 | 200 |
| 10 | auth-svc | /v1/otp | 270 | 200 |
Output
| service | endpoint | requests | avg_latency_ms |
|---|---|---|---|
| auth-svc | /v1/login | 1 | 210 |
| auth-svc | /v1/otp | 2 | 210 |
| checkout-api | /v1/orders | 2 | 300 |
| search-svc | /v1/search | 1 | 95 |
| search-svc | /v1/suggest | 2 | 95 |
How to solve Slowest Endpoint in Each Service
Two levels of grouping are involved: requests roll up to endpoints, and endpoints compete within a service. So aggregate first — GROUP BY service, endpoint with COUNT(*) and AVG(latency_ms) — and then compare the endpoint rows of each service.
A window does the comparison without another join: RANK() OVER (PARTITION BY service ORDER BY avg_ms DESC) numbers the endpoints of each service from the slowest, and endpoints with the same average share a rank, so rk = 1 returns every endpoint tied for slowest — exactly the tie rule. ROW_NUMBER would silently pick one of the tied endpoints, and MAX(avg_ms) OVER (PARTITION BY service) compared with each row's average (the second alternative) is an equivalent way to keep all of them.
Rounding is applied only to the displayed column, so two averages that differ in the third decimal are not mistaken for a tie. The first alternative is the pre-window approach: keep an endpoint when no endpoint of the same service has a higher average, tested with NOT EXISTS. It compares averages by cross-multiplying totals and counts (o.total * s.requests > s.total * o.requests), so it never compares two divided numbers at all — the safest way to test averages for equality. It is quadratic in the endpoints of a service, which is tiny; the window version aggregates once and sorts each service's handful of endpoints.
Reference solution (MySQL)
WITH stats AS (
SELECT service, endpoint, COUNT(*) AS requests, AVG(latency_ms) AS avg_ms
FROM ApiRequest
GROUP BY service, endpoint
), ranked AS (
SELECT service, endpoint, requests, avg_ms,
RANK() OVER (PARTITION BY service ORDER BY avg_ms DESC) AS rk
FROM stats
)
SELECT service, endpoint, requests, ROUND(avg_ms, 2) AS avg_latency_ms
FROM ranked
WHERE rk = 1Another way
WITH stats AS (SELECT service, endpoint, COUNT(*) AS requests, SUM(latency_ms) AS total_ms FROM ApiRequest GROUP BY service, endpoint)
SELECT s.service, s.endpoint, s.requests, ROUND(s.total_ms / s.requests, 2) AS avg_latency_ms
FROM stats s
WHERE NOT EXISTS (
SELECT 1 FROM stats o
WHERE o.service = s.service AND o.total_ms * s.requests > s.total_ms * o.requests
)Another way
WITH stats AS (SELECT service, endpoint, COUNT(*) AS requests, AVG(latency_ms) AS avg_ms FROM ApiRequest GROUP BY service, endpoint)
SELECT service, endpoint, requests, ROUND(avg_ms, 2) AS avg_latency_ms
FROM (SELECT service, endpoint, requests, avg_ms, MAX(avg_ms) OVER (PARTITION BY service) AS best FROM stats) s
WHERE avg_ms = best← Support Agents With a High Ticket Reopen Rate · Monthly Change Failure Rate of Production Deploys →