# Back From the Brink

> Roll it back, then nail the next one.

Canonical URL: <https://datadriven.io/problems/back_from_the_brink>

Domain: SQL · Difficulty: hard · Seniority: L5

## Problem

Each row in deploy_logs is one deployment: the engineer who ran it (author), a status, and a deploy_at timestamp. The same engineer sometimes shows up under different name casing, and status is logged in mixed casing too, so treat both case-insensitively. Line up each engineer's deployments in time order and count the different engineers who bounced back at least once, meaning a rolled_back deployment whose very next deployment succeeded.

## Worked solution and explanation

### What this really is

Strip the deployment story away and this is a per-engineer state-transition hunt: for each person, does a 'rolled_back' ever sit immediately before a 'success' in their own time order? Anyone can filter for rolled_back rows and anyone can filter for success rows. The real work is pairing each rollback with that engineer's very NEXT deployment (not just any later success) and doing it without letting one engineer's timeline bleed into another's. Miss the casing fold and you split 'Alice' from 'alice' into two people, quietly dropping her recovery; match any later success instead of the immediate next and you inflate the count.

> **Trick to Solving**
>
> Whenever the prompt asks you to compare a row to the one that comes right after it in time, that is a `LEAD` (next-row lookahead) signal.
> 
> 1. Identify the comparison direction (here: the very next deployment)
> 2. Partition by the grouping key (the engineer, normalized with LOWER)
> 3. Order by the time column (`deploy_at`)
> 4. Filter on the (current status, next status) pair in the outer query

---

### Break down the requirements

#### Step 1: Isolate the lookahead in a CTE

The `ordered_deploys` CTE normalizes author and status casing with LOWER and computes each row's next status via LEAD over `PARTITION BY LOWER(author) ORDER BY deploy_at`. This separation keeps the per-engineer lookahead isolated from the final count.

#### Step 2: Filter the transition and count distinct engineers

The outer query filters to rows where the current status is 'rolled_back' and the next status is 'success', then returns COUNT(DISTINCT author) AS recovery_count. Returning extra columns or the wrong alias would fail the grading check.

---

### The solution

**Lead-lookahead for rolled_back -> success recovery**

```sql
WITH ordered_deploys AS (
    SELECT LOWER(author) AS author, LOWER(status) AS status, deploy_at,
           LEAD(LOWER(status)) OVER (PARTITION BY LOWER(author) ORDER BY deploy_at) AS next_status
    FROM deploy_logs
)
SELECT COUNT(DISTINCT author) AS recovery_count
FROM ordered_deploys
WHERE status = 'rolled_back' AND next_status = 'success'
```

> **Cost Analysis**
>
> With ~1M rows, the window function runs over the full table once, sorted within each engineer partition; CTEs materialize intermediate results, which can be beneficial or costly depending on the engine. An index on `(author, deploy_at)` would let the engine satisfy the partition+order without a separate sort.

> **Interviewers Watch For**
>
> Interviewers watch for whether you decompose the problem into named, testable stages rather than nesting everything; whether you reach for window functions or attempt a self-join for the row-to-next-row comparison; and whether you remember to normalize casing on BOTH author and status before partitioning and comparing.

> **Common Pitfall**
>
> Applying LEAD without the correct PARTITION BY LOWER(author) mixes one engineer's deployments with another's, so a 'rolled_back' from one engineer is wrongly paired with the 'success' of the next engineer in the global order.

---

## Common follow-up questions

- For each engineer's most recent deployment, LEAD(status) is NULL. How does your WHERE clause handle that NULL, and could it ever cause a 'rolled_back' row to be miscounted as a recovery? _(Tests whether the candidate accounts for an engineer's final deployment in `deploy_logs`, where LEAD returns NULL.)_
- If `deploy_logs` grew to billions of rows, which part of your query becomes the bottleneck, and how would partitioning or indexing on `(author, deploy_at)` help? _(Tests ability to identify performance hotspots in `deploy_logs` at scale.)_
- What is the default window frame for your LEAD call, and would explicitly setting ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW change the result? _(Tests knowledge of implicit vs explicit window frame specifications.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/back_from_the_brink)
- [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.