How would you find duplicate sample identifiers in a table with SQL?

Why

Finding duplicates is a two-step job: count them, then look at them. The count is a grouped query: SELECT sample_id, COUNT(*) FROM samples GROUP BY sample_id HAVING COUNT(*) > 1. That shows which identifiers repeat but hides how the rows differ.

To see the rows, rank them inside each identifier with ROW_NUMBER() OVER (PARTITION BY sample_id ORDER BY created_at), which numbers each copy, so rows numbered 2 or higher are the extra copies. Whether those copies are true duplicates or different samples sharing a label decides what happens next. Identical rows from a repeated import can be removed.

Rows with different collection dates or volumes need a person to decide, because deleting one may delete a real sample. Normalize before you count, because S-0012, s-0012 and S-0012 with a trailing space look different to = but are the same label to a person. Comparing upper(trim(sample_id)) catches those.

Finally, a unique constraint or unique index stops new duplicates at the door. A count of zero today proves nothing about tomorrow's import, while a constraint does.