# Temporal Interval SQL Pattern

A reusable analytical pattern for turning dated status snapshots into continuous effective-from and effective-to intervals.

Audience: Analytics practitioners working with event history
Publication: Synthetic example

Source: https://dangnguyen.cv/resources/temporal-interval-sql-pattern

## Design principle

Reliable temporal analysis starts by separating raw events, state changes, and the intervals those changes create.

## Core logic

- Order events by entity and effective timestamp.
- Normalize nullable status fields before comparison.
- Use LAG to identify genuine state changes.
- Create a cumulative group identifier for each stable state.
- Use LEAD on grouped boundaries to derive the effective-to date.

## Where it helps

The pattern is useful for eligibility history, treatment assignment, pricing state, customer status, and other event-sourced datasets. Public examples should always use synthetic fields and records.