Duplicate Removal Pattern

How can we deduplicate records by keeping only the most recently created entry?

Advanced⏱ 3 Minutes⭐ 1840 XP

🤔 THE QUESTION

How can we deduplicate records by keeping only the most recently created entry?

💡 WHAT IS IT?

Ranking partitions by created_at DESC with ROW_NUMBER() and filtering for rn = 1 removes older duplicates.

🎯 WHAT IS IT USED FOR?

Golden customer records, warehouse deduplication staging, event stream cleanup.

💻 EXAMPLE

SELECT *
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY created_at DESC
        ) AS rn
    FROM customers
) deduplicated
WHERE rn = 1;

🎯 Mission Objectives

Practice typing production-grade SQL code for Duplicate Removal Pattern.

✓ Deduplication pattern
✓ Latest record retention
✓ rn = 1 filter
✓ Golden record creation

🚀 Start Mission

← Prev (Duplicate Detection)Next Lesson (Latest Record) →