# The Shape of Waiting > Every request waits its turn, and some wait far longer than the rest. Map where the patience runs out. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L4 ## Problem We're running a latency review across the API and want to see how each endpoint's response times spread out rather than collapse into a single average. Split every endpoint's calls into four equal-sized tiers running from fastest to slowest, and for each endpoint-tier report the fastest, slowest, and average latency, skipping any call that has no recorded latency. ## Worked solution and explanation ### What this is really testing This is per-endpoint quantile bucketing wearing a performance-review costume. The real question: can you split each endpoint's latencies into four equal-count tiers and then summarize each tier, without letting the two grains collide? Anyone can reach for NTILE. The trap is where you put it. The tiering has to happen per endpoint, over non-null latencies, inside a subquery, and only then do you aggregate. Bucket across the whole table instead of per endpoint, or leave NULL latencies in the sort, and every boundary shifts underneath you. > **Two passes, not one** > > Do the tiering and the aggregation in two separate passes. The inner pass assigns each row a bucket with a window function; the outer pass collapses each bucket into MIN, MAX, and AVG. Trying to do both at once mixes a per-row window with a per-group aggregate and the grain falls apart. --- ### Building it #### Step 1: Tier each endpoint's calls Inner query: for each endpoint, order its calls by latency and hand each row a tier with NTILE(4) OVER (PARTITION BY endpoint ORDER BY latency). PARTITION BY endpoint is what makes tier 1 mean the fastest quarter of THAT endpoint, not the fastest quarter of the whole API. Filter WHERE latency IS NOT NULL here so calls with no latency never enter the ordering. #### Step 2: Summarize each tier Outer query: GROUP BY endpoint, bucket and compute MIN(latency), MAX(latency), AVG(latency). Now you are aggregating at the endpoint-tier grain, which is coarser than the per-row grain the window produced. MIN and MAX give you the tier boundaries; AVG gives the tier center. #### Step 3: Order the output ORDER BY endpoint, bucket produces the deterministic sequence the preview shows: endpoint name ascending, then tier 1 through 4 within each endpoint. Because bucket is an integer 1 to 4, no secondary tie-break is needed. --- ### The solution **Per-endpoint latency tiers** ```sql WITH tiered AS ( SELECT endpoint, latency, NTILE(4) OVER (PARTITION BY endpoint ORDER BY latency) AS bucket FROM api_calls WHERE latency IS NOT NULL ) SELECT endpoint, bucket, MIN(latency) AS min_latency, MAX(latency) AS max_latency, AVG(latency) AS avg_latency FROM tiered GROUP BY endpoint, bucket ORDER BY endpoint, bucket ``` > **Common pitfall** > > Leaving NULL latencies in the inner query is the quiet killer. NULLs sort to the front under ORDER BY latency, so they get swept into bucket 1, which pushes every tier boundary and quietly corrupts MIN and the boundaries of all four tiers. Filter them before NTILE ever sees them. **Global NTILE (wrong grain)** NTILE(4) OVER (ORDER BY latency) with no partition tiers the ENTIRE table at once. A slow endpoint's fastest calls can land in tier 3 or 4 because they are slow relative to the whole API. The tiers no longer describe any single endpoint. **Per-endpoint NTILE (correct)** NTILE(4) OVER (PARTITION BY endpoint ORDER BY latency) resets the tiering at each endpoint boundary, so tier 1 is always that endpoint's own fastest quarter. This is what makes the per-endpoint comparison meaningful. > **Interviewers watch for** > > Say out loud that equal-sized means equal COUNT, not equal latency RANGE, and that NTILE drops any remainder into the lowest-numbered tiers first. Candidates who reach for width-based bucketing (dividing max-min by four) are answering a different question, and the tell is whether they notice the distinction before writing SQL. > **Cost at scale** > > At 500M rows the cost lives in the sort NTILE requires: O(n log n), done per endpoint partition. There is no cheaper exact path, but the table is partitioned by call_time across 730 daily partitions, so scoping the review to a date range prunes partitions and shrinks the sort input dramatically. The window is the expensive step; the GROUP BY that follows only ever sees the already-tiered rows. --- ## Common follow-up questions - If api_calls were sharded across many databases by user_id, how would you compute per-endpoint tiers without pulling every row to one node? _(Tests whether the candidate knows NTILE bucketing does not translate cleanly to a scatter-gather split.)_ - The team now wants the p50, p95, and p99 latency per endpoint instead of tier summaries. How does your query change? _(Tests understanding of percentile semantics versus equal-count bucketing.)_ - If this fed a dashboard refreshing every five minutes, could you make it incremental, and where does that break down for quantiles? _(Tests awareness of incremental computation and why quantiles resist it.)_ - One endpoint carries 90% of all traffic. What does that do to the per-partition sort, and how would you mitigate it? _(Tests data-skew reasoning on sort-based window operations.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_shape_of_waiting) - [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.