# Where The Lights Stay On > Every region promises reliability. See which ones keep it. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem The SRE team is setting uptime SLA targets and needs to compare regions: give each region's average service uptime, most reliable first. ## Worked solution and explanation ### What this is really asking This is a per-region average wearing an SLA costume. The entire problem is one grouped mean, but two habits sink candidates. The first is collapsing every row into a single fleet-wide number instead of one average per region. The second is reading 'most reliable' as the smallest value and sorting the wrong way. Miss the grouping and you report one meaningless number; miss the direction and you crown your least reliable region as the gold standard. --- ### Build it in two moves #### Step 1: Average uptime within each region Collapse the rows to one per region and take the mean uptime inside each. `AVG(uptime)` computes the per-group average and, importantly, ignores NULLs in the averaged column, so you do not need to guard against missing readings by hand. Note that the NULL in the sample sits in `latency`, not `uptime`, so it never touches this metric. #### Step 2: Order most reliable first The business phrase 'most reliable first' is a descending sort on the average you just computed. Because you now have one row per region, ordering by the average puts the steadiest region at the top. If two regions tie, the order is undefined unless you add a secondary sort key. --- ### The solution **Grouped average with ordering** ```sql SELECT region, AVG(uptime) AS avg_uptime FROM svc_health GROUP BY region ORDER BY avg_uptime DESC ``` > **Why return them all** > > Averaging every region is different from asking for only the single best one. The full ranked list lets the SRE team see the spread between regions, which is what actually informs an SLA target: one number in isolation hides whether the runner-up is a hair behind or a full point back. > **Interviewers watch for** > > The single hottest tell here is sort direction. Candidates who write ORDER BY avg_uptime (ascending by default) silently invert the answer and put the worst region on top. Say 'descending' out loud and make the DESC explicit. > **Common pitfall** > > Averaging uptime across all 10 rows without GROUP BY returns a single scalar and answers a question nobody asked. The grain of the output must match the grain of the ask: one row per region. > **Cost profile** > > Across ~25M rows the aggregation is a single streaming scan that reduces to six region buckets before the sort, so the ORDER BY touches only a handful of rows. There is no join and no subquery to blow up: the plan stays a scan plus a tiny hash aggregate. --- ## Common follow-up questions - Some regions run far more health checks than others. Would a simple average still be a fair comparison? _(Tests whether the candidate weights an average by check volume rather than treating every region as equal.)_ - A region with one catastrophic outage but otherwise perfect uptime could still look average. How would you surface that? _(Tests handling of skewed distributions and outliers in a reliability metric.)_ - How would you turn this into a daily rolling report over the partitioned checked column? _(Tests production mindset: incremental loads, partition pruning on the checked date.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/where_the_lights_stay_on) - [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.