# The Long Wait > Every route runs at its own pace. Find the ones lagging. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The SLO team is auditing platform latency and wants each endpoint's average latency, slowest first. ## Worked solution and explanation ### What this really asks This is a plain per-group average wearing an SLO costume. The skill being probed: can you collapse many rows per endpoint into a single latency number at the right grain, and keep the breakdown the team asked for. Anyone can type AVG(latency). The trick is remembering that the grouping key and the ordering both live in the same query, and that one stray decision quietly changes the shape of the answer. The trap: average everything and you hand back one platform number when they asked which routes are slow. Put endpoint in SELECT but forget it in the grouping and the engine either errors or, worse in a lax dialect, returns an arbitrary endpoint stapled to a global average that looks plausible and is wrong. --- ### The one decision that matters #### Step 1: Group at the endpoint grain Collapse the rows with GROUP BY endpoint and let AVG(latency) run inside each group. That single choice is the difference between a per-route breakdown and a meaningless global scalar. Every non-aggregated column you select must appear in the grouping. #### Step 2: Order so the slow routes surface The team wants the worst offenders on top, so sort by the average descending. Ordering by the aggregate is the natural reading of 'slowest first', and it is what makes the result actionable instead of just correct. **Per-endpoint average, slowest first** ```sql SELECT endpoint, AVG(latency) AS avg_latency FROM api_calls GROUP BY endpoint ORDER BY avg_latency DESC ``` > **Two paths, one route?** > > Grouping is exact-string matching. In this data /api/v1/users and /api/v1/users/ (and the /api/v2/products pair) are treated as different endpoints, so their traffic is split across two rows. If the team considers them the same route, you have to normalize the trailing slash before grouping. Nobody will tell you this in the prompt; spotting it in the data is the senior move. > **NULL latency** > > AVG silently ignores rows where latency is NULL: they drop out of both the sum and the count. That is usually what you want, but say it out loud so the interviewer knows you did not just get lucky. **Global average (the mistake)** SELECT AVG(latency) FROM api_calls returns one number for the whole platform. It answers a question nobody asked and hides the slow routes inside the mean. **Per-endpoint average (the answer)** GROUP BY endpoint keeps one row per route, so the /api/v1/users spike at 37.5 stands out instead of being averaged away against the fast /api/v1/orders calls. > **Cost at 100M rows** > > This is a full-table aggregate: partitioning on call_time buys nothing because there is no time filter to prune on. On a hot dashboard you would back this with a rollup keyed by endpoint refreshed on a schedule, so the interactive query reads thousands of pre-aggregated rows instead of scanning a hundred million. --- ## Common follow-up questions - How would you restrict this to only the last 7 days of calls, and where does that filter go? _(Tests time-window reasoning and where a filter belongs relative to the aggregate.)_ - The SLO is defined on p95 latency, not the mean. How would you compute a per-endpoint p95 instead? _(Tests understanding that mean hides tail behavior, which is what SLOs actually track.)_ - If /api/v1/users and /api/v1/users/ should be one endpoint, how do you fold them together before grouping? _(Tests path normalization and grouping on a derived expression.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_long_wait) - [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.