Guide · updated 2026-11-04
SQL window functions for cleaning
Ranking survivors, finding duplicates and comparing rows without collapsing them.
ROW_NUMBER for survivors
ROW_NUMBER() OVER (PARTITION BY key ORDER BY … , unique_id) numbers rows per key; keep rn = 1. DuckDB's QUALIFY filters on it directly.
COUNT over a partition
COUNT(*) OVER (PARTITION BY key) shows how many rows share a key without grouping, which is useful for duplicate reports.
Order by typed values
Cast versions and timestamps before ordering. Text ordering puts 9 after 10.