# Top Regions by Effective Uptime

> The most reliable regions.

Canonical URL: <https://datadriven.io/problems/top_regions_by_effective_uptime>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The SRE dashboard tracks effective uptime by region. Effective uptime hours equals uptime minus (latency / 10); treat negative results as zero. Round to 2 decimal places. Return the top 3 regions by total effective uptime hours, where tied regions share the same rank. Show region and total effective hours.

## Worked solution and explanation

### Why this problem exists in real interviews

Two formulas that look identical produce different numbers here: flooring `uptime - latency/10` per row and then summing is not the same as summing first and flooring once. A bad checkout latency on one probe should not eat the credit from a good probe an hour later. Getting the order of operations right on `svc_health` is the entire problem.

---

### Break down the requirements

#### Step 1: Per-row effective hours

Compute `MAX(uptime - latency/10.0, 0)` for each probe. Use `10.0` so integer `latency` does not truncate.

#### Step 2: Aggregate per region

Sum the per-row values grouped by `region`. The ROUND inside the SUM matches the spec phrasing of rounding the per-row term.

#### Step 3: Rank with ties

`DENSE_RANK` over the descending region totals, then filter to rnk <= 3 so a 3-way tie at #3 still returns three regions, not gets cut to two.

---

### The solution

**TOP REGIONS BY EFFECTIVE UPTIME**

```sql
WITH effective AS (
  SELECT region,
         SUM(ROUND(MAX(uptime - (latency / 10.0), 0), 2)) AS total_effective_hours
  FROM svc_health
  GROUP BY region
),
ranked AS (
  SELECT region,
         total_effective_hours,
         DENSE_RANK() OVER (ORDER BY total_effective_hours DESC) AS rnk
  FROM effective
)
SELECT region, ROUND(total_effective_hours, 2) AS total_effective_hours
FROM ranked
WHERE rnk <= 3
```

> **Cost Analysis**
>
> 30M rows, no date filter. The `checked` partitioning buys nothing here because every partition contributes to the regional totals. A pre-aggregated `region_uptime_daily` rollup would turn this into a small scan; without one, expect a full table read and a hash group by region cardinality.

> **Interviewers Watch For**
>
> Whether you floor per row or per region. Watch for someone writing `MAX(SUM(uptime) - SUM(latency)/10, 0)` and call out that it lets a single 10000ms latency spike erase real uptime hours from a quiet hour. Order of operations is the whole question.

> **Common Pitfall**
>
> Using `RANK` instead of `DENSE_RANK`. With `RANK`, a 3-way tie at #1 jumps the next region to rank 4 and you return only the three tied rows. The spec says top 3 regions sharing rank, so `DENSE_RANK` matches the wording.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would you make this incremental so the SRE dashboard refreshes hourly without rescanning 30M rows? _(Probes whether you can design a rollup table keyed on (region, checked_hour) and a merge strategy.)_
- What if `latency` is sometimes NULL because the probe timed out before recording it? _(Tests whether you reason about NULL propagation in arithmetic and pick a deliberate substitute (COALESCE to a penalty constant vs zero).)_
- Could you do this in a single query without CTEs, and would you? _(Checks whether you can produce a window-and-filter one-shot and articulate the readability tradeoff.)_

> **Why `10.0` and not `10`**
>
> If `latency` is INTEGER, `latency / 10` truncates toward zero in most engines. A latency of 7 becomes 0, not 0.7, and your effective hours overstate by the truncation residue. The decimal literal forces a float promotion.

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/top_regions_by_effective_uptime)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.