The Undone

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain
SQL
Difficulty
medium
Seniority
L4

Problem

Using the migrations table, report each database's total migration count, how many of its migrations have an applied status and how many were rolled back, most rollbacks first. The reliability reviewer only wants databases with at least one rollback for every two applied migrations, and a status means the same outcome whatever its letter case.

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

Tables

migrations

Columns: migr_id INTEGER, version TEXT, status TEXT, applied TEXT, rollback TEXT, dur_ms INTEGER, author TEXT, db_name TEXT

migrations: 10 of 200 rows
migr_idversionstatusappliedrollbackdur_msauthordb_name
123002_add_userspending2026-02-02 01:00:00DROP TABLE IF EXISTS tbl_141bobstaging_main
146003_add_ordersfailed2026-03-03 02:00:00DROP TABLE IF EXISTS tbl_272charlieanalytics
169004_indexesrolled_back2026-04-04 03:00:00DROP TABLE IF EXISTS tbl_3103danareporting
192005_add_productsApplied2026-05-05 04:00:00DROP TABLE IF EXISTS tbl_4134migration-botarchive
215006_add_sessionsPENDINGNULLDROP TABLE IF EXISTS tbl_5NULLAliceprod_main
238007_add_eventsapplied2026-07-07 06:00:00DROP TABLE IF EXISTS tbl_6196alicestaging_main
261008_add_searchpending2026-08-08 07:00:00NULL227bobanalytics
284v009_paymentsfailed2026-09-09 08:00:00DROP TABLE IF EXISTS tbl_8258charliereporting
307V010_analyticsrolled_back2026-01-10 09:00:00DROP TABLE IF EXISTS tbl_9289danaarchive
330001_initAppliedNULLDROP TABLE IF EXISTS tbl_10NULLmigration-botprod_main

Expected output

First 3 of 3 rows
db_nametotal_migrationsapplied_migrationstotal_rollbacks
reporting40128
archive40148
analytics40126

Worked solution and explanation

What this problem is really testing

This is a ratio of two conditional counts dressed up as a reliability audit. Every database in migrations has seen a rollback, so 'has a rollback' filters nothing. The real bar is rollbacks measured against clean applies: at least one for every two. Anyone can count rollbacks. The trick is the other side of the ratio, because an applied status is written as both 'applied' and 'Applied'. Match it literally and you count half the applies, the bar drops, and prod_main and staging_main get into an audit they should not be in.

Building the query

Step 1: Count everything in one grouped pass

Group by db_name. COUNT(*) is the total across every status, so the restriction to rolled-back rows has to live inside its own count, SUM(CASE WHEN status = 'rolled_back' THEN 1 ELSE 0 END). Putting it in a WHERE would shrink the total down to the rollbacks.

Step 2: Count applies case-blind

SUM(CASE WHEN LOWER(status) = 'applied' THEN 1 ELSE 0 END) gives 12 or 14 per database. The bare comparison gives 6 or 8, and the bar in the next step inherits that error.

Step 3: Apply the bar after aggregating, then sort

At least one rollback per two applies is 2 * rollbacks >= applies. Multiplying keeps the comparison in integers and never divides by a zero apply count. The >= keeps analytics, which sits exactly on the line at 6 against 12. Then sort by total_rollbacks descending.

Two conditional counts and a ratio bar
SELECT
    db_name,
    COUNT(*) AS total_migrations,
    SUM(CASE WHEN LOWER(status) = 'applied' THEN 1 ELSE 0 END) AS applied_migrations,
    SUM(CASE WHEN status = 'rolled_back' THEN 1 ELSE 0 END) AS total_rollbacks
FROM migrations
GROUP BY db_name
HAVING 2 * SUM(CASE WHEN status = 'rolled_back' THEN 1 ELSE 0 END)
       >= SUM(CASE WHEN LOWER(status) = 'applied' THEN 1 ELSE 0 END)
ORDER BY total_rollbacks DESC
Literal status match
status = 'applied' counts 6 applies on prod_main and 8 on staging_main. Both clear 2 * 6 >= 8, so all five databases come back.
Case-folded match
LOWER(status) = 'applied' counts 14 on both. 2 * 6 >= 14 fails, so only reporting, archive and analytics survive.

Common follow-up questions