← All practice problems

Build SCD Type 2 history from a change log

senior · sql🔒Walkthroughs

NEW

Build SCD Type 2 history from a change log

dupes is a raw change log: customer_id, email, phone, loaded_ts, multiple rows per customer over time. Build a Type 2 history of each customer's email.

Rules: - Ignore rows where email is NULL. - Collapse consecutive identical emails — a load that doesn't change the email does not open a new version. - One row per email version: customer_id, email, valid_from (the loaded_ts it took effect, as YYYY-MM-DD), valid_to (the valid_from of the next version, or NULL if current), is_current (boolean — true for the latest version).

Sort by customer_id, then valid_from.

Tables & sample data

dupes

Loading preview…

query.sql

Loading…

Loading editor…