Drill 10 of 12 in Data engineering questions
0

Interview Question Bank · Gaps and islands · LAG change flag · LEAD for valid_to

10. Build SCD2 ranges from daily snapshots

HardData engineeringQuestion #250

Concept: Gaps and islands · LAG change flag · LEAD for valid_toshow

To turn daily snapshots into version ranges, mark each row whose value differs from the previous day's (value IS DISTINCT FROM LAG(value)), and a running SUM of those marks numbers the versions. Each version starts at its first snapshot date, and its valid_to is the next version's valid_from, which LEAD gives you. Grouping by the value alone would merge a value that comes back later with its earlier run.

Example (different data):

WITH marked AS (
  SELECT store, day, manager,
    CASE WHEN manager IS DISTINCT FROM LAG(manager) OVER (PARTITION BY store ORDER BY day)
      THEN 1 ELSE 0 END AS changed
  FROM store_daily
), numbered AS (
  SELECT *, SUM(changed) OVER (PARTITION BY store ORDER BY day) AS stint FROM marked
)
SELECT store, manager, MIN(day) AS started
FROM numbered
GROUP BY store, stint, manager;

Your task

Fernhill's billing system only offers a full daily dump of accounts, stored in de_snap_accounts. The warehouse team wants a compact type 2 history instead of a copy per day.

Collapse the snapshots into one row per unbroken run of the same plan: account_id, plan, valid_from (first snapshot_date of the run) and valid_to (the valid_from of the account's next run, NULL for the current one). A plan that comes back later starts a new row. Sort by account_id, then valid_from.

Starting Postgres in your browser…

Checking your access…