Skip to main content

// advent-of-postgres/day-4

Day 4 of 24IntermediateWindow functionsGaps and islands

The Longest Outage

Health checks run every minute. Find the longest unbroken run of failures, not the service that failed the most in total.

Opens 4 December 2026, 00:00 UTC

Sugarplum is writing the outage report for 4 December. The status page has to show the single worst outage of the day, and the health checker is the only record.

The checker writes one row per service per minute to health_checks:

Column Type Notes
service text Part of the primary key
checked_at timestamptz Every minute on 4 December
ok boolean false when the check failed

An outage is a run of failed checks in a row for one service. One passing check ends it. Each failed check counts as one minute, so 12 failed checks in a row is a 12-minute outage. A service can have many short outages and still not have the longest one.

Your task

How many minutes long was the longest outage of the day, across all services?

Submit the number of minutes.

Stuck? Show a hint

Number the checks per service, and number them again per service and ok value. The difference stays the same inside a run.

No database yet. You get a real Postgres database on Neon, free, with no account.
One statement at a time. Ctrl+Enter or Cmd+Enter runs it.

Download today's data as SQL to solve it on your own Postgres (14 or later).

Solved this puzzle? Share it.

Tag @thedevopsdaily and share it with the community.