The Slow Lane

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

Asked in

City and County of San Francisco

Problem

Find the highest single-request latency each endpoint has hit in March, counting every March in api_calls regardless of year, and list the endpoints from the highest peak down to the lowest. For the performance team's spike review, paths that differ only by stray spaces around them count as the same endpoint.

Peak API load. The slow endpoints.

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 recurring-calendar filter wearing a latency report's clothes. 'March of any year' is not a stretch of timeline: it is every row whose month field reads '03', in 2023 as much as 2026. Anyone can write MAX(latency) per endpoint. What separates candidates is pulling the month out of call_time instead of typing BETWEEN '2026-03-01' AND '2026-03-31'. Write the range and you quietly answer for one year: '/api/v1/orders' falls from its 1160.1 spike in 2025 to 41.2, and the whole leaderboard reshuffles.

Three decisions, in order

Step 1: Pull the month out of call_time

The filter is the whole problem. strftime('%m', call_time) = '03' keeps every March row from every year and nothing from late February or early April. A date range can only describe one contiguous stretch, so it either picks one year or, widened to cover several, sweeps in every month in between.

Step 2: Trim the path before you group

One March call is logged as '/api/v1/orders ' with a trailing space. Group on the raw string and the output grows a phantom endpoint at 41.2 beside the real '/api/v1/orders'. TRIM(endpoint) in both the select list and the GROUP BY folds it back in. Stop there: '/api/v1/users/' is a different route from '/api/v1/users', and stray spaces are all the statement forgives.

Step 3: Take the peak, then sort

MAX(latency) per trimmed endpoint, aliased max_latency, then ORDER BY max_latency DESC. MAX skips NULLs, which matters here: a March call to '/api/v1/auth/logout' has no recorded latency and simply drops out of its group instead of erasing the 1540.7 peak.

SELECT
    TRIM(endpoint) AS endpoint,
    MAX(latency) AS max_latency
FROM api_calls
WHERE strftime('%m', call_time) = '03'
GROUP BY TRIM(endpoint)
ORDER BY max_latency DESC

One year's March

call_time BETWEEN '2026-03-01' AND '2026-03-31' sees only 2026. '/api/v1/orders' reports 41.2, '/api/v2/products' 7.9, '/api/v1/search' 307.6: three of the five biggest spikes vanish.

Every March

strftime('%m', call_time) = '03' sees every year. The same three endpoints report 1160.1, 969.8 and 779.5, their real worst Marches.

Common follow-up questions

• The team now wants only endpoints whose first call landed in 2026. Where does that filter go? (The first call is a `MIN(call_time)` over all months, so it cannot share the March `WHERE`; tests filter placement.)
• Production latency is in microseconds and the team wants milliseconds. Where do you convert, and why? (Query, view or storage schema: tests where type and unit transformations belong.)
• How would you also return the `user_id` and `call_time` of the slowest request per endpoint? (Forces the move from `GROUP BY` plus `MAX` to a per-endpoint `ROW_NUMBER()`.)
• An endpoint has only NULL `latency` in March. Does it appear, and where does it sort? (`MAX` returns NULL; SQLite puts NULLs last under `DESC`, Postgres needs `NULLS LAST` spelled out.)
• The query takes 8 minutes on the partitioned table. What changes? (Not an index: a rollup keyed by endpoint, year and month.)