task History, SCD & as-of · hard

Build a region history from snapshots

shopper_snapshots records each shopper's region at month end. Return shopper_id, region, valid_from and valid_to — one row per unbroken run of the same region.

what to return

Columns

shopper_idregionvalid_fromvalid_to in this order

Rows

11 rows · any order

Watch for

valid_from is the run's first snapshot_date; valid_to is the first snapshot_date of the shopper's next run, or NULL for the latest run.

A missing region (NULL) is its own value: consecutive NULL snapshots form one run.

If a shopper returns to an earlier region later, that's a new run.

Skill

Compress snapshots into change history. Refresher: LEAD & the next event →

expected result

Your query should return exactly these columns and values. Run it and compare.

data you can query

Sample rows from each table. Each column shows its type underneath; → marks a link to another table.