Write a query to identify duplicate records in a large manufacturing telemetry table using common table expressions.
Treat records as duplicates when all telemetry attributes except telemetry_id match, including rows with NULL attribute values. Return one row for each duplicated signature.
tool_id, sensor_name, observed_at, reading_value, unit, quality_flag, duplicate_count, and telemetry_idstool_id, sensor_name with NULLs last, observed_at, and reading_value| Column | Type | Description |
|---|---|---|
| telemetry_idPK | BIGINT | Unique telemetry record identifier |
| tool_id | VARCHAR(50) | Manufacturing tool identifier |
| sensor_name | VARCHAR(80) | Sensor that produced the observation |
| observed_at | TIMESTAMP | Time at which the telemetry was observed |
| reading_value | NUMERIC(12,4) | Numeric sensor reading |
| unit | VARCHAR(20) | Measurement unit |
| quality_flag | VARCHAR(20) | Telemetry quality classification |