Latest Migration Output per Author

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

The release manager wants the latest migration each author shipped. For every author in the migrations table, find their most recent migration by applied date, breaking ties in favor of the largest migration ID. Return the author, version, and applied timestamp, sorted alphabetically by author.

Each author's most recent migration output.

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

What this really is

Strip off the release-management costume and this is the latest-row-per-group problem, the most common interview shape in all of SQL. You are not aggregating anything: you want the WHOLE migration row that happens to be the newest one for each author. That distinction is the entire test. Everyone reaches for GROUP BY author, but the moment you do, you can keep the MAX of applied and you LOSE the version that belongs to it. An aggregate collapses the group; it cannot hand you the other columns from the winning row.

The trap

Aggregate then self-join (fragile)

GROUP BY author to get MAX(applied), then join back to migrations on (author, applied) to fetch version. Works until a tie on applied returns two rows, double-counting the author, with no clean way to break the tie inside the join.

Rank then filter (clean)

Number the rows within each author by recency, then keep number 1. The tie-break lives inside the ORDER BY, so a duplicate timestamp resolves deterministically and the author still appears exactly once.

The build

Step 1: Number rows within each author by recency

ROW_NUMBER() OVER (PARTITION BY author ORDER BY applied DESC, migr_id DESC). PARTITION BY author restarts the count for every author, so each author's newest migration gets rn = 1. Note authors are case-sensitive here: Alice and alice partition separately, which is exactly what the data intends.

Step 2: Make the tie-break deterministic

The prompt says break ties by the largest migration ID, so the second ORDER BY key is migr_id DESC. Without it, two migrations sharing an applied timestamp would get rn 1 and 2 in an undefined order, and your output would flip between runs. This is the difference between a query that passes once and one you would trust in production.

Step 3: Keep the winners and sort the result

Wrap the numbered set in a subquery (window functions cannot live in a WHERE clause directly) and filter rn = 1. Then ORDER BY author for the alphabetical output the release manager asked for. Do not confuse the inner recency ordering with this outer presentation ordering; they are two different sorts doing two different jobs.

SELECT author, version, applied
FROM (
  SELECT author, version, applied, migr_id,
         ROW_NUMBER() OVER (PARTITION BY author ORDER BY applied DESC, migr_id DESC) AS rn
  FROM migrations
) t
WHERE rn = 1
ORDER BY author

Common follow-up questions

• What changes if the release manager wants the latest SUCCESSFUL migration only, where status normalizes to 'applied' case-insensitively? (Tests adding a WHERE LOWER(status) = 'applied' before the window so filtering happens on the base rows, not after ranking.)
• How would you return the two most recent migrations per author instead of just the latest? (Tests generalizing rn = 1 to rn <= 2, the natural top-N-per-group extension.)
• If two migrations for an author share both applied and `migr_id`, how do you guarantee determinism? (Tests awareness that the ORDER BY keys must be unique within a partition or ties stay non-deterministic.)