# Latest Migration Output per Author > Each author's most recent migration output. Canonical URL: 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. ## 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 > **MAX(applied) cannot carry the version** > > The seductive wrong answer is SELECT author, MAX(applied) FROM migrations GROUP BY author. It gives the right timestamp, but then candidates bolt on MAX(version) or pick an arbitrary version and ship a row that never existed: the latest date from one migration and a version string from another. To recover the real version you would have to self-join the aggregate back to the base table on author AND applied, and that join silently fans out the instant two migrations for the same author share an applied timestamp. **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. **Latest migration per author** ```sql 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 ``` *One pass over migrations, one window, no join, no fan-out.* > **The tie-break is the whole point** > > The single insight that cracks this cleanly is putting migr_id DESC as the secondary sort key inside the window. It both satisfies the stated requirement and guarantees exactly one row survives per author no matter how many share a timestamp. That is something a GROUP BY plus self-join cannot promise. > **What signals seniority** > > The tell is whether you reach for ROW_NUMBER the moment you hear 'the latest WHOLE row per group' instead of GROUP BY. Strong candidates also volunteer the tie-break unprompted and ask whether author is case-sensitive. Reaching for MAX and then trying to drag the other columns along is the junior reflex interviewers are listening for. > **Why this plan stays cheap** > > ROW_NUMBER is a single sort of the table partitioned by author: one scan, one sort, O(n log n), no intermediate join product. The self-join alternative scans migrations twice and can balloon to O(n^2) per author on tied timestamps. On a migrations table with millions of rows the window form is both faster and the one that gives a correct answer under ties. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/latest_migration_output_per_author) - [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.