Your question is SQL Duplicate Records Query. Start with the requirements and the one table on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Grail's assay-ingestion pipeline records each received sample in assay_ingestion_records. A repeated sample_identifier can indicate a retried upload or an upstream data-quality issue.
Write a PostgreSQL query to find non-null sample identifiers that occur more than once.
sample_identifier and the number of records using the alias duplicate_count.sample_identifier is NULL, and sort results by duplicate_count descending, then sample_identifier ascending.| Column | Type | Description |
|---|---|---|
| ingestion_idPK | INTEGER | Unique identifier for an ingestion record. |
| sample_identifier | VARCHAR(50) | Identifier assigned to an assay sample. |
| received_at | TIMESTAMP | Timestamp when the record was received. |
| source_system | VARCHAR(30) | System that submitted the ingestion record. |