# The Undone

> Some migrations don't stick. Find the databases where they keep coming undone.

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

Domain: SQL · Difficulty: hard · Seniority: L4

## Problem

A reliability review is auditing the databases where migrations have been rolled back. For each of those databases, report its total migration count and how many of those migrations were rolled back, most rollbacks first.

## Worked solution and explanation

### What this problem is really testing

This is conditional counting wearing a data-quality costume. The real question: can you count one specific category of rows per group while a look-alike column and inconsistent casing both try to trip you? Two decoys are planted in the schema. There is a column literally named rollback, which feels like the answer but actually holds the down-migration DDL (a DROP TABLE script) and is null about 70 percent of the time, so it tells you nothing about whether a rollback ever ran. The genuine signal lives in status, where the value 'rolled_back' marks a migration that was undone. Reach for the wrong column and every count comes back zero.

---

### The two traps in the data

> **The rollback column is a decoy**
>
> The rollback column is bait. It stores the compensating DDL a migration would run if reversed, not a flag that it was reversed. It is populated for plenty of migrations that applied cleanly and is null for many that failed. Filtering or counting on it answers a different question than the one asked. The state of a migration lives in status, full stop.

> **Interviewers watch for**
>
> Look closely at status: you will see Applied beside applied and PENDING beside pending. The casing is dirty. A bare status = 'rolled_back' silently drops any rolled-back row that was written as 'Rolled_Back' or 'ROLLED_BACK'. Normalizing with LOWER before the comparison is the move that separates someone who eyeballed the data from someone who trusted the column name.

---

### Building the query

#### Step 1: Aggregate both metrics per database

One pass over the table, grouped by db_name. COUNT(*) gives the total migrations for the database. A SUM over CASE WHEN LOWER(status) = 'rolled_back' THEN 1 ELSE 0 END counts just the undone ones. Doing both in a single aggregate scan means you never touch the table twice.

#### Step 2: Filter to the problem databases, then order

The review only cares about databases that have actually seen a rollback, so HAVING SUM(CASE WHEN LOWER(status) = 'rolled_back' THEN 1 ELSE 0 END) > 0 drops the spotless ones. Then ORDER BY total_rollbacks DESC surfaces the worst offenders first, with db_name as a stable tie-breaker.

**Conditional count with a HAVING guard**

```sql
SELECT
    db_name,
    COUNT(*) AS total_migrations,
    SUM(CASE WHEN LOWER(status) = 'rolled_back' THEN 1 ELSE 0 END) AS total_rollbacks
FROM migrations
GROUP BY db_name
HAVING SUM(CASE WHEN LOWER(status) = 'rolled_back' THEN 1 ELSE 0 END) > 0
ORDER BY total_rollbacks DESC, db_name
```

**Counting the rollback column**

SUM(CASE WHEN rollback IS NOT NULL THEN 1 ELSE 0 END). This counts migrations that merely carry a reversal script, including ones that applied without a hitch. It over-reports and answers the wrong question.

**Counting the status value**

SUM(CASE WHEN LOWER(status) = 'rolled_back' THEN 1 ELSE 0 END). This counts only migrations whose recorded outcome was a rollback, which is exactly the reliability signal the review wants.

> **Cost analysis**
>
> A single grouped scan of 5K rows. The conditional SUM and the HAVING evaluate during the same aggregation, so there is no second pass and no join. On this size it is effectively instant; the same shape holds up on millions of rows because it stays one sequential scan plus a hash aggregate.

## Common follow-up questions

- What happens to rows where status is NULL, and does your query need to guard against them? _(The CASE lands NULL in the ELSE branch and contributes 0, so null statuses are ignored rather than crashing the sum. Tests whether the candidate reasons about NULL in conditional aggregation.)_
- How would you rank databases by rollback rate instead of raw rollback count? _(Add a rollback rate as SUM(rolled_back) * 1.0 / COUNT(*) and order or filter on that. Tests normalizing a raw count into a comparable ratio.)_
- Could you also report the average duration of the rolled-back migrations per database? _(AVG(dur_ms) or a conditional average scoped to rolled-back rows, added to the SELECT. Tests combining a second aggregate with the existing grouping.)_

## Related

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