# The Long Way Back > Some services fell apart and clawed their way back. Find them, and measure the climb. Canonical URL: 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. ## Worked solution and explanation ### What this problem really is Under the recovery story, this is a gaps-and-islands problem wearing a comparison costume. The trap is the phrase 'a month of declining uptime.' It sounds like a single month-over-month drop, so most candidates flag every month lower than the one before it, flag every month higher, and pair the two sets. That is the wrong unit. A decline is a consecutive RUN of falling months collapsed into one period, and its lowest value is the minimum across that entire run, not any single month. Treat each dip as its own event and you get 142 rows where the answer has 55: every one-month wobble becomes a phantom decline, and the min and max feeding the ratio come from the wrong scope. > **Trick to solving** > > The signal is 'one or more consecutive months.' Whenever an unbroken run of same-trend rows must collapse into one period, reach for the fixed-difference identity: number the months per service overall, number them again per service within each trend, and subtract. Rows inside the same unbroken run share a constant difference, so that difference becomes the run's group id. Aggregate on it to get each period's boundaries plus its minimum and maximum. --- ### Building it in stages #### Step 1: Reduce to a monthly grain Collapse the raw checks into one row per service per month holding the average uptime, dropping null uptime first. Everything downstream compares months to each other, so comparing raw checks instead of monthly averages compares noise and the trends dissolve. #### Step 2: Label each month against the month before it Carry the previous month's average onto each row and mark the month decline, growth, or flat. This is the row-to-row comparison the prompt hints at, but on its own it is only the raw material: a list of per-month verdicts, not the periods you actually need. #### Step 3: Collapse consecutive months into periods Apply the fixed-difference trick to turn each unbroken run of decline (or growth) months into one group, then aggregate per group for its start month, end month, minimum average, and maximum average. This is the step the naive solution skips, and skipping it is what multiplies the row count. #### Step 4: Pair declines with later recoveries For each service, match every decline period to every growth period that begins after the decline period ends. The ratio then uses the decline period's minimum and the growth period's maximum, so a single decline can legitimately produce several rows, one per later recovery. ### The solution **Detecting decline-then-growth periods** ```sql WITH monthly AS ( SELECT svc_name, strftime('%Y-%m', checked) AS month, AVG(uptime) AS avg_uptime FROM svc_health WHERE uptime IS NOT NULL GROUP BY svc_name, strftime('%Y-%m', checked) ), with_lag AS ( SELECT svc_name, month, avg_uptime, LAG(avg_uptime) OVER (PARTITION BY svc_name ORDER BY month) AS prev_uptime FROM monthly ), trends AS ( SELECT svc_name, month, avg_uptime, prev_uptime, CASE WHEN avg_uptime < prev_uptime THEN 'decline' WHEN avg_uptime > prev_uptime THEN 'growth' ELSE 'flat' END AS trend FROM with_lag WHERE prev_uptime IS NOT NULL ), numbered AS ( SELECT svc_name, month, avg_uptime, trend, ROW_NUMBER() OVER (PARTITION BY svc_name ORDER BY month) AS rn FROM trends ), groups AS ( SELECT svc_name, month, avg_uptime, trend, rn - ROW_NUMBER() OVER (PARTITION BY svc_name, trend ORDER BY month) AS grp FROM numbered ), streaks AS ( SELECT svc_name, trend, grp, MIN(month) AS start_month, MAX(month) AS end_month, COUNT(*) AS streak_len, MIN(avg_uptime) AS min_uptime, MAX(avg_uptime) AS max_uptime FROM groups GROUP BY svc_name, trend, grp ), decline_then_growth AS ( SELECT d.svc_name, d.start_month AS decline_start, g.start_month AS growth_start, CAST((g.max_uptime - d.min_uptime) AS DOUBLE) / d.min_uptime AS growth_ratio FROM streaks d INNER JOIN streaks g ON d.svc_name = g.svc_name AND d.trend = 'decline' AND g.trend = 'growth' AND g.start_month > d.end_month ) SELECT svc_name, decline_start, growth_start, growth_ratio FROM decline_then_growth ``` **Per-month flags (naive)** Flag every month below its predecessor and every month above it, then pair the sets. Counts each one-month wobble as its own decline, reuses months across overlapping pairs, and pulls min and max from month-to-growth windows. Result: 142 rows with drifted ratios. **Collapsed periods (correct)** Collapse consecutive falls and consecutive rises into periods first, then pair periods. One decline per unbroken run, min and max scoped inside each period. Result: 55 rows with the expected ratios. > **Common pitfall** > > Even candidates who find the streaks often botch the scope of lowest and peak. Watch for computing lowest as the minimum from the decline's start all the way to the growth's start, or peak as the maximum of everything after the growth begins. The expected ratio is strict: minimum WITHIN the decline period, maximum WITHIN the growth period. Widen either window by a month and the ratio silently changes, which is exactly what separates 0.0105 from 0.0823 on the same pair. > **Interviewers watch for** > > The tell for seniority is whether you name the unit before writing SQL: do you ask whether a decline is one month or a run of months, and whether a lone moving month still counts? A strong candidate decomposes into named stages, reaches for the numbering-difference trick instead of a self-join to detect runs, and scopes the minimum and maximum to each period. A weaker one nests month-over-month comparisons and never notices the row count exploded. > **Cost analysis** > > At roughly 50M checks the monthly reduction is the whole game: averaging per service per month shrinks the working set from tens of millions of rows to a few thousand month buckets before any ordering runs. Every later step (the labeling, the two numberings, the period aggregation, the period-to-period pairing) operates on that tiny reduced set, so the cost is dominated by the single grouped scan over checked and uptime. A covering structure on (svc_name, checked, uptime) turns that scan into an ordered read. --- ## Common follow-up questions - A service's very first month has no prior month to compare against. What trend does that month get, and can it ever start a decline or growth period? _(Tests whether the candidate handles the null from the first-row comparison and excludes it from period building.)_ - How do you treat a month whose average uptime exactly equals the previous month's? Does it extend, break, or get ignored by the surrounding run? _(Tests flat-month handling and its effect on where a consecutive run ends.)_ - This runs over about 50,000,000 checks. Which single step dominates the cost, and what structure would you add on svc_health to avoid a full scan? _(Tests understanding that the monthly reduction dominates cost and that a covering index on the grouped columns avoids a full scan.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_long_way_back) - [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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.