Find Duplicates Without DISTINCT | Dataford Interview Questions

Zebra Technologies receives device inventory data from systems supporting products such as Zebra TC53, TC58, and DS3678 devices. Duplicate imports can distort asset counts and downstream analytics.

Write a PostgreSQL query to find duplicate logical rows in device_inventory_imports without using DISTINCT. Treat rows as duplicates when all business columns match, excluding the surrogate import_id and ingestion timestamp.

Requirements

Group by device_serial, device_model, firmware_version, warehouse_code, and lifecycle_status.
Return only groups containing at least two records.
Return the duplicate count and an ordered array of the corresponding import_id values.
Treat NULL business values as equal for duplicate detection and order the output by duplicate count descending, then serial number.

Schema

device_inventory_imports

Column	Type	Description
import_idPK	INTEGER	Unique import record identifier
device_serial	VARCHAR(30)	Zebra device serial number
device_model	VARCHAR(40)	Device model reported by the source system
firmware_version	VARCHAR(20)	Reported firmware version
warehouse_code	VARCHAR(10)	Warehouse holding the device
lifecycle_status	VARCHAR(20)	Current lifecycle status
imported_at	TIMESTAMPTZ	Timestamp when the source row was ingested

Your question is Find Duplicates Without DISTINCT. 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

Zebra Technologies receives device inventory data from systems supporting products such as Zebra TC53, TC58, and DS3678 devices. Duplicate imports can distort asset counts and downstream analytics.

Requirements

Group by device_serial, device_model, firmware_version, warehouse_code, and lifecycle_status.
Return only groups containing at least two records.
Return the duplicate count and an ordered array of the corresponding import_id values.
Treat NULL business values as equal for duplicate detection and order the output by duplicate count descending, then serial number.

Schema

device_inventory_imports

Column	Type	Description
import_idPK	INTEGER	Unique import record identifier
device_serial	VARCHAR(30)	Zebra device serial number
device_model	VARCHAR(40)	Device model reported by the source system
firmware_version	VARCHAR(20)	Reported firmware version
warehouse_code	VARCHAR(10)	Warehouse holding the device
lifecycle_status	VARCHAR(20)	Current lifecycle status
imported_at	TIMESTAMPTZ	Timestamp when the source row was ingested

Your question is Find Duplicates Without DISTINCT. 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.