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 monthly average uptime, and we want the services that slid downhill and then clawed their way back. Reading one service's months in time order, treat each unbroken run of consecutive falling months as a single decline stretch and each unbroken run of consecutive rising months as a single recovery stretch; a month whose average equals the one before it belongs to neither and ends whichever stretch was open. Match every decline stretch with each recovery stretch that starts after the decline stretch's final month, and for each pairing report the month each stretch began along with how far the service climbed: the highest monthly average anywhere in the recovery stretch minus the lowest anywhere in the decline stretch, 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.