Last Migration Record

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

Domain: SQL
Difficulty: easy
Seniority: L3
Asked in: Google

Problem

The migration holding the maximum ID in migrations shipped a version that has often run on other databases too. For the platform team's release check, return the full record of every migration that ran that same version: its ID, version, status, applied date, rollback, duration, author, and database name.

The most recent migration. Is it the last?

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_id	version	status	applied	rollback	dur_ms	author	db_name
123	002_add_users	pending	2026-02-02 01:00:00	DROP TABLE IF EXISTS tbl_1	41	bob	staging_main
146	003_add_orders	failed	2026-03-03 02:00:00	DROP TABLE IF EXISTS tbl_2	72	charlie	analytics
169	004_indexes	rolled_back	2026-04-04 03:00:00	DROP TABLE IF EXISTS tbl_3	103	dana	reporting
192	005_add_products	Applied	2026-05-05 04:00:00	DROP TABLE IF EXISTS tbl_4	134	migration-bot	archive
215	006_add_sessions	PENDING	NULL	DROP TABLE IF EXISTS tbl_5	NULL	Alice	prod_main
238	007_add_events	applied	2026-07-07 06:00:00	DROP TABLE IF EXISTS tbl_6	196	alice	staging_main
261	008_add_search	pending	2026-08-08 07:00:00	NULL	227	bob	analytics
284	v009_payments	failed	2026-09-09 08:00:00	DROP TABLE IF EXISTS tbl_8	258	charlie	reporting
307	V010_analytics	rolled_back	2026-01-10 09:00:00	DROP TABLE IF EXISTS tbl_9	289	dana	archive
330	001_init	Applied	NULL	DROP TABLE IF EXISTS tbl_10	NULL	migration-bot	prod_main

Expected output

First 5 of 20 rows
migr_id	version	status	applied	rollback	dur_ms	author	db_name
330	001_init	Applied	NULL	DROP TABLE IF EXISTS tbl_10	NULL	migration-bot	prod_main
560	001_init	failed	NULL	DROP TABLE IF EXISTS tbl_20	NULL	charlie	prod_main
790	001_init	applied	NULL	DROP TABLE IF EXISTS tbl_30	NULL	alice	prod_main
1020	001_init	Applied	NULL	DROP TABLE IF EXISTS tbl_40	NULL	migration-bot	prod_main
1250	001_init	failed	NULL	DROP TABLE IF EXISTS tbl_50	NULL	charlie	prod_main

Worked solution and explanation

What this really is

This is an argmax used as an anchor, wearing a release-check costume. Anyone can find the biggest migr_id; MAX(migr_id) does it in one line. The problem asks for two things that sit behind that number: the whole row that owns it, and then every other migration that ran the same version. The max is a pointer, not the answer. The trap is writing SELECT MAX(migr_id), version and trusting the engine to hand back the right version, or reading '001_init' off the preview and hardcoding it. Either way the release check audits a version that is not guaranteed to be the one that shipped last.

Building it

Step 1: Pin the newest migration by ID

(SELECT MAX(migr_id) FROM migrations) is a scalar subquery with no outer references: it runs once and yields one number. Newest is by ID, not by applied, because the timestamp is null on some rows and out of step with issue order.

Step 2: Turn the number into a row with `latest`

Give the table a second name, latest, and filter it to the row whose migr_id equals that scalar. That leaves exactly one latest row, and with it the real version of the newest change, taken from the row itself rather than stitched beside an aggregate.

Step 3: Fan the version back out through `m`

Join the outer m to latest on latest.version = m.version. Because latest holds one row, the join cannot fan out: it simply keeps every migration in m that ran the same version, on any database, including the newest one itself. Select the eight columns from m and each output row is a whole, real migration.

Every run of the newest version

SELECT
    m.migr_id,
    m.version,
    m.status,
    m.applied,
    m.rollback,
    m.dur_ms,
    m.author,
    m.db_name
FROM migrations AS m
JOIN migrations AS latest
    ON latest.version = m.version
WHERE latest.migr_id = (
    SELECT MAX(migr_id)
    FROM migrations
);

latest is pinned to one row by the scalar MAX, so the join on version returns each matching migration exactly once.

Looks right: WHERE version = '001_init' copied from the preview. It returns the right rows today and the wrong release the moment a newer migration lands, because nothing ties the literal to MAX(migr_id).
Is right: Anchor on latest.migr_id = (SELECT MAX(migr_id) FROM migrations) and join on version. The version is read from the newest row every time the query runs.

Common follow-up questions

The team now wants the newest migration on each `db_name` instead. What changes? (The scalar max must become scoped to each database, which moves the query to a correlated subquery or a window rank.)
Treat '`V001_init`', '`v001_init`' and '001_init' as one release. Where does the normalization go? (Both sides of the join on `version` need the same normalization, not just one.)
Only report runs of that version whose `status` is applied, in any casing. Which alias gets the filter? (The filter belongs on `m`, not `latest`, and `status` needs `LOWER()`.)