Latest Customer Record

How can we retrieve the latest version of every customer record?

Advanced⏱ 3 Minutes⭐ 2000 XP

🤔 THE QUESTION

How can we retrieve the latest version of every customer record?

💡 WHAT IS IT?

Partitioned window ranking on updated_at extracts current SCD Type 1 customer snapshots.

🎯 WHAT IS IT USED FOR?

Dimension snapshot resolution, customer master data, deduplicating update histories.

💻 EXAMPLE

SELECT *
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY customer_id
            ORDER BY updated_at DESC
        ) AS rn
    FROM customers
) latest
WHERE rn = 1;

🎯 Mission Objectives

Practice typing production-grade SQL code for Latest Customer Record.

✓ Latest snapshot resolution
✓ SCD Type 1 staging
✓ ROW_NUMBER partition
✓ Master data management

🚀 Start Mission

← Prev (Repeat Customers)Next Lesson (Top Products) →