Deduplication Strategy

When duplicates exist, you need a consistent rule to pick the best record.

🧠 Key Concept: Completeness Score

Count how many important fields have data:

-- Completeness score = count of non-null fields
CASE WHEN phone IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN company IS NOT NULL THEN 1 ELSE 0 END
-- Score: 0 (worst) to 2 (best)

🏗️ Pattern: ROW_NUMBER + CTE

WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER(
            PARTITION BY LOWER(TRIM(email))  -- group duplicates
            ORDER BY completeness DESC, id   -- best first
        ) as rn
    FROM leads
)
SELECT * FROM ranked WHERE rn = 1;  -- keep #1 only

Step	What It Does
PARTITION BY	Groups rows by cleaned email
ORDER BY ... DESC	Puts "best" record first
WHERE rn = 1	Keeps only the winner

Your Task

For each unique email, keep the record with the most data:

• Use ROW_NUMBER() partitioned by cleaned email
• Order by completeness (count non-null fields), then id
• Show: id, first_name, last_name, email, phone
• Only include row_number = 1

Use a CTE to calculate and rank.