90th Percentile API Latency per Endpoint

Hard
341.

90th Percentile API Latency per Endpoint

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

ColumnType
request_idint
endpointvarchar
latency_msint
requested_atdatetime

Primary key: request_id.

One row per request in the sampling window. latency_ms is NULL when the client gave up waiting.

Example

The rows must come back in the order shown.

ApiLatency

request_idendpointlatency_msrequested_at
1/v1/orders1202025-07-14 10:00:00
2/v1/orders802025-07-14 10:00:01
3/v1/orders952025-07-14 10:00:02
4/v1/orders4002025-07-14 10:00:03
5/v1/orders1102025-07-14 10:00:04
6/v1/ordersNULL2025-07-14 10:00:05
7/v1/search602025-07-14 10:00:06
8/v1/search602025-07-14 10:00:07
9/v1/login2302025-07-14 10:00:08
10/v1/paymentsNULL2025-07-14 10:00:09

Output

endpointrequestsp90_latency_ms
/v1/login1230
/v1/orders5400
/v1/search260
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
request_idint
endpointvarchar
latency_msint
requested_atdatetime

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_idendpointlatency_msrequested_at
1/v1/orders1202025-07-14 10:00:00
2/v1/orders802025-07-14 10:00:01
3/v1/orders952025-07-14 10:00:02
4/v1/orders4002025-07-14 10:00:03
5/v1/orders1102025-07-14 10:00:04
6/v1/ordersNULL2025-07-14 10:00:05
7/v1/search602025-07-14 10:00:06
8/v1/search602025-07-14 10:00:07
9/v1/login2302025-07-14 10:00:08
10/v1/paymentsNULL2025-07-14 10:00:09

Output

endpointrequestsp90_latency_ms
/v1/login1230
/v1/orders5400
/v1/search260

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 endpoint

Another 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.endpoint

Another 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 →