Find Duplicate Serial Numbers | Dataford Interview Questions

Write a query to detect duplicate serial numbers in a product inventory table and keep only the most recent entry based on a timestamp.

Return one row per non-null serial number, retaining only its latest inventory record. If timestamps tie, retain the row with the greatest inventory_id.

Output

Return inventory_id, serial_number, product_model, warehouse_code, inventory_status, and recorded_at.
Exclude older duplicate records and rows with a null serial number.
Order by serial_number ascending.

Schema

inventory_entries

Column	Type	Description
inventory_idPK	INT	Unique inventory record identifier
serial_number	VARCHAR(40)	Product serial number
product_model	VARCHAR(80)	Product model or component family
warehouse_code	VARCHAR(20)	Warehouse or facility code
inventory_status	VARCHAR(20)	Current status recorded for the inventory item
recorded_at	TIMESTAMP	Timestamp when the inventory record was recorded

Your question is Find Duplicate Serial Numbers. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem