lesson 79 Warehouse & reporting projects · advanced

Product validity with LEAD & half-open intervals

Define product validity using the next change date.

the idea

LEAD finds the next version start. Use [valid_from,valid_to) so a change date belongs to exactly one version; NULL end means current. This lab has unique starts per product. Latest-only output discards history; an as-of join needs all versions.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT
  version_id,
  product_id,
  valid_from,
  LEAD(valid_from) OVER(PARTITION BY product_id ORDER BY valid_from,version_id) AS valid_to
FROM product_versions;

Watch for: Do not subtract a day from timestamp boundaries; half-open intervals avoid gaps and double matches.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   Understand the source contract. Fictional extracts, load counts, invalid dates, versioned dimensions, refresh staging and a data catalog. The foundation project is latest-state; separate history and incremental labs demonstrate extensions.

   step-1.sql Run

   sql›

   SELECT *
   FROM product_versions;

2. 2

   Execute the transformation. LEAD finds the next version start. Use [valid_from,valid_to) so a change date belongs to exactly one version; NULL end means current. This lab has unique starts per product. Latest-only output discards history; an as-of join needs all versions.

   step-2.sql Run

   sql›

   SELECT
     version_id,
     product_id,
     valid_from,
     LEAD(valid_from) OVER(PARTITION BY product_id ORDER BY valid_from,version_id) AS valid_to
   FROM product_versions;

3. 3

   Test another question. Do not subtract a day from timestamp boundaries; half-open intervals avoid gaps and double matches.

   step-3.sql Run

   sql›

   WITH versions AS(
     SELECT
       *,
       LEAD(valid_from) OVER(PARTITION BY product_id ORDER BY valid_from,version_id) AS valid_to
     FROM product_versions
   )
   SELECT product_id,cost
   FROM versions
   WHERE valid_from<='2025-02-01'
     AND (valid_to>'2025-02-01' OR valid_to IS NULL)