90th Percentile API Latency per Endpoint — SQL Problem
The SRE dashboard reports each endpoint's p90 latency by the nearest-rank method: sort the endpoint's latencies ascending; the p90 is the value at position…
- Difficulty: Hard
- Topics: Window Functions, Aggregation
- Dialect: MySQL
- Problem: #341
Problem statement
The SRE dashboard reports each endpoint's p90 latency by the nearest-rank method: sort the endpoint's latencies ascending; the p90 is the value at position ⌈0.9 × n⌉ (1-based), where n is the number of latencies. For 10 requests that is the 9th value, for 11 the 10th, for 1 the only one. Requests with latency_ms NULL timed out at the client and are not counted. Endpoints with no counted request are left out.
Return the columns endpoint, requests (the counted requests, n) and p90_latency_ms, ordered by endpoint.
Tables
Table: ApiLatency
| Column | Type |
|---|---|
| request_id | int |
| endpoint | varchar |
| latency_ms | int |
| requested_at | datetime |
Primary key: request_id.
One row per request in the sampling window. latency_ms is NULL when the client gave up waiting.
Examples
Example 1
ApiLatency
| request_id | endpoint | latency_ms | requested_at |
|---|---|---|---|
| 1 | /v1/orders | 120 | 2025-07-14 10:00:00 |
| 2 | /v1/orders | 80 | 2025-07-14 10:00:01 |
| 3 | /v1/orders | 95 | 2025-07-14 10:00:02 |
| 4 | /v1/orders | 400 | 2025-07-14 10:00:03 |
| 5 | /v1/orders | 110 | 2025-07-14 10:00:04 |
| 6 | /v1/orders | NULL | 2025-07-14 10:00:05 |
| 7 | /v1/search | 60 | 2025-07-14 10:00:06 |
| 8 | /v1/search | 60 | 2025-07-14 10:00:07 |
| 9 | /v1/login | 230 | 2025-07-14 10:00:08 |
| 10 | /v1/payments | NULL | 2025-07-14 10:00:09 |
Output
| endpoint | requests | p90_latency_ms |
|---|---|---|
| /v1/login | 1 | 230 |
| /v1/orders | 5 | 400 |
| /v1/search | 2 | 60 |
How to solve 90th Percentile API Latency per Endpoint
Percentiles are an ordering question, so window functions do the heavy lifting. In one pass over the non-NULL latencies, ROW_NUMBER() OVER (PARTITION BY endpoint ORDER BY latency_ms) gives each value its 1-based position and COUNT(*) OVER (PARTITION BY endpoint) puts n on every row. The nearest-rank p90 is the row whose position is ⌈0.9n⌉, written in integer arithmetic as (9 * n + 9) DIV 10 — 9 for n = 10, 10 for n = 11, 1 for n = 1. Computing CEIL(0.9 * n) in floating point is a classic trap: 0.9 is not exact in binary, and a product a hair above an integer rounds up to the wrong position.
Ties in latency are harmless: ROW_NUMBER breaks them in some order, but every candidate for the position holds the same value. NULL latencies are filtered out before the windows run, so they affect neither the positions nor n, and an endpoint whose every request timed out has no rows and drops out.
The first alternative is the definition stated as a filter: the smallest latency v such that at least 90% of the endpoint's values are ≤ v (10 * count(≤ v) >= 9 * n). It is quadratic per endpoint. The second uses CUME_DIST, which is exactly the fraction of values ≤ the current one, and keeps the smallest value at or above 0.9, with a tiny tolerance for the floating-point fraction. The ROW_NUMBER version needs one sort per endpoint.
Reference solution (MySQL)
WITH ranked AS (
SELECT endpoint, latency_ms,
ROW_NUMBER() OVER (PARTITION BY endpoint ORDER BY latency_ms) AS pos,
COUNT(*) OVER (PARTITION BY endpoint) AS n
FROM ApiLatency
WHERE latency_ms IS NOT NULL
)
SELECT endpoint, n AS requests, latency_ms AS p90_latency_ms
FROM ranked
WHERE pos = (9 * n + 9) DIV 10
ORDER BY endpointAnother way
WITH cnt AS (
SELECT endpoint, COUNT(*) AS n FROM ApiLatency WHERE latency_ms IS NOT NULL GROUP BY endpoint
)
SELECT c.endpoint, c.n AS requests, MIN(a.latency_ms) AS p90_latency_ms
FROM cnt c
JOIN ApiLatency a ON a.endpoint = c.endpoint AND a.latency_ms IS NOT NULL
WHERE 10 * (SELECT COUNT(*) FROM ApiLatency b WHERE b.endpoint = a.endpoint AND b.latency_ms <= a.latency_ms) >= 9 * c.n
GROUP BY c.endpoint, c.n
ORDER BY c.endpointAnother way
WITH ranked AS (
SELECT endpoint, latency_ms,
CUME_DIST() OVER (PARTITION BY endpoint ORDER BY latency_ms) AS cd,
COUNT(*) OVER (PARTITION BY endpoint) AS n
FROM ApiLatency WHERE latency_ms IS NOT NULL
)
SELECT endpoint, MIN(n) AS requests, MIN(latency_ms) AS p90_latency_ms
FROM ranked
WHERE cd * 10 >= 9 - 0.0000001
GROUP BY endpoint
ORDER BY endpoint← Peak Ticket Hour for Each Day of the Week · Longest Streak of Clean Deploys per Service →