# Longest Uptime Streak

> Pass, pass, pass. How long until fail?

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

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

Find the longest consecutive streak of 'healthy' statuses (case-insensitive) for any single service. A streak ends when the service records a non-healthy check. Return the service name and streak length.

## Worked solution and explanation

### Why this problem exists in real interviews

SRE teams need to quantify reliability as 'longest run of consecutive healthy checks' because that maps directly to incident-free uptime. Interviewers use this prompt to test whether you can apply the gaps-and-islands trick: subtract two ROW_NUMBERs to collapse consecutive matching rows into a single group key.

---

### Break down the requirements

#### Step 1: Number every row per service in time order

ROW_NUMBER() OVER (PARTITION BY svc_name ORDER BY checked) gives rn, the global per-service position. This includes every row regardless of status.

#### Step 2: Number only the healthy rows per service

Filter to LOWER(status) = 'healthy' (case-insensitive per the prompt) and assign pass_rn = ROW_NUMBER() over the same partition and order. Healthy runs separated by an unhealthy row will have a jump in rn but pass_rn keeps incrementing.

#### Step 3: rn minus pass_rn is the streak group key

Within a single contiguous healthy run, both counters advance by 1 each step, so rn - pass_rn stays constant. As soon as an unhealthy row breaks the run, rn jumps but pass_rn does not, so the next healthy row gets a new group key. GROUP BY (svc_name, rn - pass_rn) and COUNT(*) gives the streak length.

---

### The solution

**Two ROW_NUMBERs, subtract for the island key, count and rank**

```sql
WITH ordered AS (
  SELECT svc_name, status, checked, ROW_NUMBER() OVER (PARTITION BY svc_name ORDER BY checked) AS rn FROM svc_health
),
healthy_only AS (
  SELECT svc_name, rn, ROW_NUMBER() OVER (PARTITION BY svc_name ORDER BY rn) AS pass_rn
  FROM ordered WHERE LOWER(status) = 'healthy'
),
streaks AS (
  SELECT svc_name, rn - pass_rn AS grp, COUNT(*) AS streak_len
  FROM healthy_only GROUP BY svc_name, rn - pass_rn
)
SELECT svc_name, streak_len FROM streaks
ORDER BY streak_len DESC, svc_name ASC
LIMIT 1
```

> **Cost Analysis**
>
> svc_health is 60M rows across only 200 services, so each partition averages 300k rows. The two ROW_NUMBER windows need (svc_name, checked) order; an index on that pair eliminates the sort. The final ORDER BY runs over at most a few thousand groups.

> **Interviewers Watch For**
>
> Did you use LOWER(status) = 'healthy' to handle case variation, use rn - pass_rn (not rn + pass_rn or just rn), and tie-break alphabetically by svc_name? Candidates who use LAG to detect transitions and a running SUM also work but are more error-prone than gaps-and-islands.

> **Common Pitfall**
>
> The classic mistake is to filter WHERE status = 'healthy' before any window, which destroys the rn that includes unhealthy rows. You need both ROW_NUMBERs in the same ordering, and only the second one filters; subtracting them is what reveals the islands.

---

## Common follow-up questions

- What if 'healthy' had to mean status='healthy' AND latency < 500? _(Replace the WHERE in healthy_only with the compound predicate. The gaps-and-islands trick generalizes to any boolean condition.)_
- How would you find the longest UNHEALTHY streak instead? _(Same query with the predicate flipped. Or compute every streak by status with GROUP BY (svc_name, status, rn - pass_rn) and pick the unhealthy max.)_
- How do you handle missing checks (gaps in the timestamp series)? _(Gaps-and-islands counts consecutive ROWS, not consecutive TIME. If the prompt meant 'consecutive minutes', you'd need to detect time gaps with LAG and treat them as breaks too.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/longest_uptime_streak)
- [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.