Slowest Endpoint in Each Service

Medium
333.

Slowest Endpoint in Each Service

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

ColumnType
request_idint
servicevarchar
endpointvarchar
latency_msint
status_codeint

Primary key: request_id.

One row per request served. An endpoint path can exist in more than one service.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

ApiRequest

request_idserviceendpointlatency_msstatus_code
1checkout-api/v1/cart120200
2checkout-api/v1/cart180200
3checkout-api/v1/orders340200
4checkout-api/v1/orders260500
5search-svc/v1/search95200
6search-svc/v1/suggest60200
7search-svc/v1/suggest130200
8auth-svc/v1/login210200
9auth-svc/v1/otp150200
10auth-svc/v1/otp270200

Output

serviceendpointrequestsavg_latency_ms
auth-svc/v1/login1210
auth-svc/v1/otp2210
checkout-api/v1/orders2300
search-svc/v1/search195
search-svc/v1/suggest295
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.

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

ColumnType
request_idint
servicevarchar
endpointvarchar
latency_msint
status_codeint

Primary key: request_id.

One row per request served. An endpoint path can exist in more than one service.

Examples

Example 1

ApiRequest

request_idserviceendpointlatency_msstatus_code
1checkout-api/v1/cart120200
2checkout-api/v1/cart180200
3checkout-api/v1/orders340200
4checkout-api/v1/orders260500
5search-svc/v1/search95200
6search-svc/v1/suggest60200
7search-svc/v1/suggest130200
8auth-svc/v1/login210200
9auth-svc/v1/otp150200
10auth-svc/v1/otp270200

Output

serviceendpointrequestsavg_latency_ms
auth-svc/v1/login1210
auth-svc/v1/otp2210
checkout-api/v1/orders2300
search-svc/v1/search195
search-svc/v1/suggest295

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 = 1

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