Deduplicating Data with ROW_NUMBER()

Problem

A glitch in the Kafka pipeline caused thousands of employee records to be inserted twice. You must clean the table by keeping only the most recently hired record for each employee_id. Assume the employees table has duplicate employee_ids. Write a CTE that assigns a ROW_NUMBER() partitioned by employee_id ordered by hire_date DESC, then select only rows where the row number is 1.

Database Schema

Table: employees

• employee_id (INT): Unique identifier for the employee.
• first_name (VARCHAR): First name of the employee.
• last_name (VARCHAR): Last name of the employee.
• salary (INT): Employee's salary.
• department_id (INT): Foreign key referencing the department.
• manager_id (INT): Foreign key referencing the manager (another employee).
• hire_date (DATE): Date the employee was hired.

Table: departments

• department_id (INT): Unique identifier for the department.
• department_name (VARCHAR): Name of the department.

Try It

Hint

Solution