The Long Way Back

A hard SQL interview practice problem on DataDriven. Write and execute real sql code with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L5

Problem

From our service health checks we track each service's average uptime per month, ignoring any check that never recorded an uptime figure so a month with no valid reading simply isn't part of the timeline. Reading a service's months in order, a decline is an unbroken run of months each lower than the one before it and a recovery is an unbroken run each higher than the one before, where a single qualifying month already forms a run on its own; a month equal to the one before belongs to neither and closes whichever run was open. Pair every decline with each recovery that begins after the decline's last month, and for each pairing report the service, the month each run began, and how far it climbed back: the highest average within the recovery minus the lowest within the decline, over that lowest.

Summary

Some services fell apart and clawed their way back. Find them, and measure the climb.

Practice This Problem

Solve this SQL problem with real code execution. DataDriven runs your SQL queries against a real database and checks it automatically.