Longest Uptime Streak

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L5

Asked in

AXA

Problem

An on-call engineer suspects the reliability board rewards services for sheer volume of passing checks rather than for staying up, so she wants the service in svc_health with the longest unbroken run of healthy checks, where 'Healthy' and 'healthy' count alike and any other status ends the run. Return that service with its streak length, and when two services tie, the alphabetically first name wins.

Pass, pass, pass. How long until fail?

Tables

svc_health

Columns: check_id INTEGER, svc_name TEXT, status TEXT, latency REAL, uptime REAL, checked TEXT, region TEXT

Expected output

Worked solution and explanation

What this really is

This is gaps-and-islands wearing an SRE badge. The question is not which service passed the most checks but which one passed the most in a row, ordered by checked within each svc_name. Anyone can count healthy rows. What separates candidates is building a key that stays constant across one unbroken run and changes the instant a degraded or timeout check lands. Skip that and a per-service COUNT(*) crowns whoever logged the most scattered passes: on this seed that is a 7, while the real best run is 3, held by both cache-svc and ml-serving. Compare on LOWER(status) too, because the seed mixes 'Healthy' and 'healthy', and a case-sensitive filter quietly cuts cache-svc's run to 2.

Build it in three moves

Step 1: Number every check per service in time order

ROW_NUMBER() OVER (PARTITION BY svc_name ORDER BY checked) gives rn, each check's position in its service's history. It counts every row whatever the status, so a failed check still takes up a slot.

Step 2: Number only the healthy checks

Keep rows where LOWER(status) = 'healthy' and assign pass_rn over the same partition, ordered by rn. Across an outage rn jumps past the failed checks, but pass_rn just takes the next number.

Step 3: Use rn - pass_rn as the run key

Inside one unbroken run both counters advance together, so rn - pass_rn stays fixed. The first failed check widens the gap for every healthy row after it, which starts a new island. Group by svc_name and that difference, and COUNT(*) is the run length.

The solution

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

Common follow-up questions

• What if healthy had to mean `status` = 'healthy' AND `latency` < 500? (Only the predicate in `healthy_only` changes; the island key works for any boolean condition.)
• How would you find the longest UNHEALTHY streak instead? (Flip the predicate, or compute every run per status with `svc_name`, `status` and `rn - pass_rn` as the key and pick the max for the failing statuses.)
• How do you handle missing checks, gaps in the timestamp series? (This counts consecutive rows, not consecutive time. To break runs on silence, flag a gap with `LAG(checked)` beyond the expected interval and treat it as a reset.)