First Migration Record

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

L3

Asked in

Google

Problem

Nobody agrees where each database's schema history starts: some rows in migrations carry no applied date, and the dates that are there do not follow the order the migrations ran in. For the platform team's baseline report, pull each database's first migration, the one with the smallest migration ID, with every field it records.

The very first migration. Where it all began.

Tables

migrations

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

Expected output

Worked solution and explanation

This is a per-group argmin dressed up as a release audit: for every database, find the row whose migr_id is smallest inside that database and return the whole row. Everyone knows MIN(migr_id). What separates candidates is where the minimum is scoped. Run the subquery over the whole table and you get one row, migration 123 on 'staging_main', while four databases quietly lose their baseline. Use the earliest applied date instead and 'prod_main' disappears, because not one of its migrations has an applied date.

Walking to the answer

Step 1: Decide what first means before writing anything

The applied column cannot be trusted: 40 rows are NULL, every 'prod_main' row among them, and the backfilled rows dated 2022 to 2025 carry the largest IDs. Migrations run in ID order, so a database's first migration is its smallest migr_id. Asking this out loud is half the interview.

Step 2: Scope the minimum to the outer row's database

The subquery SELECT MIN(db_peer.migr_id) FROM migrations AS db_peer WHERE db_peer.db_name = mig.db_name reads mig.db_name from the outer row, so for every candidate row it answers one question: what is the smallest ID in my own database?

Step 3: Keep the row that equals its database's minimum

The outer WHERE mig.migr_id = (...) keeps a row only when it is its database's minimum. Because migr_id is unique, exactly one row per database survives: five rows, each with all eight columns intact.

SELECT
    mig.migr_id,
    mig.version,
    mig.status,
    mig.applied,
    mig.rollback,
    mig.dur_ms,
    mig.author,
    mig.db_name
FROM migrations AS mig
WHERE mig.migr_id = (
    SELECT MIN(db_peer.migr_id)
    FROM migrations AS db_peer
    WHERE db_peer.db_name = mig.db_name
)

Table-wide minimum

WHERE migr_id = (SELECT MIN(migr_id) FROM migrations) returns only migration 123. 'analytics', 'reporting', 'archive' and 'prod_main' have no baseline in the report, and nothing errors.

Minimum per database

Correlating on db_name returns 123, 146, 169, 192 and 215: one baseline per database, with each database's own version, status and author.

SELECT mig.migr_id, mig.version, mig.status, mig.applied,
       mig.rollback, mig.dur_ms, mig.author, mig.db_name
FROM migrations AS mig
WHERE mig.migr_id = (SELECT MIN(migr_id) FROM migrations)

Common follow-up questions

• How would you return the first three migrations of each database instead of one? (Moves from an extremum to a per-group top-k.)
• If `db_name` arrived with mixed case or stray spaces, what would your result look like and how would you fix it? (Tests whether the candidate sees that dirty group keys split one database into several.)
• The log grows by thousands of rows an hour. How would you keep a per-database baseline table current without rescanning `migrations`? (Incremental maintenance: a new row can only become a baseline for a database that has none yet.)