# Average API Latency by Year > Latency year over year. Is it getting better? Canonical URL: 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. ## Tables ### api_calls - call_id INTEGER - endpoint TEXT - method TEXT - status INTEGER - latency REAL - user_id INTEGER - call_time TEXT - err_msg TEXT Sample rows (10 of 670): | call_id | endpoint | method | status | latency | user_id | call_time | err_msg | | --- | --- | --- | --- | --- | --- | --- | --- | | 8079 | /api/v1/orders | POST | 200 | 4.2 | 100 | 2026-02-02 01:07:00 | NULL | | 8158 | /api/v2/products | PUT | 200 | 7.9 | 197 | 2026-03-03 02:14:00 | NULL | | 8237 | /api/v1/search | DELETE | 201 | 11.6 | 294 | 2026-04-04 03:21:00 | NULL | | 8316 | /api/v1/auth/login | PATCH | 204 | 15.3 | 391 | 2026-05-05 04:28:00 | NULL | | 8395 | /api/v1/auth/logout | get | 301 | 19 | NULL | 2026-06-06 05:35:00 | NULL | | 8474 | /api/v2/analytics | post | 400 | 22.7 | 585 | 2026-07-07 06:42:00 | Not found | | 8553 | /api/v1/payments | GET | 401 | 26.4 | 682 | 2026-08-08 07:49:00 | Unauthorized | | 8632 | /api/v1/users/ | POST | 403 | 30.1 | 779 | 2026-09-09 08:56:00 | Rate limit exceeded | | 8711 | /api/v2/products/ | PUT | 404 | 33.8 | 876 | 2026-01-10 09:03:00 | Internal server error | | 8790 | /api/v1/users | DELETE | 500 | 37.5 | NULL | 2026-02-11 10:10:00 | Bad gateway | ## Expected output First 5 of 30 rows: | call_year | endpoint | avg_latency | | --- | --- | --- | | 2023 | /api/v1/auth/login | 225.9 | | 2023 | /api/v1/auth/logout | 243.2 | | 2024 | /api/v1/auth/login | 641.1 | | 2025 | /api/v1/auth/login | 1056.3 | | 2026 | /api/v2/products | 81.9 | ## 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. **Average latency per year and endpoint** ```sql 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. > **A raw timestamp is not a bucket** > > Grouping on `call_time` runs without complaint. It just returns roughly one row per call, because almost no two calls share a second, and each `avg_latency` is a single call's latency. If your row count is close to the table's, your grain is wrong. > **Missing latencies drop out on their own** > > Six calls in `api_calls` have a NULL `latency`, all in 2026. `AVG(latency)` leaves them out of both the sum and the count, which is right: a call whose timing was never captured is not a zero-millisecond call. Wrapping it in `COALESCE(latency, 0)` would drag those groups down. > **Stray whitespace splits a route** > > In 2026 the result carries '/api/v1/orders ' and ' /api/v2/products' as rows of their own: two calls were logged with padding in `endpoint`. This report shows routes as stored. In production you would clean that key upstream, before one padded call shows up as a separate endpoint on a leadership slide. > **Filter the timestamp, never the derived year** > > `api_calls` is partitioned on `call_time`. A year filter written as `strftime('%Y', call_time) = '2026'` hides the column inside a function and scans every partition; a range on `call_time` itself prunes to that year. This all-years report has no filter, so one scan is the cost, and the `GROUP BY` collapses it to a few dozen rows. ## 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`.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/average_api_latency_by_year) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.