Data Cleaning Patterns

Duplicate Rows

Rank Cleaned Keys

Window functions can rank rows that share the same cleaned key so a workflow can inspect or keep one copy.

Program

Play the script to choose which duplicate rank is returned after normalizing names.

rank_duplicate_rows.sql 
CREATE TABLE signups (id INTEGER, name TEXT, score INTEGER);
INSERT INTO signups VALUES (1, ' Ada ', 90), (2, 'ada', 95), (3, 'Lin', 88), (4, ' lin ', 82);
WITH params(keep_rank) AS (VALUES ()), cleaned AS (SELECT id, lower(trim(name)) AS clean_name, score FROM signups), ranked AS (SELECT id, clean_name, score, ROW_NUMBER() OVER (PARTITION BY clean_name ORDER BY score DESC, id) AS rn FROM cleaned) SELECT clean_name, id, score, rn FROM ranked WHERE rn = (SELECT keep_rank FROM params) ORDER BY clean_name;

CREATE TABLE signups (id INTEGER, name TEXT, score INTEGER);
INSERT INTO signups VALUES (1, ' Ada ', 90), (2, 'ada', 95), (3, 'Lin', 88), (4, ' lin ', 82);
WITH params(keep_rank) AS (VALUES ()), cleaned AS (SELECT id, lower(trim(name)) AS clean_name, score FROM signups), ranked AS (SELECT id, clean_name, score, ROW_NUMBER() OVER (PARTITION BY clean_name ORDER BY score DESC, id) AS rn FROM cleaned) SELECT clean_name, id, score, rn FROM ranked WHERE rn = (SELECT keep_rank FROM params) ORDER BY clean_name;
cleaned key `lower(trim(name))` makes `Ada` and ` ada ` compare as the same key.
ROW_NUMBER `ROW_NUMBER` ranks rows inside each cleaned-name group.
dedupe review The selector lets the replay show either the preferred row or the next duplicate.