Dedupe: Keep the Most Complete Row — Solution & ExplanationProblems / Dedupe: Keep the Most Complete Row / EditorialApproach

Score completeness: CASE WHEN name IS NOT NULL THEN 1 ELSE 0 END + ... across name, phone, company gives each row a 0–3 score. (DuckDB also allows the shorthand (name IS NOT NULL)::INT + ....)

Rank within each email: ROW_NUMBER() OVER (PARTITION BY email ORDER BY completeness DESC, contact_id ASC). The contact_id ASC tiebreaker makes the ranking deterministic — Carol and Frank each have two rows tied on score, and the lower id must win.

Keep the winners: filter rn = 1, project the original columns (NULLs and all), order by email.

Key InsightNot all duplicates are equal. A naive DISTINCT cannot dedupe these rows (they differ in their NULL patterns), and keeping an arbitrary row may discard the one copy that has the phone number. Scoring completeness and ranking with ROW_NUMBER implements a survivorship rule — the record with the most information survives. Two details separate correct from lucky solutions: a deterministic tiebreaker (ties are real in merged CRM data), and *keeping* the winning row as-is rather than merging fields across rows, which is a different (and riskier) cleanup strategy.Hints

Score each row first: (name IS NOT NULL) + (phone IS NOT NULL) + (company IS NOT NULL), written as a sum of CASE WHEN ... THEN 1 ELSE 0 END terms.

ROW_NUMBER() OVER (PARTITION BY email ORDER BY completeness DESC, contact_id ASC) ranks each email's rows; keep rn = 1. The contact_id tiebreaker is not optional — two emails in this dataset have rows tied on completeness.

In PySpark, add the score with withColumn, then Window.partitionBy('email').orderBy(F.col('completeness').desc(), F.col('contact_id').asc()) and F.row_number().over(w) == 1.

Solve Dedupe: Keep the Most Complete Row yourself →