Customer State Machine Snapshot

What you will practice

At 2025-06-30 23:59:59 derive each customer's lifecycle state from three streams. Verification is true after the latest verify/unverify event. Subscription is active after the latest activated/reactivated event unless the later state is cancelled. Payment health uses the latest succeeded/failed payment. Events after the snapshot are excluded; same-time higher event_id wins. State priority: UNVERIFIED, then NO_ACTIVE_SUBSCRIPTION, then PAYMENT_AT_RISK, else ACTIVE. Return columns: customer_id, customer_state.

Difficulty

Expert

SQL concept

CTE