Latest Customer State

How can we reconstruct the latest customer state from an event log stream?

Advanced⏱ 3 Minutes⭐ 1960 XP

🤔 THE QUESTION

How can we reconstruct the latest customer state from an event log stream?

💡 WHAT IS IT?

Partitioning customer events by customer_id extracts the latest snapshot state for each customer.

🎯 WHAT IS IT USED FOR?

Event sourcing snapshotting, Change Data Capture (CDC) state reconstruction, customer 360 views.

💻 EXAMPLE

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

🎯 Mission Objectives

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

✓ Event sourcing snapshot
✓ Customer 360 state
✓ CDC reconstruction
✓ Window ranking

🚀 Start Mission

← Prev (Aggregation Layer)Next Lesson (Production Transformation) →