Back From the Brink

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

LinkedIn

Problem

In deploy_logs, a rolled-back deployment counts as a bounce-back for the release team when that same engineer's very next deployment succeeds, and both the author names and the status values were written in whatever casing the tooling happened to use, which means nothing. For each service, report how many bounce-backs started on it and how many different engineers pulled one off.

Roll it back, then nail the next one.

Tables

deploy_logs

Columns: log_id INTEGER, svc_name TEXT, version TEXT, env_name TEXT, status TEXT, deploy_at TEXT, dur_secs INTEGER, author TEXT

Expected output

Worked solution and explanation

What this really is

Strip off the release dashboard and this is a per-engineer state transition: a 'rolled_back' row whose very next row, in that engineer's own timeline, is a 'success'. Anyone can find rollbacks. What separates candidates is deciding who 'that engineer' is and what 'success' looks like before the lookahead runs. The log writes 'Alice' and 'alice', 'Rolled_Back' and 'rolled_back'. Fold casing in one place and feed it everywhere, or the timeline splits, the next row is the wrong row, and real rescues vanish from the count.

Build it in three moves

Step 1: Fold casing once, at the source

The normalized CTE emits LOWER(author) as engineer and LOWER(status) as status. Doing it once means the partition, the lookahead, the filter and the engineer count all agree on identity; lowering in some places and not others is how answers quietly drift.

Step 2: Peek one deployment ahead per engineer

with_next computes LEAD(status) OVER (PARTITION BY engineer ORDER BY deploy_at). Partitioning by the folded engineer keeps 'Alice' and 'alice' on one timeline, so their next deployment is genuinely their next one.

Step 3: Keep the rescue pair, then roll up by service

Keep status = 'rolled_back' AND next_status = 'success'. A trailing rollback has a NULL next_status and fails the equality on its own. Then GROUP BY svc_name: COUNT(*) is the rescue events, COUNT(DISTINCT engineer) is the people behind them.

The solution

WITH normalized AS (
    SELECT LOWER(author) AS engineer,
           LOWER(status) AS status,
           svc_name,
           deploy_at
    FROM deploy_logs
),
with_next AS (
    SELECT engineer,
           status,
           svc_name,
           LEAD(status) OVER (PARTITION BY engineer ORDER BY deploy_at) AS next_status
    FROM normalized
)
SELECT svc_name,
       COUNT(*) AS recovery_count,
       COUNT(DISTINCT engineer) AS engineer_count
FROM with_next
WHERE status = 'rolled_back' AND next_status = 'success'
GROUP BY svc_name

Raw casing

Partition on raw author and compare raw status: 'Alice' and 'alice' are two timelines, a 'Rolled_Back' never equals 'rolled_back', and a 'Success' never equals 'success'. Rescues disappear and the next row is sometimes the wrong deployment.

Folded once

engineer and status are lowered in normalized and reused downstream. One person, one timeline, one spelling of each outcome.

Common follow-up questions

• An engineer's last deployment has a `NULL` `next_status`. Why does a trailing '`rolled_back`' fall out without an explicit check? (Tests whether the candidate knows how `NULL` behaves in an equality filter.)
• How would the query change if a rescue only counted when the next success was on the same `svc_name` as the rollback? (Tests a stricter recovery definition.)
• What happens to the lookahead if one engineer has two deployments with the same `deploy_at`, and how would you make it deterministic? (Tests tie handling in a time-ordered window.)