Where The Lights Stay On
A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.
- Domain
- SQL
- Difficulty
- medium
- Seniority
- L3
- Asked in
- City and County of San Francisco
Problem
Our SLA targets keep getting set off one fleet-wide uptime number, which hides how differently each region behaves. From the health checks in svc_health, give each region's average service uptime, most reliable first.
Every region promises reliability. See which ones keep it.
Tables
svc_health
Columns: check_id INTEGER, svc_name TEXT, status TEXT, latency REAL, uptime REAL, checked TEXT, region TEXT
| check_id | svc_name | status | latency | uptime | checked | region |
|---|---|---|---|---|---|---|
| 137 | payment-api | degraded | 3.6 | 97.03 | 2026-02-02 01:05:00 | us-west-2 |
| 174 | user-svc | unhealthy | 6.7 | 94.06 | 2026-03-03 02:10:00 | eu-west-1 |
| 211 | search-api | timeout | 9.8 | 91.09 | 2026-04-04 03:15:00 | ap-south-1 |
| 248 | gateway | Healthy | 12.9 | 98.12 | 2026-05-05 04:20:00 | eu-central-1 |
| 285 | notif-svc | DEGRADED | 16 | 95.15 | 2026-06-06 05:25:00 | us-east-1 |
| 322 | ml-serving | healthy | 19.1 | 92.18 | 2026-07-07 06:30:00 | us-west-2 |
| 359 | cache-svc | degraded | NULL | 99.21 | 2026-08-08 07:35:00 | eu-west-1 |
| 396 | queue-worker | unhealthy | 25.3 | 96.24 | 2026-09-09 08:40:00 | ap-south-1 |
| 433 | cron-svc | timeout | 28.4 | 93.27 | 2026-01-10 09:45:00 | eu-central-1 |
| 470 | auth-svc | Healthy | 31.5 | 90.3 | 2026-02-11 10:50:00 | us-east-1 |
Expected output
| region | avg_uptime |
|---|---|
| eu-west-1 | 95.63459459459459 |
| eu-central-1 | 95.36783783783784 |
| us-west-2 | 95.02810810810811 |
| ap-south-1 | 94.01166666666667 |
| us-east-1 | 93.4828947368421 |
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) skips NULLs, and uptime does have missing readings across the full table, so those checks simply drop out of their region's mean instead of dragging it toward zero. The NULL you can see in the sample rows sits in latency, which this metric never reads.
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 avg_uptime puts the steadiest region at the top. If two regions tied, either could come first; on this data the five averages are all different.
The solution
SELECT region, AVG(uptime) AS avg_uptime
FROM svc_health
GROUP BY region
ORDER BY avg_uptime DESCCommon 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.)