Keep Only the Latest Salary Per Employee (Deduplication)

Interview brief

Understand the request

salaries_history holds multiple rows per employee. Keep only the most recent record per employee (latest effective_date). Use ROW_NUMBER. Return employee_id, salary, effective_date — ordered by employee_id.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

WITH ranked AS (SELECT employee_id, salary, effective_date, ROW_NUMBER() OVER (PARTITION BY employee_id ORDER BY effective_date DESC) AS rn FROM salaries_history) SELECT employee_id, salary, effective_date FROM ranked WHERE rn = 1 ORDER BY employee_id;

Expected result

Use this output to verify values, aliases, ordering, and row count.