# Where You Stand > Each service versus its region's average. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L4 ## Problem The reliability team wants each service's latency sitting next to the baseline for its region. For every health check, return the service name, region, its latency, and the average latency across all services in that same region. ## Worked solution and explanation ### What this problem is really about This is a per-row comparison against a group mean, dressed up as latency monitoring. The real question: can you hang each region's average latency on every service row without collapsing the rows? Everyone reaches for AVG. The tell is choosing a window over the region instead of a GROUP BY that would crush the ten input rows down to five and throw away each service's own latency. Reach for GROUP BY here and you lose exactly the column the prompt asks you to keep. --- ### Break down the requirements #### Step 1: Attach the regional average Compute the baseline with AVG(latency) OVER (PARTITION BY region). Because it is a window, every service in a region sees the same average while keeping its own row and its own latency value. #### Step 2: Shape and order the output Select svc_name, region, latency, and the windowed regional average, then order by region and service so equal-region rows sit together the way the preview shows. --- ### The solution **Window AVG for per-row deviation from the regional mean** ```sql SELECT svc_name, region, latency, AVG(latency) OVER (PARTITION BY region) AS avg_region_latency FROM svc_health ORDER BY region, svc_name, latency ``` > **One pass, not two** > > The window computes each region's average in a single pass over svc_health. The alternative, a self-join back to a grouped subquery, would scan the 25M-row table twice; the window keeps it to one scan. > **Interviewers Watch For** > > Recognizing that the result needs one row per health check, not one per region, is the signal that a window belongs here instead of GROUP BY. Saying that out loud before writing the query shows you reason about the output grain, not just syntax. > **Common Pitfall** > > AVG silently skips NULL latencies, so a region with a missing reading averages only its recorded values. Candidates who assume NULL counts as zero will report a lower baseline than the grader expects. --- ## Common follow-up questions - What does your regional average return for a region whose latency values are all NULL? _(Tests whether the candidate knows AVG excludes NULLs and can reason about the edge where a whole region has none.)_ - How would this query change if you computed the regional average with a GROUP BY subquery and joined it back, and what does that cost you? _(Tests understanding of how the window plan differs from a grouped self-join at scale.)_ - How would you also return each service's latency as a percentage of its regional average? _(Tests extending the window to relative comparison rather than just the raw average.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/where_you_stand) - [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.