Build SCD2 ranges from daily snapshots

SQL data engineering interview problem. Difficulty: advanced. Pattern: SCD Type 2. About 20 minutes. Part of the Pro drill bank.

customer_snapshots contains daily records of each customer's location with columns customer_id, snapshot_date, and city. Collapse consecutive daily snapshots with the same city into SCD Type 2 date ranges showing valid_from (start date of the streak) and valid_to (end date of the streak). If a customer moves back to a previous city after living elsewhere, that move must start a fresh new range. Order by customer_id and valid_from.

Requirements

• Output columns: customer_id, city, valid_from, valid_to.
• Order results by customer_id ascending, valid_from ascending.

Constraints

• One snapshot row per customer per calendar day.
• snapshot_date has no temporal gaps within a customer's active history.

Examples

Input: customer_snapshots customer_id,snapshot_date,city C1,2024-01-01,Bengaluru C1,2024-01-02,Bengaluru C1,2024-01-03,Bengaluru C1,2024-01-04,Mumbai C1,2024-01-05,Mumbai C1,2024-01-06,Bengaluru C1,2024-01-07,Bengaluru ... Output: customer_id | city | valid_from | valid_to C1 | Bengaluru | 2024-01-01 | 2024-01-03 C1 | Mumbai | 2024-01-04 | 2024-01-05 C1 | Bengaluru | 2024-01-06 | 2024-01-07 C2 | Delhi | 2024-01-01 | 2024-01-02 Why this passes: C1 stays in Bengaluru for Jan 1-3, moves to Mumbai Jan 4-5, then returns to Bengaluru Jan 6-7, so the return trip is a new range rather than a merge with the first one.

Input: customer_snapshots customer_id | snapshot_date | city C9 | 2024-05-01 | Chennai C9 | 2024-05-02 | Chennai Output: customer_id | city | valid_from | valid_to C9 | Chennai | 2024-05-01 | 2024-05-02 Why this passes: A single uninterrupted stay collapses into one range.

Topics: lakebench, sql, gaps-and-islands, change-detection.

More SQL interview questions · All interview problems · Learn data engineering