The Long Way Back

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

Visa

Problem

From our service health checks we track each service's average uptime for each calendar month, leaving out any check that recorded no uptime figure. Reading one service's months in chronological order, a slump is a run of back-to-back months whose average uptime sits below the month right before it, a recovery is a run of back-to-back months whose average sits above the month right before it, a single such month already counts as a run on its own, and a month whose average ties the one right before belongs to neither and closes whichever run was open. Pair every slump with each recovery that begins after the slump's last month, and for each pairing report the service, the month the slump began, the month the recovery began, and how far the climb back went relative to the fall: the recovery's number of months divided by the slump's number of months, as a decimal.

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

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 problem really is

Under the recovery story this is a gaps-and-islands problem in a comparison costume. The trap is the phrase 'a month of declining uptime': it sounds like one month-over-month drop, so candidates flag every month below its predecessor, every month above, and pair the sets. Wrong unit. A slump is a consecutive RUN of falling months collapsed into one stretch, and its length is the count of months across that run, not any single step. Treat each dip as its own event and a one-month wobble becomes a phantom slump. Quieter second trap: filter with WHERE uptime IS NOT NULL first. A month whose checks all lack an uptime reading has no real average, and left in, that empty month reads as a gap that breaks a run.

Building it in stages

Step 1: Reduce to a monthly average

Collapse the raw checks into one row per service per month holding AVG(uptime), filtering WHERE uptime IS NOT NULL first. Everything downstream compares these averages, so an empty month would enter the series and break an intact run.

Step 2: Label each month against the one before

Carry the previous month's average onto each row with LAG over month order and label it 'decline', 'growth', or 'flat' with a CASE. On its own this is only raw material: per-month verdicts, not stretches.

Step 3: Collapse consecutive months into stretches

Apply the numbering-difference trick to turn each run of same-trend months into one group, then aggregate: COUNT(*) for length and MIN(month)/MAX(month) for boundaries. Skipping this is what multiplies the row count.

Step 4: Pair slumps with later recoveries

Match every slump to every recovery that begins after it ends, via an inequality self-join on svc_name with growth_start > end_month. One slump can yield several rows, one per later recovery.

The solution

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,
        CASE
            WHEN avg_uptime < prev_uptime THEN 'decline'
            WHEN avg_uptime > prev_uptime THEN 'growth'
            ELSE 'flat'
        END AS trend
    FROM with_lag
),
numbered AS (
    SELECT svc_name, month, trend,
        ROW_NUMBER() OVER (PARTITION BY svc_name ORDER BY month) AS rn
    FROM trends
),
groups AS (
    SELECT svc_name, month, 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
    FROM groups
    GROUP BY svc_name, trend, grp
),
decline_then_recovery AS (
    SELECT d.svc_name, d.start_month AS decline_start, g.start_month AS growth_start,
        CAST(g.streak_len AS REAL) / d.streak_len 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_recovery

Per-month flags (naive)

Flag every month below its predecessor and above it, then pair the sets. Counts each wobble as its own slump, reuses months across pairs, reads lengths from single steps, and keeps empty AVG(uptime) months that split runs.

Collapsed stretches (correct)

Collapse consecutive falls and rises into stretches first, then pair them. One slump per run, COUNT(*) for true length, only real readings averaged, CAST keeping the ratio a decimal.

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 slump or recovery? (Tests handling the null from the first-row comparison and keeping it out of the stretches.)
• 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 run ends.)
• This runs over about 50,000,000 checks in `svc_health`. Which single step dominates the cost, and what structure avoids a full scan? (Tests that the monthly reduction dominates cost and a covering index avoids a full scan.)