Average API Latency by Year

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

L3

Asked in

ESPN

Problem

How has each endpoint's average latency in api_calls moved from one calendar year to the next? List every endpoint's unrounded average for every year, earliest year first and endpoints alphabetical within a year.

Latency year over year. Is it getting better?

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 re-bucketing problem dressed up as a latency review. api_calls has no year column: the only clock is call_time, stamped to the second, so the grain the question asks for (one row per year and endpoint) has to be manufactured before anything is averaged. Everyone writes AVG(latency) correctly. What separates candidates is grouping on the derived year instead of the raw timestamp, or dropping the year entirely. Group on call_time and every 'average' is one call's latency. Group on endpoint alone and '/api/v1/auth/login' collapses into one blended number, hiding its climb from 225.9 in 2023 to 1056.3 in 2025.

Build it from the grain down

Step 1: Say the output grain before writing SQL

One row per calendar year per endpoint. Saying that sentence out loud tells you the year is a key you must compute, because nothing in api_calls stores it. Candidates who skip this step reach for whatever date column is lying around and group on it as-is.

Step 2: Derive the year from call_time

strftime('%Y', call_time) turns '2026-02-02 01:07:00' into '2026'. Alias it call_year and group on that alias together with endpoint. The timestamp itself never appears in the GROUP BY; only its year does.

Step 3: Average, then sort by the two keys

AVG(latency) per group gives avg_latency. Sorting by call_year then endpoint lays each year out as a block, so reading one route down the years is how the trend shows up.

SELECT strftime('%Y', call_time) AS call_year, endpoint, AVG(latency) AS avg_latency FROM api_calls GROUP BY call_year, endpoint ORDER BY call_year, endpoint

Blended per endpoint

GROUP BY endpoint returns one row per route. '/api/v1/auth/login' averages its 2023, 2024 and 2025 calls into a single figure, so 'is latency trending up?' has no answer in the result.

Bucketed per year

GROUP BY call_year, endpoint gives that same route 225.9, 641.1 and 1056.3 across those three years. The climb is the whole point of the review, and it only exists at this grain.

Common follow-up questions

• Leadership now wants only routes whose average got worse every single year. How do you compare each year with the one before it? (Moves from building buckets to comparing adjacent buckets within each `endpoint`.)
• The mean can hide a slow tail. How would you report p95 latency per year and endpoint instead? (Tests computing a percentile per group rather than a mean.)
• What changes if stray whitespace in `endpoint` must be cleaned before the routes are bucketed? (Tests normalizing a dirty key before it becomes a grouping key.)
• How would you switch this report to monthly buckets? (Tests seeing that the bucket is just a different key derived from `call_time`.)