Endpoint Ranking

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Problem

How does every endpoint in api_calls stand on average latency for the SRE team's performance review, slowest first, with tied averages sharing a place the way a race result does? A call with no recorded latency still counts toward its endpoint's call total but stays out of the average.

The slowest endpoints. Called to the principal's office.

Tables

api_calls

Columns: call_id INTEGER, endpoint TEXT, method TEXT, status INTEGER, latency REAL, user_id INTEGER, call_time TEXT, err_msg TEXT

Expected output

Worked solution and explanation

This is a leaderboard built on top of an aggregate, dressed up as an SRE review. Two things have to be right at once. The average must come only from calls that were actually timed, while the call count still sees every call. The position must treat equal averages as one place, the way a race result does (1, 1, 3). Anyone can write the GROUP BY. What separates candidates is the numbering: reach for ROW_NUMBER() and two endpoints with the same average get 1 and 2, so one is reported faster than an endpoint it exactly matches. Fold the missing latencies in as zeros, or divide by COUNT(*), and every endpoint with an untimed call looks faster than it is, which can reshuffle the list the review works from.

Two passes: aggregate, then number

Step 1: Collapse api_calls to one row per endpoint

Do the aggregation in a CTE. COUNT(*) counts rows, so a call whose latency is missing still counts. AVG(latency) skips NULL inputs on its own, so the mean is taken over timed calls only. The two numbers deliberately read different row sets, which is why there is no WHERE latency IS NOT NULL: that filter would quietly shrink call_count.

Step 2: Number the endpoints, not the calls

Apply RANK() OVER (ORDER BY avg_latency DESC) to the aggregated rows. Because the window reads endpoint_stats, it orders a dozen endpoint rows instead of 670 raw calls. RANK() gives tied averages the same place and skips the places they used, which is the race-result rule.

Step 3: Return the whole list, slowest first

Sort the result by position. There is no top-N cut, because the review wants every endpoint, and no tiebreaker, because rows that share a position are interchangeable.

WITH endpoint_stats AS (
    SELECT endpoint,
           COUNT(*) AS call_count,
           AVG(latency) AS avg_latency
    FROM api_calls
    GROUP BY endpoint
)
SELECT endpoint,
       call_count,
       avg_latency,
       RANK() OVER (ORDER BY avg_latency DESC) AS position
FROM endpoint_stats
ORDER BY position;

Sequential numbering

ROW_NUMBER() hands out 1, 2, 3 no matter what, so two endpoints tied on avg_latency get different positions in an arbitrary order. DENSE_RANK() shares the place but then closes the gap (1, 1, 2), which overstates how close the next endpoint is to the top.

Race-result numbering

RANK() shares the place and leaves the gap (1, 1, 3). The third endpoint really is third slowest: two endpoints are slower than it. That is the standing the review asked for.

Common follow-up questions

• How would you number endpoints within each `method` instead of across the whole log? (Partitioning the window so the numbering restarts per group.)
• Two endpoints show up with stray spaces, such as '/api/v1/orders '. What changes if those should merge with their clean spelling? (Normalizing the key before grouping moves both the counts and the averages.)
• When would `DENSE_RANK()` be the right choice here? (Whether a reader needs true standings or just tiers.)
• What if the review wanted p95 latency instead of the mean? (Swapping the aggregate while the numbering stays the same.)